Excel makes it possible to add dynamic elements to charts such as chart titles, axis titles, and data labels that will automatically update based on the contents in a cell.
Worksheet 02d has the same data and chart as the previous section. We can edit the axis titles so that they are linked to the cell containing the title for that column. This way, the chart will update if we make any changes to the title of our data. To do this, select the y-axis title, go to the formula bar, type an equals sign, and click the column header containing the text “Voltage (volts).” Press Enter.
If you change the text in this cell, the axis title automatically updates.
Another usage of dynamic chart elements is to include information about the inputs to a calculation in the chart title. For example, you can make the title “RC Circuit Response: 100000 Ω, 0.000004 F,” and have it update if we change those inputs.
To do this, you’ll first create a cell that will dynamically display that text. Choose a cell, and enter in:
=”RC Circuit Response: “&C6&” “&D6&”, “&C7&” “&D7
This formula uses the CONCATENATE operator (&) to link together text with values from cells – in this case, to retrieve the values for resistance and capacitance with their respective units. When you press Enter, the cell will display the string “RC Circuit Response: 100000 Ω, 0.000004 F.”
Now, we can link the chart title to the string we just created. Select the chart title, type = in the formula bar, and select the cell containing the string. Press Enter.
To verify that the title will update, try changing an input, such as the capacitance in cell C7 to 6 µF. The title should update accordingly. Because the voltage calculation contains the capacitance, the data in the chart will update as well.
We can label a particular data point directly on the chart. We’ll add a label for the magnitude of the voltage at t = 4 sec. First, click on the series in the chart, then left-click the last data marker to highlight it. Right-click it and select Add Data Label. A label will appear indicating the voltage at this point.
Data labels can also be linked to cells just like chart and axis titles. Choose a cell and enter:
=”Voltage at t= “&B31&” sec: “&ROUND(C31,3)&” volts”
This formula returns a string containing text, the final time and the final voltage value. Here, the “ROUND” function is used to display the voltage at 3 decimal places (otherwise it would show too many decimal places). Click the data label that you just added to select it, then click again to enable editing. Type an equals sign in the formula bar, then select the cell in the worksheet with the string. You can move the data label and resize it as needed.
If we change the capacitance back to 4 µF, the chart title and the data label automatically update (as well as the actual data).
This is a useful way to add titles and labels to your charts that automatically update, saving you time and preventing potential errors.