In this tutorial we’ll take a look at how to create a simple dashboard using a combo box, a chart, an INDEX/MATCH combination formula and a few simple formatting concepts to easily evaluate the sales for any individual salesperson.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
To get the full impact and details of this process, you need to watch the video, but I’ll give you the main points here.
Here is the dashboard we will create. As I select any of the salespeople from the drop down list, it changes the Sales and Quota numbers, identifies the percentage of quota achieved and formats it if it is above or below 100%, and presents a chart showing the monthly sales vs. quota for that salesperson;
The data is coming from Sheet1 that has all the sales details along with a quota for each salesperson. I’ve also included the Budget Trend for the industry to spread out the Quota values:
The key to this process is to create an area that defines the information for the chart, in our case A12:M14 of Sheet1, and populate that area dynamically based on the selection from the Combo Box drop down list on the Dashboard on Sheet2.
Cell A13 where the Salesperson’s name is indicated is tied to the Linked Cell for the Combo Box. From there we use a formula to pull the information from the data range above for that individual. The formula to do that is:
The MATCH portion of the formula is used to identify which row to draw the numbers from. The COLUMNS function will just count from 1 to 12 to pull the data for the correct month.
Below that is a VLOOKUP formula that selects the quota for the appropriate salesperson and multiplies it times the trend percentage:
The rest is just basic SUM functions, dividing Sales by Quota, and using Conditional Formatting to change the percentage from Red to Green depending upon the fact that it did or did not exceed Quota.
Take a few minutes and watch the video to see the details of how this dashboard was constructed.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!