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.
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:
This is shown in the spreadsheet below.
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:
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.
To do this:
- Click on cell A9
- From the Data menu on your Excel spreadsheet, select the Data Validation option
- From the 'Allow:' drop-down menu, select the option 'List'
- A 'Source' box will appear. Enter the range:
- Click OK
The resulting drop-down list is shown in the spreadsheet on the right.
