No Ticket Needed

Appearance

5 min fix 14 min read Recently reviewedBeginner

By , IT support technician

Quick Answer

Divide the part by the total — =B2/$B$10 — then press Ctrl + Shift + % to apply Percent Style. Do not multiply by 100 as well: Excel stores 25% as the value 0.25 and the cell format does the scaling.

How to Calculate Percentages in Excel ​

Applies to: Microsoft 365 (Excel for Windows, Excel for Mac, Excel for the Web); Excel 2016, 2019, 2021
Article Type: How-To
Last Updated: 2026-07-29

Summary ​

Excel handles every percentage calculation with ordinary arithmetic — there is no PERCENT function to learn. This article covers the five calculations people actually need (share of a total, change between two periods, adding a percentage, removing one, and progress against a target) and settles the formatting confusion that makes a correct formula look wrong on screen.

Prerequisites ​

  • Any version of Excel. Every formula here uses arithmetic that has worked since Excel 97.
  • Edit permission on the workbook.
  • No admin rights required.

Instructions ​

Sections 2 and 3 use the same sheet, a tab named Sales, with eight reps in rows 2 to 9 and a total in row 10:

RowABCDE
1RepQ2 SalesQ1 Sales% of Total% Change
2Alvarez1240010800
3Boyd81509400
4Chen1590014100
5Diaz65506550
10Total82000

1. Apply Percent Style Instead of Multiplying by 100 ​

Read this section first even if you know the formulas. It accounts for more wrong-looking spreadsheets than every formula error combined.

Excel stores a percentage as a decimal. The value 0.25 and the display 25% are the same number — the cell format multiplies by 100 for display and adds the sign. Nothing in the stored value changes.

  1. Select the cells holding your percentage formulas.
  2. On the Home tab, in the Number group, click Percent Style — the button showing a % symbol. A result of 0.25 now reads 25%.
  3. Press Ctrl + Shift + % for the same thing from the keyboard. On a Mac, press Control + Shift + %.
  4. Click Increase Decimal (to the right of Percent Style, showing .00 with a left arrow) once or twice. 15% becomes 15.1%.

Note: The ribbon Percent Style button applies zero decimal places. The Percentage entry in the Number Format dropdown at the top of the Number group applies two. Press Ctrl + 1 to open Format Cells, choose Percentage on the Number tab, and set Decimal places yourself when you want a specific number.

Two failures follow from the same rule, and they look like opposites:

  • A correct formula displays as 0.25. The cell is still in General format. Apply Percent Style. Do not "fix" it by adding *100 to the formula — that produces 2500% the moment anyone formats the column properly.
  • A value of 25 becomes 2500%. The cells already held whole numbers meant to read as percentages, and Percent Style scaled them as if they were decimals. The stored value really is 25, which is 2500%.

To repair a column of whole numbers that turned into thousands of percent:

  1. Type 100 into any empty cell and press Enter.
  2. Select that cell and press Ctrl + C.
  3. Select the column of broken percentages.
  4. Right-click the selection and choose Paste Special. The Paste Special dialog opens.
  5. Under Operation, select Divide, then click OK. Every value is divided by 100 in place, and the column now reads 25% rather than 2500%.
  6. Delete the cell holding 100.

Tip: When you type a percentage by hand, type the sign: 25%, not 25. Excel converts it to 0.25 and formats the cell in one move, and the value is then correct for use in a formula.

2. Calculate a Percentage of a Total ​

Each row divided by the grand total gives its share.

  1. Open the Sales tab and click cell D2.
  2. Type the formula:
    =B2/$B$10
  3. Press Enter. The cell shows 0.151219512 — the right answer in the wrong clothes.
  4. With D2 still selected, press Ctrl + Shift + %. The cell now reads 15%.
  5. Hover over the bottom-right corner of D2 until the pointer becomes a thin black cross — the fill handle — then drag down to D9. Excel copies the formula and the format together.

The dollar signs are the point of the exercise. $B$10 is an absolute reference: it stays pointing at B10 no matter where the formula is copied. B2 has no dollar signs, so it becomes B3 on the next row, B4 on the one after, which is exactly what you want for the numerator.

Leave the dollar signs out and row 3 divides by B11, row 4 divides by B12, and so on down an empty column — every result after the first comes back as #DIV/0!, or as a nonsense number if anything happens to sit below the total.

  1. To add the dollar signs without typing them, click into the formula bar, click anywhere inside B10, and press F4. It becomes $B$10.
  2. On a Mac, press Command + T if F4 controls screen brightness on your keyboard.

Verification: Click cell D10 and type =SUM(D2:D9). The result is 100%. Anything else means a row was missed or the total in B10 does not cover rows 2 to 9.

If the total does not exist yet, calculate the share against a SUM directly: =B2/SUM($B$2:$B$9). The locked range behaves the same way as a locked cell.

Tip: A PivotTable does this without a formula. Drag the same field into the Values area twice, then right-click the second copy and choose Show Values As > % of Grand Total. See How to Create a PivotTable in Excel.

3. Calculate Percentage Change Between Two Periods ​

Percentage change is the difference divided by where you started.

=(New-Old)/Old
  1. Click cell E2 on the Sales tab.
  2. Type the formula, with the current quarter in B and the previous quarter in C:
    =(B2-C2)/C2
  3. Press Enter, then press Ctrl + Shift + %. The cell reads 15% — Alvarez sold 15% more in Q2 than in Q1.
  4. Drag the fill handle down to E9.

The shorter version returns the identical number and saves a pair of brackets:

=B2/C2-1

Negative results are decreases, and they are correct. Row 3 returns -13% because Boyd went from 9,400 to 8,150. The minus sign is the answer. Two habits keep people from misreading it:

  • Read -13% as "down 13 percent", never as "13 percent of".
  • Make the direction visible at a glance. Select the column, press Ctrl + 1, choose Custom on the Number tab of Format Cells, and type 0.0%;[Red]-0.0% into the Type box. Increases stay black, decreases turn red.

A starting value of zero returns #DIV/0!. This is not a bug to route around — a percentage change from nothing has no meaning, because there is no baseline to compare against. Growing from 0 to 500 is not "infinite percent growth", it is a start. Show it as text instead of an error:

=IF(C2=0, "New", (B2-C2)/C2)

To hide any error in the column, including blanks and text in the previous-period cell:

=IFERROR((B2-C2)/C2, "")

Warning: Percentage change against a negative starting value inverts. A move from -200 to -50 is an improvement, but =(B2-C2)/C2 returns -75% because the divisor is negative. On any column that can hold negative figures — profit, net position, variance — report the change in currency rather than as a percentage, or divide by ABS(C2) and state on the sheet that you have done so.

4. Add a Percentage for Markup or Sales Tax ​

Adding a percentage is one multiplication, not two steps.

To add 20% to the value in B2:

=B2*1.2

The 1.2 is "the whole thing, plus 20%" — 1 for the original amount, 0.2 for the addition. The same pattern covers every rate: 1.05 adds 5%, 1.075 adds 7.5%, 1.15 adds 15%.

  1. Put the rate in a cell of its own rather than inside the formula. Click cell H1, type 20%, and press Enter.
  2. Click the cell where the gross amount belongs and type:
    =B2*(1+$H$1)
  3. Press Enter. With 1,250 in B2 and 20% in H1, the result is 1500.
  4. Format the result as currency, not as a percentage — press Ctrl + 1, choose Currency on the Number tab, and click OK.

To show the tax or markup amount on its own, multiply by the rate alone:

=B2*$H$1

That returns 250, and =B2+B2*$H$1 gets you to the same 1,500 by the long route.

Tip: A rate in a labelled cell survives a rate change. When sales tax moves from 7.5% to 8%, you edit one cell and every formula on the sheet follows. A rate typed into 40 formulas means finding all 40.

5. Remove a Percentage to Get Back to the Original Amount ​

This is where spreadsheets quietly go wrong, and the error is worth understanding rather than memorising.

To take 20% tax off a gross figure, divide by 1.2. Do not multiply by 0.8.

=B2/1.2

Start with a net price of 1,000. Add 20% and the gross is 1,200. Now multiply that 1,200 by 0.8 and you get 960 — not the 1,000 you started with, and 4% short. The two percentages are taken from different numbers: the 20% was added to 1,000, while the 0.8 removes 20% of 1,200. Bigger base, bigger deduction.

Dividing reverses the original multiplication exactly: 1,200 ÷ 1.2 = 1,000.

  1. Click the cell where the net amount belongs.
  2. With the gross amount in B2 and the rate in H1, type:
    =B2/(1+$H$1)
  3. Press Enter. A gross figure of 1,200 returns 1000.
  4. To show the tax portion separately, type:
    =B2-B2/(1+$H$1)
  5. Press Enter. The result is 200, and it adds back to the net figure exactly.

The same reasoning applies to discounts running in the other direction. If 70 is the price after a 30% discount and you want the original:

=B2/(1-30%)

That returns 100. Multiplying 70 by 1.3 returns 91, for the same reason as before.

What you haveWhat you wantFormula
Net amount in B2Gross, tax added at 20%=B2*1.2
Gross amount in B2Net, tax removed at 20%=B2/1.2
Gross amount in B2The tax portion only=B2-B2/1.2
Full price in B2Price after a 30% discount=B2*0.7
Discounted price in B2The original full price=B2/0.7

Verification: Take any net figure, add the percentage, then remove it again with the division formula. You land back on the number you started with, to the penny. If you do not, one of the two formulas is multiplying where it needs to divide.

6. Measure Progress Against a Target ​

Progress against a target is a share calculation with the target as the denominator.

  1. Put the target in a cell of its own — click H3 and type the target figure.
  2. Click the cell where progress belongs and type:
    =B2/$H$3
  3. Press Enter, then press Ctrl + Shift + %.

Results above 100% are correct and mean the target was beaten. Do not cap them by accident — a rep at 118% has earned the number. When a progress bar genuinely needs to stop at full, cap it deliberately:

=MIN(B2/$H$3, 1)

Progress and variance answer different questions, and mixing them up produces reports nobody trusts:

  • Percentage of target — =B2/$H$3 — returns 92%, meaning "we reached 92% of what we aimed for".
  • Variance to target — =(B2-$H$3)/$H$3 — returns -8%, meaning "we fell 8% short".

Pick one per column, label the header with the one you picked, and stay consistent down the sheet.

To run the question backwards — "what does this month have to be for us to land on 100%?" — use Goal Seek, which finds the input value that makes a formula return a number you name. See How to Use Goal Seek in Excel.

Tip: Add Data Bars so the column reads at a glance: select the percentages, go to Home > Conditional Formatting > Data Bars, and pick a fill. See How to Use Conditional Formatting in Excel for setting the bar's minimum and maximum by hand, which matters when values run past 100%.

7. Increase or Decrease a Whole Column Without a Formula ​

When a price list needs a 5% rise applied to the prices themselves, Paste Special does it in place — no helper column, no copy-and-paste-values afterwards.

  1. Type 1.05 into any empty cell and press Enter. Use 0.95 to cut prices by 5%, or 1.075 for a 7.5% rise.
  2. Select that cell and press Ctrl + C. A marching dashed border appears around it.
  3. Select the column of prices you want to change.
  4. Right-click the selection and choose Paste Special. The Paste Special dialog opens.
  5. Under Operation, select Multiply.
  6. Click OK. Every selected value is recalculated in place, and the cells keep their existing currency formatting.
  7. Press Esc to clear the marching border, then delete the cell holding 1.05.

Warning: This overwrites the original values, and Undo is the only way back once you save and close. Copy the column to a spare sheet first if the old prices matter. Cells holding formulas are multiplied too — Excel appends *1.05 to them rather than replacing them.

Troubleshooting ​

TIP

Keep every rate in its own labelled cell at the top of the sheet — tax rate, markup, discount, target — and point the formulas at those cells with F4 to lock them. Rates change, and a sheet built this way needs one edit rather than a find-and-replace across hundreds of formulas that you then have to check by hand.

Symptom / ErrorPotential CauseSolution
Result displays as 0.25 rather than 25%Cell still in General formatSelect the cells and press Ctrl + Shift + % (Step 1). Do not add *100 to the formula.
A value of 25 displays as 2500%Percent Style applied to whole numbersDivide the column by 100 using Paste Special > Divide (Step 1), or retype the values with the % sign.
#DIV/0! down most of a columnTotal reference not locked, or a zero starting valueLock the total as $B$10 with F4 (Step 2). For a genuine zero baseline, wrap the formula in IF as shown in Step 3.
Shares do not add up to 100%Total does not cover every row, or roundingCheck the SUM range behind the total. Rounding to whole percentages can land on 99% or 101%; add a decimal place with Increase Decimal to confirm.
Removing a percentage does not return the original figureMultiplied by 0.8 instead of dividing by 1.2Divide by one plus the rate: =B2/1.2 (Step 5).
#VALUE! in a percentage formulaA number in the calculation is stored as textClick the green triangle in the corner of the cell and choose Convert to Number. See How to Fix Common Excel Formula Errors.
Column fills with ######Column too narrow for the formatted resultDouble-click the right edge of the column heading to autofit. See How to Fix "######" Errors in Excel Cells.