Percentages 101
While Excel can do a lot of things, it can’t really teach you math. If you need a quick refresher on percentages, I recommend starting with a few excellent videos from The Khan Academy:
Displaying values as percentages
To show a number as a percent in Excel, you need to apply the percentage number format. To apply this format, select the cells that need formatting, and then click the Percent Style button in the Number group on the ribbon’s Home tab.
Of course, it’s even faster to use the keyboard shortcut for applying the format, which is Ctrl + Shift + %.
In Excel, the underlying value is always stored in decimal form. So, even if you’ve used number formatting to display something as a percentage (10%), that’s just what it is — formatting, or a symbolic representation of the underlying value. Excel always performs calculations on that underlying value, which is a decimal (0.1). To double-check the underlying value, select the cell, press Ctrl + 1, and look in the Sample box on the General category.
Percentage formatting can be (*ahem*) interesting. There’s a method to the madness, but you need to consider the following:
- Format existing values: When you apply percentage formatting to a cell that already has a number in it, Excel multiplies that number by 100 and adds the % sign at the end. This causes confusion, so let me explain what’s happening. Let’s say that you type 10 into cell A2, and you then apply the percentage number format. Because Excel multiples your number by 100 to show it as a percentage (remember that 1% is one part of one hundred), you’ll see 1000% displayed in the cell, not 10%. To get around this, you can calculate your numbers as percentages first. For example, if you type the formula =10/100 in cell A2, Excel will display the result as 0.1. If you then format that decimal as a percentage, the number will be displayed as 10%, as you ‘d expect. You can also just type the number in its decimal form directly into the cell — that is, type 0.1 and then apply percentage format.
- Format empty cells: Excel behaves differently when you pre-format empty cells with percentage formatting andthen enter numbers. Numbers equal to and larger than 1 are converted to percentages by default; numbers smaller than 1 that are not preceded with a zero are multiplied by 100 to convert them to percentages. For example, if you type 10 or .1 in a preformatted cell, you’ll see 10% appear in the cell. (Now, if you type 0.1 in the cell, something different happens. If you want details, see the More Information section in this KB article.)
- Format as you type: If you type 10% directly in the cell, Excel will automatically apply percentage formatting. This is useful when you want to type just a single percentage on your worksheet, such as a tax or commission rate.
If you’re still confused, I recommend that you watch Mike Girvin explain the percentage number format. Mike does a fine job describing the gotchas associated with this format.
Calculating percentages
As with any formula in Excel, you need to start by typing an equal sign (=) in the cell where you want your result, followed by the rest of the formula. So, I hear your next question in my head — which formula do I use to get the result I desire? Well, that depends. The basic formula for calculating a percentage is part/total = percentage, which might look something like this:
Now, let’s say that you want to reduce a particular amount by 25%, like when you’re trying to apply a discount. Here’s a formula that would get you that result. (Think of the 1 in the formula below as a stand-in for 100%.)
To increase the amount by 25%, replace the minus sign in the formula above with a plus sign.
The next example is slightly more complicated. Say the earnings for your department are $2,342 in November and $2,500 in December. What is the percentage change in earnings between these two months? To find the answer, divide the difference between December and November earnings ($158) by the value of the November earning ($2,342). Then, format the difference as a percentage with two decimal places.
You can find these and other examples in the Display numbers as percentages article on Office.com.
Need help with your formulas?
If you create formulas in Excel, you know that coming up with the right formula is often more art than science. The examples above are pretty simple. They’re meant to serve only as a jumping off point for creating your own formulas. If you need a more sophisticated formula for solving a percentage problem, or if you want troubleshooting advice, the best thing to do is ask a question in the Excel forum on the Microsoft Answers site.
You might also review the following videos, which cover common scenarios and formulas in more detail:
- Mike Girvin: Formulas for percent change / increase percentage / decrease percentage
- Mike Girvin: Percentage change formula and chart
- Bill Jelen (MrExcel): Calculate a running percentage of total
A few last tips before we part…
- Decimal places: Press Ctrl + 1 to open the Format Cells dialog box, and then click Percentage. In the Decimal places box, enter the number of decimal places that you want to display. For example, if you want to see 10% instead of 10.00%, enter 0. You can also use the Increase Decimal and Decrease Decimal buttons in the Numbergroup (Home tab).
- Rounding issues: Note that sometimes what you see in a cell (e.g., 10%) doesn’t match the number you expected to see (e.g., 9.75%). To see the true percentage in the cell, rather than a rounded version, increase the decimal places. Again, Excel always uses the underlying value to perform calculations.
- Negative percentages: If you want negative percentages to be formatted differently — for example, to appear as red text or within parentheses — you can create a custom number format such as 0.00%;[Red]-0.00% or 0.00%_);(0.00%). This technique is outlined in the Display numbers as percentages article.
Do you have other tips or formulas to share? If so, leave a comment. We’d love to hear from you.
No comments:
Post a Comment