Creating Dynamic Charts Using the OFFSET Function and Named Ranges in Excel 2007 and Excel 2010
How to Create a Dynamic Chart in Excel 2007 and Excel 2010 That Updates Automatically as Data Is Added or Removed
Hi and welcome to my second hub on using the OFFSET function. In today’s hub, I am going to investigate how to create dynamic charts in Excel using OFFSET as well as Defined Names to create named ranges.
What this will allow you to do is to create charts from your data which will update automatically when you add or remove data. With a static chart, when you add data, you have to manually update your chart so that it displays the new data. A dynamic chart does this for you automatically.
In my first hub on the OFFSET function, I investigated how to use it with the COUNTA function to create dynamic data ranges so you could, for example, use it with SUM to add up invoices, sales or web traffic data. Should you delete or add data to the dynamic range your formula will be automatically recalculated to reflect the new data. This hub can be found here.
Creating Named Ranges in Excel 2007 and Excel 2010
The first step is to define named ranges from our data shown to the right:
The named ranges will then be used to define the data that we will use to create the dynamic charts.
We need to create two named ranges, one for the data itself and another for the chart labels. Firstly, we will create the range for the data that will appear in our chart:
- Select the Define Name button on the Formulas tab in the Defined Names group
- Define a Name (without spaces) you can see I used DailyVisits
- In the Refers to box enter the following OFFSET formula
The above formula creates a dynamic range starting at cell B2. It counts down the same number of rows as the number of rows found to be containing data by COUNTA.
So, if COUNTA finds 23 cells containing data, the range will start at B2 and end at B25.
COUNTA is dynamic so if data is added or removed, it automatically updates which changes the range. The OFFSET function is explained in far more detail in my first hub on OFFSET (the link can be found in the Introduction).
Note: When making changes to the Refers to box, do not use the arrow keys to move around this dialogue box or it will add cell references to the formula. Move by clicking the mouse instead.
Note: to edit your named ranges once they are created, use the Name Manager button to the left of the Define Name button you used to create it.
- Next, we need to repeat the above process, this time using Label for the Name and the formula below in the Refers to box to create our dynamic range for the chart labels.
Creating a Dynamic Chart in Excel 2007 and Excel 2010
Now we have the named ranges, we need to create the chart:
- Select the Column button in the Charts group on the Insert tab
This will create a completely blank chart
- With the chart selected, click on the Select Data button in the Data group on the Design tab under Chart Tools
- Change the Chart data Range so that the name you gave your named range above for the data (I used DailyVisits) is entered after the name of the tab as shown below
So in my example, I changed:
The chart will now look like this:
To add our dynamic labels:
- Select the chart again
- Click on the Select Data button in the Data group on the Design tab under Chart Tools
- Under Horizontal (Category) Axis Labels, click Edit
- In the Axis label range, enter in the name of your named range for your labels (I chose Labels for mine)
- Click OK twice and Excel will now have added your labels to the chart
Finally, we need to ensure that the chart is using the named ranges. To do this:
- Ensure that the chart is once again selected
- Click on the data series
- On the formula bar, you will see a formula beginning with =SERIES
The formula in my chart is:
Before we edit this formula, let’s look at its syntax:
The formula is made up of four parts
- The title of the chart (this is optional)
- The location of the labels for the horizontal axis
- The location of the data for the series
- The order that the series appear on the chart (1 for first, 2 for second etc)
There are three parts of the formula that you should edit:
- The first is to add the chart title should you wish to from the data header (if you click to the left of the first comma in the formula and then select the cell that contains your data header (which is B2 in my example) Excel will add it automatically for you) so that first part of the formula becomes:
- The second part is the part that contains the dynamic labels. These will be updated automatically should the labels in your spreadsheet change.
- The third part is to ensure that the chart updates when you add or remove data dynamically. To do this:
Change the last part of the formula $B$3:$B$39 to your named data range for the data (mine is DailyVisits)
The formula becomes:
If you would like to learn more about creating and configuring charts and graphs in Excel 2007 and Excel 2010, I have a hub that covers this in greater detail. The hub can be found here.
Now we have the chart completed, one final thing to note when entering in data. If you enter in data without the corresponding labels in the adjacent row, Excel will not add them to your chart. It will only display the new data once the labels are added. If you add in a number of labels with no data, Excel will display the first label without data but will not add any more labels without data to the chart.
As our lives become more and more busy and the amount of information we work with increases, it is always very useful from both a time saving and error reduction standpoint to automate as much as we possibly can and the OFFSET function is very useful indeed in allowing us to automate both formulas and charts in Excel. Particularly for spreadsheets that are often updated, automating the updating of your charts is very handy indeed.
The OFFSET function in Excel allows you to create dynamic ranges and dynamic charts which update automatically when data is added or removed within the range. In today’s hub, we created named ranges and used them with the OFFSET function to create dynamic charts.
Many thanks for reading and I hope that you are now enjoying your dynamically updating charts and spending your new free time on something more enjoyable. Please leave any comments you may have below.
© 2013 Robbie C Wilson
More by this Author
Toggle Buttons in Excel 2007 and Excel 2010 using Visual Basic code allow you to toggle or switch between two states; e.g. turning grid lines on and off, hiding or showing rows by clicking a button.
- 0Use the Sampling Tool in the Excel 2007 and Excel 2010 Analysis ToolPak for Sampling and to Analyse Periodic Variations
The Sampling Tool from the Analysis Toolpak in Excel 2007 and Excel 2010 allows you to create random samples or smaller samples from larger data sources as well as analysing your data for variations.
If your data range contains many blank cells, it can be hard to correctly create a chart. Use the IF function to convert blank cells to #N/A which Excel ignores or change how Excel deals with blanks.
No comments yet.