Help with excel 2007 chart

[Kenneth]

Hey, this seems fairly simple but is stumping me. I have a table with
static data that shows my budgetary costs per month for the last 2
years. The row labels in my table are the categories (mortgage,
insurance, power, gas, phone, etc.) and the column labels are each
month for 2007 and 2008. What I want to do is create a chart that will
graph the data (line chart) for each category over the last two years.
I would like to choose what category to display on the chart so
they're not all on the chart at the same time. I have tried a Pivot
Chart, but it's just not displaying the data correctly. A normal chart
works, but I can't easily turn the categories on and off the display.
This seems like it should be really easy since the data is not
dynamic. It seems like the Pivot Chart is the way to go because it
will let me select which category to display, but I can't get it to
come out right. Help would be greatly appreciated!

 

[Bernard Liengme]

Copy the row and column headings somewhere. Add a row above the months where
you can type a 'flag' such as an "X". To make this easy to relate, say the
first flag is in K2 while the first bit of numeric data in the original
table is in B2
In the first cell of the data part of this table use a formula like
=IF(ISBLANK(K$20),NA(),B2)
Copy this to fill the new table
Make a chart with the new table. Any column which has a blank flag will not
appear in the chart (there will be a legend entry for it, however)
If this works for you, could Cut and Paste the row of flags to be above the
original data so you need only work in one place.
best wishes

 

[Herbert Seidenberg]

Easy enough with Excel 2007 Pivot Chart.
Select category from drop-down list.
No formulas needed.
Accommodates dynamic data.
http://www.mediafire.com/file/r22z4ijmnzj/01_21_09.xlsx

 

[Kenneth]

Easy enough with Excel 2007 Pivot Chart.
Select category from drop-down list.
No formulas needed.
Accommodates dynamic data.http://www.mediafire.com/file/r22z4ijmnzj/01_21_09.xlsx

Herbert,
Wow, great chart. This is exactly what I'm trying to do. However, I
already have all the data layed out in a table as you do on the
PivotTableH page. This way I don't have to keep repeating the dates
for each item like you do on the Source page. Can I create the Pivot
Chart from the table I already have or do I have to arrange the data
as you have done on the Source page? This would mean rearranging all
of my data so that all of the amounts are in one column. Since I have
many more budget categories, I rather leave the data arranged the way
that I have it.

 

[Herbert Seidenberg]

Create "Source" page from your existing data
with reverse pivot table.
Do it manually without formulas or with the code provided.
http://www.mediafire.com/file/wmmwmtiyyny/01_22_09.xlsm

 

[Kenneth]

Herbert,
Thanks for the additional worksheet. I think I got it now without
having to redo my data source. The reverse pivot table worked better
than what I had before. Thanks for your help!