Excel Dynamic Charts: A Great Time Saving Tool

If you use Excel for reporting on results each month, you have probably needed to adjust your charts automatically to add a new month, state, or sales rep. Typically, that means clicking on each chart and adding a new data series or expanding the range of the current series.

Unfortunately, it is easy to miss an update, and nobody likes turning in the monthly reports to the boss and hearing, “Why don’t these charts include February?” Wouldn’t it be nice if the chart adjusted itself automatically?

Make a dynamic chart

Your chart will adjust itself to include new rows and column in your data if you base the chart on a table. (We have demonstrated this feature in Excel 2013, but the table has been present in all versions since Microsoft Excel 2007.)

1. Enter your data in rows and columns, just as you have always done. To make your table and your chart easier to understand, add labels at the top and on the left. Here is a sample showing a company’s sales by state and by month.

Fred Pryor Seminars_Excel Dynamic Charts_figure1

2. Click on the word “State” and, holding down the mouse button, drag until you have selected the entire range.
Fred Pryor Seminars_Excel Dynamic Charts_figure2

3. On the Insert ribbon, click Table.
Fred Pryor Seminars_Excel Dynamic Charts_figure3

4. Your new table now has special formatting, sorting, and other capabilities. (Feel free to explore the Table Tools Design ribbon, though you won’t need those capabilities to set up your chart.)

5. With the table still selected, click on your favorite chart type on the Insert ribbon.
Fred Pryor Seminars_Excel Dynamic Charts_figure4

6. This is a sample column chart that shows the results grouped by state.
Fred Pryor Seminars_Excel Dynamic Charts_figure5

7. This is the same chart with the rows and columns switched.
Fred Pryor Seminars_Excel Dynamic Charts_figure6
So far, it’s just like the ordinary process of creating a chart, right?

Now try adding a row

1. Your data table is enclosed by a thin border with a right angle bracket on the lower right corner.
Fred Pryor Seminars_Excel Dynamic Charts_figure7

2. Drag that bracket to the right (to add a month) or down (to add a state), and the chart adjusts automatically to include the new data.
Fred Pryor Seminars_Excel Dynamic Charts_figure8

3. Enter your new numbers, and watch the columns appear in the chart.
Fred Pryor Seminars_Excel Dynamic Charts_figure9

Other options for Excel dynamic charts

Do you want even more flexibility in your range selections? Do you ever need to graph, say, only the last three months, and do you want your chart to update itself automatically to show data from those last three?

Or are you using an older Excel version that does not include tables?

Excel boasts a more powerful means to create dynamic charts using range names and offsets. Unfortunately, the new flexibility comes with increased complexity and more opportunities for errors. We’ll discuss these dynamic range names in a future post.