Excel 2007 | Remove blank fields from pivot tables

1

3

Every time I create a pivot table (available for all Excel versions) I get one or several blank fields. How can I get rid of them?

One workaround I used was to select the blank field, right click | Filter | Hide Selected Items. This can solve my problem but I need to do it manually... Is there a way to automatically hide/exclude the blanks?

Nicu Zecheru

Posted 2009-08-18T15:17:39.843

Reputation: 5 234

Answers

1

When selecting the cells to create the Pivot Table from, don't select any blank rows or columns.

Edit:

Here's what I came up with in VBA:

Private Sub CommandButton1_Click()

Dim pt As PivotTable
Dim pi As PivotItem

Set pt = ActiveSheet.PivotTables("PivotTable2")

For Each pi In pt.PivotFields("B").PivotItems
    If pi.Value = "(blank)" Then pi.Visible = False
Next pi

End Sub

Where "PivotTable2" is the name of your Pivot Table, and "B" is the name of the Row Field you're trying to eliminate blanks from. You can modify the code to hide other types of fields.

Lance Roberts

Posted 2009-08-18T15:17:39.843

Reputation: 7 895

Right, but there are some fields that need to be blank (or just there is no value for these fields). I need to find a way to automatically exclude the blanks (data range is already set, only new data is added and then the pivot table is refreshed) – Nicu Zecheru – 2009-08-18T16:37:52.800

1

I had this problem. My solution was to create defaults for the cells that would be blank. If it's meant to be text use "Empty" or if it's a figure than input "0".

It's not pretty but it gets you out of the rut and moving on. Otherwise I think you'll always have blanks.

RocketGoal

Posted 2009-08-18T15:17:39.843

Reputation: 1 468

you're right, this is a good workaround – Nicu Zecheru – 2010-03-30T09:09:36.053

0

This usually happens if you select blank rows or columns in your source data. Usually for me it happens because I've been lazy and selected whole columns instead of just the area with the data in it.

Col

Posted 2009-08-18T15:17:39.843

Reputation: 6 995

I do need all the data in the table. However the columns don't change only new rows are added and then i refresh the pivot table – Nicu Zecheru – 2009-08-18T16:36:45.720

-1

Use condition formatting to make the blank the writing the same color as the background color. the blank is still there but appears to be gone

user231602

Posted 2009-08-18T15:17:39.843

Reputation: 1