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.

...
Every cell in a worksheet can have different formatting.
credit: Image courtesy of Microsoft

Set Percentage Formatting

Step

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").

...
Change existing cells to percentage formatting.
credit: Image courtesy of Microsoft

Step

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.

...
Format blank cells to type in percentages.
credit: Image courtesy of Microsoft

Step

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.

...
Add decimal places.
credit: Image courtesy of Microsoft

Step

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

Step

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

...
Multiply by a percentage.
credit: Image courtesy of Microsoft
...
Calculate a discount.
credit: Image courtesy of Microsoft

Step

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)."

Step

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.

...
Find the percent change.
credit: Image courtesy of Microsoft

Step

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