• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Custom format for a percentage column - Excel

I have a column defined as percentage.  Is there a way to get '-' do display in the field if the value is 0.00%.
0
morinia
Asked:
morinia
  • 3
  • 3
  • 2
2 Solutions
 
armchair_scouseCommented:
Yes there is.  You need a custom format.  Custom formats allow you to specify how to display a value for positive, negative, zero and text values.  you separate the formats with a semi-colon.

Right-click on the cell to be formatted, select Format Cells, select Custom from the Caetgory list, then in the box below 'Type:', type in:

0%;0%;-

This will format positive and negative values as percentage, and zero values as dash.
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
Thanks,  

Can you tell me how to format a value less than .00005 as 0?
0
 
armchair_scouseCommented:
You can use Conditonal Formatting for this.  Highlight your range of cells to be formatted, then select Conditional Formatting (from the Home tab), then:
- select 'Format only cells that contain'
- edit the rule description so that it reads 'Cell Value less than 0.0005'
- click the Format button
- select Custom format, then type in 0 in the format box, then click OK
- click OK again to apply the Conditional Formatting

That hopefully will do the trick.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Patrick MatthewsCommented:
You can actually do it without Conditional Formatting.  Just use a custom number format like this:

[<0.0005]0%;0%

If you wanted, say, 2.47%...

[<0.0005]0.00%;0.00%
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
The conditional formatting works perfectly.  Just one thing when accounting format is used the "-" is centered.  Is there a way with conditional formatting to get the "-" in the center?Currently it is right justified.

When there is a number I don't want it centered.  I was just wondering if I can get the "-" centered.
0
 
Patrick MatthewsCommented:
The "-" is NOT centered when you use the Accounting format.  Rather, the "-" is positioned to align with the decimal point, or to be flush to the right is there are no decimal places shown.
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
Am I correct that there is no way to center the "-"?
0
 
Patrick MatthewsCommented:
Not to my knowledge
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now