Use Formulas to Create an Excel Variable Chart

The following example shows you how to use Excel formulas in an Excel Chart that varies according to the value of a single cell.

Imagine you have monthly sales figures for 5 salespeople, as listed below, and you want to produce a graph that will show the monthly figures for a selected individual. You also want the graph to automatically update when you select the name of another individual.

Data for Variable Chart Example

In order to do this, we will initially create a range of cells that will show the monthly figures for any selected individual and will automatically update for different individuals. We will then create an Excel chart that relies on the contents of this range of cells.

For the example data above, we can do this via the following steps:

  1. Type the name of one of the sales team members into cell A9 of the spreadsheet.
  2. Enter the formula:

    =VLOOKUP($A9, $A2:$M6, COLUMN(), 0)

    into cell B9 (see below):

    Formula for Variable Chart Example

    Copy the formula into cells C9-M9.

    Note that the formula in cells B9-M9 uses the Excel Column and the Excel Vlookup functions to look up the data for the name in cell A9. If the name in cell A9 changes, these figures will also update.

  3. We now need to create a chart based on the values in cells A8-M9. Every time the contents of cells B9-M9 change (which will occur every time cell A9 changes), the chart will automatically update to show the new values.

    To create the chart:

    • Highlight the cell range A8:M9.

    • Buttons for Different Chart Types in Excel Ribbon

      In current versions of Excel (Excel 2007 and later), various chart types are provided in the Charts grouping on the Insert tab (see right).

      (In Excel 2003, the chart option is found in the Insert menu).

    • Select the type of chart you want to use and let Excel do all the work for you!

      A simple bar chart from the data in cells A8-M9 is shown in the spreadsheet below:

      Final Result for Variable Chart Example
  4. To make your spreadsheet look more professional, it is a good idea to add a drop-down list to cell A9, from which a name can be selected (see below).

    Final Result for Variable Chart Example Showing Drop-Down Menu

    To insert a drop-down list into the example spreadsheet:

    • Click on cell A9;
    • Data Validation Option in Excel

      From the Data tab of your Excel spreadsheet, select Data Validation→Data Validation...

      (In Excel 2003, the Data Validation option is found in the Data menu);
    • You will be presented with the Data Validation dialog box. Within this:

      • From the 'Allow:' drop-down menu, select the option 'List';
      • A 'Source' box will appear. Enter the range:
      • Click OK.

    This produces a drop-down list that contains the list of names in cells A2-A6 (as shown in the above spreadsheet).