Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've got a workbook with a table that has the months across the top. Each month, another month column will be added and the data for that month will be filled in.

For each month, I want to create the same charts, just based on each month's specific data.

If I manually create the first month's chart to use as a template, how can I set it up so that upon entering in the new month's data, Excel will create the new month's charts automatically and put them in a new tab?

I want to avoid macros, if possible.

share|improve this question
What version of excel? – soandos Aug 29 '12 at 19:48
We're using 2010 – Chad Aug 29 '12 at 22:13
up vote 2 down vote accepted

In general, you can't automatically "create" or "generate" stuff in excel without macros.

However, if it were me, I would create a simple PivotTable on a new sheet based on your source data (including blanks for future months), and create a PivotChart from it.

What's nice about a PivotTable is that changing the month (and therefore the chart) is as simple as clicking refresh and selecting a month in a drop-down filter menu. Other users could easily do it too.

If you want to keep past charts, you could copy that sheet and change the month in the drop-down. If you want the month in your chart title, I would use a formula to pick the date from the cell with the Pivot month filter and show it well formatted (eg: 2012-08-01 -> August 2012) on top of the chart (as you can't put formulas in chart titles) to automate that part.

Basically, whenever you would have added a new month's data, you would merely have to:

  • copy sheet
  • refresh the pivot
  • change filter to the new month

I wouldn't recommend attempting to automate even these few steps through a macro. It's a lot of hassle: when would you fire the macro and how to prevent a bunch of empty or duplicate charts being created?

I find doing these few steps manually is probably the simplest and less error-prone way to go.

share|improve this answer
This worked perfectly. Thank you! – Chad Aug 31 '12 at 20:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .