Spreadsheet Practice

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 to C8
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%"

  1. $B$2:B2$86,400

    A range: one cell.

  2. SUM($B$2:B2)86400

    SUM: Adds numbers and ranges. Skips text and blanks.

    • number1$B$2:B2$86,400
  3. B2$86,400

    The value in B2.

  4. SUM($B$2:B2)-B20

    The - operator subtracts.

  5. $B$2:$B$87 rows × 1 col: $86,400, $52,300, $31,800, $18,900, …

    A range: 7 cells in one column.

  6. SUM($B$2:$B$8)212800

    SUM: Adds numbers and ranges. Skips text and blanks.

    • number1$B$2:$B$87 rows × 1 col: $86,400, $52,300, $31,800, $18,900, …
  7. 0.8*SUM($B$2:$B$8)170240

    The * operator multiplies.

  8. SUM($B$2:B2)-B2<0.8*SUM($B$2:$B$8)TRUE

    The < operator tests less than.

  9. 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_testSUM($B$2:B2)-B2<0.8*SUM($B$2:$B$8)TRUE
    • value_if_true"Top 80%"
    • value_if_false""not used

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.

FormulaResultWhen to use it
=IF(SUM($B$2:B2)/SUM($B$2:$B$8)<=0.8,"Top 80%","")Top 80%, Top 80%, …Different resultSimpler 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

The products are sorted by revenue, largest first. In 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-21: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.