Ever have a situation where you have spent time getting your chart or charts to look nice, then try to use different data ranges in these charts? You’ve discovered that there’s no easy way to approach this change. At least there’s no easy way built into Excel, but there is a utility that can make the task easier.
You’ve made a gorgeous chart of the data in Sheet1. You copied the chart from Sheet1 to Sheet2 so you could plot Sheet2′s data in the same splendor. And you hit a snag: the chart on Sheet2 refers back to Sheet1′s data. There are two ways to correct this:
- Create a copy of Sheet1 including the chart, so the chart on the copied sheet refers to the data on the copied sheet. Then copy Sheet2′s data and paste it over the copied sheet’s data.
- Edit the series formulas of the copied chart in Sheet2, changing all instances of one sheet name to the other. This becomes tedious if there are multiple series in the copied chart, or if you’ve copied multiple charts.
You’ve charted data in rows 1 to 100 of your worksheet. Then you’ve updated the data so it reaches down to row 150. Your wonderful chart only shows data down to row 100. There are three ways to correct this:
- Before updating the data, convert the data range to a List (Excel 2003) or a Table (Excel 2007). Then update the data. The list/table will expand to include all of the data, and all formulas that refer to all rows in the list/table will update accordingly. This includes the chart’s SERIES formula.
- Create dynamic named ranges for the X and Y data ranges in the chart, and apply them to the chart series. This is described in Dynamic Charts in this blog and in a number of Dynamic and Interactive Chart examples described in this web site. However, this is an involved procedure that you never have time for.
- Edit the series formulas of the chart, changing all instances of one row number to another. This becomes tedious if there are multiple series or multiple charts to correct.
Your charts show the data for product Alpha beautifully, but you want to show the data for product Beta instead. The data is listed in another column. You can:
- Select the series, then drag the highlighted rectangles on the worksheet to reflect the new data range. This becomes a laborious process for multiple charts and series.
- Edit the series formulas of the chart, changing all instances of one column to another. This becomes tedious if there are multiple series or multiple charts to correct.
These suggested means for editing your charts all leave out one approach. In Change Series Formulas I showed how to programmatically change the series formulas in your charts, and I provided a utility that does the task for you. I’ve just recently updated the utility to account for glitches in Excel, and to streamline using the utility.
The Change Series Formula Utility
The new utility is located in PTS_ChangeSeriesFormula.zip. You can install it following the protocol in Installing an Excel Add-In. (The utility has been updated to reflect enhancements described in Change Series Formula – Improved Routines.
Upon installation, the utility creates a toolbar (In Excel 2007, the toolbar buttons are buried on the Add-Ins tab of the ribbon).
Not too fancy or complicated. Suppose I have the following scenario, a chart showing data for Alpha in column B, and I want to show Beta from column C instead.
If it’s only one series in one chart, I can drag the colored highlight rectangles in the sheet, or edit the series formula in the formula bar. But if there’s more, why waste time? With the chart selected (I like to select the series, so the formula bar is visible and I don’t have to remember what to change), click on the Change Series Formula button on the toolbar. Enter the old and new text in the dialog. In the old text entry box, I like to explicitly use punctuation like the dollar signs, to make sure I’m changing a column designation and not some other text in the formula. In the new text box, it doesn’t matter, because Excel capitalizes and adds dollar signs automatically.
Click the Change Active Chart Only button (the other button is inactive because there are no other charts on this worksheet).
Magically, the series formula, the colored highlights, and the chart itself now reference the new data.
Here is how I would extend a chart’s data from row 6 to row 12. In this case there are multiple charts on the worksheet, and you have a choice of changing the active chart or changing all charts on the worksheet.
This dialog shows how to change references from Sheet1 to Sheet2. In addition, the buttons reflect that multiple charts have been selected using Shift+Click. The options are to change all selected charts or all charts on the worksheet.
It’s a pretty smart utility, and it has saved me tons of time. Download it, install it, and use it. Give me feedback, make suggestions, and report errors in a comment below.