Use Formulas to Create an Excel Variable Chart

This page shows you how to use Excel formulas to create an Excel Chart that varies according to the value of a single cell.

Imagine you have monthly sales figures for 5 salespeople (as shown in the spreadsheet below), and you want to create a graph that will show the monthly figures for any selected individual. You also need the graph to automatically update when you select the name of a different 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 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 (e.g. the name 'Steven' has been typed into cell A9 of the spreadsheet below).
  2. Enter the formula:

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

    into cell B9 (see below):

    Formula for Variable Chart Example

    Copy the formula from cell B9, 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 you change the name in cell A9, the formulas will update to display the figures for the new name.

  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
      This chart will update whenever the name in cell A9 is changed.

Extra Detail - Add a Drop-Down List to the Name Selection Cell

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:

This creates a drop-down list in cell A9, which allows the user to select any of the team names from the list in cells A2-A6.