?
Solved

Custom format for a percentage column - Excel

Posted on 2012-03-14
8
Medium Priority
?
216 Views
Last Modified: 2012-03-14
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
Comment
Question by:morinia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 9

Assisted Solution

by:armchair_scouse
armchair_scouse earned 1000 total points
ID: 37720991
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
 

Author Comment

by:morinia
ID: 37721127
Thanks,  

Can you tell me how to format a value less than .00005 as 0?
0
 
LVL 9

Accepted Solution

by:
armchair_scouse earned 1000 total points
ID: 37721639
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37722372
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
 

Author Comment

by:morinia
ID: 37722441
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37722473
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
 

Author Comment

by:morinia
ID: 37722529
Am I correct that there is no way to center the "-"?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37722559
Not to my knowledge
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question