How to show percentages in Excel

In this short tutorial, you will find many helpful details about Excel percent format and learn how to format existing values as per cents, how to show percentage in empty cell and change numbers to percentages as you type.

In Microsoft Excel, displaying values as percentages is very straightforward. To apply the percent format to a given cell or several cells, select them all, and then click the Percent Style button in the Number group on the Home tab:
Click the Percent Style button to display numbers as percentages in Excel.

Even a faster way is pressing the Ctrl + Shift + % shortcut (Excel will remind you of it every time you hover over the Percent Style button).

Although formatting numbers as percentages in Excel takes just a single mouse click, the results may vary depending on whether you apply the percent formatting to existing numbers or empty cells.

Formatting existing values as percentage

When you apply the Percentage format to cells that already contain numbers, Excel multiplies those numbers by 100 and adds the per cent sign (%) at the end. From Excel's viewpoint, this is the correct approach since 1% is one part of one hundred.

However, this way does not always work right. For example, if you have 20 in cell A1 and you apply the percentage format to it, you will get 2000% as the result, and not 20% as you might expect.

Possible workarounds:

  • Calculate numbers as percentages before applying the percent format. For example, if your original numbers are in column A, you can enter the formula =A2/100 in cell B2 and then copy it down to all other cells in column B. Then select the entire column B and click the Percent Style. You will get a result similar to this:
    Formatting existing values as percentage in Excel

    Finally, you can replace the formulas with values in column B, copy them back to column A and delete column B if you do not need it any longer.

  • If you want to apply the percentage formatting to just a few numbers, you can type a number in its decimal form directly into the cell. For example, to have 28% in cell A2, type 0.28 and then apply the percentage format.

Applying percentage format to empty cells

Microsoft Excel behaves differently when you enter numbers into empty cells pre-formatted as Percentage:

  • Any number equal to or greater than 1 is converted to a percent by default. For example, 2 is turned into 2%, 20 into 20%, 2.1. into 2.1% and so on.
  • Numbers smaller than 1 without a preceding zero are multiplied by 100. For example, if you type .2 in a percentage preformatted cell, you'll see 20% in that cell. However, if you enter 0.2 in the same cell, 0.2% will appear exactly as it should.
    Applying percentage format to empty cells in Excel

Display numbers as percentages as you type

If you type 20% (with the percentage sign) directly in a cell, Excel will understand that you are entering a percent and will apply percentage formatting automatically.

Important note!

When applying the percentage formatting it Excel, please keep in mind that it is nothing more but a visual representation of a real value stored in a cell. The underlying value is always stored in decimal form.

In other words, 20% is stored as 0.2, 2% is stored as 0.02, 0.2% is 0.002, etc. When performing calculations, Excel always deals with the underling decimal values. Please remember this fact when referencing percent cells in your formulas.

To see the real value behind percentage formatting, right-click the cell, click Format Cells (or press Ctrl + 1) and look in the Sample box under the General category on the Number tab.
Viewing the real value behind percentage formatting in Excel

Tips to display percentages in Excel

Showing percentage in Excel seems to be one of the earliest tasks, right? But experienced Excel users know that a path to the goal almost never runs smooth :)

1. Display as many decimal places as you want

When applying the percent formatting to numbers, Excel sometimes shows rounded percentages with no decimal places, which may cause some confusion. For example, apply the percent format to an empty cell and then type 0.2 into it. What do you see? In my Excel 2013, I see 0% though I know with certainty it should be 0.2%.

To see the actual percentage instead of a rounded version, you just need to increase the number of decimal places showing. To do this, open the Format Cells dialog either by pressing Ctrl + 1 or right-clicking the cell and selecting Format Cells… from the context menu. Make sure the Percentage category is selected and specify the desired number of decimal places in the Decimal places box.
Displaying decimal places

When done, click the OK button to save your settings.

Alternatively, you can control the number of displayed decimal places, by clicking the Increase Decimal or Decrease Decimal icons on the ribbon (Home tab > Number group):
Displaying more or fewer decimal places

2. Apply a custom format to negative percentages

If you want to format negative percentages in a different way, say in red font, you can create a custom number format. Open the Format Cells dialog again, navigate to the Number tab > Custom category and enter one of the below formats in the Type box:

  • 00%;[Red]-0.00% - format negative percentages in red and display 2 decimal places.
  • 0%;[Red]-0% - format negative percentages in red without any decimal places showing.

Special formatting for negative percentages

You can find more details about this formatting technique in the Display numbers as percentages article by Microsoft.

3. Format negative percentages using Excel conditional formatting

Compared to the previous method, Excel conditional formatting is more versatile and it lets you display negative percentages, e.g. percent decrease, in any format of your choosing.

The fastest way to create a conditional formatting rule for negative percentages is to click Conditional formatting > Highlight cells rules > Less than and put 0 in the "Format cells that are LESS THAN" box:
Excel conditional formatting rule for negative percentages

Then you choose one of the formatting options from the drop-down list on the right, or click Custom Format... at the end of the list to set up for own formatting.

For more information about creating conditional formatting rules, please see How to use conditional formatting in Excel.

This is how you work with Excel percent format. Hopefully, this knowledge will help you avoid a lot of headache when you in the future. In the next articles, we are going to learn how to calculate percentages in Excel and write formulas for percent change, percentage of a total, compound interest and more. Please stay tuned and thank you for reading!

Table of contents

42 comments

  1. I have 7 rows with a dollar amount of doctors' production of goods. I need to get a % of all those at the ends. How do I figure that out on a worksheet?

  2. If I have two totals and I want to shoe the %. Example Total is 60, but only 43 are good. need to figure the 5 of good out of the total.

    Thanks

  3. How would i input the formula to calculate the following;

    Collumns E, F & G - how would i show in G the percentage in F of the amount in E so for example;

    E = 1000
    F = 5%
    G = This should show up as 50

    There will always be amounts in columns E & F

  4. Can someone help me please
    I’m trying to calculate x as a percentage of sales.
    E.g
    Sales are 2,000,000.00
    COG are 25%
    I want to show the financial (500k) as a number not as a percentage

  5. Hi I need to calculate percentages by occupancies
    so if I have 5 people and I have 20 spots, the percentage should 25%, I want to know if there is any simple way to apply a formula here as it is taking me so much time thank you.

  6. Hi
    I need to work out a percentage on KPI measures in our reponse time
    Let say: We had on incident at a area response time are 5min but we respond within 7min so how will I work out my incident response time for VDC for the month
    Let say:We had total of 23 incidents for the month only 22 incidents were within the response time and 1 was not with in. How do I work out my response KPI

  7. If I insert =4/20 the result will display .20 which is correct but if I insert =(22-20)+2/20 the result is .21 which is incorrect. How do I get excel to display the correct answer in to =(22-20)+2/20?

    • Hello!
      When I write in the cell Excel =(22-20)+2/20, I get the result 2.1
      Perhaps your .21 result is due to the number set in the custom format cell.

  8. Sir,
    10% =right(j10,1)
    not show ( % percent sign)

    • $20.00- $18.00 = $2.00/20 * 100 % =10 %. thus you give him a discount of 10%

  9. Dear Madam/Sir
    Good Evening,
    Greetings!
    I need help
    I gave the price to the customer 20.00$ and he gave me offer 18.00$ (how much percentage he reduce the prices?

  10. total class 4
    one student present in class 4
    how to use presentage ratio 100
    send me presentage formula

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)