How to Make ABC Curve in Excel

Learn how to make ABC curve in Excel
Excel Spreadsheets

The spreadsheet in Excel with the so-called ABC curve allows the user to make the inventory control your company. Through the data in this spreadsheet, the manager can make a comparison between the items and determine which ones are relevant and which are disposable for increasing the company's revenue.

Do not you know the ABC curve? You do not perform a comparison to control your stock? Know that using this tool is very important to reduce the costs of your business.

The origin of the ABC curve is a method based on theories of the Italian economist Vilfredo Pareto. It is about dividing the items of your stock into 3 groups according to their importance: A, B or C. In group A are the most important items. In group B are the intermediate level items. In group C are the items with low relevance.

Stock management with the ABC curve in Excel

We elaborated a step by step how to make an ABC curve worksheet in Excel. Follow:

Creating the 4 First Columns

Learn how to make ABC curve in ExcelStep 1: Initially we will create four columns and enter the values. Are they:

  • First Column: A1-A11: It is the code of each product in your stock.
  • Second Column: B1-B11: Quantity of product in stock.
  • Third column: C1-C11: Unit value of each product.
  • Fourth Column: D1-D11: Total value of the products.

Step 2: To complete the data in the fourth column, multiply the value of column B with the value of column C for the respective product. For example: To get the value of the D2 column, use the formula = B2 * C2. Therefore, the quantity of products multiplied by the unit value will result in the amount spent in your company for that stock item.

Step 3: After that, put the values ​​in the D2 column in descending order.

Step 4: In sequence, in the D12 cell use the formula = SUM (D2: D11). The value will represent the amount of the amount spent in your company's current inventory.

Inserting the%

Learn how to make ABC curve in ExcelStep 5: Create the% column in cell E1. The values ​​of cells E2 through E11 represent the percentage that each value of Column D (cells D1 to D11) in relation to the value of cell D12.

Use the respective formulas in each cell below:

  • Cell E2: = D2 / D12
  • Cell E3: = D3 / D12
  • Cell E4: = D4 / D12
  • Cell E5: = D5 / D12
  • Cell E6: = D6 / D12
  • Cell E7: = D7 / D12
  • Cell E8: = D8 / D12
  • Cell E9: = D9 / D12
  • Cell E10: = D10 / D12
  • Cell E11: = D11 / D12

Step 6: After that, select the E2 cells up to E11 and click Home / Number /%.

Step 7: In sequence, in the E12 cell use the formula = SUM (E2: E11). If the result is 100%, the worksheet is correct until now. Otherwise, check the step-by-step again to notice the error and correct the error.

Inserting% Accumulated column

Learn how to make ABC curve in ExcelStep 8: Create the% Accumulated column in the F1 cell. The values ​​of the F2 cells to F11 represent the sum of the percentages of the% column.

Use the respective formulas in each cell below:

  • F2 cell: = E2
  • F3 cell: = F2 + E3
  • F4 cell: = F3 + E4
  • F5 cell: = F4 + E5
  • F6 cell: = F5 + E6
  • F7 cell: = F6 + E7
  • F8 cell: = F7 + E8
  • F9 cell: = F8 + E9
  • F10 cell: = F9 + E10
  • F11 cell: = F10 + E11

Step 9: Once this is done, select cells F2 through F11 and click Home / Number /%.

Inserting the graph with the ABC curve

Learn how to make ABC curve in ExcelStep 10: Select the values ​​of the A2 cells up to A11.

Step 11: Holding down the CTRL key also select the values ​​of the F2 cells to F11.

Step 12: Select the chart: Insert / Graph / Rows / Line 2d / Rows.

Learn how to make ABC curve in ExcelStep 13: Select the 1 Series caption and then click DELETE.

Learn how to make ABC curve in ExcelStep 14: The percentage axis is between 0 and 120%. To leave between the values ​​of 0 and 100% select the axis and click on Format axis.

Step 15: In axis options, select FIXED for minimum and maximum. Respectively leave them at 0,0 and 1,0. Click CLOSE.

Learn how to make ABC curve in Excel

Through these steps the worksheet will be ready. You can change colors and leave it any way you like.

Analyzing the spreadsheet data with ABC curve

Learn how to make ABC curve in Excel

Taking into account 3 groups A, B and C, we chose the reference 70-20-10.

That way group A represents 70% of total costs, group B 20%, and group 10%.

Learn how to make ABC curve in Excel

Taking into account the data of this worksheet, the groups are represented:

  • GROUP A: 3 products (9, 4 and 10 codes);
  • GROUP B: 2 products (5 and 8 codes);
  • GROUP C: 5 products (1, 3, 6, 7 and 2 codes).


  • 30% of products represent 70% of total costs;
  • 20% of products represent 20% of total costs;
  • 50% of products represent 10% of total costs.

This data is of fundamental importance for you to maintain a controlled inventory and perform an adequate management for perfect investments.

Did you like the spreadsheet? Has the analysis of a spreadsheet with ABC curve in excel already helped you to control your stock and make correct investments? Leave your comments regarding the experience with this spreadsheet.

See also the Inventory Control Worksheet from light!

Excel Spreadsheets


  1. You're welcome, Claudio! Hugs and any other questions I am at your disposal.

  2. No problem, they are approximate values. When defining the cuts you will have to make a decision, whether you want to pick up to the last value before the cut values ​​(70 - 20 - 10) or the next value

  3. Is Cesar talking, is everything okay? Let's do it by steps:

    1 - To make the third column, just take the values ​​of the accumulated percentages (column F), see that in the third product (quantity of type A) we have approximately 70%. To find this value you can do a VLOOKUP (I would create an initial auxiliary column with the numbering of the quantity of products (1, 2, 3, 4, etc.) and use the PRODUCTS column of the auxiliary table to find the corresponding percentage.

    2 - The% of total products is even simpler, just make a CONT of the total quantity of products and make a simple division of the quantity of products in a certain group (A, B or C) by this total.

    3 - The colors of the lines are a little more complicated. I don't really remember how it was done, but I believe that he inserted three series of data in the graph, each series looking for the accumulated percentage values ​​for each group and then changed the color of each of the lines - particularly I don't think that make the graph more explanatory ...

  4. it was necessary to place the region below the filled curve to be 10 = v

  5. I'm making an ABC curve using an autofilter table, I wonder if you know any formula that calculates the percentage accumulated when the filter is applied.

  6. Could you explain how this second table does and how do you determine these colored ABC lines?
    Thank you

  7. good afternoon,
    how do I get the values ​​in the last column of the second table,% total products?

  8. Hi Bruno, to do this calculation it is necessary to put the values ​​in descending order in a column and make the accumulated of each successively in another.

    So you get what it represents in% and how much the accumulated represents. From there, just think of the percentages and establish the criteria of which groups A, B and C are

  9. Hi Mineirinho, how are things good?

    If you want to improve your Excel skills, we have two very good online courses that can help you a lot! One is a beginner / intermediate and the other advanced. You can find out more about them (and even see some free classes) at this link -


Please, write your comment
Please enter your name