How to Work Out 80/20 in Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

The Italian economist Vilfredo Pareto produced a concept which is now referred to as "Pareto's Principle," or the "80-20 Rule." This rule simply states that 20 percent of effort accounts for 80 percent of results. Likewise, 80 percent of effort contributes very little to the overall total. This principle is applied in many areas, such as sales and management. Using Microsoft Excel, you can work out the 80-20 Rule to determine which products contribute the majority of profits.

Advertisement

Step 1

Open Microsoft Excel.

Video of the Day

Step 2

Enter "Product," "Sales," "Sales Percentage" and "Cumulative Percentage" in cells A1 though D1, respectively.

Step 3

Enter a list of products or categories in column A, starting in cell A2.

Advertisement

Step 4

Enter each products' sales in column B.

Step 5

Enter "=B2/SUM(B:B)" without quotes in cell C2.

Step 6

Select cell C2, click its bottom right corner and drag to the last data position in column C. This copies the formula to the selected cells.

Advertisement

Step 7

Click the "Data" tab, click the "Sort Largest to Smallest" icon in the "Sort & Filter" group, and click "Sort" from the dialog window, which asks you to expand the selection. This sorts the data based on the sales percentage.

Step 8

Enter "=C2" without quotes in cell D2.

Advertisement

Step 9

Enter "=D2+C3" without quotes in cell D3.

Advertisement

Step 10

Copy the formula from cell D3 down to the last data point in column D, just as you did previously for column C.

Step 11

Click and drag across the column headers C and D to select both columns.

Advertisement

Step 12

Right-click either selected column and select "Format Cells."

Step 13

Click "Percentage" from the Category list of the Number tab and click "OK" to format the cells as percentages.

Advertisement

Step 14

Read down column D to see where the cumulative percentage reaches around 80 percent. The products listed down to, and including, that row contribute to 80 percent of your sales.

Video of the Day

Advertisement

Advertisement

references

Report an Issue

screenshot of the current page

Screenshot loading...