Use our dashboard example to create an effective report using simple steps. Our primary goal is to show the main metrics and provide a comparative view of other products. The result looks like this:
Measuring performance and creating a report is not always a straightforward procedure. First, we’ll apply the OFFSET function and conditional formatting. This spreadsheet allows the user to select the data dimension and shows sales efficiency immediately.
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 fail to boom with your customers.
For more templates, check our ultimate guide.
Dashboard Example: Step-by-step guide
Step 1: Prepare data
We have the raw data for 100 sales reps and four products. The data looks as shown below.
Step 2: Create named ranges
First, create named ranges for further calculations. Next, select the cell or range. In this example, we’ll use both of them. Finally, choose 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!
Step 3: Use the OFFSET function
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)
Step 4: Create a unique list
Use the =E10+$C10/1000000000 formula to create a unique list!
Step 5: Retrieve the position using CHOOSE function
Use the CHOOSE and LARGE and SMALL functions to get the proper value in a range.
=CHOOSE($E$7;LARGE($F$10:$F$109;$C10);SMALL($F$10:$F$109;$C10))
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. WHEN SORTED BY VALUE, the SMALL function retrieves numeric values based on their place in a list.
Step 6: Get the item’s position in an array
To get the position of an item in an array, apply the MATCH function.
=MATCH(G10;$F$10:$F$109;0)
Step 7: Calculate the 1st position
Now use the OFFSET(data!F$9;$H10;0) to calculate the 1st position for Product 1.
Step 8: Insert an option button
Insert an option button to make data entry easier. Option buttons are perfect when you have just one choice.
To add the Options button, click the Developer tab, and select Insert. Finally, under Form Controls, click the Options button icon.
Step 9: Create a cell link
To add a linked cell, right-click and click on Format Control. Next, jump to the Control tab. Then, enter a cell reference in the Cell link box containing the button’s current state. For example, use the same linked cell (E6 on the calculation sheet) for Product 1 to Product 4.
Step 10: Assign a macro to a picture
To assign a macro to the inserted picture (icon), execute the following steps: Right-click on the image. Next, choose the Assign Macro command. Then, add a macro from the list.
Step 11: Insert a macro
The Macro is very simple. So I think it’s not necessary to explain further.
Step 12: Apply conditional formatting
You can combine the conditional formatting tool with your settings.
Step 13: Use conditional formatting rules
Use the Conditional Formatting Rules Manager to manage all conditional formatting rules in this worksheet.
Step 14: Manage rules
On the Home tab, click Conditional Formatting and click on Manage Rules. Next, select the ‘Format all cells based on their values’ rule type option.
Step 15: Insert a slider
To insert a slider in Excel, go to Developer Tab –> Insert –> Scroll Bar). Then, click the Scroll Bar button and insert it into the worksheet.
Step 16: Link the actual value to the slider
Right-click on the Scroll Bar and click on ‘Format Control.’ The Format Control dialogue box will appear. Go to the Control tab, and make the following changes to create a 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.
Our dashboard example is ready to use.