How to Use Sales Revenue Formula in Excel (6 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

In the business world, calculating sales revenue is a crucial task that helps organizations monitor their financial performance. Excel, being one of the most popular spreadsheet software, offers various formulas to calculate sales revenue with ease and accuracy. Using Excel, you can quickly calculate sales revenue based on different scenarios, such as fixed pricing, variable pricing, etc. In this article, we will explore six different examples of how to calculate different types of sales revenue using Excel formulas. By the end of this article, you will have a better understanding of the Sales Revenue Formula Excel and how to use it effectively

Overview of Calculating Sales Revenue Using Excel Formula


How to Use Sales Revenue Formula in Excel: 6 Suitable Examples

1. Calculating Sales Revenue By Multiplying No of Units sold with the Price

In this article, we are going to find out sales revenue by multiplying no of units sold by their prices.

Sales revenue= No of Unit Sold x Their Prices

We are going to use conventional formulas for most of the calculations, and the SUM function at the end. The SUM function sums up the range, arguments, or arrays inside of it.

To do so we are going to follow the following steps on the below dataset.

Dataset for Calculating Sales Revenue By Multiplying No of Units sold with Price

  • First, we are going to multiply price and quantity to find individual revenue. Here in cell F5, we calculated sales revenue by multiplying price (cell E5) and quantity(cell D5).

Calculating Sales Revenue by multiplying Quantity and Price

  • Now we are going to drag the Fill Handle bar down from F5 to F12. After pressing Enter in the F5 cell.

Calculating Individual Sales Revenue

  • Now we are going to find out Gross Sales Revenue by summing up individual sales revenue.

Summing up the individual sales to find gross profit

  • Finally, we are going to find out individual and gross sales revenue.

Showing Gross sales Revenue


2. Calculating Sales Revenue By Pricing Table

In this article, we are going to find out sales by using a pricing table. We used the VLOOKUP function to find out the individual price from the pricing table then we calculated the individual sales revenue and summed them up for gross revenue. To do so we are going to follow the steps given below in the dataset shown below.

Dataset for Calculating Sales Revenue By Pricing Table

  • First, we are going to use the VLOOKUP function to seek out the price of the fabric.

=VLOOKUP(B5,$H$5:$J$8,3) 

🔎Formula Breakdown

👉 The first argument, “B5”, is the value that we want to look up in the table.

👉 The second argument, “$H$5:$J$8”, is the table array that we want to search. This table array is composed of three columns: H, I, and J, and four rows: 5, 6, 7, and 8. Note that the dollar signs in front of the row and column references make them absolute references, which means that they won’t change when the formula is copied to other cells.

👉 The third argument, “3”, is the column index number that we want to return a value from. In this case, we want to return a value from the third column of the table array (column J).

Seeking out price from pricing table

  • After pressing Enter we are going to drag the Fill Handle down C5 to C11 to find individual prices.

Seeking out individual Price

  • Then we are going to find out sales revenue like example 1 by multiplying the Price and Meters of Fabric Sold. We will be using the following formula.=C5*B5.

Calculating Individual Sales revenue

  • Now after pressing Enter and dragging the Fill Handle icon down, we find the individual sales revenue.

Calculating Sales Revenue by filling the column

  • After that, we are going to find out gross sales revenue. To do so, we just have to sum up the individual sales revenue.

Finding gross revenue by summing up the sales column

  • After pressing Enter, we are going to have total sales revenue.

Finally figuring out gross sales revenue


3. Calculating Incremental Sales Revenue

It is very easy to find incremental sales revenue. The formula for incremental sales revenue,

Incremental Sales Revenue=Sales Revenue(Present)-Sales Revenue(Previous Date)

In order to find incremental sales revenue in Excel we are going to follow the steps given below. We have used a dataset given below.

Dataset for Calculating Incremental Sales Revenue

  • First, we put the formula in the G5 cell.

Calculating incremental sales revenue using formula

  • After pressing Enter, we are going to find Incremental Sales Revenue.

Calculation done for incremental sales revenue


4. Calculating Average Sales Revenue

For calculating average sales revenue we have divided gross revenue by the total quantity sold. How to find gross sales revenue is given in both example and example 2. We are just going to divide gross sales revenue by the total quantity sold. To do so, we have to follow the steps given below and use the dataset below.

Dataset for Calculating Average Sales Revenue

  • First, we are going to write the formula in H8. The formula is Gross Sales Revenue/total sold quantity. We have used the SUM function to find out the total quantity sold.

Calculating average sales revenue by excel formula

  • The formula in Excel is

=H5/SUM(D5:D12)

SUM(D5:D12)=Sum of Sold Quantity

  • Now pressing Enter, we are going to find the average sales revenue.

Calculation done for average sales revenue


5. Calculating Quarterly Sales Revenue

In this section, we are going to find quarterly sales revenue using the Excel formula by subtracting the old total sales revenue from the new quarterly sales revenue. We have used the SUM function to find out the quarterly sales revenue of both quarter 1 and quarter 2 and subtracted quarter 1 total sales from Quarter 2 sales to find out quarterly sales revenue. To do so, we are going to follow the steps given below and we have used the dataset below.

Dataset for Calculating Quarterly Sales Revenue

  • First, we are going to find out the total individual sale revenue below.

Calculating Individual Sales Revenue of Quarter 1

  • Then we are going to use the following formula in cell E5 and fill up to E8 accordingly.

=D5*C5

Creating individual sales revenue for quarter 1

  • Then we are going to sum up the total sales of quarter 1 in the F9 cell.

Calculating total sales for quarter 1

  • Likewise, we have to create the data set of quarter 2 like quarter 1 by multiplying Price(D12)*Unit Sold(C12) in the E12 cell and dragging the fill handle icon from E12 to the E15 cell. Then sum up E12 to E15 in the E16 cell.

Calculating total sales for quarter 2 like quarter 1

  • Then finally we are going to find out the Quarterly Sales Revenue by subtracting Q2 Total(E16)-Q1 Total(E9).

Calculating quarterly sales revenue


6. Calculating Marginal Sales Revenue in Excel

To calculate marginal sales revenue  we have to follow this formula,

(Total Revenue - Old Revenue) / (Total Quantity - Old Quantity)

To do so in Excel, we have to follow the steps and use the dataset below.

Dataset for Calculating Marginal Sales Revenue in Excel

  • First, we have to write the Excel formula in the B9 cell.

Marginal Sales Revenue =(Total Revenue(F6) – Old Revenue(F5)) / (Total Quantity(D6) – Old Quantity(D5)), using this formula we find the marginal sales revenue.

  • You can copy the formula from here.

=(F6-F5)/(D6-D5)

Calculating Marginal Sales Revenue by using excel formula

  • After pressing Enter, we are going to have the Marginal Sales Revenue.

Calculation done for marginal sales revenue by using excel formula


Download Practice Workbook


Things to Remember

  • Make sure to enter your sales data accurately into Excel, including the quantity, price, discounts, taxes, and shipping costs for each product sold.
  • Use absolute cell references ($) when using formulas that refer to specific cells or ranges, to avoid errors when copying and pasting the formulas.
  • Check your formulas and calculations carefully to make sure they are correct and use error-checking tools such as Excel’s “Trace Error” feature to identify and fix any errors.
  • Use Excel’s formatting tools to make your sales revenue calculations more visually appealing and easier to read, such as number formats, conditional formatting, and charts.
  • Regularly update your sales data and recalibrate your formulas as needed to ensure accurate and up-to-date sales revenue calculations.

Conclusion

In conclusion, learning how to calculate sales revenue using the Excel formula can be a powerful tool for businesses of all sizes. By mastering the different formulas for sales revenue, businesses can automate their sales revenue calculations, gain insights into their financial performance, and make data-driven decisions to improve their revenue streams. With the right data inputs, formulas, and formatting techniques, Excel can be a powerful ally in the quest for increased revenue and profitability.


Frequently Asked Question

  • Q: Why is it important to calculate sales revenue in Excel?

A: Calculating sales revenue in Excel is important because it helps businesses track their financial performance, monitor their sales trends, and identify areas where they can improve their revenue streams. By using Excel formulas to calculate sales revenue, businesses can also automate their calculations, save time, and reduce the risk of errors.

  • Q: Can I use Excel to create sales reports and projections based on sales revenue data?

A: Yes, you can use Excel to create sales reports and projections based on sales revenue data by using pivot tables, charts, and other analysis tools. These reports and projections can help you gain insights into your sales performance, forecast future trends, and make data-driven decisions to improve your business outcomes.

  • Q: How can I use Excel to track my sales revenue on a daily, weekly, or monthly basis?

A: You can use Excel to track your sales revenue on a daily, weekly, or monthly basis by entering your sales data into a worksheet or table, and using date and time functions to group your data by day, week, or month. You can then use formulas and charts to calculate and visualize your sales revenue for each period.

  • Q: Can I use Excel to calculate sales revenue for e-commerce transactions, such as online orders and digital products?

A: Yes, you can use Excel to calculate sales revenue for e-commerce transactions by importing your sales data from your e-commerce platform or payment processor into Excel, and using formulas to calculate the revenue based on the quantity and price of the products sold. You can also use Excel to track your e-commerce sales performance, analyze customer behavior, and optimize your online store for better revenue outcomes.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Joyanta Mitra
Joyanta Mitra

Joyanta Mitra, a BSc graduate in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. Specializing in programming, he has authored and modified 60 articles, predominantly focusing on Power Query and VBA (Visual Basic for Applications). His expertise in VBA programming is evident through the substantial body of work he has contributed, showcasing a deep understanding of Excel automation, and enhancing the ExcelDemy project's resources with valuable... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo