Creating Excel Charts that Automatically Update
By Allen Wyatt for Excel.Tips.Net
David has a worksheet that he uses to track sales by company over a number of months. The company names are in column A and up to fifteen months of sales are in columns B:P. David would like to create a chart that could be dynamically changed to show the sales for a single company from the worksheet.
There are several ways that this can be done; I’ll examine three of them in this tip. For the sake of example, let’s assume that the worksheet is named MyData, and that the first row contains data headers. The company names are in the range A2:A151, and the sales data for those companies is in B2:P151.
One approach is to use Excel’s AutoFilter capabilities. Create your chart as you normally would, making sure that the chart is configured to draw its data series from the rows of the MyData worksheet. You should also place the chart on its own sheet.
Now, select A1 on MyData and apply an AutoFilter. (Display the Data tab of the ribbon and click the Filter tool.) A small drop-down arrow appears at the top of each column. Click the drop-down arrow for column A and select the company you want to view in the chart. Excel redraws the chart to include only the single company.
The only potential drawback to the AutoFilter approach is that each company is considered an independent data series, even though only one of them is displayed in the chart. Because they are independent, each company is charted in a different color. If you want the same charting colors to always be used, then you will need to use one of the other approaches.
Another way to approach the problem is through the use of an “intermediate” data table—one that is created dynamically, pulling only the information you want from the larger data table. The chart is then based on the dynamic intermediate table.
This post appears with permission from Allen Wyatt and Excel.Tips.Net.
