Pivot Table (blank) cells

kcompton

Board Regular
Joined
Mar 16, 2009
Messages
209
I am using Excel 2010.
My data source has blank cells.
The Pivot Table displays (blank) for those cells.
I have tried right clicking on a cell in the Pivot Table, selecting Pivot Table Options, Layout & Format, toggling on "For empty cells show:" and put in n/a. Nothing changes on the Pivot Table. I have refreshed the table

What is the correct procedure to eliminate the "(blank)"s in the Pivot Table or replace them with "n/a"

Thank you.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Perhaps your blank cells aren't actually blank. Cells often contain unseen characters particularly when downloaded from an external database (spaces, carriage returns, etc.) I would empty the "blank" cells before pivoting them by using the CLEAN or TRIM function. Use a help column and simply use =CLEAN(A1)...and run the formula down as far as you need. Then copy the results as VALUES back into the column A (in this example). Good luck.
 
Upvote 0
You can just overwrite (blank) in the pivot table with whatever you want it to be, eg Other, by editing the cell.
 
Upvote 0
Thank you all-
1) The blank cells in the data source are truly blank. I ran a trim spaces and found no leading or trailing spaces
2) Replacing the blank cells in the data source with N/A works but I am wondering if there is another option that does not include changing the data source.
3) How do you edit the cell in the Pivot Table? This pivot table has many (blank) cells.

Why doesn't entering "n/a" in the "for empty cells" in the Pivot Table format Options work?

Thank you
 
Upvote 0
The 'for empty cells' option affects the Data area, not the row/column/page field areas.
 
Upvote 0
Thank you for your clarification on the "show empty cells" option. I did not understand that it only works on the Data or Values fields. I reorganized my pivot table and that option now works.
Thank you all for your help
 
Upvote 0

Forum statistics

Threads
1,225,073
Messages
6,182,707
Members
453,132
Latest member
nsnodgrass73

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top