Here we help you begin exploring data analytics by showing how you can use Excel to display dynamic data in powerful and professional ways.
What is a dynamic chart?
A dynamic chart is a way to create one chart that can be used to look at different views of a large data set.
It’s easy to create a standard chart in Excel using a wizard – especially when only a couple of metrics are involved.
But what if you have dozens of metrics to choose from? And what if you might need to analyse different time periods from dozens of columns?
The temptation might be to draw dozens of graphs – incredibly difficult to follow, and time consuming.
This is where a dynamic Excel chart is your friend.
What are some uses for a dynamic chart?
Have you ever seen the interactive dashboards in Google Analytics for websites? A single reporting chart can be used to analyse different aspects of website performance.
Dynamic charts work in a similar way.
Dynamic charts make it very easy to do ad hoc comparisons of data. The user can select up to three options and the chart will pull through the values automatically.
A dynamic chart also lets the user select which year they want to view (see above). So, if we have several years of data and only want to see a snapshot, we can select the relevant year from the dropdown. We have set it up so that the user can compare the selected year with the previous year.
What are the steps to create one?
The key to creating a dynamic chart lies in the way that the data is set up. In particular, it should be a single tranche, as per the diagram above.
It’s a good idea to extend the time period so that it includes the next three years from the outset. So, as our data set covers 2014-19, we have added 2020-22. This means that the person doing the data entry won’t have to come back to you each year to update the formulae.
There are three key stages in setting up a dynamic chart:
- Creating the data-entry area: we specify the year and dropdowns for the values.
- Setting up a mini data table: this uses the values from the dropdowns to pull the data from the main table.
- Setting up the graphs, using the values in the mini data table.
Step 1: Creating the data-entry area
In this stage, we set up the interface so that the user can specify the years and create the dropdowns. For simplicity, we will keep everything on the same sheet.
In cells B28, B29 and B30, enter the values ‘Year’, ‘Option 1’ and ‘Option 2’. For simplicity, we’re going to do a dynamic chart that allows the user to compare two values. It could easily be extended to have additional values, however.
The next step is to set up a couple of named ranges called ‘Graph_Options’ and ‘Years’, respectively.
Named ranges can then be set up via
the ‘Formulas’ ribbon on the main menu. Select ‘Name manager’ and ‘New name’.
(This will appear
as ‘Define name’ on a Mac. Click the ‘+’ to create a new name.) In this case, the named range consists of the values in the range B6:B22, which
are the possible options for the graphs. If the cells B6:B22 are selected before ‘Name manager’ is selected, they automatically fill the ‘Refers
to’ section of the table.
We also need to set up one for the years. In this case, we need a blank area of the sheet, which the user won’t see (I often use a separate sheet), and we need to type in the consecutive values for the years. Start the values for the year from 2015, rather than 2014, as in the graph we are comparing one year with a previous year. So, when we select 2015, the previous year is 2014.
Now go back to the left-hand side of the sheet and use data validation to restrict the values that can be entered in cells D28 (for the year) and D29 and D30 for the graph options. This means that on these cells only values from the dropdown will be permitted.
Then use the dropdown to select the values. For now, we will select the values ‘2015’, ‘Actual Turnover’ and ‘Forecasted Monthly Turnover’. That completes the first stage.
Step 2: Setting up a mini data table
The mini data table uses the values from the dropdowns to pull the data from the main table.
The first stage in creating a data table is to create the monthly timeframe. Using the date function, enter the formula in cell D34. This sets up a link to 1 January of the selected previous year. So, if cell D28 has the value ‘2015’, then the formula above gives the value ‘1/1/2014’.
We now need the next 23 consecutive months in our formulae. Excel’s EOMONTH formula can be used to specify the end of a month whose value is stored in another cell. So, for example, if we have the date ‘23/04/2015’ in cell C12, then putting the formulae ‘D12=EOMONTH(C12,0)’ in D12 will return the value 30/04/2015 – the end of the current month. And, if we want the first of the next month, we just add one: ‘D12=EOMONTH(D12,0)+1’, which returns 1 May 2015.
So in cell E34 enter the formula ‘E34=EOMONTH (D34,0)+1’. Then in cell F34 enter ‘F34=EOMONTH (E34,0)+1’. Now we drag this latter formula to the right to complete the timeline, which should be 24 columns showing adjacent months. Altering the value in D28 will automatically adjust the values in the timeline.
The next step is to incorporate the two axes into the data table. These are just cell references to the values in cells B29 and B30. So, in cells B36 and B37 put ‘=D29’ and ‘=D30’ respectively.
The next step is the most challenging: populating the mini data table using the values from the axes and the timeline. We’ll use a combination of the OFFSET and MATCH functions to do this. The OFFSET function allows you to return cell values relative to an ‘anchor’ cell. The MATCH function returns the position of one value within another range.
Enter the formula in cell D36. This will be the intersection of the first month and first axis option in the data table. And now drag this formula across both axes of the timeline to complete the values in the data table.
If you use the dropdowns in cells D28, D29 and D30, then the data table should update automatically.
Step 3: Setting up the charts
This is the final part of the dynamic chart – actually creating the chart. Highlight the first row of the data table and insert a standard line chart. This is the first axis in our graph. We can click on the graph and select the data source to specify the second axis.
We now have a dynamic chart that allows us to select two rows from the main data table and compare.
About the author
Kapil Kapur is the director of Fingertips Intelligence, which helps companies obtain management information (a.k.a business intelligence) easily.
David Nunn is Content Manager at AAT.