You are here:

Excel/Percentage Formatting

Advertisement


Question
I'm trying to format a cell with percentage formatting so that when I enter a fraction of a percent, I won't have to enter two leading zeros.  For example, .22% now has to be entered as .0022; if I enter .22, then it comes out as 22%.  Any suggestions?

Answer
Jim

Of course the reason that .22 comes out 22% when you enter it is because .22 is in fact twenty two percent not .22 percent, excel just converts the fraction that you enter into a percent.  So to get around that you can use a formula that will divide by the number of integers that are necessary to produce the result you want.  If you want .22 to equal .22% then you can divide by 100.

So in the column that you want to have the percentage appear (assume its column D), you might have the formula +B divided by C,  and in C you have the number 100.  In B you enter the .22.  So the result in column D is .22%.  (after you have formatted the column using percentage and two decimal places)Try this out and you will see what I mean.

Another way to do it is to use the formula +B&C, where in B you put your entry of .22, and in C you put the % sign.  This formula will put the two together and give you .22%, but the result will be text and not numerical.


Hope this helps.

Richard  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Roberts

Expertise

Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.

Experience

Have been working with Excel for about 20 years primarily in accounting and financial areas.

Education/Credentials
BA, Certified Public Accountant

©2014 About.com. All rights reserved.