Pipeline Management with Excel – CRM

My favorite feature of Microsoft Dynamics CRM is the native Excel integration.  As we all know, business is dynamic.  Every day we have new challenges which requires us to look at our business in new ways.  Because business agility is so critical we often need the flexibility to look at information in vastly different ways.  Most traditional CRM applications allow you to write reports to review information.  These reports are good if you always want to view information in the same manner but they are essentially useless when you want to slice and dice your data based off of that day’s business issue.  Thus the beauty of Excel integration with Microsoft Dynamics CRM.  Definitely my reporting tool of choice.
Microsoft Dynamics CRM allows you to extract any view to Excel.  My personal favorite is creating a dynamic link between CRM and Excel for viewing the Sales Pipeline.



My goal is to create a Dynamic PivotTable but I am going to start by creating a Dynamic Worksheet.  Yes, there is a method to my madness.



I love to create filters and view data by different measurements.  If I create a Dynamic worksheet link between Excel and CRM I can still create a PivotTable and I get to take advantage of Excel filters all at the same time.

CAUTION: I have learned this the hard way over time.  Stop and think about all the different attributes you might want to use when slicing and dicing your opportunities.  Take a moment and add those columns right now to your Dynamic worksheet.  Otherwise, you’ll need to go back at a later time and recreate the Dynamic worksheet to include the fields you missed the first time.   


Remember that when Excel opens you will need to Enable the data connection.



Now the fun begins!  First things first – SAVE THE DOCUMENT.  If you forget to save the document you won’t be able to create your PivotTable due to the temporary file name given to the document by the extract.  Don’t forget to change the file type to .xlsx.
I like to create filters right away on the Dynamic worksheet.  This allows me to look at the data in the worksheet by applying filters to any attribute/column.  This comes in very handy when I want to look at deals by owner, district, service, etc.



An example I use all the time is pulling up our Pipeline report, looking for deals in a district and then discussing those with our PAM.



As I mentioned earlier, I do like using a PivotTable but I prefer to create a Dynamic worksheet first and then creating the PivotTable.  The primary purposes is to allow me to group fields on the PivotTable.  From the worksheet click on Insert – PivotTable to create your Dynamic PivotTable connected to CRM.



Select the range of cells you want included in your PivotTable.  Just include the entire worksheet.  This way, anytime you update the worksheet you will automatically update your PivotTable data too.



Create your PivotTable – Add items to the Rows and Columns to build out your table.  For a Pipeline view I always add Est. Close Date to the Column header.



I like to look at things by Months, Quarters and Years so I like to group my Est. Close Date by those values.  This is pretty simple to do and something I find to be quite effective.  Right mouse click on the first date entry and then click on Group.



Select your options for how you want to group the data.  In this example I’m using Months, Quarters, and Years.



My view now shows the sales pipeline by how I like to manage the business.  However, I’m not quite done yet.  I still want my values to be formatted as currency.  This too is an easy fix.  Simply click on the Sum of Est. Revenue from the field chooser and change the Value Field Settings.



Click on the Number Format and then choose Currency and modify the number of decimals to be displayed.



Once again, SAVE your document and you are ready to go.  You can repeat these steps and also create a PivotChart from the Dynamic worksheet if you like visuals more than tables.  Your options are endless.  The best part is – it takes less than 5 minutes to create this report in Excel and once you’re done you can re-use it over and over and constantly change how you view your data (by filters, drilling down on the PivotTable, creating PivotCharts, etc.).  There’s no going back to the developers to create another static report – everything is at your fingertips.