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. You also want the graph to automatically update when you select the name of another 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 will automatically update for different individuals. We will 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:
Enter the formula:
into cell B9 (see below):
Copy the formula into cells C9-M9.
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:
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).
This produces a drop-down list that contains the list of names in cells A2-A6 (as shown in the above spreadsheet).