PDA

View Full Version : Pivot Table - Blank rows/columns



nikki
10-01-2008, 01:58 AM
Hi,

Is there any way to show blank rows or columns in a pivot table e.g.

Sum of frequencyNameConfectionaryDetails
A
B
C
CrispsSalt & Vinegar
5




Cheese and Onion



2


Ready Salted



3

Chocolate
Maltesers

5




Toffee Crisp

3

2


Twix





Sweets
Skittles

1




Starburst



3


Haribo

4



I need to do this because the format needs to be the same each time for the purpose of copying and pasting from a pivot table using a macro.

I really am not sure if this is possible (very annoying if it isn't! ), but i am sure that if it is, it will be something very simple!

Thank you very much in adavance

Nikki

nikki
10-01-2008, 01:58 AM
sorry - the pivot table i pasted in seems to have not worked!! - i will try again.

please see attachment.

Thank you!

Bob Phillips
10-01-2008, 02:18 AM
As long as you have at least one value in each Name category, it should appear as you wish.

nikki
10-01-2008, 02:27 AM
Ok thank you.

So is there any way that the pivot tbale will pick up all values for a variable irrelevant of whether they appear in the data?

for e.g. Twix, not being present for person A,B or C... but present for person D and E, so that within the pivot (previously attached) the field 'twix' still shows even though person D and E are not within the pivot table?

I assume this isn't possible so i will have to think of another way around it.

But thank you very much for the helpful and quick reply.

Nikki

Bob Phillips
10-01-2008, 02:33 AM
I think you would be far better to create a sample table and try it rather than speculate about what may or may not happen. Then you can ask specific questions.

nikki
10-01-2008, 02:37 AM
Hi

I am asking a specific question (maybe i am not being clear?)... I want excel to produce the blanks within my pivot table and i am asking whether there is a way to make it to that, becuase at the moment it doesn't and i am unable to find a way, within various options, that produce the result i require.

Thank you

Bob Phillips
10-01-2008, 03:42 AM
As I said, as long as you have at least one entry for every name category, you will get the blanks. If you don't have such data, you will need to create it.

nikki
10-01-2008, 03:45 AM
Ok, thats great - thank you.

Nikki

nikki
10-02-2008, 04:15 AM
Just in case other people have this issue - i have actually found out a way to do this without adding an entry for each category (which would take a long time with the amount of data i have). - As i thought, it is very simple:

In your pivot table, double click on the variable name (in grey) that you want to show all entries for, even if they are blank, and tick the box at the bottom 'show items with no data'.

Simple, but very helpful when it comes to needing uniform looking pivot tables to paste from, within a macro.

Bob Phillips
10-02-2008, 04:48 AM
I wasn't suggestint that you create every combination, but just one in each category. If you don't have an item in a category, you can't show blank values, there aren't even blanks to show.