How to Do Percentages in Excel: Every Formula and Method (2026)

Percentages turn raw numbers into a story your boss actually reads. A quarterly report that says "sales went from 412 to 503" gets a shrug. The same sheet that says sales rose 22% gets the meeting. Excel is built for exactly this kind of math, but the moment you type =A1/B1 and see 0.218 instead of a clean 22%, things fall apart fast.

This guide fixes that. You will learn the four percentage situations that cover roughly 95% of spreadsheet work: calculating a percent of a total, working out percent change, applying a percentage increase or decrease, and reverse-engineering a number when you only know the percentage. Every method here works in Excel 2016, 2019, 2021, Microsoft 365, and Excel for the web. The formulas have not changed in a decade.

Why does this trip up so many people? Because Excel does two things at once: it does the math behind your formula, and it controls how the number gets displayed. Those are two separate systems that beginners think are one. Once you split them in your head, percentages stop fighting you. The math is just division and multiplication. The display is just a format flag. Mix them up and you get nonsense numbers. Keep them separate and every percentage problem becomes a one-line formula.

Before any formula, fix the format. Excel stores percentages as decimals. 0.5 on the back end is 50% on the front end. When you apply Percentage format (the % button on the Home ribbon, or Ctrl + Shift + %), Excel multiplies the displayed value by 100 and appends the symbol. It does not change the underlying number. Get that mental model right and three-quarters of percentage confusion disappears.

Two situations trip people up. First, typing 50 into a cell that is already formatted as a percentage gives you 5000%. Excel assumes you meant the decimal. Type 0.5 or 50% instead. Second, copying a percentage cell into a fresh worksheet sometimes pastes the formatted display, sometimes the underlying decimal, depending on your Paste Special choice. When in doubt, paste values and re-apply the % format.

A neat sanity check: type a known value like 0.5 into any percent-formatted cell and confirm it displays as 50%. If it shows 0.5%, the cell is on Number format, not Percentage. If it shows 50.0%, you have one decimal place active. If it shows 50% exactly, you are aligned. Run this test whenever a sheet looks wrong, before you start rewriting formulas. Nine times out of ten the bug is a format mismatch, not bad math.

The simplest case is percent of a total: part divided by whole. If A2 holds your category sales and A10 holds the grand total, the formula is =A2/$A$10. The dollar signs lock the total when you drag the formula down a column. Without them, row 3 would reference A11, row 4 would reference A12, and your percentages would collapse into a mess of #DIV/0! errors.

Format the result cell as percentage and you are done. To check the math, the column should sum to exactly 100%. If it sums to 99.99% or 100.01%, that is rounding display. The underlying decimals still add to 1.0. To verify, widen the column or temporarily switch to Number format with four decimals. For analysis of marketing-style ratios, see our guide to averaging in Excel, which pairs naturally with percent-of-total calculations.

Want to know each row's share of total without typing the denominator manually every time? Use SUM inside the divisor: =A2/SUM($A$2:$A$9). Excel computes the sum once per cell, which is slower than a fixed reference but more flexible when rows get added or deleted. For a sales sheet with 5,000 rows, the speed difference is invisible. For a 500,000-row dataset feeding a dashboard, prefer the fixed reference and store the grand total in its own cell.

One more trick. Press the F4 key while editing a formula and Excel cycles through the four reference modes: relative (A10), fully absolute ($A$10), row-locked (A$10), column-locked ($A10). This single keystroke saves more time than any other Excel feature once you build the habit. Position your cursor on a cell reference, hit F4 until you get the lock pattern you want, then keep typing.

Percent change is the formula most people actually need. If January revenue is in A2 and February revenue is in B2, the change formula is =(B2-A2)/A2. A result of 0.18 formatted as percentage shows 18%. That means February was 18% larger than January. A negative result (say -0.12) means a 12% drop. Some analysts prefer the equivalent shortcut =B2/A2-1, which produces the exact same number with one less subtraction.

Watch the denominator. The bottom of the fraction is always the baseline, the earlier or smaller value you are comparing from. Flip A2 and B2 and you get a different answer, because percent change is not symmetric. Going from 100 to 150 is a 50% increase, but going from 150 to 100 is a 33% decrease. Our detailed percent change tutorial walks through this asymmetry with worked examples.

What about percent change over many years? Use the compound annual growth rate (CAGR), which smooths the ride into a single annual figure: =(End/Start)^(1/Years)-1. If revenue grew from $100,000 to $250,000 over 5 years, CAGR is =(250000/100000)^(1/5)-1, returning roughly 20.11%. CAGR is what investors quote and what finance teams chart, because it tells you the steady annual pace even when actual growth was lumpy. A 150% total gain over 5 years could mean huge year-one growth then stagnation, or smooth 20% gains every year. CAGR exposes which one you actually had.

Applying a percentage increase uses the formula =A2*(1+B2). If A2 contains the list price 200 and B2 contains the sales tax rate 0.0825 (formatted as 8.25%), the result is 216.50. That is the final price including tax. The (1 + rate) trick keeps the original amount and adds the percentage in one step, which is faster than computing the tax separately and adding it back.

Discounts follow the mirror image: =A2*(1-B2). A jacket marked at $180 with a 30% promo code becomes =180*(1-0.30) = $126. For chained discounts, say 20% off plus an extra 10% off, the discounts do not add up to 30%. They compound: =A2*(1-0.20)*(1-0.10) = 72% of the original, or a 28% total discount. The order does not matter mathematically, but stack them in a single formula to keep the audit trail clean.

Use the same pattern for percentage points rather than percentage change. If your team conversion rate moves from 4% to 5.5%, that is a 1.5 percentage point increase, not a 1.5% increase. The correct percent change is =(0.055-0.04)/0.04, which equals 37.5%. Newspapers and pundits routinely mix these up. When precision matters, always state "points" or "percent" explicitly and never let an ambiguous percentage slide through into a board deck.

Percentage formulas break on two common errors. #DIV/0! appears whenever the denominator is zero or blank. A brand-new product with zero sales last quarter cannot have a percent change calculation, because the baseline is zero. Wrap the formula in IFERROR to swap the error for a friendly message: =IFERROR((B2-A2)/A2, "N/A"). The second argument is whatever you want displayed when the math fails.

The other regular offender is #VALUE!, which means a cell in your formula contains text instead of a number. Numbers stored as text are silent. They look like numbers but Excel will not do math on them. Select the column, hit Data > Text to Columns > Finish to force a number conversion in one click. Our Text to Columns guide covers this fix in depth, along with delimiter splits and date-format repairs.

Watch for one subtle gotcha: imported CSVs from accounting systems often contain percentages already written as text strings like "18.5%". Excel sees a string, not a number, and silently skips it in SUM and AVERAGE.

The fix is =VALUE(SUBSTITUTE(A2,"%",""))/100, which strips the symbol, converts to a number, then divides by 100 to land back at the decimal. Wrap this in a helper column, then reference the helper column from your real formulas. Always run a quick =COUNT(A:A) on imported numeric columns. If the count is lower than expected, you have hidden text strings that need cleaning before percentages will work.

Real spreadsheets rarely calculate one flat percentage. You need conditional percentages: what percent of customers in California bought Product A, what share of orders over $500 came from repeat buyers, what percent of test takers passed each section. The pattern is SUMIFS or COUNTIFS divided by the unfiltered total. For example, =COUNTIFS(C:C,"CA")/COUNTA(C:C) returns the share of rows where column C equals California.

For sales mix, swap COUNTIFS for SUMIFS: =SUMIFS(B:B,A:A,"Product A")/SUM(B:B) divides Product A's revenue by total revenue. The denominator is the unfiltered sum. The numerator is the filtered sum. Both sides of the fraction must reference the same column or the percentage is meaningless. Deep dives on this pattern live in our SUMIFS tutorial and the COUNTIFS reference.

For weighted averages with percentages, reach for SUMPRODUCT. A student with 90% on a quiz that counts 10% and 60% on a final that counts 60% does not have a 75% average. They have a weighted average that leans on the final. The formula =SUMPRODUCT(Scores, Weights)/SUM(Weights) multiplies each score by its weight, sums everything, then divides by total weight. The result respects the actual importance of each component. Use this for grade calculations, portfolio returns, blended customer satisfaction scores, and anything else where components matter differently.

Running totals as cumulative percentages are the trick behind Pareto analysis (the 80/20 rule made visible). In column F, type =SUM(B$2:B2)/SUM(B$2:B$50) and drag down. The first reference, B$2:B2, locks only the start of the range. As you drag, it expands one row at a time. The denominator stays locked at the full range. Each result shows the cumulative share through that row. Sort the source data descending and you can see exactly which top 3 or top 5 products account for 80% of revenue.

Pivot tables compute percentages without writing a single formula. After building a pivot, right-click any value cell, choose Show Values As, and pick from a menu: % of Grand Total, % of Column Total, % of Row Total, % Difference From, or % of Parent Total. The pivot recalculates everything live and you never type a fraction. For year-over-year analysis, % Difference From with the previous year as the base column is the single most-used view in finance reports.

The catch is that pivot percentages disappear when you turn the pivot back into a flat table. If you need the percentages to survive a copy-paste into another sheet, convert the pivot output using Paste Special > Values. The numbers stay but the live calculation breaks. Refreshing the source data no longer updates them. For permanent dashboards, keep the pivot live and link charts directly to the pivot range.

Power Query users have an even slicker option. Inside the query editor, select a column and choose Add Column > Standard > Percentage Of. The query writes the M-code expression for you, and the percentage refreshes every time the underlying source updates. This is the cleanest pattern for ETL pipelines that pull from databases, web pages, or CSV folders, because percentages stay logically attached to the data instead of living in a separate calculation layer that drifts out of sync.

Three keyboard shortcuts pay for themselves on day one. Ctrl + Shift + % applies percentage format to the selected cells with zero decimal places. Ctrl + Shift + 5 does the same on some keyboard layouts (the 5 key has the % symbol). Ctrl + 1 opens the full Format Cells dialog where you can set decimals, choose how negatives display (red, parentheses, with a minus sign), and add a custom symbol like the per-mille mark for scientific reports.

For repeat work, build the format once and save it as a cell style. Home ribbon > Cell Styles > New Cell Style. Now every analyst on the team applies the same percentage format with two clicks, which kills inconsistency across departmental reports. Combine this with conditional formatting (red for negative percent change, green for positive) and your sheet starts looking like a Bloomberg terminal instead of a beige grid.

Want to nudge one decimal up or down? Hit Alt + H + 0 to add a decimal place. Hit Alt + H + 9 to remove one. These are ribbon-driven shortcuts (the menu letters press themselves in sequence), which means they keep working even if Microsoft reshuffles the layout in a future Office update. For volume work where you are formatting dozens of report sheets, learn these four keystrokes and your formatting speed roughly triples.

One last efficiency tip: when you finish a formula and want to lock down precision before sharing the workbook, copy the percentage column, then use Paste Special > Values in place. This converts live formulas into static numbers so colleagues cannot accidentally break the calculations by editing input cells. Pair this with Review > Protect Sheet for client-facing reports where the percentages need to stay locked.