What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
An 80/20 sales analysis in Excel is a Pareto analysis: group transactions by customer, product, salesperson, or territory; total the chosen measure; sort from largest to smallest; calculate each group’s share and cumulative share; then identify the first group that brings cumulative sales to at least 80%.
The result is not required to be exactly 80/20. Your data may show that 12 of 94 customers generate 80% of revenue, or that 32% of products generate 80% of gross profit. Treat the ratio as an observed concentration and a prioritization tool—not a law.
What the 80/20 result actually means
Three questions are often confused:
- Revenue concentration: How many entities are needed to produce 80% of revenue?
- Entity share: Do the top 20% of entities produce approximately 80% of revenue?
- Priority threshold: Which entities belong in the group responsible for the first 80% of revenue?
Only the first and third questions identify the threshold group. The second compares a fixed 20% of entities with their sales contribution, and may produce a completely different answer.
Choose the measure before opening Excel
| Business question | Measure |
|---|---|
| Which customers produce the most billings? | Revenue |
| Which products sell most frequently? | Units |
| Which accounts create the most economic value? | Gross profit or contribution margin |
| Which customers create the most workload? | Orders, tickets, service hours, or transactions |
| Which salespeople drive results? | Revenue, profit, or quota attainment |
| Which customers are strategically important? | Revenue plus margin, growth, retention, and risk |
Revenue is easy to obtain but can over-prioritize low-margin accounts. When reliable cost data exists, run the analysis on both revenue and gross profit. Label estimated or allocated costs as contribution or estimated margin rather than audited profit.
Prepare the Excel sales data
Start with one transaction per row, or a clearly aggregated table, containing a category and a numeric measure. A useful structure is:
| Date | Customer | Product | Salesperson | Region | Revenue | Cost | Gross Profit |
|---|---|---|---|---|---|---|---|
| 1/5/2026 | Acme Corp | Product A | Jordan | West | 12,500 | 7,000 | 5,500 |
| 1/8/2026 | Northwind | Product B | Taylor | East | 4,800 | 2,600 | 2,200 |
- Click inside the source range and choose Insert > Table.
- Confirm that the table has headers and name it, for example,
SalesData. - Check that the measure column contains numbers, not text.
- Standardize names such as
Acme Corp,ACME, andAcme Corporationbefore grouping. - Define the date range and apply it consistently to both the numerator and denominator.
- Decide how returns, cancellations, discounts, tax, shipping, and internal transfers should be treated.
Do not delete apparent duplicates until you confirm whether they are legitimate transactions. Microsoft distinguishes temporarily filtering unique values from permanently removing duplicate rows in its duplicate-value guidance.
- Investigate blank customer or product names; do not let a large “(blank)” group hide a data problem.
- Keep returns and credits negative when they represent real activity, or report a separate returns view.
- Show one-time contracts separately when they dominate a period.
- Record whether the analysis covers year-to-date, a prior comparable period, trailing 12 months, or recurring revenue only.
Method 1: Build the analysis with a PivotTable
1. Summarize sales by category
- Select a cell in
SalesData. - Choose Insert > PivotTable and place it on a new worksheet.
- Drag Customer (or Product, Salesperson, or Region) to Rows.
- Drag Revenue to Values and confirm the calculation is Sum, not Count.
PivotTables summarize records and support percentage and running-total calculations; see Microsoft’s PivotTable overview and calculation guide.
2. Sort the aggregated totals
Right-click a value in the revenue field and choose Sort > Largest to Smallest. Sorting must occur after aggregation. Sorting individual transactions first does not produce a customer-level Pareto analysis. Excel’s general controls are documented in the sorting guide.
3. Add each category’s percentage
- Drag Revenue into Values a second time.
- Right-click the second field and choose Show Values As > % of Grand Total.
- Rename it Sales % and format it as a percentage.
4. Add cumulative sales percentage
Drag Revenue into Values a third time. Choose Show Values As > % Running Total In, select Customer as the base field, and rename the result Cumulative Sales %. The running total is meaningful only while the category totals remain sorted descending.
For maximum transparency, copy the category and total columns, paste them as values into a normal worksheet, and calculate the percentages with helper columns. This also makes custom charting easier.
Method 2: Use worksheet formulas
Assume column A contains the category, column B contains already-aggregated sales, rows 2:100 contain the summary, and H1 contains 80%. Sort the entire summary table by column B from largest to smallest before entering cumulative formulas.
Percentage of total
In C2 enter:
=B2/SUM($B$2:$B$100)
Copy down and format column C as a percentage.
Cumulative percentage
In D2 enter =C2. In D3 enter =D2+C3 and copy down. Alternatively, use this single pattern starting in row 2:
=SUM($C$2:C2)
Flag the threshold group
To include the category that crosses 80%, enter in E2:
=IF(D2-C2<$H$1,"Top 80%","Remaining")
If cumulative sales move from 74% to 86%, this includes the row that was necessary to reach the threshold. A strict rule that excludes the crossing row is:
Rank #3
=IF(D2<=$H$1,"Top 80%","Remaining")
Choose and disclose one rule; the inclusive rule is generally more useful for resource planning.
Count entities and calculate their actual share
In newer Excel versions:
=XMATCH(TRUE,D2:D100>=H1)
With versions supporting MATCH:
=MATCH(TRUE,D2:D100>=H1,0)
The result is the position within the summary range, not necessarily the worksheet row number. If that count is in H2 and the total number of categories is in H3, calculate the actual entity share with:
Recommended Free Tools
=H2/H3
Do not label that result “20%” unless your data actually produces 20%.
Dynamic Excel 365 method
Microsoft 365 and newer compatible Excel editions can create a spilling summary. In A2:
=UNIQUE(SalesData[Customer])
In B2:
=SUMIF(SalesData[Customer],A2#,SalesData[Revenue])
Sort the two-column result by sales descending with:
=SORTBY(A2:B100,B2:B100,-1)
Microsoft documents UNIQUE, SORT, SORTBY, and FILTER. These functions are not universal across older installations; check Microsoft’s function compatibility list. Spilling formulas also need clear destination cells, and dynamic-array links to a closed source workbook can return #REF!.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Create a Pareto chart
Native Pareto chart
- Select the category and aggregated sales-total columns.
- Choose Insert > Insert Statistic Chart > Pareto.
- Confirm that categories appear in descending order.
- Format titles and axes, and make the 80% threshold visible if the chart supports the decision.
Excel’s native chart combines descending columns with a cumulative percentage line and can group identical categories and sum corresponding values. See Microsoft’s Pareto chart instructions.
Custom combination chart
- Select category, sales-total, and cumulative-percentage columns.
- Choose Insert > Combo Chart.
- Use clustered columns for sales totals and a line for cumulative percentage.
- Put the percentage line on a secondary axis.
- Set that axis from 0% to 100% and add an 80% horizontal reference series if needed.
The left axis represents sales value and the right axis cumulative percentage, as described in Microsoft’s Pareto chart explanation. Keep labels readable, avoid pie charts with many categories, and retain the full table even if the presentation groups the tail as “Other.”
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Interpret the result and turn it into action
Report the measured result plainly: “14 of 120 customers generate 80% of revenue in the trailing 12 months,” not “the top 20% generate 80%” unless that is what the worksheet shows. A Pareto chart demonstrates concentration, not causation or overall customer importance.
- Assign senior coverage and retention plans to the threshold group.
- Investigate high-revenue, low-margin accounts for pricing, service-cost, or contract changes.
- Give products near the threshold appropriate inventory and promotional attention.
- Review the long tail for self-service, automation, lower-touch service, or discontinuation.
- Protect strategically important smaller accounts that may have growth potential, high retention risk, reliable payment, or market value.
Run at least a customer view and a product view, ideally on both revenue and gross profit. Compare current, prior comparable, trailing-12-month, and new-versus-repeat periods because concentration changes over time.
Common errors and recovery steps
- Totals are not descending: Sort the entire summary by the value field, not just one column.
- Cumulative percentages exceed 100%: Check for duplicated rows, an incorrect denominator, or negative and positive values mixed in a zero or near-zero total.
- Transactions were accumulated without grouping: Build a PivotTable or category summary before calculating cumulative percentages.
- The crossing category was omitted: Include the first row where cumulative sales reach or exceed 80% when the goal is to reach the threshold.
- Duplicate names split one customer: Use a master-customer mapping table and standardize names.
- Blank categories dominate: Investigate the source; call them “Unknown” only when that accurately describes the records.
- Returns create negative totals or a zero denominator: Use
=IFERROR(B2/SUM($B$2:$B$100),0)and consider a separate returns analysis. - Filtered numerator and denominator differ: Apply the same date, region, salesperson, and segment scope to both.
- New transactions are missing from a PivotTable: Use an Excel Table as the source and refresh after importing data.
- Dynamic formulas show
#SPILL!: Clear blocked cells or move the formula.
When Excel is no longer enough
| Approach | Best use | Main trade-off |
|---|---|---|
| PivotTable | Most one-off and recurring analyses | Running-total settings can be confusing |
| Helper-column formulas | Teaching, auditing, and visible mechanics | Requires careful ranges and sorting |
| Dynamic arrays | Microsoft 365 or newer Excel | Compatibility and spill errors |
| Power Query | Repeated CRM, CSV, or monthly imports | Requires setup and transformation knowledge |
| Power Pivot/DAX | Large, related, multidimensional models | Steeper learning curve |
Power Query can aggregate by unique values and connect to Excel, CSV/TXT, databases, and CRM-related sources; see Microsoft’s pivot-columns documentation and best practices. Move to a centralized BI platform only when you need governed definitions, automated refreshes, shared permissions, or multi-user dashboards. For a modest dataset or a single decision, Excel is usually sufficient.
Frequently Asked Questions
Is 80/20 always exactly 80/20?
No. Measure the actual concentration in your data and report both the threshold group and its percentage of entities.
Should I analyze customers by revenue or profit?
Use revenue for billing concentration and gross profit or contribution margin for economic prioritization when cost data is reliable.
How do I find the number of customers producing 80% of sales?
Sort aggregated customer totals descending, calculate cumulative percentage, and use =XMATCH(TRUE,D2:D100>=H1) or =MATCH(TRUE,D2:D100>=H1,0).
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Can I do this without a PivotTable?
Yes. Use an aggregated summary with percentage, cumulative-percentage, threshold, and count formulas.
Why does my PivotTable omit new transactions?
Use an Excel Table as the source and refresh the PivotTable after adding or importing rows.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
