Let’s take a look. After you’ve created a chart, you normally don’t need to worry about updating the chart manually. That’s because Excel will automatically update the chart when the source data changes, as long as the calculation is set to Automatic. You can verify that Calculation is set to automatic on the Formulas toolbar. Just click Calculation Options and confirm that Automatic is checked. For this chart, the source values are in the range B7:C11. If we edit any values in this range, the chart is automatically updated. However, if we add data at the bottom of the range, the chart is not updated to include this new information. That’s because the chart’s reference to the source data is static and doesn’t expand automatically. To fix this problem, just select the chart, and drag one of the data handles down to include the cells that contain new values. The chart will then immediately update. The opposite problem can occur if you delete information in cells that contain source data for the chart. In this case, you may see space left in the chart for values that no longer exist. You can fix this problem in the same way. Select the chart, and use a data handle to resize the data range so that the blank cells are no longer included. You can use this same approach to add a new data series. For example, let’s add the values in the expense column to our chart. Just select the chart and drag to expand the data range to include the new column. Because a column chart can easily handle more than one data series, the chart is updated to include Expenses. Note that not all chart types in Excel can be used to plot multiple data series. To exclude Expenses from the chart again, just select the chart and adjust the data range to exclude the values in the expense column.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
