Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.