Graphing in a Pivot table without mathematical manipulation

[paul061]

Hello, new to the forum.
Here is my issue. I have an Excel database of about 500 rows of data and 35 colums. I'd like to be able to examine any colum vs any row and chart the result without any mathematical manipilation, just graph the data. I'm trying to do this with a pivot table but the data field always has some mathematical thing done to it i.e sum, total etc. I just want to chart the discreet data that I have queried. I can make the Pivot Table and make it filter the data I want, I just can't figure out how to put any of the data in the data portion and chart it without manipulation. Sorry this is most likely clear as mud.

 

[andiemac]

Is this something you are looking to do programmatically so that it can be repeated with your reporting frequency?

Also, why don't you want mathematical manipulation?

 

[paul061]

It is more of a reporting tool. If I have a meeting with management and they say how is this data looking, I can quickly filter through the mass amount of data show them the numbers and a graph. There isn't a mathematical representation I need. I don't need any numbers to be summed, totalled etc. I just need to chart the raw numbers. I'm thinking it may need an Access data base?

 

[andiemac]

Do you want all the data in one graph? That would be difficult to interpret.

If you want to chart one row with all the columns, or one column with all the rows, you could add a second worksheet and use an index or lookup to populate the data based on one entry cell.

So, if you have a number of say accounting buckets as row headers, and months of the year as column headers... Create a new tab in your workbook. Paste or link your 35 column headers along the top. Use validation (named range) to create a drop down box to select an individual row, probably in column A.

Then values could populate based on the selection in column A and the column headers listed above. As example in cell B2 "=INDEX(Sheet1!B:B,MATCH($A$2,Sheet1!$A:$A,0))" and fill across.

Then you just build a standard chart off that one row of data, which would update each time you select a new option from the drop down box.

 

[paul061]

Lets see if I can do a better job of explaining this. Lets say I own a lumber yard. I have various sizes of wood, various types of wood, various lengths of wood, I also have the same things in plastic. I receive a shipment daily. It would look like this:

Date size type length material
oct 29 2x4 fir 12.2 wood
oct 30 2x4 hem 12.5 wood
oct 31 2x4 oak 12.6 wood

Now suppose this table had 2 years worth of data, multiple sizes, types, lengths and materials, and I wanted to compare how the length of a 2x4 changes with the type of wood over time. I would filter the pivot table to make the page be wood (major topic), I would filter size (2x4), type, date and length. I don't want the lengths to be totaled, added, multiplied, I just want a graph that shows the dates on one axis, the lengths on another axis and the series would be the type of wood. Now my boss says he wants to see the same chart but for 2x6's, I change the filter to 2x6 the chart updates I'm a hero . Again I just want to chart the raw data. My programming skills are limited, no wait that's a lie. I have zero programming skills

 

[andiemac]

OK, I'm seeing what you are trying to do now. So I have one more question. Using your lumber example, does each row of your raw data have a seperate line for board length? So you received 10 pine 2x4 on 8/24, 5 are 10 foot, 5 are 12 foot. Are those listed seperately?

If so, assuming your pivot table has dates and length down the side, species across the top, you could Average the length. Since it's grouped by length the average would represent the actual length. However if you regularly get a variety of lengths the trend on your chart wont tell you much.

 

[paul061]

In reality my data is test data that has to be graphed as the values are. Normally there are 4 series values. It boggles my mind that I can't do this easily.

 

[paul061]

Anyone else with an idea or is it just the nature of the beast with pivot tables that you must mathematically manipulate the data?

 

[andiemac]

The purpose of a pivot table is to organize and summarize data. Since your goal is to Not summarize the data you might be better off looking for alternate options.

Have you tried charting your entire set of data, then using the AutoFilter option to filter down to your topic, size, species... Still thinking about the lumber example, I'd suggest only charting the date and length. You lose out on the nice feature that Pivots give you in that they display what you have filtered down to right at the top. This won't do that automatically, but it's probably possible to code that in.

 

[paul061]

Do you think I could do what I need with Access?