Our primary goal is to show the main metrics and provide a comparative view of other products. Let us see how the final result looks like:
To measuring performance and creating a 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.
Build a 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,”value1″,”value2″,”value3″) returns “value3”, since value3 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. Insert an 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 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.
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.