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.

 

[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.