To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.
Your department probably is responsible for producing a dozen or so monthly trend charts every month. These charts end up being sent to an executive dashboard or tacked up on a bulletin board on the shop floor. Each month one of your company analysts collects data for the month that just ended, then re-creates the charts. This process usually takes the better part of a morning in Excel.
There are several easy methods for adding a new data point to last month’s chart. These methods will make it quick to update an old chart rather than re-creating a new chart.
Method 1: If Your Chart and Data Are on Different Worksheets
I prefer this method because it works even if your charts and data are on separate worksheets. Say you have a chart with monthly data running from January through July. You now have new data for August. Type the August heading and the August data to the right of the July data.
Select the new data, including the August heading. Use Ctrl+C to copy the data and the heading.
Navigate to the existing chart. Click on the chart to select it. Press Ctrl+V to paste the new data onto the chart. Magically, Excel will keep your current formatting but will add the new August data to the chart.
Method 2: If Your Chart and Data Are Near Each Other
This method allows you to add new data to the right side of the chart and also to remove data from the left side of the chart. This is very handy if you want to show a rolling six months of data.
Click on the chart. You will see a blue outline appear around the chart data in the worksheet as shown in Figure 1. In each corner of the outline is a blue square handle.
To add August to the chart, grab the handle in the lower right corner of the blue outline and drag to the right. This will add a new month to the chart, as shown in Figure 2. If you have a chart that needs to show a rolling six months, grab any edge of the blue outline and drag it one column to the right. This will add August to the chart and remove February from the left side of the chart.
If you have an Excel guru on your staff, it is possible that he or she has created an OFFSET function in the SERIES formula for the chart so that the chart automatically extends as you type new data next to the existing data. Mere mortals can replicate this trick easily if they have Excel 2007 or newer. Go to the original chart data and press Ctrl+T to define the range as a table. Now, any additional months that are typed next to the original data automatically will be included in the chart. This is far simpler than mastering the OFFSET function.
Bill Jelen is the author of 32 books, including Pivot Table Data Crunching, and is the host of MrExcel.com. Suggest a topic for his next column at CFO.com’s Spreadsheet Community Center (right) and if your suggestion is chosen, you’ll receive a copy of one of Jelen’s new books.