Percent Style

In this brusk tutorial, you will observe many helpful details virtually Excel percentage format and learn how to format existing values as per cents, how to prove percentage in empty jail cell and change numbers to percentages equally 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
Percentage Manner
button in the
Number
group on the
Habitation
tab:

Even a faster mode is pressing the
Ctrl + Shift + %
shortcut (Excel will remind you of it every fourth dimension 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 utilize the percent formatting to existing numbers or empty cells.

• Format existing values every bit per centum
• Utilise percentage format to empty cells
• Make Excel show percentages as yous type
• Tips to display percentages in Excel

Formatting existing values equally percent

When you apply the
Percentage
format to cells that already contain numbers, Excel multiplies those numbers past 100 and adds the per cent sign (%) at the end. From Excel’due south viewpoint, this is the correct approach since ane% is i office of one hundred.

Yet, this way does not always piece of work right. For instance, if you accept 20 in cell A1 and you utilise the percentage format to information technology, you will get 2000% equally the result, and not 20% every bit you might await.

Possible workarounds:

• Summate numbers every bit percentages before applying the percent format. For instance, if your original numbers are in column A, you tin can enter the formula
  =A2/100
  in cell B2 and then copy information technology down to all other cells in cavalcade B. Then select the entire column B and click the
  Per centum Style. Yous will become a issue similar to this:
  

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

• If you lot want to apply the pct formatting to just a few numbers, you can type a number in its decimal grade direct into the cell. For example, to accept 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 case, ii is turned into two%, 20 into 20%, two.1. into ii.1% and then on.
• Numbers smaller than one without a preceding cipher are multiplied by 100. For example, if you type .2 in a percentage preformatted jail cell, you’ll see 20% in that cell. However, if you enter 0.two in the same cell, 0.two% volition appear exactly as it should.
  

Display numbers every bit percentages equally y’all type

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

Important note!

When applying the pct formatting it Excel, please keep in mind that it is cypher more but a visual representation of a real value stored in a jail cell. The underlying value is ever stored in
decimal course.

In other words, xx% is stored as 0.2, ii% is stored as 0.02, 0.2% is 0.002, etc. When performing calculations, Excel e’er deals with the underling decimal values. Please call back this fact when referencing percentage cells in your formulas.

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

Tips to display percentages in Excel

Showing pct in Excel seems to exist one of the earliest tasks, right? But experienced Excel users know that a path to the goal almost never runs polish 🙂

ane. Display as many decimal places as you want.

When applying percentage formatting to numbers, Excel 2016, 2013 and 2010 brandish their rounded versions with no decimal places, which may cause some defoliation. For example, apply the percent format to an empty cell and then type 0.ii into it. What do you see? In my Excel 2013, I see 0% though I know with certainty information technology should be 0.2%.

To see the actual percentage instead of a rounded version, you only need to increase the number of decimal places showing. To do this, open the
Format Cells
dialog either past pressing
Ctrl + i
or correct-clicking the cell and selecting
Format Cells…

from the context menu.
Brand sure the
Percent
category is selected and specify the desired number of decimal places in the
Decimal places
box.

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

Alternatively, you can control the number of displayed decimal places, by clicking the
Increment Decimal
or
Decrease Decimal
icons on the ribbon (Home
tab >
Number
grouping):

ii. Apply a custom format to negative percentages.

If you want to format negative percentages in a different manner, say in blood-red font, you tin can create a custom number format. Open up the
Format Cells
dialog once more, navigate to the
Number
tab >
Custom
category and enter ane of the below formats in the
Blazon
box:

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

Y’all tin can find more details near this formatting technique in the Brandish numbers as percentages article by Microsoft.

3. Format negative percentages using Excel provisional formatting.

Compared to the previous method, Excel conditional formatting is more versatile and it lets yous display negative percentages, eastward.g. percentage subtract, in whatsoever format of your choosing.

The fastest way to create a conditional formatting dominion 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:

And so 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 gear up up for ain formatting.

For more information about creating conditional formatting rules, delight encounter How to use conditional formatting in Excel.

This is how you piece of work with Excel percent format. Hopefully, this knowledge will help yous avoid a lot of headache when yous in the future. In the next manufactures, we are going to learn how to summate percentages in Excel and write formulas for percentage modify, per centum of a full, compound involvement and more. Please stay tuned and thank y’all for reading!

Yous may also be interested in