How to Calculate Total Sales in Excel – 3 Easy Steps

Step 1- Create a List of Products in Excel

• Create a new worksheet and enter the product list, the product code and its price.

• Select the cells to define a Name for the Product Code column.
• Go to Formulas > Define Name.

• Enter a name in the window.
• Click OK.

Step 2 -Save the Sales in a New Worksheet

• Open a new sheet and insert the necessary columns.
• Go to the Product Code column.
• To restrict the cells: select them and go to Data > Data Validation

• In the Data Validation window, select Settings.
• Choose List in Allow.
• Enter “=code” in the Source box.
• Click OK.

• A drop-down list will be displayed in every cell in this column.

• Create the Sales Data for the period of time.

Step 3 – Calculate the Total Sales in a New Worksheet

Calculate the Total Sales of each product in that period.

• Enter the list of products.

• Enter this formula in D5.

• Use the Fill Handle to drag the formula to the other cells or press Ctrl+C and Ctrl+V to copy and paste the formula.

• The total sales of each product is displayed.
• To calculate the total revenue of each product, enter this formula in E5.

The formula will multiply the value of D5 (total sale value) by the D5 in the product worksheet (the Unit Price).

• Use the Fill Handle to drag the formula to the other cells
• The total sales and the total revenue of each product in a period of time are displayed.

Download Sample Workbook

<< Go Back to Sales | Formula List | Learn Excel