Our primary goal is to show the main KPIs and provide a comparative view of other KPIs or products using interactive visual elements.
In this guide, I will show you how to create kpi in Excel 2013 or above. The dashboard is fully compatible with Office365 for Excel. The Workbook is an updated and improved version of a dashboard that I created in 2016. The spreadsheet is compatible with Excel 2010 too.
Let us see how the final result looks like:
Our spreadsheet is divided into four parts by products. It will be easy to compare the main indicators. To measuring performance and creating a sales report is a simple procedure, we’ll apply the good old offset function and conditional formatting. This spreadsheet allows the user to select the dimension of data and shows sales efficiency right away.
The OFFSET function helps to determine the ranking and grouping as soon as possible, and with the help of formatting, we can analyze the high and low differences.
We should rank the highest and weakest performing products to determine which products are failing to boom with your customers.
The three units of our template:
• KPI is the main Excel worksheet; we summarize and visualize the results here.
• Now go to the ‘data’ sheet, here are the main data.
• On the calculation sheet can be found in the calculation area.
Build a KPI Report
1. We have the raw data for 100 sales reps and for 4 products. The data looks as shown below.
2. First, create named ranges for further calculations. Select the cell or range, in this example, we’ll use both of them. Select the =data!$E$10:$E$109 the range on the ‘data’ sheet.
Go to the Formulas tab! In the Defined Names group, click Define Name. Enter a Name (menu) for the range. Select cell E6 and add a name (sort_order)
See the results in the picture below!
3. Use the OFFSET function to build a dynamic named range for further calculations. On the calculations sheet type:=OFFSET(data!$E10;0;sort_order).
Explanation: How to get the correct name from the unsorted list? OFFSET(name of the sales rep from data table; 0 = same row; 1st position)
4. Use the =E10+$C10/1000000000 formula to create a unique list!
5. To get the proper value in a range, you can use the CHOOSE function together with the LARGE and SMALL functions.
The CHOOSE formula returns a value from a list using a given position or index. For example, CHOOSE(3,”KPI1″,”KPI2″,”KPI3″) returns “KPI3”, since KPI3 is the 3rd value listed after the index number.
The LARGE function retrieves numeric values based on their position in a list when sorted by value. The SMALL function retrieves numeric values based on their position in a list when sorted by value.
6. To get the position of an item in an array, apply the MATCH function.
7. Now use the OFFSET(data!F$9;$H10;0) to calculate the 1st position for Product 1.
8. Jump to KPI sheet! You can insert option buttons to make data entry easier. Option buttons are perfect when you have just one choice.
To add the Options button, click the Developer tab, click Insert. Finally, under Form Controls, click the Options button icon.
9. To add a linked cell, right-click the button, and then click Format Control. Jump to the Control tab. In the Cell link box, enter a cell reference that contains the current state of the button. Use the same linked cell (E6 on the calculation sheet) for Product 1 to Product 4.
10. To assign a macro to the inserted picture (icon), execute the following steps: Right-click on the picture. Choose the Assign Macro command. Add a macro from the list.
11. The Macro is very simple. I think it’s not necessary to further explanation.
12. You can combine the conditional formatting tool with your KPI settings.
13. To manage all conditional formatting rules in this worksheet, use the Conditional Formatting Rules Manager.
14. On the Home tab, click Conditional Formatting, and then click Manage Rules. Select the ‘Format all cells based on their values’ rule type option. This is the easiest way to create kpi based conditional formatting.
15. To insert a slider in Excel, go to Developer Tab –> Insert –> Scroll Bar).
Click on the Scroll Bar button and insert it in the worksheet.
16. Right-click on the Scroll Bar and click on ‘Format Control’. This will open a Format Control dialogue box.
Go to the ‘Control’ tab, and make the following changes to create scrolling list:
- Current Value: 1
- Minimum Value: 1
- Maximum Value: 76
- Incremental Change: 1
- Page Change: 10
- Cell Link: $E$5 on the calculation sheet.
Finally, it’s done!!!