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

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

##  ### 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, • Press Enter.
• Drag down the “Fill handle” to get the result in all cells. • Thus we can get all the products sold on a particular day by using VLOOKUP formula. ### 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)` • Click Enter.
• Pull the “Fill handle” down. • This way unit price is sold in every day. ### 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` • Press Enter.
• To get total sales of every date drag down the “fill handle”. • As you can see, our sales report is ready if we want to calculate everyday total sales. ### 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)` • To get the name of the product in the cell click Enter after writing the formula down.
• Drag down the “Fill handle”. • In the following, you will see we got our product names sold in a particular month. ### 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. • Press Enter.
• Drag down the “fill handle”. • So here we got our total quantity sold in a month. ### 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)` • Click Enter.
• Now pull the “Fill handle” down. • Thus we got our total amount sold in a month. Simple isn’t it? So, here we have successfully completed our sales report. ## 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

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 