Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Automatically Creating Charts for Individual Rows in a Data Table.
Written by Allen Wyatt (last updated September 11, 2018)
This tip applies to Excel 2007, 2010, and 2013
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. Follow these steps:
Figure 1. The Format Control dialog box.
=INDEX(MyData!A2:A151,$A$1)
="Data for " & A3
=ChartData!$A$3
You now have a fully functioning dynamic chart. You can use the Combo Box to select a company and the chart is redrawn using the data for the company you select. If you want, you can move or copy the Combo Box to the sheet containing your chart so that you can view the updated chart every time you make a selection. You can also, if desired, hide the ChartData worksheet.
A third approach is to use a macro to modify the range on which a chart is based. To prepare for this approach, create two named ranges in your workbook. The first name should be ChartTitle, and it should refer to the formula =OFFSET(MyData!$A$1,22,0,1,1). The second name should be ChartXRange, and it should refer to the formula =OFFSET(MyData!$A$1,22,0,1,15).
With the names defined, you can select the range MyData!B1:P2 and create your chart. You should base the chart on this simple range, and make sure that you place some temporary text in the chart title. Make sure the chart is created on its own sheet and that you name the sheet ChartSheet.
With the chart created, right-click the chart and choose Select Data. Excel displays the Select Data Source dialog box. Select the data series and click Edit. Excel displays the Edit Series dialog box. Replace whatever is in the Series Values box with the following formula:
='Book1'!ChartXRange
Make sure you replace Book1 with the name of the workbook in which you are working. Click OK, and the chart is now based on the named range you specified earlier. You can now select the chart title and place the following in the Formula bar to make the title dynamic:
=MyData!ChartTitle
Now you are ready to add the macro that makes everything dynamic. Display the VBA Editor and add the following macro to the code window for the MyData worksheet. (Double-click the worksheet name in the Project Explorer area.)
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) ActiveWorkbook.Names.Add Name:="ChartXRange", _ RefersToR1C1:="=OFFSET(MyData!R1C1," & _ ActiveCell.Row - 1 & ",1,1,15)" ActiveWorkbook.Names.Add Name:="ChartTitle", _ RefersToR1C1:="=OFFSET(MyData!R1C1," & _ ActiveCell.Row - 1 & ",0,1,1)" Sheets("ChartSheet").Activate Cancel = True End Sub
Now you can display the MyData worksheet and double-click any row. (Well, double-click in column A for a row.) The macro then updates the named ranges so that they point to the row on which you double-clicked, and then displays the ChartSheet sheet. The chart (and title) are redrawn to reflect the data in the row on which you double-clicked.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7887) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Automatically Creating Charts for Individual Rows in a Data Table.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When creating a chart, you may want to adjust the default scaling that Excel applies to an axis. This is relatively easy ...
Discover MoreWant to change the groupings used by Excel when it creates pie charts? Your options are limited, as you learn in this tip.
Discover MoreCharts can either be embedded in a worksheet or take up an entire sheet by themselves. Changing from one type of chart to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-09-02 07:56:49
Willy Vanhaelen
@Expat
Try the solution I posted 15 June 2014.
2016-09-01 06:36:41
Expat
Tried the autofilter method in Excel 2010 and it doesn't work. When I use the drop down to choose another country the chart goes blank. When I switch it back to the original company the chart is rendered again.
2014-08-20 08:41:33
Kare
I would use Pivot Table, Pivot Charts and Slicers....
2014-07-28 04:44:36
Clive
Re Don's comment on 16th June - I too haven't used control boxes for charting data. This method has certainly got me thinking again!!
2014-06-16 12:00:40
Don
rkeev: Sparklines? ooooh...had not tried them before. Very sweet.
That said, why not use Sparklines? For this example they may be sufficient. However, for more complex issues they may not work as well. I think that where the lesson lies.
For example, if the worksheet with the chart is redefined so that up to five (or some other arbitrary number) of companies are chosen then the chart could would show them in comparison.
2014-06-16 11:47:45
Don
This was interesting because I tend to shy away from putting controls on worksheets. It forced me to look at the behavior of the control and a worksheet function I seldom use, Index. It was a good lesson for me.
An alternative to the ComboBox control would be to use a Data Validation dropdown to select the company name then use VLOOKUP to populate the rest of the fields.
2014-06-16 11:08:24
rkeev
Why not just use sparklines?
2014-06-15 12:10:59
Willy Vanhaelen
In the macro version entering =MyData!ChartTitle in the Formula bar produces an error (Excel 2007). I worked around it by entering =MyData!ChartTitle in a cell and then enter the cell reference in the Formula bar. Anyhow I find this approach with the macro changing the range names way to complicated.
So I worked out a solution that dynamically changes the source range in the graph directly. It is shorter and easier to understand:
- No range names with complex formulas.
- No need to replace any source data with references to range names.
- Just create your chart, enter the macro at the right place and double click any row.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Y As Integer
Y = Target.Row
With Sheets("ChartSheet")
.SetSourceData Source:=Sheets("MyData").Range(Cells(Y, 2), Cells(Y, 16))
.HasTitle = True
.ChartTitle.Text = Cells(Y, 1)
.Activate
End With
Cancel = True
End Sub
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments