How to Make Sales Report in Excel (with Easy Steps)

In Microsoft Excel, it is a common task to make a sales report. Often we need to calculate sales for a day or month. I will show you some easy tricks to make a sales report in excel so that you can save a lot of time. Stay tuned.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


7 Quick Steps to Make Sales Report in Excel

In this article, I will explain 7 quick steps to make a sales report for a mobile shop in Excel. Here we will use three different datasets to make our sales report. In this report, we will find the total amount of money for each mobile phone model. After you complete all the steps the dataset will look like the following.

Make Sales Report in Excel


Step 1: Get Product Name from Product List

  • In order to make a sales report let’s take some data like a list of products containing product Code, Product Name, and Unit Price.

  • Now we will calculate daily sales. For that, we took a dataset like the following from which we will calculate product Name, Unit Price, and Total Sales of the day.

  • We got another data table to calculate the total sales of the whole month. Collecting data from the daily sales transaction table we will calculate total monthly sales.

  • Select cell (E7) to apply the formula.
  • Put the formula in the selected cell-
=VLOOKUP(D7,$B$20:$D$24,2,FALSE)

Where,

Get Product Name from Product List

  • Press Enter.
  • Drag down the “Fill handle” to get the result in all cells.

Get Product Name from Product List

  • Thus we can get all the products sold on a particular day by using VLOOKUP formula.

Read More: How to Make Daily Activity Report in Excel (5 Easy Examples)


Step 2: Find Out Unit Price in Sales Report

  • Let’s calculate the unit price of those products.
  • Select a cell (G7) and apply the following formula-
=VLOOKUP(D7,$B$20:$D$24,3,FALSE)

Find Out Unit Price in Sales Report

  • Click Enter.
  • Pull the “Fill handle” down.

Find Out Unit Price in Sales Report

  • This way unit price is sold in every day.

Read More: How to Generate Reports from Excel Data (2 Easy Methods)


Similar Readings


Step 3: Calculate Total Sales of the Day

  • To get the total sales of a single day we selected a cell (H7) to calculate.
  • Apply the formula-
=G7*F7

Calculate Total Sales of the Day

  • Press Enter.
  • To get total sales of every date drag down the “fill handle”.

Calculate Total Sales of the Day

  • As you can see, our sales report is ready if we want to calculate everyday total sales.

Read More: Create a Report That Displays Quarterly Sales in Excel (with Easy Steps)


Step 4: Calculate Monthly Sales of a Single Product in Excel

  • To get the overview of a sales report we need to calculate the monthly sales. Let’s calculate the monthly sales from our dataset.
  • In the monthly sales section of the dataset, at first, we will calculate the product names sold in the particular month. To do that-
  • Select a cell (G21).
  • Write the formula-
=VLOOKUP(F21,$B$20:$D$24,2,FALSE)

Calculate Monthly Sales of a Single Product in Excel

  • To get the name of the product in the cell click Enter after writing the formula down.
  • Drag down the “Fill handle”.

Calculate Monthly Sales of a Single Product in Excel

  • In the following, you will see we got our product names sold in a particular month.

Read More: How to Make Monthly Report in Excel (with Quick Steps)


Similar Readings


Step 5: Get Total Quantity of Products Sold

  • To compute the total quantity sold in the month, follow these steps-
  • I have selected cell (H21) to get the desired output-
  • Put the formula down in the cell-
=SUMIF($D$7:$D$16,F21,F7:F16)

Where,

  • The SUMIF function returns the sum of cells that meet a single condition.

Get Total Quantity of Products Sold

  • Press Enter.
  • Drag down the “fill handle”.

Get Total Quantity of Products Sold

  • So here we got our total quantity sold in a month.

Read More: Create a report that displays the quarterly sales by territory


Step 6: Calculate Total Sales to Complete the Sales Report

  • It’s time for the finishing touch. We will compute the total amount sold in the month.
  • To calculate total volume choose a cell (I21) to write down the formula.
  • In the cell apply the formula-
=SUMIF($D$7:$D$16,F21,H7:H16)

Calculate Total Sales to Complete the Sales Report

  • Click Enter.
  • Now pull the “Fill handle” down.

Calculate Total Sales to Complete the Sales Report

  • Thus we got our total amount sold in a month. Simple isn’t it? So, here we have successfully completed our sales report.

Read More: How to Make MIS Report in Excel for Sales (with Easy Steps)


Things to Remember

  • When applying the VLOOKUP formula in cells, sometimes #N/A error may show. This happens when a formula can’t find what it’s been asked to look for.
  • VLOOKUP function always lookup for values from left to right so keep your lookup value in the first column in your dataset.
  • For cell range, we will always use the absolute reference ($) symbol to keep the range fixed.

Conclusion

In this article, I have gone through all the steps to make a sales report properly. Hope you find it useful. If you find any problems regarding this article don’t forget to let me know in the comment section. Enjoy your time!


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo