How to find the items that make up 80% of the total in Excel
Formula (Excel and Google Sheets)
=IF(SUM($B$2:B2)-B2<0.8*SUM($B$2:$B$8),"Top 80%","")Sort largest first, then flag a row while the total of the rows above it is still under 80% of the grand total: =IF(SUM($B$2:B2)-B2<0.8*SUM($B$2:$B$8),"Top 80%","").
- Result on the example
- one result per row in
C2, filled down toC8 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C8. Change any input and the result updates, or edit the formula to experiment.
How it works
This is a Pareto (80/20) analysis. SUM($B$2:B2)-B2 is the running total before the current row. While that is still under 80% of the grand total, the current row is needed to reach the target, so it gets flagged.
The row that crosses 80% is included, because without it you would not reach the target.
=IF(SUM($B$2:B2)-B2<0.8*SUM($B$2:$B$8),"Top 80%","")Result"Top 80%"
$B$2:B2$86,400A range: one cell.
SUM($B$2:B2)86400SUM: Adds numbers and ranges. Skips text and blanks.- number1
$B$2:B2$86,400
- number1
B2$86,400The value in B2.
SUM($B$2:B2)-B20The - operator subtracts.
$B$2:$B$87 rows × 1 col: $86,400, $52,300, $31,800, $18,900, …A range: 7 cells in one column.
SUM($B$2:$B$8)212800SUM: Adds numbers and ranges. Skips text and blanks.- number1
$B$2:$B$87 rows × 1 col: $86,400, $52,300, $31,800, $18,900, …
- number1
0.8*SUM($B$2:$B$8)170240The * operator multiplies.
SUM($B$2:B2)-B2<0.8*SUM($B$2:$B$8)TRUEThe < operator tests less than.
IF(SUM($B$2:B2)-B2<0.8*SUM($B$2:$B$8),"Top 80%","")"Top 80%"IF: Returns one value if a test is true, another if false.- logical_test
SUM($B$2:B2)-B2<0.8*SUM($B$2:$B$8)TRUE - value_if_true
"Top 80%" - value_if_false
""not used
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(SUM($B$2:B2)-B2<0.8*SUM($B$2:$B$8),"Top 80%",""). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(SUM($B$2:B2)/SUM($B$2:$B$8)<=0.8,"Top 80%","") | Top 80%, Top 80%, …Different result | Simpler to read. Leaves out the row that crosses 80%, so it can stop short of the target. |
Common mistakes
- The list must be sorted largest first or the running total means nothing. Sort a copy of the data with SORT or the Sort button first.
- Lock the grand total with $ on both ends.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the items that make up 80% of the total
C2, show Top 80% for the products needed to reach 80% of total revenue, and blank (""). Fill down to C8.Questions
How do I show the cumulative percentage too?
=SUM($B$2:B2)/SUM($B$2:$B$8) in another column, formatted as a percentage.
Checked by running on 2026-11-20: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.