How to Work Out 80/20 in Excel
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.
Open Microsoft Excel.
Enter "Product," "Sales," "Sales Percentage" and "Cumulative Percentage" in cells A1 though D1, respectively.
Enter a list of products or categories in column A, starting in cell A2.
Enter each products' sales in column B.
Enter "=B2/SUM(B:B)" without quotes in cell C2.
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.
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.
Enter "=C2" without quotes in cell D2.
Enter "=D2+C3" without quotes in cell D3.
Copy the formula from cell D3 down to the last data point in column D, just as you did previously for column C.
Click and drag across the column headers C and D to select both columns.
Right-click either selected column and select "Format Cells."
Click "Percentage" from the Category list of the Number tab and click "OK" to format the cells as percentages.
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.