Automatically updated Excel charts

Charts that update themselves when you add new rows to the chart data can be a great help, and save you bags of time. In this lesson, we'll show you how it's done.

The key to automatic chart updates is to turn your data into a table. First, create the simple spreadsheet below:

A simple two-column Excel spreadsheet

The spreadsheet is just two columns. The A column records a month as a number, and the B column is the amount we put aside in savings. We'd like to make a line chart of this data. We'd also like the chart to update itself when new months and savings are added.

The first step is to turn the data into a table. So select the cells from A1 to B30. With the cells selected, click on the Insert ribbon at the top of Excel. On the Insert ribbon, locate the Tables panel, and click the Table item. Your data is turned into an Excel table. (You can also use the keyboard shortcut CTRL + T to create a table.)

The Table item in the Excel ribbon

When you click Table, you'll see a small dialogue box appear. Make sure "My table has headers" is selected, and click OK. Choose a design for your table and it may look something like this:

Data turned into a Table in Excel

Now that we have a table, we can go ahead and create the chart.

With the cells A1 to B11 still selected, click on the Insert ribbon at the top of Excel. From the Insert ribbon, locate the Charts panel. Select the Line Chart item:

The Line Chart in the Excel ribbon

Pick the first line chart, under 2-D Line:

The Line Chart menu showing chart options

A chart will be added to your worksheet and will look like this:

A Line Chart added in Excel

We don't want the blue month line on there. To get rid of it, right-click on your chart to see a menu appear: (If you don't see the menu, right-click on the borders of the chart.)

The Select Data menu option

From the menu, click on Select Data. You will then see a dialogue box appear. This one:

The Select Data Source dialogue box

Under Legend Entries you'll see two items, Month and Savings. Deselect the Month item to leave just the Savings selected:

Select your Legend Entries

Click OK on the Select Data Source dialogue box. Your chart will then show only the Savings line:

A Line chart with only one line

Now for the fun part! Enter a new row in your table. Add 11, and 45 as the values for row 12:

A new row added to the Excel tab;le

As soon as you add the new row, Excel will update your chart and plot the new point:

An Excel chart automatically updated when a new row is added

In the image above, a value of 11 has been added to the horizontal axis. The line itself now goes up to 45 on the vertical axis.

Keep adding new rows to your table and watch what happens to your chart. It should automatically update itself to reflect the new rows.

 

In the next lesson, you learn about Data Table, which are not the same as the simple tables you've been creating.