Excel - Pivot Table - Show All

anthonyT

New Member
Joined
Oct 13, 2008
Messages
3
Hi All

Need to link data in a pivot table to a formated page. As such it would be handy to get the pivot table to remain the same size and have the data appear in the same order regardless of whether there is actualy any reslust...

EG
Current pivot table

Rate Count
AAA 2
AA 4
A 3
B 1


Required result:

Rate Count
AAA 2
AA+ 0
AA 4
AA- 0
A+ 0
A 3
A- 0
BBB+ 0
B 1
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,346
Hi, Anthony.

If you must stay with a pivot table, maybe the easiest way is to use GETPIVOTDATA formula in the destination cells. And return 0 if there is an error with the function. Along the lines =if(iserror(getpivotdata(),0,getpivotdata())

HTH, Fazza
 

anthonyT

New Member
Joined
Oct 13, 2008
Messages
3
cheers Faza

Worked it - went with an if(isna(vlookup...with a 0 as the errror.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,749
Members
406,495
Latest member
Arlind Elezi

This Week's Hot Topics

Top