• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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