Excel Sales Formula: All You Need to Know

In this article, we will explore several important Excel Sales formulas that can greatly improve your sales analysis and help you to find valuable insights from your data.

We’ll start by showing a step-by-step process of how to calculate your sales using simple formulas. Next, you will learn an easy technique to determine the sales percentages for different products or regions.

For more advanced calculations, we will introduce the SUMPRODUCT function. We’ll also cover array formulas, which offer flexibility and control when calculating your total sales.

To ensure accuracy in your calculations, we will explain how to use data validation methods. These techniques help prevent errors and inconsistencies when entering data, making your analysis more reliable.

Lastly, we’ll guide you through measuring the percentage increase or decrease in sales over time. By mastering these Excel sales formulas, you’ll be able to analyze your sales data effectively and make smarter business decisions.

Download Practice Workbook

Example 1 – Calculating Sales Using the Sales Formula

To start, let’s calculate sales value using the simple sales formula. We will calculate the sales revenue of each product by multiplying the quantity and unit price. Then, we will add the sales of each item to get the total sales using the SUM function.

• Enter the following formula in cell F5:

• Use the Fill Handle to AutoFill down to cell F12.

• To add all the individual sales revenues to get the total sales revenue, enter the following formula in cell F13:

Example 2 – Calculating Sales Percentage

Now let’s measure the sales percentage of individual items. We will divide the sales revenue of each product by the total sales.

• The formula in cell G5 is:

• Change the format of range G5:G12 to Percentage.
• AutoFill down to G13.

Example 3 – Using SUMPRODUCT Function to Calculate Total Sales

We can calculate total sales with a single click using the SUMPRODUCT function.

• The formula in E13 is:

The function returns the sum of the products of the ranges D5:D12 and E5:E12. In other words, it will add the results of D5*E5, D6*E6, and so on.

Example 4 – Using an Array Formula to Calculate Total Sales

Now, let’s use an array formula to calculate total sales.

• The formula in E13 is:

• Press CTRL + SHIFT + ENTER in lieu of ENTER to execute an array formula.

Read More: How to Calculate Percentage of Sales in Excel

Example 5 – Using Data Validation to Calculate Total Sales

Now, let’s explore a more advanced way to calculate total sales, using the Data Validation feature and the SUMIF function.

Suppose we have a product list like this on a sheet named Product List:

In another sheet named Sales Record, we will apply the Product Code (P. Code column) as a Data Validation feature.

Steps:

• Select the range where data validation will be applied (in this case, D5:D14).
• Go to the Data tab.
• Click the Data Validation icon.

A Data Validation window will appear.

• Choose List criteria from the Allow option.
• Insert the following formula in the Source box and click OK:

In the Sales Record sheet, you can now select the product code using the drop-down button.

The sheet will look like this.

To calculate the total unit sales:

• Go to cell D5 of another sheet named Sales Revenue and enter the following formula:

• AutoFill down to cell D8.

Now we multiply the total units by the unit price (from the Product List sheet).

• The formula in E5 is:

• AutoFill down to E8.

Read More: How to Calculate Total Sales in Excel

Example 6 – Calculating Percent Increase or Decline in Sales

The formula to calculate the change in sales percentage is:

A positive value indicates an increase and a negative value indicates a decline in sales.

• The formula in cell D6 is:

• AutoFill down to D12.
• Change the format to Percentage.

The growth or decline in sales will be returned.

Example 7 – Measuring Gross Margin

The formula to calculate the gross margin is:

Let’s apply the formula to our dataset:

• Enter the following formula in cell F5:

• AutoFill down to F12.
• Change the format to Percentage.

The gross margin is returned.

Read More: How to Calculate Sales Conversion Rate Using Formula in Excel

Things to Remember

• In the latest Excel versions, you do not need to press CTRL + SHIFT + ENTER to execute an array formula, ENTER will suffice.
• To get the decline or growth in any period, you need the data from the previous period.
• Gross margin is calculated in percentages mostly.

Frequently Asked Questions

1. How can I calculate sales using Excel formulas?

Use basic functions depending on your specific criteria, such as product, region, or time period. See above for examples.

2. How do I determine the sales percentage for a specific product?

Divide the sales of that product by the total sales and multiply by 100.

3. What are array formulas and how can they be beneficial for calculating total sales?

Array formulas allow you to perform calculations on multiple cells or ranges of data. They provide flexibility and control when calculating total sales by enabling you to process multiple conditions or criteria in a single formula.

Excel Sales Formula: Knowledge Hub

<< Go Back to Formula List | Learn Excel