How to Use Percentages in MS Excel
A percentage is a way of expressing a decimal number or fraction. For instance, you can write the decimal 0.25 as 25 percent, 0.05 as 5 percent and 1.0 as 100 percent. MS Excel 2007 provides tools for calculating decimals, and you may format any decimal number with a percent sign (%). Since cell formatting only changes the number's appearance, you can use percentages in mathematical formulas.
Format a cell to display a percentage. In Excel, type a decimal value into cell A1, such as 0.3. Press "Enter." On the "Home" tab of the ribbon, in the "Number" section, click "%" to display 30%."
Calculate a percentage. If you have 100 boxes and 12 of them are red, find the percentage of red boxes by dividing the amount,12, by the total, 100. In Excel, enter the following:Cell A1: 12Cell A2: 100Cell A3: =A1/A2In cell A3, press "Enter" to view the result, 0.12. Format cell A3 as a percentage---as in Step 1---to display "12%."
Figure the amount of a percentage. If you are buying a camera for $150 and you want to find how much a 5 percent sales tax will be, multiply the total, 150 by the decimal that represents the percentage (divided the percent, 5, by 100), 0.05. In Excel, enter the following:Cell B1: 150Cell B2: .05Cell B3: =B1*B2In cell B3, press "Enter" to see the result, 7.5. To show the value as a dollar amount, format cell B3 as currency. On the "Home" tab of the ribbon, in the "Number" section, click "$" to display "$7.50."
Find the original amount from a total that has been reduced by a percentage. If you are shopping for shirts at 30 percent off and the reduced price is $21, calculate the original price by dividing the discounted price, 21, by the difference between 100 percent and the 30 percent discount (1 minus 0.3), in this case 0.7. In Excel, enter the following:Cell C1: 21Cell C2: .7Cell C3: =C1/C2In cell C3, press "Enter" to see the original price, 30. Format cell C3 as currency to display "$30.00."
Increase a total by a percentage. If you get a 3 percent raise and your salary is $48,000, figure your new salary by multiplying your current yearly wages, 48,000, by 1 plus the percentage of the increase (1 plus 0.03), 1.03. In Excel, enter the following:Cell D1: 48000Cell D2: 1.03Cell D3: =D1*D2In cell D3, press "Enter" to see your new salary, 49,440. Format cell D3 as currency to display "$49,440.00."
Decrease a total by a percentage. If your monthly entertainment expenses are $100 and you want to reduce expenditure by 5 percent, multiply your current budget, 100, by 1 minus the percentage of the decrease (1 minus 0.05), or 0.95 in this example. In Excel, enter the following:Cell E1: 100Cell E2: .95Cell E3: =E1*E2In cell E3, press "Enter" to view your revised entertainment budget, 95. Format cell E3 as currency to display "$95.00."
Tips & Warnings
- The error message "#DIV/0!" means that you tried to divide a number by zero.
- If a cell displays "#####," the column is not wide enough to show the value. Double-click the column divider to adjust the column width.
- If your cell formatting limits display to only a few decimal places, the values will still be correct, but they will appear to be rounded. For instance, 25.006 formatted as currency looks like "$25.01." In some cases, using these values in calculations will give results that look inaccurate. If you need a precise display, use a general number format.
References & Resources
- Microsoft Office Online: Display Numbers as Percentages
- California State University at East Bay: Understanding Percentages
- Microsoft Office Online: Calculate Percentages
- Henderson State University: Percentages
- Microsoft Office Online: Watch This: Format numbers as percentages
- Microsoft Office Online: Examples of Commonly Used Formulas
- Microsoft Office Online: Calculation Operators and Precedence