Excel Sales Formula: All You Need to Know

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.

Excel Sales Formula.xlsx

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.

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.

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%

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

Get FREE Advanced Excel Exercises with Solutions!