Pivot Table - Exclude Rows with Zero Values

[tshaffer03]

In Excel, we have data on each individual by month on what project that person is assigned.

For example (very simplified)
Name - Project ID - Jan Forecast (0-100%), Feb Forecast, etc through December
John Smith - ID 12345 (Project X), Jan 100%, Feb 50%, Mar 50%, etc

When I create a Pivot Table and only include forecast data for June - December in the "data" section of the Pivot Table, rows still appear in the Pivot Table for an indivdual but the values are blank (zero) for June - December. When I drill down further, it looks like data is assigned for a month which has not been requested (like February) but zero forecast for June - Dec.

The blank lines are causing confusion. I haven't found a way to suppress these rows. Is there a Pivot Table option I'm missing?

If I'm not asking the Pivot Table to report on Jan-May data, why does it appear to include rows which contain Jan-May data in the Pivot Table report?

Any suggestions would be appreciated. I consider myself an intermediate/advanced user of Pivot Tables but this sure has stumped me. (Hopefully, this is something very simple that has just been overlooked).

 

[Carvel]

Could you post your data? I'm having a hard time visualizing your problem.

- Kevin

 

[saurabh726]

well you can do one thing...whatever its showing blank for ex in results it showing blank...u will see results button out there...click it...u will see a kind of drop box with results and they all have been checked...uncheck the blank one and u wont see the blank data...

Saurabh

 

[tshaffer03]

Full Name Smith, Jane
Data
Project Name June Jul
11-Project X 1.0 1.0
12-Project Z - -
Grand Total 1.0 1.0

Sorry, I can't get a screen shot pasted. Above is a simple example. The Pivot Table is created to select the name from the drop down list and show the project assigned and the values for June and July. In this case, there is a row of data for Jane Smith with Project Z in the database. The values for June/July are zero. There is also a row for Project X with 1.0 (100%) assigned for June/July.

However, the desired outcome of the Pivot Table is to only display rows with data values (greater than zero). Is there any way to suppress zero values (i.e. automatically suppress the "Project Z" row in the Pivot Table?)

Thanks!

 

[Ecoti]

If possible you might want to rearrange the data in this format:

Name Project Month Percent

This way you can put month on the column axis and percent as the data field, this will remove your problem of "empty" projects showing up.

 

[Grootz]

I am having the same problem. I don't want to 'untick' columns or rows as the data will be unpdated monthly. Rearranging the data that the table runs off does not help either as I need column and row headings.

I have song titles as row headings and record companies as column headings with Royalty payments as the data. To further complicate things I have different compilations with different mixes of songs and record companies (thus the blank data columns and rows).

Can anyone help?

PS sorry if it is bad form to tag another question onto this one instead of an answer.

 

[PaddyD]

a general solution to getting rid of unwanted data rows from pivot table summary calculations:

1) define a formula that will flag each row in the data. for example, if you wanted to exclude records where the value in one column was less than 10, a formula might be:

=a1<10

...this will flag the data records true / false

2) include the new 'flagging' column as a page field in the pivot & select the appropriate value.

 

[Panchito Mandefua]

Hi,

You can use power query and change all the empty answers to NULL that solved it for me.