Home » Excel-Formulas » 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.

Suppose 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.

Initially, you need to create a range of cells that will show the monthly figures for any selected individual. In the case of the example above, the name of one of the individuals could be typed into cell A9, and then a formula can be inserted into cells B9 - M9 to make these cells show the monthly figures for the selected individual.

In the example above, the following formula, which combines the VLOOKUP function and the COLUMN function, would produce the figures for the name entered into cell A9:

This is shown in the spreadsheet below.

If the above formula is copied into each of the cells B9 - M9, this gives a set of cells that varies according to the name entered into cell A9.

A chart, built 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 Excel literally does all the work for you! A simple column chart is shown in the spreadsheet below:

To make your spreadsheet look that bit more professional, it is a good idea to hide rows 1-7, which contain the original data, or place this data onto a different worksheet.

A further finishing touch could be to add a drop-down list to cell A9, from which a name can be selected. Click here for a simple way to do this using Excel Data Validation