Quick, clean, and to the point

How to use percentage formatting in Excel

In this lesson we'll look at the Percentage format. The Percentage format is made to display fractional values as percentages. For instance, the value .05, formatted as a percent, will display as 5%.

Let's take a look.

In column B of our table we have a set of numbers in General format. Let's first copy the numbers to the rest of our table.

Now let's apply the Percentage format. Although percentage is listed in the Number format menu on the ribbon, it's faster to use the Percentage button just below the menu.

As we can see, Excel displays the numbers in standard percentage format. 

If we check the options available for percentage in the Format Cells dialog box, we see that decimal places are the only option. Let's set decimal places to 1.

We can also use the buttons on the ribbon to adjust decimal places.

When entering percentages by hand in cells formatted with the General format, be sure to use true decimal values. If you enter whole numbers and then apply the percentage format, Excel will assume fractional values and multiply by 100.

If we instead enter the correct decimal values, then apply percentage, we get the expected result.

However, when cells have the percentage format pre-applied, you can enter either fractional numbers or whole numbers for values less than 100%.

When you enter whole numbers, be aware that Excel will automatically and silently convert the number to a fractional value when you press enter. You can temporarily switch the format back to General to check actual values.

For percentages greater than or equal to 100, Excel does not perform this automatic conversion—you'll need to type in the full number.

You can also type fractions directly using the formula notation. For example, if you know a ratio is 5 out of 8, you can enter this as a fraction using "equals 5 / 8" to get the correct percentage.

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.