How to Write Percentage Formulas in Excel
The default style in Excel 2010 and 2013 displays every number using decimals. To calculate percentage results with a formula or to convert a cell to a percentage, you need to change the *cell formatting*. A cell's formatting affects how it appears, but won't break any existing equations. With percentage formatting, Excel displays a cell's decimal value multiplied by 100 and adds a percent sign -- "0.5" becomes "50%." Combined with one of a few formulas, Excel can calculate percentage differences or use a percentage to alter another value.
Set Percentage Formatting
Select one or more cells that contain values and **click the "%" icon in the Number section of the Home tab** to switch to percentage formatting. As a shortcut, press "Ctrl-Shift-5" ("Ctrl-Percent").When switching cells that already contain values, those values *appear* multiplied by 100, so "1" becomes "100%," "2" becomes "200%" and so on. If you instead want to keep the digits the same -- for example, "1" to "1%" -- delete the number after changing the formatting and retype it.
Switch blank cells to percentage formatting, and Excel interprets any number you type thereafter as a percentage. For example, switch the formatting, and *then* type "50" to get the result "50%." Instead of clicking the "%" icon, you could also simply type "50%" into a blank cell to set the formatting automatically.
Right-click a cell, choose "Format Cells" and pick "Percentage" to specify how many decimal places appear in your percent. If you use the "%" icon on the Home tab, this option defaults to zero decimal places. This method also works to set the formatting on previous versions of Excel that don't have the ribbon bar.
Work with Percentage Formulas
**Use the PRODUCT function to multiply a value by a percentage** and calculate a new value. For example, if A1 contains "5,000" and B1 contains "60%," write "**=PRODUCT(A1, B1)**" to output the new value, "3,000." You can also use the shorthand syntax "**=A1*B1**."
**Subtract the percentage in your formula from "1"** to calculate a difference based on a percentage, such as to find a price discount. Continuing with the above example, "**=PRODUCT(A1, (1-B1))**" would output "2,000." In shorthand, this formula reads "**=A1*(1-B1)**."The equation uses the "1," equal to "100%," to find the percentage to subtract. *100% - 60% = 40%*, so 60% off of 5,000 is the same as 40% of 5,000: 2,000.
Use the formula **(b-a)/b** to calculate a percentage difference between two numbers. For example, given the discount price $2,000 in cell A4 and the original price of $5,000 in cell B4, write "**=(B4-A4)/B4**" to generate the difference: 60%. After you enter this formula, switch the cell to percentage formatting to see the correct answer -- if Excel defaults to another style, such as currency formatting, you'll receive a bizarre answer like "$1."
Tips & Warnings
- When referencing cells, add dollar signs before the column and row if you want to use absolute references, which remain the same when the formula is copied into another cell. For example, instead of writing "=(B4-A4)/B4," you could write "=($B$4-$A$4)/$B$4." Press "F4" while editing a formula to switch an existing reference to an absolute reference.
- To reset a cell's formatting, pick "General" from the drop-down menu in the Number section of the Home tab. You can also press "Ctrl-Shift-1" to set the formatting to "Number," which displays thousands separators and two decimal places.
- If a formula outputs an unexpected answer, check the cell's formatting. A cell accidentally set to a percentage can turn "5,000" into "500000%."
- Hidden decimal places might also make a result look incorrect. Add more decimals in the Format Cells window to see the full answer. Even if a cell doesn't display its decimal places, however, Excel still uses them in equations.