In this article, we will explore several important **Excel Sales formula** 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**

Download the **Excel** file here and practice.

**Table of Contents**Expand

## How to Calculate Sales in Excel Using Formula

### 1. Calculate Sales Using Sales Formula

First, I will explain how you can calculate sales using the simple sales formula. Here, 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**.

- The formula in
**cell F5**is

`=D5*E5`

- Then, we used the
**Fill Handle**to**AutoFill**up to**F12**.

- Next, we will add all the individual sales revenue to get the total sales revenue using the following formula in
**cell F13**.

`=SUM(F5:F12)`

### 2. Calculate Sales Percentage

Now, I will 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`

- Then, change the format of
**range G5:G12**to**Percentage****>>****AutoFill**up to**G13**.

### 3. Apply SUMPRODUCT Function to Calculate Total Sales

Another function you can use to calculate total sales in a single click is **the SUMPRODUCT function**.

- The formula in
**E13**is

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

**Excel** returns the sum of the products of the **ranges D5:D12** and **E5:E12**. That means, Excel will add the results of **D5*E5**, **D6*E6,** and so on.

### 4. Apply 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)`

- Then, press
**CTRL + SHIFT + ENTER**in lieu of**ENTER**to execute an array formula.

**Read More:** How to Calculate Percentage of Sales in Excel

### 5. Use Data Validation to Calculate Total Sales

Now, letâ€™s explore a slightly advanced way to calculate total sales. This method will require the use of the **Data Validation** feature and **the SUMIF function**.

- Letâ€™s assume that 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 in the data validation feature. - Select the range where you are going to apply the data validation feature (in this case, it is
**D5:D14**)**>>**go to the**Data**tab**>>**select 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**>>**click**OK**.

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

- Now, in the
**Sales Record**sheet, you can select the product code using the drop-down button.

- The sheet will look like this.

- Now, to calculate the total unit sales, go to
**cell D5**of another sheet named**Sales Revenue**and write down the formula.

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

- Then,
**AutoFill**up to**cell****D8**.

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

- Then, multiply the total units by the unit price (from the
**Product List**sheet). The formula in**E5**is,

`=D5*'Product List'!D5`

- After that,
**AutoFill**up to**E8**.

**Read More:** How to Calculate Total Sales in Excel

### 6. Calculate Percent Increase or Decline in Sales

Itâ€™s always important to know the increase or decline in sales percentage. 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`

- Then,
**AutoFill**up to**D12****>>**change the format to**Percentage**.

**Excel** calculates the growth or decline in sales.

**Note:**

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

### 7. Measure Gross Margin in Excel

Now, we will explain how you can calculate the gross margin in **Excel**. The formula to calculate the gross margin is,

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

Letâ€™s calculate the gross margin using this formula.

- Write down the following formula in
**cell F5**.

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

- Then
**AutoFill**up to**F12****>>**change the format to**Percentage**.

**Excel** returns the gross margin.

**Explanation**

**((D5-E5)*C5)**â†’ This calculates**Total Revenue â€“ Total Cost of Goods Sold**.

**Output: 2500**

**((D5-E5)*C5)/(D5*C5)**â†’ This calculates the**Gross Margin**.

**Output: 9%**

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

## Things to Remember

- In the lates
**Excel**versions, you do not need to press the**CTRL + SHIFT + ENTER**to execute an array formula. - To get the decline or growth of any period, you should have the data of the previous period.
- Gross margin is calculated in percentages mostly.

## Conclusion

In conclusion, by learning and using the basic **Excel** sales formula we discussed in this article, you can improve how you analyze sales data and make smarter business choices.

These formulas help you calculate sales accurately and figure out the percentages for different products or regions.

Advanced functions like **SUMPRODUCT** and array formulas provide more detailed insights. Data validation techniques ensure your calculations are correct, and measuring percentage changes in sales helps you see how well your strategies are working.

With these powerful tools, you can make the most of **Excel** for sales analysis and drive your business toward success.

## Frequently Asked Questions

**1. How can I calculate sales using Excel formulas?**

**Ans:** To calculate sales in **Excel**, you can use basic functions depending on your specific requirements. These formulas allow you to add up sales values based on different criteria, such as product, region, or time period.

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

**Ans:** To determine the sales percentage for a product, you can 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?**

**Ans:** Array formulas in **Excel** 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**