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.

Data for Variable Chart Example

Initially, you need to create a range of cells that will show the monthly figures for any selected individual.

For the current example, we want to type the name of one of the sales team into cell A9, and have cells B9-M9 show the monthly sales figures for this individual. This can be done with the use of an Excel formula.

This can be done by typing the following Excel formula, which combines the Vlookup function and the Column function, into cells B9-M9:

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

This is shown in the spreadsheet below.

Formula for Variable Chart Example

When the above formula is copied into each of the cells B9 - M9, this gives the sales figures for the name in cell A9. If the name in cell A9 changes, these figures will also update.

A chart, based on the values in cells A8:M9 will therefore also depend on the value of the cell A9. To create a basic chart from these cells, highlight the range A8:M9 and select Insert->Chart. Select the type of chart you want to use and let Excel do all the work for you! A simple column chart from the data in cells A8-M9 is shown in the spreadsheet below:

Final Result for Variable Chart Example

To make your spreadsheet look that bit more professional, it is a good idea to add a drop-down list to cell A9, from which a name can be selected.

Final Result for Variable Chart Example Showing Drop-Down Menu

To do this:


The resulting drop-down list is shown in the above spreadsheet.