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.

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.

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.

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:

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:

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.

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.

Advertisement