Creating a full and cohesive user experience in an Excel based dashboard can be really difficult if you don’t know where to start. Often, the easiest way to approach the situation will be to utilise Pivot Tables and Pivot Charts, which come with useful tools like slicers to dynamically filter the data with a touch of a button. I have a few pet peeves with Pivots however, mainly being that they require data be in a particular layout; that they can be restrictive on the type and layout of the output they produce (although they do provide a good level of options); and when updating or changing data within a spreadsheet Pivoted data does not refresh unless it’s explicitly told to (which can create a jarring experience when handling and presenting larger data sets).
So, how would you go about creating a dynamic data table and dashboard without using Pivot Tables? There are a seemingly massive number of ways to go about this task, but the way I find most versatile and that I’ve become comfortable using is to make use of Combo Boxes and different Excel functions like INDIRECT (covered in this tutorial), OFFSET, SUMIFS and INDEX.
In this tutorial I will show how to utilise combo boxes and the INDIRECT function to create a dynamic data table which can be used to better visualise your data and be used as a solution for a client or manager-facing reporting tool. This tutorial will be using dummy data to show a basic Key Performance Indicator dashboard with data from April 15 to March 17. The INDIRECT function will be used specifically to dynamically reference all of our backing sheets.
Step 1.
First we’ll need to create structured backing sheets which follow a consistent lay-out. This is essential to allow the INDIRECT function to consistently output the correct arrays for our data.
The above example shows my preferred structure:
The top-left of the sheet includes a quick description of where we got the data from and how up-to-date our data is. Including this is general best practice for maintaining data back-sheets to allow other people to understand the data’s history and for us to better quality assure our data. All three of the back-sheets in this workbook will follow the same format, including the same organisation list and dates.
Step 2.
Now we’ll create the output sheet that will hold the dynamic data table. In this example I’ve copied the same structure used in the back-sheets, with the same organisations and all the dates. It would be possible to pick and choose dates and organisations for this output sheet, as the formula used will be able to deal with any combination of the 2 variables.
Step 3.
We can now create the combo box and the lists of measures and sheet names that feed the combo box. To create a combo box, go to the developer tab of the ribbon
(If the developer tab of the ribbon isn’t visible for you, go to:
File -> Options -> Customise Ribbon
then on the list on the right hand side of this tab tick the selection box for the developer tab).
From the developer tab, select Insert and under ‘Form Controls’ select the combo box option. You should then be able to draw a combo box on the spreadsheet.
Now we will create 2 lists: one of measure names and one corresponding to each of our back-sheets. In this example we have 3 measures:
- ‘Average Time Taken’;
- ‘Total Throughput’; and
- ‘Number of Staff’
Corresponding to our back-sheets:
- ‘AverageTimeData’;
- ‘TotalThroughputData’; and
- ‘StaffData’
I’ve created the lists next to one another below the data table as below:
Step 4.
Now we need to link the combo box we created earlier to the measure list. Right click the combo box and select ‘Format Control’. For the ‘Input Range’ select the measure list; in this example it’s A16:A18. For the ‘Cell Link’ select an empty cell nearby; in this example the cell chosen was G16. Hit OK, the combo box should now allow you to select the measures in the measure list, with the linked cell showing the number corresponding to the measure’s position in the list. In the cell beside the linked cell we will now use an INDEX function to work out the sheet name from the list created beside the measure names.
INDEX looks at an array and works out the position of a cell based on a given row and column number. As the linked cell gives the position of the measure in the measure list (thus acting as the row number), it will also return the corresponding back-sheet if we use it in an INDEX function.
Step 5.
Now we can construct the INDIRECT function; in this example it will be done within a named range, but it’s also possible to do this within a cell’s formula. Using named ranges just cleans up the final formula and makes it easier to parse for other users. To create a new named range go to the ‘Formulas’ tab of the ribbon and select the ‘Define Name’ option. Give the named range an appropriate name like ‘SelectedData’, and insert the INDIRECT function. In this example the formula will be:
=INDIRECT("'"&'Output Sheet'!$H$16&"'!$C$7:$Z$16")
The purpose of the INDIRECT function is to allow excel to interpret text as an array or cell. Breaking down this formula gives us the following:
- =INDIRECT(“TEXT TO BE INTERPRETED”)
- =INDIRECT(“ ‘ “& CELL WITH SHEET NAME &” ‘! DATA TABLE’S ARRAY”) this is then interpreted by excel as:
- =’SHEETNAME’!DATA TABLE’S ARRAY
Note: The data table’s array does not include the column headings or the row headings i.e. the dates or the organisation names – it starts at the actual data.
Step 6.
We can now construct the formula used in the dynamic data table. We’ll be using an INDEX function, in conjunction with our ‘SelectedData’ named range and the MATCH function.
The formula in the top left of our dynamic data table is:
=INDEX(SelectedData,MATCH($B4,$B$4:$B$13,0),MATCH(C$3,$C$3:$Z$3,0))
Breaking this down to each part gives us:
=INDEX(SelectedData, this is the link to our named range, so our named range is the array we are ‘indexing’.MATCH($B4,$B$4:$B$13,0), this is where we work out the row number to look for. B4 is our first organisation name ‘Supplier A’ (its column is ‘frozen’ by using the ‘$’ before B), and the array B4:B13 is the full list or organisation names. The 0 shows we’re looking for an exact match. MATCH then returns the row number of ‘Supplier A’ in the list of organisation names; 1 in this case. The array (the list of organisation names) in this function is able to be the list on the output sheet as it’s the same as the list in the data back-sheets. If you were to create a custom list of organisations to show, you would need to check B4 against the full list of organisations used within the data back-sheets.MATCH(C$3,$C$3:$Z$3,0)this is where we work out the column number to look for. C3 is our first date ‘Apr-15’ (its row is ‘frozen’ by using the ‘$’ before C), and the array C3:Z3 is the full list of dates. The 0 again shows we’re looking for an exact match. MATCH then returns the column number of ‘Apr-15’ in the list of dates; 1 in this case. As mentioned in the previous point, the array (the list of dates) in this function is able to be the list on the output sheet as it’s the same as the list in the data back-sheets.
This formula can then be copy and pasted across the entire data table. This should then result in a table that can dynamically update like so:
Step 7.
Now that we have a dynamically updating data table we can add in dynamically updating conditional formatting! Alongside the measure and sheet lists we can include a boundary at which our green turns to red and vice versa. I’ve set these to 2, 200, and no boundary for ‘Average Time Taken’, ‘Total Throughput’ and ‘Number of Staff’ respectively. Next to these lists I’ve included a cell that contains the boundary for the selected measure. This is done in the same way as the sheet name cell, using the INDEX function. For this example the formula is:
=INDEX($C$16:$C$18,$J$1)
This looks at the array of boundaries (C16:C18) and returns the one corresponding to the chosen measure.
Now you can highlight the data table, go to the ‘Home’ tab of the ribbon and select the ‘Conditional Formatting’ then ‘Highlight Cells Rules’ and ‘Greater Than…’.
Select the cell that contains the boundary for the selected measure; D16 in this case. Choose the ‘Light Red Fill with Dark Red Text’ option and hit OK. Do the same for ‘Less Than…’, selecting the same cell and this time choosing ‘Light Green Fill with Dark Green Text’. Sorted!
Step 8.
Now you may have realised upon changing the measures that these don’t make sense for our ‘Total Throughput’ measure and the ‘Number of Staff’ measure is incorrectly showing the conditional formatting. To combat this we’ll need to change the rules to an IF statement rather than a simple greater than or less than rule. To change the rules, go to the ‘Manage Rules’ section of the ‘Conditional Formatting’ menu. For the Red formatting, select ‘Edit Rule’ and change the rule to ‘Use a formula to determine which cells to format’. The formula used in this example is:
=IF($H$16="TotalThroughputData",C4<$D$16,C4>$D$16)
This checks the selected data back-sheet, and if it’s the back-sheet for the Total Throughput data: highlight the cells that are less than D16 (our cell containing the selected measure’s boundary) red; and if it’s not highlight the cells that are greater than D16 red. This means that the rule now correctly shows those organisations with low throughput as red, instead of green.
Now edit the green formatting in the same way, using the formula:
=IF($H$16="TotalThroughputData",C4>=$D$16,C4<=$D$16)
This checks the selected data back-sheet, and if it’s the back-sheet for the Total Throughput data: highlight the cells that are greater than or equal to D16 (our cell containing the selected measure’s boundary) green; and if it’s not highlight the cells that are less than or equal to D16 red. This means that the rule now correctly shows those organisations with high throughput as red, instead of green. By using the ‘greater/less than or equal to’ inequality we have now also included the boundary’s number within the formatting which is another error that needed to be corrected.
The final error that needs to be corrected is that ‘Number of Staff’ will still show the conditional formatting despite not having a boundary. To correct this add a new rule with ‘Use a formula to determine which cells to format’, and add the formula that will exclude those measures with no boundary – in this example we’ve used ‘–‘ to denote no boundary in the list of boundaries. The formula used is then as follows:
=$D$16="-"
Leave the format as ‘No Format Set’ and hit OK. Then tick the ‘Stop If True’ checkbox in the ‘Manage Rules’ options window for this rule.
The sheet is now complete from a functionality standpoint – I’ve made some general formatting changes to try and make the table clearer and easier to interpret. I’ve increased the row size of the table rows to 20, removed the gridlines on the sheet by going to ‘View’ on the ribbon and unchecking the ‘Gridlines’ box. I also added a title: ‘Select Measures’, above the combo box to help aid user experience. I’ve also used the ‘Freeze Panes’ setting in the ‘View’ tab at cell C2 to lock the organisation names and combo box in the view of the spreadsheet. I’ve hid rows 15-18 and added a filter to the column headings, by highlighting them all and then going to the ‘Home’ tab and selecting ‘Filter’ under the ‘Sort & Filter’ option on the right of the tab.
One thing to be aware of with this method is a weakness of the INDIRECT function. INDIRECT is known as a volatile function, this means whenever any change is made to the workbook it will cause any formulae with an INDIRECT function in it to recalculate. Other functions that share this property are the functions like RAND and RANDBETWEEN. This means incredibly large data tables which use INDIRECT can cause massive slow down when making changes to the workbook. A way to avoid the INDIRECT function would be to format the data back-sheets in an alternative way – with one master data back-sheet instead of multiple. I’ll cover an example of this in future tutorials!
So after following this method you should be able to create a dynamic data table perfect for Key Performance Indicator dashboards! It offers more flexibility (in my opinion) than Pivot Tables, and using combo boxes allows us to link multiple parts of a spreadsheet to a single dynamic cell to create a more cohesive user experience.
Thanks for reading, if you have any questions or feedback please do comment below!
You can access the workbook here.
Static Interest 