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**:

`=D5*E5`

- 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**:

`=SUM(F5:F12)`

### 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:

`=F5/$F$13`

- 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:

`=SUMPRODUCT(D5:D12,E5:E12)`

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:

`=SUM(D5:D12*E5:E12)`

- 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**OK**:

`='Product List'!$B$5:$B$8`

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:

`=SUMIF('Sales Record'!$D$5:$D$14,B5,'Sales Record'!E5:E14)`

**AutoFill**down to**cell****D8**.

**Formula Explanation**

- The formula adds up the values in the
**‘Sales Record**‘ sheet’s**range E5:E14**only if the corresponding cells in the**‘Sales Record’**sheet’s**range D5:D14**match the value in**cell B5**. - This allows you to calculate the total sales from the
**‘Sales Record’**sheet that meets a specific criterion, such as summing the sales for a particular product.

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

- The formula in
**E5**is:

`=D5*'Product List'!D5`

**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:

`(New sales-Old sales)/Old sales (in percentage)`

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

- The formula in
**cell D6**is:

`=(C6-C5)/C5`

**AutoFill**down to**D12**.- Change the format to
**Percentage**.

The growth or decline in sales will be returned.

**Note:**

Since we have no data for the year **2015**, we cannot calculate the growth or decline in **2016**.

### Example 7 – Measuring Gross Margin

The formula to calculate the gross margin is:

`Gross Margin = (Total Revenue - Toral Cost of Goods Sold) / Total Cost of Goods Sold`

Let’s apply the formula to our dataset:

- Enter the following formula in
**cell F5**:

`=((D5-E5)*C5)/(D5*C5)`

**AutoFill**down to**F12**.- Change the format to
**Percentage**.

The gross margin is returned.

**Explanation**

**((D5-E5)*C5)**→ Calculates**Total Revenue – Total Cost of Goods Sold**.

**Output: 2500**

**((D5-E5)*C5)/(D5*C5)**→ Calculates the**Gross Margin**.

**Output: 9%**

**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**

How do you incorporate the missed sales in these functions