Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Automatically Creating Charts for Individual Rows in a Data Table.

Automatically Creating Charts for Individual Rows in a Data Table

Written by Allen Wyatt (last updated September 10, 2022)
This tip applies to Excel 97, 2000, 2002, and 2003


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 (Data | Filter | AutoFilter). 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:

  1. Create a new worksheet and name it something like "ChartData".
  2. Copy the column headers from the MyData worksheet to the second row on the ChartData sheet. (In other words, copy MyData!A1:P1 to ChartData!A2:P2. This leaves the first row of the ChartData sheet temporarily empty.)
  3. With the MyData worksheet displayed, choose View | Toolbars | Forms. The Forms toolbar should be displayed.
  4. Using the Forms toolbar, draw a Combo Box control somewhere on the MyData worksheet.
  5. Display the Format Control dialog box for the newly created Combo Box. (Right-click the Combo Box and choose Format Control.)
  6. Using the controls in the dialog box, specify the Input Range as MyData!$A$2:$A$151, specify the Cell Link as ChartData!$A$1, and specify the Drop Down Lines as 25 (or whatever figure you want). (See Figure 1.)
  7. Figure 1. The Format Control dialog box.

  8. Click OK to dismiss the dialog box. You now have a functioning Combo Box that, once you use it to select a company name, will place a value in cell A1 of the ChartData worksheet that indicates what you selected.
  9. With the ChartData worksheet displayed, enter the following formula into cell A3:
  10. =INDEX(MyData!A2:A151,$A$1)
  11. Copy the contents of cell A3 to the range B3:P3. Row 3 now contains the data of whatever company is selected in the Combo Box.
  12. In cell B1 enter the following formula. (The result of this formula will act as the title for your dynamic chart.)
  13. ="Data for " & A3
  14. Select the column headers and data (B2:P3) and create a chart based on this data. Set the chart's title to some placeholder text; it doesn't matter what it is right now.
  15. In the finished chart, select the chart title.
  16. In the Formula bar, enter the following formula:
  17. =ChartData!$A$3

    You now have a fully functioning dynamic chart. You can use the Combo Box to select a different 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) Click Add, and then define the second name. This one should be called ChartXRange, and it should refer to the formula =OFFSET(MyData!$A$1,22,0,1,15). (See Figure 2.)

    Figure 2. The Define Name dialog box.

    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 Source Data. Excel displays the Source Data dialog box, and you should choose the Series tab. Since you based the chart on the headers and a single row of data, there should be a single data series in the dialog box. Replace whatever is in the Values box with the following formula:


    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:


    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)"
        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 (2377) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Automatically Creating Charts for Individual Rows in a Data Table.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Rounding Religious Wars

How to round values half way between two boundaries is apparently a topic of contention in some sectors of the ...

Discover More

Inserting the User's Name in a Cell

Need to understand who is using a particular workbook? There are a number of ways you can find out, as discussed in this tip.

Discover More

Dragging to Clear Cells

If you want to get rid of the contents of a range of cells, a quick way to do it is with the Fill handle. Yes, you can ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (menu)

Numeric Value and Percentage Value in a Graph Column

Need more than a single data label on a data series? Here's a relatively easy way to get what you need.

Discover More

Specifying Chart Sizes

If you need a number of charts in your workbook to all be the same size, it can be a bother to manually change each of ...

Discover More

Moving Groups of Data Labels at One Time

Having problems working with data labels in your charts? That's not unusual; here's an idea of an add-in that could help ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six less than 9?

2022-12-16 20:13:39


Dear Allan

I have found your tips very helpful. RE: Automatically Creating Charts for Individual Rows in a Data Table

I teach and I am trying to create charts for each student following a final exam, so this page has been very helpful. But I have over 100 students.

I love solution 2 for what I am trying to do. However, all the names do not show in the choice box. Is there a way that gives me a scrolling drop down list for this solution.

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.