Office 365 and SharePoint – Sales Contract Lifecycle Management (CLM) – Microsoft Excel Slicers and Conditional Formatting Reports

We already saw how Excel, Office 365, and SharePoint empowers your team to deliver reports online in this post. The report example in that post used a pivot table to report on the status of Sales Contract Lifecycle Tasks as they relate to a Sales Contract stage. In this post, we’ll see how the Sales team directly targets the Sales Contracts document library to produce a visual and interactive report using Microsoft Excel Slicers and Conditional Formatting for the Contract Lifecycle Management (CLM) system.

Export the Sales Contract Library to Microsoft Excel

You can see a video of how to export content from SharePoint to Excel in the Office 365 and SharePoint – Sales Contract Lifeycle Management (CLM) – Reporting with Microsoft Excel post.

  • Navigate to the Sales Contracts document library.
  • Click the “Export to Excel” button
  • After Microsoft Excel opens, trim your columns down to: Customer Name, Sales Contract Title, and Sales Contract Stage

Business Management O365 SharePoint sales contracts in Excel.

Add the Microsoft Excel Slicers

With that done:

  • Select cell A1 (any cell in the table is fine).
  • Click “Insert Slicer” in the Table Tools > Design tab.
  • Choose “Customer Name” and “Sales Contract Stage” from the “Insert Slicers” dialogue.

Business Management O365 SharePoint Excel Slicer Tool

Add a new column to the right of Sales Contract Stage named “Sales Contract Progress”. We’ll utilize this in the next section to render our conditional formatting.

Adjust the slicers on the Microsoft Excel worksheet to your liking, for example:

Business Management O365 SharePoint Excel slicer report layout.

Add Excel Conditional Formatting Against CLM Sales Contract Stage

For reference, here is the formula the Sales team applies to the Sales Contract Progress column:

=IF($C2="Creation",0,IF($C2="Collaboration",25,IF($C2="Execution",50,IF($C2="Administration",75,IF($C2="Closeout",100,0)))))

Click into cell D2, and write out the formula above. After you press the <Enter> key, the formula should cascade down into the remaining cells in column D, giving you:

Business Management O365 SharePoint preparing for conditional formatting.

  • Click the heading for column ‘D’.
  • Navigate to the Home tab in the ribbon.
  • Use Conditional Formatting to enter the appropriate color scale, icon set, data bar…whatever your team wants
  • Save the Microsoft Excel workbook

Business Management O365 SharePoint Adding Excel Conditional Formatting

View the Report in Office 365 SharePoint on the Contract Lifecycle Management (CLM) Site

You should have completed the work in Office 365 and SharePoint – Contract Lifecycle Management (CLM) – Reporting with Microsoft Excel post.

Having done so, you will already have the “Sales Report Workbooks” document library and the “Visual Reports” page created. That blog also explains how to add and configure the Microsoft Excel Web Access web part on your page in SharePoint.

  • Upload your new Microsoft Excel workbook to the Sales Report Workbooks.
  • Add the Workbook to your Visual Reports page.

Business Management O365 SharePoint conditional formatting in SharePoint.

Another quick win for the Sales team who has used the ubiquitous BI tool of choice, Microsoft Excel, to deliver a powerful, intuitive, interactive, and meaningful report in Office 365 and SharePoint using Excel Slicers and Conditional Formatting.

Advertisements


Categories: Business, Business Management, Contract Lifecycle Management, Excel, Office 365 and O365, SharePoint

Tags: , , , ,

2 replies

Trackbacks

  1. Office 365 and SharePoint – Human Resources (HR) – Employee Onboarding with Microsoft Excel and SharePoint Designer (SPD) Workflow – Westmorr Consulting
  2. Power BI Reports for SharePoint CLM Tasks – The Colorblind Theme and Using a Visual Level Filter – Westmorr Consulting

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: