Percent Style

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
group on the
Click the Percent Style button to display numbers as percentages in Excel.

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

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
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
    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:
    Formatting existing values as percentage in Excel

    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.
Baca Juga :   Unsur Unsur Sajak

Applying percentage format to empty cells

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

  • 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.
    Applying percentage format to empty cells in Excel

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
box nether the
category on the
Viewing the real value behind percentage formatting in Excel

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 🙂

Baca Juga :   Yang Harus Dilakukan Saat Melakukan Pertahanan Adalah

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
category is selected and specify the desired number of decimal places in the
Decimal places
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
Increment Decimal
Decrease Decimal
icons on the ribbon (Home
tab >
Displaying more or fewer decimal places

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
tab >
category and enter ane of the below formats in the

  • 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.

Special formatting for negative percentages

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

Baca Juga :   Gerakan Melompat Bertumpu Pada

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:
Excel conditional formatting rule for negative percentages

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

Percent Style