Dashboard Example

Use our dashboard example to create an effective report using a few simple steps. Our primary goal is to show the main metrics and provide a comparative view of other products. The result looks like this:

this is a dashboard example

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.

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.

base data set example

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!

formulas name manager

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)

how to use offset

Step 4: Create a unique list

Use the =E10+$C10/1000000000 formula to create a unique list!

sorting data

Step 5: Retrieve the position using CHOOSE function

Use the CHOOSE function and the 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.

sort data

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)

dashboard example - use the match function

Step 7: Calculate the 1st position

Now use the OFFSET(data!F$9;$H10;0) to calculate the 1st position for Product 1.

dashboard 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.

build a radio button

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.

use a linked cell

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.

assign a macro

Step 11: Insert a macro

The Macro is very simple. So I think it’s not necessary to explain further.

macro details

Step 12: Apply conditional formatting

You can combine the conditional formatting tool with your settings.

dashboard conditional formatting KPI

Step 13: Use conditional formatting rules

Use the Conditional Formatting Rules Manager to manage all conditional formatting rules in this worksheet.

format dashboard icons

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.

advanced formatting

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.

insert a scroll bar

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.

Additional resources: