Pivot Tables - Leaving out blank rows

[Lewis Clark]

Hello, Group!

The first table below shows some of my raw client data. The columns represent names, their associated profit center (PC) and several account numbers that are common to each profit center. When I created a pivot table (see second chart below) it includes accounts within each profit center that have no activity in this period.

Is there any way to create a pivot table with only those rows (profit center and account) that have data? I can't hide the rows as I need to know when the profit center changes unless I add a column with the profit center number only and copy it down all nine rows associated with that profit center.

Thanks in advance for any assistance you can provide!



Raw Data:

Name PC 5644- 5641- 5632- 5633- 5643- 5637- 5629-
Adames, Felix 10
Adames, Felix 10
Albano, Justin 25
Aldrich, Rob 10
Aldrich, Rob 10
Allen, Chris 10 2,221.50
Allen, Chris 10
Alston, Leon 10 40.50
Alvarez, Gregory 10 42.16 960.33 25.36



...and so on for about 400 rows.



Pivot Table Summary:

PC Data Total
10 Sum of 5644- 42.16
Sum of 5641- 178.50
Sum of 5632- 1,739.57
Sum of 5633- 339.25
Sum of 5643- 524.18
Sum of 5637- 1,000.00
Sum of 5629- 5,270.09
Sum of 5430- 293.08
Sum of 5480- 180.44
25 Sum of 5644-
Sum of 5641-
Sum of 5632- 154.41
Sum of 5633- 94.18
Sum of 5643-
Sum of 5637-
Sum of 5629-
Sum of 5430-
Sum of 5480-


.... and so on

 

[Spreadsheet]

At the top of you pivot table column there should be a down arrow. Click
on it and a box appears listing all of your categories with checkmarks
beside them. THere should be one category listed as (blank). Just
remove the checkmark beside it.

 

[Lewis Clark]

Thank you for your reply. Unfortunately, it doesn't quite do what I had hoped.

The column with the profit centers has a down arrow, and "blank" is an option, but since every profit center has some activity unchecking the "blank" option correctly does not hide any profit centers.

There is another down arrow for the column of account numbers, but there is no option for "blank" to uncheck. Every account number has activity, so this is probably correct as far as Excel is concerned.

Some account numbers have activity in only some of the profit centers, as I tried to show in my original post. Within the section for each profit center, I would like to hide the line for any account that has no activity for that profit center.

I hope this description, combined with my original post, explains what I would like to be able to do.

Thank you very much!

--


At the top of you pivot table column there should be a down arrow. Click
on it and a box appears listing all of your categories with checkmarks
beside them. THere should be one category listed as (blank). Just
remove the checkmark beside it.

 

[Debra Dalgleish]

Instead of the data layout you currently use, you could change it so
there's only one column with account information:

Name PC Account Amt
Allen, Chris 10 5629 2,221.50
Alston, Leon 10 5629 40.5

Then, create the pivot table from the revised table, with PC and Account
in the row area, and acount in the data area.

 

[Lewis Clark]

Debra,

Thank you for the suggestion. Unfortuately, it would be too much work to change the way the client is entering the data. Already tried that.

Guess we will just live with it.


--

Instead of the data layout you currently use, you could change it so
there's only one column with account information:

Name PC Account Amt
Allen, Chris 10 5629 2,221.50
Alston, Leon 10 5629 40.5

Then, create the pivot table from the revised table, with PC and Account
in the row area, and acount in the data area.