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.
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:
- 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).
-
Enter the formula:
=VLOOKUP($A9, $A2:$M6, COLUMN(), 0)into cell B9 (see below):
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.
-
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.
-
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:
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).
To insert a drop-down list into the example spreadsheet:
- Click on cell A9;
-
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 (see right).
Within this:
- From the 'Allow:' drop-down menu, select the option 'List'. This should cause further options to appear in the dialog box;
- Ensure the option 'In-cell dropdown' is checked;
-
Within the 'Source' box, enter the range:
=A2:A6
- Click OK.
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.