Home and Learn: Microsoft Excel Course
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:
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.)
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:
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:
Pick the first line chart, under 2-D Line:
A chart will be added to your worksheet and will look like this:
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.)
From the menu, click on Select Data. You will then see a dialogue box appear. This one:
Under Legend Entries you'll see two items, Month and Savings. Deselect the Month item to leave just the Savings selected:
Click OK on the Select Data Source dialogue box. Your chart will then show only the Savings line:
Now for the fun part! Enter a new row in your table. Add 11, and 45 as the values for row 12:
As soon as you add the new row, Excel will update your chart and plot the new point:
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.
<--Back to the Excel Contents Page