After you create a chart, you don’t have to worry about updating the chart when values change. As long as worksheet calculation is set to Automatic, Excel will automatically update the chart when values in the source data change. You can verify that Calculation is set to automatic on the Formulas tab of the ribbon. Just click Calculation Options and confirm that Automatic is checked. In this chart, the source values are are currently in the range C5:C9. If I edit any value in this range, the chart automatically updates. However, if I add new data below the data range, the chart does not automatically include this new information. That’s because the reference to the source data is static, and doesn’t automatically expand. To include this data, just select the chart, and use the drag handles to expand the data range as needed. The chart will immediately update. The opposite problem can occur when source data is removed.  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 the drag handles to resize the data range so the blank cells are no longer included. You can use the same approach to add and remove data series. For example, here I can drag to add the values in the Expense column to the chart. To exclude Expenses again, I just reverse the process. Later versions of Excel include a feature called Chart Filters, a great tool for filtering data with more control. We’ll take a look at Chart Filters in more detail, in a separate video.

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.