The formulas below work identically in Excel and Google Sheets. The single most common bug — a denominator that shifts as you drag a formula down — is a one-character fix, covered at the end.
Copy-paste percentage formulas for Excel and Google Sheets — percent of total, percent change, running totals, and the absolute reference ($B$10) trick that stops your denominator from shifting.
See also: Percentage calculator · Formula cheat sheet
To find what percentage each row is of a grand total in cell B10:
=B2/$B$10
Then format the cell as a percentage (see below). The dollar signs matter — they're explained in the troubleshooting section.
This trips up almost everyone. You have two options and should never combine them:
Doing both — =B2/$B$10*100 and then formatting as percent — gives you 1500%. Pick one.
New value in C2, old value in B2:
=(C2-B2)/B2
Format as percent. A positive result is growth, negative is decline. To guard against a divide-by-zero when the old value is blank: =IFERROR((C2-B2)/B2, "").
To show each row's share accumulating toward 100%, combine a running total with the grand total using a locked range:
=SUM($B$2:B2)/$B$10
As you drag down, $B$2 stays anchored while B2 expands to B3, B4… so each cell sums everything from the top through the current row. The last row reaches exactly 100%.
| Goal | Formula (Excel & Sheets) |
|---|---|
| P% of a number | =A2*B2 (B2 formatted as %, or =A2*15%) |
| What % is A2 of B2 | =A2/B2 |
| Increase A2 by 15% | =A2*(1+15%) |
| Decrease A2 by 15% | =A2*(1-15%) |
| Percent of total | =B2/$B$10 |
| Percent change | =(C2-B2)/B2 |
| Weighted average | =SUMPRODUCT(scores, weights)/SUM(weights) |
Every formula above is identical in Google Sheets. The percent-format shortcut in Sheets is Ctrl+Shift+5, and array-style formulas can be wrapped in ARRAYFORMULA() to fill a whole column at once.
The number one spreadsheet percentage bug: you write =B2/B10, drag it down, and the results go wrong. That's because a plain reference is relative — dragging it down turns B10 into B11, B12, B13… so your total disappears out from under you.
The fix is an absolute reference: put dollar signs on the part that must not move. $B$10 locks both the column (B) and the row (10), so it stays pinned no matter where you copy the formula. Press F4 after clicking a reference to cycle through the locking options: B10 → $B$10 → B$10 → $B10. For a percent-of-total column, you almost always want the fully locked $B$10.
Your denominator is using a relative reference, so it shifts down as you copy (B10 becomes B11, B12...). Lock it with an absolute reference — $B$10 — so the total stays pinned. Press F4 on a reference to add the dollar signs.
Format as a percent. That keeps the cell's real value as a ratio (0.15) that other formulas can use, while displaying it as 15%. Don't multiply by 100 and format as percent at the same time — you'll get 1500%.
The dollar signs make the reference absolute — the column B and row 10 are both locked, so the cell won't change when you copy or drag the formula. Without them, the reference moves relative to the new location.
Yes. The percent-of-total, percent-change, and absolute-reference formulas are identical in Excel and Google Sheets. Only some keyboard shortcuts differ.
Links point to primary sources and standards bodies. Tax rates and official formulas change over time — verify against the source for current figures.