Free Microsoft Excel 2013 Quick Reference

Hide blank or zero cells in PIVOT table

I was wondering if anybody can help me with this:

Is it possible to hide cells that has zero value or that are blank (in PIVOT)? Like to show only cells that has value more then 0 and are not blank?

Thank you!

Post your answer or comment

comments powered by Disqus
I'm trying to filter the "Blank" cells in my pivot table as a "page" filter,
but I'm not getting any success. On the contrary of showing the "blank" cells
the Pivot Table shows all the values. I've already tried to format the cells
in the source table as "number", but it didn't work. Any suggestions?

I'm trying to filter the "Blank" cells in my pivot table as a "page" filter,
but I'm not getting any success. On the contrary of showing the "blank" cells
the Pivot Table shows all the values. I've already tried to format the cells
in the source table as "number", but it didn't work. Any suggestions?

I recently ran into an issue while trying to group data by date in a pivot table; the error I get from excel when I right click on a date in the date column and select group is "Cannot group that selection." I did some searching on Google and found that you can't group empty cells, of which my pivot table has many.

This is a pivot table for my personal finances: I have 49 spending categories that I'd like to summarize by month/year or quarter/year. The pivot table is generated from a spreadsheet that resembles the following:

| Date | Funds Out | Funds In | Category |
| 01/01/2011 | $2.20 | | Dining:Coffee |

Obviously there are going to be many, many blank cells in the pivot table because not every category will have a transaction on the same day as every other category. Further still, many expense-type transactions are not likely to have many, if any, "Funds In" entries (but those will appear when I've returned an item for refund, for example.) Likewise, income-type transactions aren't likely to have any "Funds Out" entries. I could simplify things a little by combining "Funds Out" and "Funds In" and just using -/+ numbers in a single column, but that still doesn't solve the issue with the categories (in fact, I've tried creating a pivot table leaving out the "Funds In" column.)

Is it totally impossible to create pivot table groups when there are empty cells? As far as I'm concerned, those cells could be "$0.00" when the grouping calculations (sum) are done. I've changed the table options to show 0 for empty cells, but I think that's just a display option and doesn't reflect in the underlying blank cell, so doing that still doesn't let me group the data.

This must be a common type of report for any business (or individual) - a summary of expense by category per month or quarter. How do I deal with the empty cells? I could probably create a pivot table per category, but that would be labour intensive and seems unnecessary. Why can't excel just treat empty cells as $0.00?

I'm using Excel as part of Office for Mac 2008, so VBA macros aren't available. I'm considering buying the latest edition of Office for Mac so if you know that this isn't an issue with the latest version that'd be more incentive for me to update.

In Excel 2003, when I link a cell in pivot table, it copies Getpivotdata
And when I drag or copy this cell, again it copies the formular.

Can you help how to just get the value, then be able to drag to link next
cell and its value?


I AM TRYING TO USE AN IF FUNCTION TO LOOK FOR THE LOWEST (MIN) NUMBER IN A RANGE OF CELLS THAT IS LESS THAN MY SELECTED CELL. EXAMPLE: =IF((MIN($F$4:$F$18)<$I$4),TRUE,FALSE). Lets say that $I$4 equals 60, and the lowest number in the F4:F18 array is 61. This should return FALSE, but since some of the cells in F4:F18 have "0" or are Blank, the formula returns TRUE even though I DO NOT WANT IT TO! However, I must have some "0" or Blank cells in this F4:F18 cell range.

So, I tried to combine AND with IF to argue something like this: =IF(AND(MIN($F$4:$F$18)>0,MIN($F$4:$F$18)<$I$4),TRUE,FALSE). This way I get TRUE if values in my cell range are "greater than 0" AND less than my selected cell ($I$4). PROBLEM IS THAT this blasted Excel will only return FALSE, even though both these arguments are TRUE!! How can I get this argument =IF(AND(MIN($F$4:$F$18)>0,MIN($F$4:$F$18)<$I$4),TRUE,FALSE) to return TRUE when some cells in my array have a "0" in them?


Happy NewYear!

I have a table (T23:Z24) witch normally don't contain any values.
But occasionally one or more cells might display a predefined text "Avvik" according to a formula in each cell in the table. In a different cell (R29) I would like to display "x" if one or more cells in the table (T23:Z24) are displaying the text "Avvik". So I made a formula for R29 that works: =IF(OR(T23="Avvik",U23="Avvik",V23="Avvik",W23="Avvik",X23="Avvik",Y23="Avvik",Z23="Avvik",T24="Avvi k",U24="Avvik",V24="Avvik",W24="Avvik",X24="Avvik",Y24="Avvik",Z24="Avvik"),"X","")
But this is not a good formula. I'm sure there's a more simple way to solve this, but unfortunately I don't know Excel that well.
Anyone got a simple solution?


How do I hide rows containing zeroes or blanks in pivot tables?

hello all,

I have a series of 5 tabs each with successively more pivot tables in them (tab 1 has 1 pivot, tab 2 has two pivots, etc) In addition, each successive pivot is more complex. In the last tab, pivot 1 has one field, pivot 2 has two fields, etc up to pivot 5 which has five fields.

There are so many values in these pivots that I only want to show rows with non-zero values.

I've successfully written the following code to zero out the values, but it's really slow.

Each time I run the code, it recalculates the value of the pivot. Because I'm doing thousands of updates, this takes many hours to complete.

Does anyone have a suggestion to keep the pivot from refreshing after each update. I'd like to get it to refresh all at once at the end of the script.

Either that or another more elegant solution???

' Case Item is 0 but Is Visible
If Cells(RowCounter, ColCounterItem) = 0 And Cells(RowCounter, ColCounterItem + 1) = 1 Then

ColCounterTable = ColCounterItem
Do Until ColCounterTable > 79
On Error Resume Next
PivotTableName = Cells(7, ColCounterItem - 1)
PivotItemName = Cells(RowCounter, ColCounterItem - 1)

With ActiveSheet.PivotTables(PivotTableName).PivotFields(PivotFieldName)
.PivotItems(PivotItemName).Visible = False
End With

ColCounterTable = ColCounterTable + 3


End If

Hello Excel Masters,

I'm trying to write a macro that will hide all zero results values of a pivot table. Is there an easy way to do this directly in the pivot table itself, or do I have to write code that looks at each result and deselects it if it returns a zero?

Many Thanks!!!



I would appreciate any help.

In my excel worksheet I have a pivot table that I filter out entries. I have an approval column and column to state if its been approved or not. In order to check if an approvers has approved the work, i look to see if their initials are next to their name. However, if it is blank that will indicate it is not dealt with.

I have over five approves that i need to check regularly. So i have used a number of COUNTIF and COUNTBLANK functions.

I have found out that i cannot use a COUNTBLANK function in a pivot table as it will count all the cells from B1:B10000 giving me an inaccurate result. I want it to count the rows that has data in the first column. I have also realized that when i add a new entry, i cannot tell my funtions that there is a new entry so count this one aswell.

My second problem is, when filtering out the pivot table with just 'Approver A', i am unable to find a function that will count the number of approved and blank cells there are. Same with 'Approver B'...

Can anyone help me please!!!

Thank you,



How could I make the zero balances hidden in a pivot table without deleting the source data or without using macro please?

Many thanks.

Hello, I have a PVT with backup data of about 20 columns, the Pivot Table (PVT) itself is only using 5 columns but I need the 15 in the background for later use so my question is...on the PVT with the 5 columns, how to I make the zeros disappear or for the practice not show up on the PVT if the value is zero? If I sound confused...I am. I tried and if/then statement in my data but I keep getting stuck on the "then" part. =if(a1=0,"",a1>0,???) need the ??? to be the value in the field that was imported to excel.

I have been searching various forums to see if this question has been answered, but have been unable to find anything that I could adapt.

I am using Excel to create a pivot table, based on information provided to me daily. Therefore, the size of the Pivot Table will vary everyday, as will the column and row headings.

Once the pivot table has been created, I want to extract the column and row headings and the sum of only the cells which contain a value.

These items would preferably be then given to me in 3 separate cells.

I can't quite see how to attach a sample workbook to this post, but am happy to do so if someone can tell me how!!

To try and describe it better, my current pivot table (beginning in cell A1) has 7 columns (the totals are in column I) and 5 rows (the totals in row 8). The last column and the last row are both called '(blank)', and this will always be the case (could use to end a loop?)

I only have values in cells B6, C3, D3, E3, F3, F4, F5, F6 and G3 (excluding the totals cells).

I would like to end up with a summary table showing me the following;
A6 row name, B2 column name, B6 value
A3 row name, C2 column name, C3 value
A3 row name, D2 column name, D3 value
A3 row name, E2 column name, E3 value
A3 row name, F2 column name, F3 value
A4 row name, F2 column name, F4 value
A5 row name, F2 column name, F5 value
A6 row name, F2 column name, F6 value
A3 row name, G2 column name, G3 value

Remembering that the table can and will change size both row and column wise every day, and the row and column titles will also change daily.

If anyone can help me with this, I would be very grateful!

Many thanks


I would like to know how to hide the cells that contains zero values on my pivot table. The catch is that I have two columns that contains values...I tried hiding the columns that contains zero values but for some reason it also hides columns that contains values. For example: I have three columns on my Pivot Table:

Category # ##
Alpha 1 1
Bravo 0 1
Charlie 2 2
Delta 3 0

When I tried hiding the zero values under column #, it hides the whole row and it throws my totals off and vise versa with column ##.

Any ideas? Thanks!!

I am trying to create a table using lookup(the lookup table has 20 columns and in excess of 4000 rows)
I have a lot of zero's or blanks being returned to me which creates a table just as big as the lookup table. Have tryed a pivot table then a lookup but there are too many columns for it to cope with.
Help - am no good at macro's!!

I'd like to be able to count blank cells in my Pivot Table - I know how to do
this using array formulas, but I was wondering if there was a feature like
this buried in the Pivot Table. Thanks.

It's been mentioned here about filling in blank cells automatically in a
list of data.

Can this be done within a pivot table automatically?

I create pivot tables but the row headings are grouped so that some of the
items have blank cells next to them. This is frustrating when you want to
run a pivot table over the top of the original as it considers these cells

As a result you have to copy and paste the row headings into the blank cells
and this can be time consuming if you have many lines of data.

Can it be done automatically within the pivot table?

Can pivot tables be automatically run over the top of another with the blank
cells considered automatically to have data within them (i.e. the blank
cells created by the grouping of items within row headers?)

Thanks in advance for any comments,


hi all,

i've attached a pivot table where some cells are empty (see GCP tab). i want the empty cells to appear empty, and not as they currently appear, with the word "blank" in parentheses. i am aware that if i enter 2 spaces in any cell and press return, all empty cells in that column will also appear empty. is there any other way to have empty cells in a pivot table appear empty? please help.

thanks, everyone.



I set up a "row" variable called Sales_Rep in column A of a pivot table, with column B representing another row field (State) and other columns representing column and data fields.

It seems like the default for a pivot table is to show each rep's name only once in column A, leaving subsequent blank values in that column until the next rep's name is to be shown. While there are workarounds outside the pivot table, is there a way within the pivot table structure itself to display the same Rep name multiple times in column A, instead of leaving those blank cell entries there?

Thanks in advance.

- Steve J


What I want to do is probably very easy. I just can't find out how to do it. (Maybe I'm just too tired..... )

I would like to see, in my pivot table, the number of occurences where there's a defect for a part witch works great (can't be easier...) But I just don't understand that when I ask for Empty cells, the number of empty cells just appear as blank. (so I don't know how many parts I have without defect).

Basically, I would like to see very someting like the table shown under the pivot table in my exemple spreadsheet.

Thanks in advance for your help

so i have worksheet with a lot of data which are not very much in order so i need to change text manually which is very hard and almost imposible to do it withou mistakes!
simple example just to show what i need:
in row A have croatia,slovenia,bosnia,hungary,italy,germany,austria.... and more other countryes.
in row B have more different data - months in year - january, february, march...
under those months have monday, thursday, wednesday etc.
So sometimes i need to change italy/february/thursday into italy/february/morning
It's a larger data and more complicated than in this example so it's really difficult and hard to do it all manually or by filtering it to look where this criteria is and to input it manually ...

So by using pivot table it looks perfectly and everything is automatically organized as it should be. Problem is that i can't change text/values in pivot table. I would like to change cells which are showed in pivot table because it is well organized and i see it easy what to change(retype), but can't do it in pivot table in order to change it also in cells. What is easiest way to solve this problem?


I have a file that I am importing that has warehouses across the top and the
inventory items down the side. Not every warehouse has every item. When I
try to do a pivot table on the warehouse (part number in row area) I get
EVERY inventory item, even those with zero values. Is there any way,
excluding macros, to have the rows where the total inventory for that part
number is zero not display? Thanks for any help.

I dunno if this was done already but,

One way i use pivot tables is often to layout some complex Data that i want
to VLOOKUP into afterwards. I know what i could use GetPivotData, but it
requires too much specific information at times to be an efficient way to
lookup my values.

I also often add some information from other data source in different sheets
(tables,queries,pivots,etc) in the columns following the pivot table columns.

The way the data is display is very efficient but accessing that data for
use in formulas often gets tricky.

Picture this display of pivot (not the real layout of pivot):

A | B | C |
1 | region | salesperson | Sum of Sales |
2 | North | John | 100$ |
------+ +----------------+--------------------+
3 | | Paul | 50$ |

if in D2 and D3 i add the following formula, i get into problems

=VLOOKUP(A2&"-"&B2,MarginsPerRegionPerSalesPerson, 2, 0)

The proble comes on Row 3. Since A3 is blanked because it is grouped with
A2, A3's value will be empty.

Would it be possible that references to any cell in a grouped cell range to
return the value of the grouped cell, instead of the value of the looked up
cell ?

What i would love is for A3 to return "North" when i access it. A3 should
know it is grouped with A2 so the real value of A3 is really the same as A2.

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

Hi All,

Can someone please help me.

Im doing a very simple formula

=D7-C7 etc etc etc

But on the cells in column c or d occasionally it will be blank or "0".

Everytime one of these cells is blank or zero i get the response #VALUE

Is there a formula I can use so that instead of getting the error message

No luck finding an answer? You could always try Google.