How to display pivot item if it doesn't exist

[kc.crow]

Hello

Sorry for the cryptic title. Hopefully I can explain it.

I have 5 possible rating values in a column. This column will be the row header for my pivot table. Say the values are 1 through 5. If the dataset I pull doesn't have a full representation of all possible values (say, it only has 2, 3, and 4, but not 1 or 5) then the pivot table rows only reflect:

2
3
4
(blank)

I'm basing a chart on this pivot table so what I really need is to have a bar on the chart for each possible rating even if the dataset doesn't provide me with that. Is there a way that I can have a placeholder for the 1 and 5 ratings?

I have a similar issue with column headings on a pivot table where I'm looking for a specific value that may not exist in the underlying dataset. Is there a way to force that specific value onto the pivot table so that I can chart it (even though the amount is null or zero)?

Thanks in advance,
Ron

 

[Lidsavr]

I think I understand what you are trying to do. It is similar to something I do with pivot tables.

See if this works:

You did not say which version of Excel you are using, but in Excel 2007, if you right click on the field and choose "Field Settings." On the Layout and Print tab, select "Show items with not data".

I do this when I am charting a data by months. When I have a month with no data, I still want the month to appear on the chart. This solved my problem.

 

[AlphaFrog]

I don't know how you are "pulling" data, but you could have the pivot table source data include so called dummy data. Maybe the first five rows of the source data has the values 1 to 5 but no other associated data (column values).

 

[kc.crow]

Thanks for the advice, Lidsavr. That is what I'm trying to do. Unfortunately I'm using Excel 2003 so I don't see that option. Do you know of an Excel 2003 way of accomplishing this?

 

[kc.crow]

Thanks, Alpha Frog. Unfortunately, if I have dummy rows it will skew my other numbers since I'm doing a percent of rows.

 

[sr12345]

Can you "seed" the data with tiny values? What I mean is that let's say you have 200 lines of data you want to analyze. Instead of pasting those values in rows 1 through 200, could you instead paste them down starting at row 10, where the first 9 rows are static and give tiny values (like .00000001) for each of your variables? So, if your variables are A, B, C, D, etc.--and you have no values in the actual data for B, the .0000001 value in line 2 takes over, so that it will still plot. If there are values for B, adding this tiny amount won't affect your total in any meaningful way.

 

[Lidsavr]

kc.crow,
I have posted a picture (click link below) that shows how to make fields that do not have data appear in the pivot table (in Excel 2003).

Picture URL: https://docs.google.com/leaf?id=0B8ZFmctoHTEBZjRhNTRlYzItMDg3Ny00OTYxLWJmN2YtZDViMjI5ODEyZWU0&sort=name&layout=list&num=50

NOTE: I did not make any of these parts from my customer in September through November of 2010. I need to report on this, so I checked the box to get the pivot tables that you see in the picture. It allows me to create my chart, show the months, and in this case, no data will appear. In other similar charts where one month has no data, all months appear, but the single month does not have data

Here is the step by step method for Excel 2003:

1. Right-click one time on the field that you want to force to show and choose "Field Settings."
   
2. A modal window will pop-up.
   
3. Check the box in the lower-right corner of the modal window.

Please respond back if you are still having problems with this.

Lidsavr