pivot table formats

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

does anyone know how to format a pivot table so there are
no blank cells? see below
I curently get
type A X 12
Y 20
Z 32
type B X 1
Y 10
Z 27
I would like to get
type A X 12
type A Y 20
type A Z 32
type B X 1
type B Y 10
type B Z 27
 
Joe

A pivot table has a hierarchical structure, so that's the way it is I'm
afraid. Looking at your example, have you tried running Data>Subtotals...?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Joe

Looking at this again, if it is a chart you need, use the pivot chart
function, not a standard chart and all should be ok. It's an option as you
build the pivot table. (In the first wizard screen)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
I am using a pivot table to sort qtys of data by
area,size,and type. My intention is to copy the pivot
table and paste as values before I send it to the vendor.
There is an option in the pivot table options to "merge
labels" if I check this box it will merge all labels into
a single cell. I don't want to merge them I just want to
repeat the label in each cell.
I may just have to write a marco to do what I want.

thanks
 
Joe

If you don't get it done, by coincidence I have to write one today at work
for just the same use for our marketing team. (Filling cells below with the
data above). Email me if you would like the code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Don't know if Nick Hodge has already shared the relevant code with you.
The following code assumes that the PT has at least one empty column
and row surrounding it (or is at the corresponding edge of the
worksheet). In addition, there is enough space to the right of the PT
to make a copy of it.

Select any cell in the PT and run the code.

Sub Macro1()
Dim PTRange As Range, DestRng As Range, EmptyCells As Range
Set PTRange = ActiveCell.CurrentRegion
PTRange.Copy
Set DestRng = PTRange.Offset(0, PTRange.Columns.Count + 1)
DestRng.PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
With DestRng
Set EmptyCells = Range(.Cells(2, 1), _
.Cells(.Rows.Count, .Columns.Count)) _
.SpecialCells(xlCellTypeBlanks)
End With
EmptyCells.FormulaR1C1 = "=R[-1]C"
With DestRng
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End Sub




--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top