Quick Steps to Make Effective Data Visualisations

Original.png

This is an example of the default settings for a bar chart in Excel. This is functional but could be far more effective; here are my main steps I use to create quick, effective data visualisations.

  • Remove chart borders.
  • Show data in a useful order (alphabetically, or ranked, or some other important order).
  • Keep gridlines and reduce their colour, or remove them entirely.
  • If you remove gridlines, consider adding data labels.
  • If data labels have been added consider removing y-axis labels.
  • Add axis titles where the values shown aren’t inherently obvious.
  • Reduce the colour used in the chart.
  • Highlight the important data points.
  • Use white space effectively, but don’t leave excessive amounts.
  • Where possible use a chart title that helps the user see the points that you’ve highlighted and their context. Annotate any other points you’ve highlighted, why are they interesting?
  • Use colour to create a relationship between your annotations/title and the highlighted data points.

These steps will help you turn your default Excel charts into impactive and attractive data visualisations:

New.png

Advertisements
Data Visualisation Excel Examples How to

HOW TO… Excel: Create a Dynamic Data Range for a Chart

Continuing the theme of automatically updating charts, we’re going to look at how to make a chart deal with a dynamically changing data range. This is useful for situations where you have multiple measures that each have a different number of independent variables (the variables plotted on our x-axis, which was the month of the year in our last example). If you use the method as lined out in the last tutorial for this you will find that the chart updates automatically, but the number of variables shown on the x-axis will not change. To solve this, we will need to use Named Ranges; OFFSET; and COUNT to produce the ranges that will feed the automatically updating charts.

We’ll be continuing with the workbook used in the last tutorial (downloadable here) for our example.

Step 1.

Step1 Example

First, we’ll need to create our Named Ranges. We will need a named range for our horizontal axis labels and for our data. We’ll name these ‘ChartDateRange’ and ‘ChartOrgRange’. Now we’ll need to construct the OFFSET function that will be used to determine the ranges of the data dynamically. For both named ranges we will be using almost the same formula, just pointed at a different reference cell. Our ‘ChartDateRange’ will be as follows:

=OFFSET(Charts!$C$30,,,,COUNT(Charts!$C$31:$CF$31))

The first parameter is the ‘Reference Cell’; C30 – the starting cell of our date ranges. We will then pass through the next 3 parameters which are as follows:

  1. The number of rows to move from the reference cell.
  2. The number of columns to move from the reference cell.
  3. The height of the range starting from the reference cell.

Which means the final parameter with our COUNT function determines the width of the range starting from the reference cell. We count all the data within the data range; in this instance I’ve extended the range of the COUNT to include extra columns. We will be extending our data range further out to allow the chart to handle extra data in the backing sheets and adding in additional months of data.

Our ‘ChartOrgRange’ is then simply:

=OFFSET(Charts!$C$31,,,,COUNT(Charts!$C$31:$CF$31))

We’ve just shifted the reference cell down one row to C31 in this case.

Step 1b.

We’ll quickly extend the data ranges of our back sheets and our INDIRECT named range ‘SelectedData’ to also cover the new extended data range. We’ve extended the back sheets to column CF and made the blank data cells show an error by using:

 

=NA()

 

This is necessary to prevent us counting these cells as they will show as 0s when not errors.

 

To match this, we’ve extended ‘SelectedData’ to include columns CF, so the formula is now:

 

=INDIRECT("'"&'Output Sheet'!$H$16&"'!$C$7:$CF$16")

 

We also need to change our chart’s data table to have the formula:

 

=INDEX(SelectedData,OrgNameLinkedCell,MATCH(C$30,AverageTimeData!$C$6:$CF$6,0))

 

We’ve changed the MATCH function to now look at the range C6:CF6 in our back sheets. We then need to extend the data table all the way across to column CF too.

Step 2.

Step 2 Example

Now we can set the chart to look at these Named Ranges instead of the actual data range.

Right click the chart and go to ‘Select Data’. First, we’ll assign a new series data range, so click the ‘Legend Entries’ option for our current series then select ‘Edit’. Change the ‘Series values’ to:

=Charts!ChartOrgRange

It’s important to include the worksheet reference ‘Charts!’ as otherwise the chart will not accept the new input.

Next, we’ll change the horizontal axis labels range, click the ‘Edit’ button for ‘Horizontal (Category) Axis Labels’. Change the ‘Axis label range’ to:

=Charts!ChartDateRange

Once again, make sure you include the worksheet reference ‘Charts!’.

Step 3.

We can now add in new data to test our changes. To showcase this, we’re going to add in some new dummy data to just one of our back sheets; ‘StaffData’ in this case.

To achieve this, we’ve added in 2 more months of data using:

 

=RANDBETWEEN(20, 500)

 

This generates some random numbers for us between 20 and 500. To ‘freeze’ these values we’ll just copy them and paste them as values.

Once this is done you should be able to change freely between the measures and note that for Number of Staff we have data all the way up to May-17, and for the other two measures they only extend up to Mar-17.

And that should be it! This method is also especially useful for automation of Excel reports. If you set up the back sheets to automatically pull their data from wherever their data source is, utilising this method will allow your front end to also automatically update meaning you will have more time to check your data and free up your time to do more proper, less tedious work!

You can download the completed workbook here.

Once again thanks so much for reading and I really do hope this helps. If you have any comments or queries about this tutorial, please do reply below or email us at staticinterest@gmail.com.

How to How to... Excel

HOW TO… Excel: Create an Automatically Updating Chart

Following on from our last series of HOW TO… Excel, we’re going to expand the concepts used to create the dynamic data table and create another sheet for the same workbook that will be the home of an automatically updating chart! Unlike pivot charts, this will be able to update as soon as new data is inputted into the data backsheets, and will be connected to the same ‘linked cell’ as our previous combo box, so the changes will be persistent throughout the workbook. This will allow us to create a consistent and pleasing user experience for whom ever will be using the tool.

We’ll be using the same workbook as the first tutorial so if you want to follow along please do download it here.

Step 1.

We need to set-up a measure input list and the linked cell used for our combo box as named ranges to make it easier to copy and paste the combo box and reference these ranges throughout our workbook.

Go to ‘Formulas‘ -> ‘Define Name‘ and define two new names, one pointing at ‘Output Sheet’!G16 (our linked cell) and the other at the range ‘Output sheet’!A16:A18 (our input list). We’ve called these our ‘MeasureLinkedCell’ and ‘MeasureInputRange’ respectively. These can be named however you would like but its important that it’s relevant to the task its being used for to make them easier to unpick for yourself and other people in the future.

Step 2.

Now we will set up a combo box using these two ranges (in this example we are going to be changing the existing combo box used for our ‘Output Sheet’). If you can’t remember how to do this please check the earlier tutorial as it covers this.

Step2

We’ll now create a new sheet to house our chart(s). We’ve called it simply ‘Charts’ in this example. Copy the combo box to this sheet; as we’ve used named ranges the links should remain intact to the old linked cell and input range. If you change the selection of this new combo box, it will also change the combo box on the other page as they are both linked to the same cell.

Step 3.

We will also need a new combo box to be used for our organisations as showing all 10 organisations in this case would create a very unwieldy chart. In this example we’ll be showing how to set-up a line chart that will drill down to different organisations and measures. Utilising the principles from this tutorial you should be able to create any type of chart you’d like, and tailor pick the data to create the chart.

Again, we will set-up some named ranges for our input range and linked cell for our organisation list. Due to the increasing number of lists we are accessing it may be worth moving these to their own sheet to create a more manageable workbook. We’ll be creating a new sheet called ‘Lists’ to do this and moving our previous named range references here too.

List Sheet 1

Side note: Our old linked cell (‘Output Sheet’!G16) for our output sheet will need to point to the named range now so that our output sheet continues to work.

Create the new named ranges for the organisation name list and the organisation name linked cell, in this case I’ve named these ‘OrgNameInputList’ and ‘OrgNameLinkedCell’. Back on the ‘Charts’ sheet we’ll create that new combo box and linked it to these named ranges. So far you should have a new Charts sheet with two combo boxes, one linked to measure names and another linked to organisation names. Here’s what I have so far, with some formatting at a basic level.

Charts Sheet 1

Step 4.

Now we need a dynamically updating data table that will take the input from the linked cells of these combo boxes to create the data array that will feed the chart on this sheet. With our named ranges and the INDEX function we can create some nifty looking formulae that retrieve the organisation name and measure name based on the linked cells. So to get the organisation name we need a cell with: =INDEX(OrgNameInputList,OrgNameLinkedCell))
And for the measure name:
=INDEX(MeasureInputList,MeasureLinkedCell)
To get the sheet name for our measures we’ll add in another section to our ‘Lists’ sheet with a measures sheet name list as follows:

List Sheet 2

Then we can add in another cell to find the sheet name with the following INDEX function:
=INDEX(Lists!$E$2:$E$4,MeasureLinkedCell)

Step 5.

Now to create the dynamic table it’s the same as in the previous tutorials using whatever methodology you would prefer (using INDIRECT, OFFSET or INDEX with/without the master data back sheet, see my other tutorials for more info). Here we’re going to use the INDIRECT method to create this dynamic data table. We’ve already got an INDIRECT named range that looks at the chosen data back sheet, which was
=INDIRECT("'"&'Output Sheet'!$H$16&"'!$C$7:$Z$16")
where H16 was our selected measure sheet.

So, we can create the data table to look as follows:

Charts Sheet 2

Using the formula
=INDEX(SelectedData,OrgNameLinkedCell,MATCH(C$30,AverageTimeData!$C$6:$Z$6,0))
where C30 is the reference to our dates row and AverageTimeData!C6:Z6 is the reference to the dates list that all the data back sheets utilise.

We don’t need to do a MATCH for the row number as all of our back sheets use the same list of organisations, so we can use the Linked Cell as that is the row number of the chosen organisation.

Step 6.

We’ll now create a line chart that uses this dynamic data table. Highlight the data table, and go to ‘Insert‘ -> ‘Charts‘ -> ‘2D Line Chart‘ to create the line chart. If you now use the combo boxes to select different combinations of measure and organisation you’ll see that the chart updates in response. The next steps are to tidy up the chart to make it more readable and understandable for our users.

Step 6a.

First, we will make the chart title update with the change of measure and organisation name. We will need to create a chart title in another cell that will be referenced by the chart title. In a cell nearby our data table (H27 in my case), add a formula such as this:

=$C$27&": "&$C$28

Where C27 is the chosen organisation name and C28 is the chosen measure name. This will create an output that looks like this: “Organisation: Measure”. We use ampersands (&) to allow us to mix strings (sections of formulae surrounded by quotation marks “”) and cell references. Now we need to link the chart title to this cell. Click into the chart title, navigate to the formula bar and type

=Charts!$H$27

Or click into the relevant cell that contains the new chart title after typing ‘=’.

Step 6b.

Next, we need to create a new vertical axis title to contextualise our data. For this we’ll need another list in our ‘Lists’ sheet that will contain the units of measurement for each of our measures. For this sample data set we need Hours, Units of Product (or just ‘Units’ for short) and No. of Staff for Average Time Taken, Total Throughput and Number of Staff respectively. We’ll then create a link to this list that provides us with the unit of measurement for the chosen measure using the formula:
=INDEX(Lists!$G$2:$G$4,MeasureLinkedCell)
where Lists!G2:G4 is the range for the ‘Units of Measurement’ list. We’ll now create a vertical axis title by selecting the chart, clicking the green plus that appears in the top right, navigating to Axis Titles and adding a Primary Vertical Axis Title.

Charts Sheet 3

Now click the axis title that appears, navigate to the formula bar and type:

=Charts!$H$28

Or click into the relevant cell that contains the axis title after typing ‘=’.

We should now have a working dynamic chart that updates the title and axis title when the measure and organisation name get changed.

You can now adjust the chart to your liking (preferably following good data visualisation principles!) but functionally everything should be working fine.

Next time we’ll discuss using OFFSET to create a dynamically adjusting data range for the chart so that it will automatically update when new data is added to the data set and creating methods to drill down into our data using different criteria.

As always thanks for reading, if you have any comments of your own or ways you’d do this instead please do let me know in the comment section below!

The final version of the workbook used is available here.

How to How to... Excel

HOW TO… Excel: A Truly Non-Volatile Solution for Dynamic Data Tables

This is a follow-up to the last tutorial. Thanks to reddit user /u/small_trunks for noticing my blunder!

Unfortunately, ‘SUMIFS’ couldn’t be used on its own to create the dynamic data tables. But don’t fret! I have another solution up my sleeve that doesn’t rely on volatile functions like ‘INDIRECT’ and ‘OFFSET’! It relies entirely on ‘INDEX’ and ‘MATCH’.

We’ll need to add an additional list next to our measure names and back-sheet names lists on our output sheet, which I’ve named the ‘Master Sheet Start Point’ column. In this column we can use the ‘MATCH’ function to work out the first iteration of each of the measure names in our backsheet. The formula in this column is:

=MATCH($A16,MasterBacksheet!$C$2:$C$31,0)

Then, below our two cells that work out which sheet name and measure name (H16 and H17) is corresponding to what has been selected by the combo box we can add another cell that will find the starting point of the selected measure in the master back-sheet. The formula will therefore be:

=INDEX($E$16:$E$18,$G$16)-1

We need to include the ‘-1’ as we’ll also be using ‘MATCH’ in the main formula for finding the row number of each organisation so we don’t want to ‘double count’.

NewSolution

The formula we’ll be using in the dynamic data table will be an ‘INDEX’, ‘MATCH’ formula as follows:

=INDEX(MasterBacksheet!$D$2:$AA$31,MATCH(‘Output Sheet’!$B4,MasterBacksheet!$B$2:$B$11,0)+’Output Sheet’!$H$18,MATCH(‘Output Sheet’!C$3,MasterBacksheet!$D$1:$AA$1,0))

Breaking this down gives us:

  1. =INDEX(Full Array of Master Back-sheet Data,
  2. MATCH(Org name, Org Name list in Master Back-sheet, Look for an exact match) + Measure’s Master Sheet Starting Point,
  3. MATCH(Date, Date Headings in Master Back-sheet, Look for an exact match))

And then this formula can be copied across the entire data table! With that we’ve solved the issue, we have a non-volatile method of creating a dynamic data table.

NewSolution2.PNG

Once again if anyone has any other methods of solving this solution please share in the comment section below – I’d love to see what creative methods the community can come up with!

The workbook can be downloaded here.

How to How to... Excel

HOW TO… Excel: Using SUMIFS to Create a Dynamic Data Table

This is a continuation of the previous tutorial.

Due to the volatility of the INDIRECT function, it may be necessary to avoid using it when trying to create large dashboards which will be handling and outputting large amounts of data. So, we’ll need an alternative in these cases. One of the ways this can be done is by using the SUMIFS function, and structuring our data to be compatible with the SUMIFS function.


This has been achieved using SUMIFS, but OFFSET (another volatile function) has been used to cover one of SUMIFS weaknesses. A completely non-volatile method can be found here.


This will be done on the worksheet from the previous tutorial. A download link is available at the bottom of that page so you can follow along.

Step 1.

First, we’ll need to create a new ‘master’ data back-sheet that compiles all the data back-sheets to allow SUMIFS to work correctly.

Step1.PNG

A new column called ‘Measure’ has been added to the old structure which contains the measure names, and the data has been pulled from the other back-sheets by simple cell references.

Step 2.

Now we need to change the formula in the output sheet’s data table to use SUMIFS. But before that, we’ll need add a new INDEX that checks the Measure Name selected by the combo box.  H16 on the output sheet can be copied down to H17 and then have its INDEX array changed to the measure name list as opposed to the back-sheet name list so the new formula in H17 is:

=INDEX($A$16:$A$18,$G$16)

We can now change the dynamic data table’s formula. The formula used in the top left (C4) of the table is as follows:

=SUMIFS(MasterBacksheet!D$2:D$31,MasterBacksheet!$C$2:$C$31,’Output Sheet’!$H$17,MasterBacksheet!$B$2:$B$31,’Output Sheet’!$B4)

If we break this down we get:

  1. =SUMIFS(SUM_Range , Criteria_Range1 , Criteria1, Criteria_Range2, Criteria 2)
  2. =SUMIFS(Apr-15 Column (frozen to row numbers using absolute referencing, $), Measure Column, Selected Measure, Org Name Column, Org Name (frozen to column number using absolute referencing, $)

A weakness of this formula as it stands is the way we’ve referenced the SUM range, using direct references. To allow this formula to be more flexible we can instead use another function to work out the SUM range instead of directly referencing it. One way to do this is to use the OFFSET function. The way to construct the OFFSET function within this formula to correctly determine the SUM range dynamically is as follows:


As pointed out by reddit user /u/small_trunks, OFFSET is also volatile so isn’t a good alternative to the ‘Indirect’ function! This is still a valid method to create the dynamic data table if volatility is not an issue, but a better method can be found here – although unfortunately ‘SUMIFS’ hasn’t been used in this instance.

UPDATE: Reddit user /u/ubbm has come to the rescue! The example below with OFFSET can be replaced with an INDEX function. INDEX can be used in a very similar way to OFFSET; if you specify an array and then only choose a row number or column number, the output will be the entire row or column within the array. So for this example the following can be used instead of OFFSET:

=INDEX(MasterBacksheet!$D$2:$AA$31,,MATCH(‘Output Sheet’!C$3,MasterBacksheet!$D$1:$AA$1,0))

See the full comment for a break down of the logic behind this method of using INDEX.


=OFFSET(MasterBacksheet!$C$2:$C$31,,MATCH(‘Output Sheet’!C$3,MasterBacksheet!$D$1:$AA$1,0))

The structure of the OFFSET function is:

  1. =OFFSET(Reference Range, Number of Rows to Move from Reference, Number of Columns to Move from Reference, [OPTIONAL] Height of the Reference, [OPTIONAL] Width of the Reference)
  2. So, our ‘OFFSET’ function is: = OFFSET(Measure Name Column (1 column before the date columns), Move 0 Rows, Find ‘Apr-15’ in the date headings and move that many columns across). We start one column before the date headings as ‘MATCH’ returns 1 if it finds a match in the first cell of the range.

So our full formula is:

=SUMIFS(OFFSET(MasterBacksheet!$C$2:$C$31,,MATCH(‘Output Sheet’!C$3,MasterBacksheet!$D$1:$AA$1,0)),MasterBacksheet!$C$2:$C$31,’Output Sheet’!$H$17,MasterBacksheet!$B$2:$B$31,’Output Sheet’!$B4)

This formula can then be copied across the entire data table.

Step2

And there we have it! This of course is just one way of going about solving this issue, and part of the beauty of Excel is the ability to bend it to your will when you know all the different functions and tricks of the trade. If you have any other ways in which you would go about creating a dynamic data table, you can share it in the comment section below. Any feedback on my methodology is also welcome!

You can download the finished workbook here.

How to How to... Excel

HOW TO… Excel: Create a Dynamic Data Table Using INDIRECT and Combo Boxes

IndirectTutorialGif

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.

Step1

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.

Step2

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.

Step3a

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:

  1. ‘Average Time Taken’;
  2. ‘Total Throughput’; and
  3. ‘Number of Staff’

Corresponding to our back-sheets:

  1. ‘AverageTimeData’;
  2. ‘TotalThroughputData’; and
  3. ‘StaffData’

I’ve created the lists next to one another below the data table as below:

Step3

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.

Step4a

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:

  1. =INDIRECT(“TEXT TO BE INTERPRETED”)
  2. =INDIRECT(“ ‘ “& CELL WITH SHEET NAME &” ‘! DATA TABLE’S ARRAY”) this is then interpreted by excel as:
  3. =’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.

Step5

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:

  1. =INDEX(SelectedData, this is the link to our named range, so our named range is the array we are ‘indexing’.
  2. 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.
  3. 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:

IndirectTutorialGif2

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…’.

Step7

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.

How to How to... Excel