Pivot Table: Zeros to Dashes

**GreatLakesJK** · 01-28-2011, 11:27 AM

I have a pivot table with many zeros throughout the sheet, and I would like to make those dashes instead. Any suggestions how I would go about accomplishing this task?

I have tried the Pivot Table Options route, by changing "For empty cells show: -" but that has not worked since these cells are technically not empty.

Thoughts? Much appreciated.

**MarvinP** · 01-28-2011, 11:38 AM

Hi GreatLakesJK,

I rarely say this, but I don't think you can do it. The Summation area of Pivot Tables does number only kinds of things. You want to put text in this area.

I think you only get to pick from this list:
http://msdn.microsoft.com/en-us/library/Aa195239.aspx

**GreatLakesJK** · 01-28-2011, 11:43 AM

Ugh. I feared that. Are you sure there isn't some type of custom formatting you can implement to make this happen?

**MarvinP** · 01-28-2011, 11:49 AM

Instead of trying to put dashes, look at table formatting.
http://office.microsoft.com/en-us/ex...010013769.aspx
This should give you a better visual affect than dashes. Also if you are getting zeros and don't want them, simply filter them out of your data.

Perhaps some of the smart gurus have a suggestion.

**DonkeyOte** · 01-28-2011, 01:29 PM

Note you can apply a Custom Number Format to your Data Fields and simply set format for 0 to be -

Example Format: #,##0.00;[red](#,##0.00);-

**MarvinP** · 01-28-2011, 01:44 PM

DonkeyOte has proven me wrong one more time.

He keeps teaching me things about Excel I never knew

That is good.

I hate it when I give the wrong answer!!!

I need to go study number formats a while.

**DonkeyOte** · 01-28-2011, 01:50 PM

some references re: Custom Number Formats:

http://pubs.logicalexpressions.com/p...cle.asp?ID=414
http://www.ozgrid.com/Excel/CustomFormats.htm
http://simoncpage.co.uk/blog/2008/09...er-formatting/

**MarvinP** · 01-28-2011, 03:16 PM

I find in your second link above that you can do a custom format for values that equal to zero.

The custom format of

[=0]"---"

will replace every zero with three dashes.

Thanks for the links. I'll try to give better answers in the future.

Search all my answers for "smart gurus" and correct them with better/right answers.

I also find a way to insert a dash or any text for blank values by checking the
For Empty cells show: show on the PivotTable Options dialog.
Pictured at http://www.pivot-table.com/pivot-tab...07-pivot-table