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

Get FREE Advanced Excel Exercises with Solutions!

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.

## How to Make Sales Report in Excel: 7 Quick Steps

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, the unit price is sold 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 for 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, • 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. I 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!

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Wasim Akram

Wasim Akram, BSc, Industrial and Production Engineering, Ahsanullah University of Science & Technology, has been working with the ExcelDemy project for 10 months. Currently working as as Excel and VBA content developer who provides authentic solutions to different Excel-related problems and writes amazing content articles regularly. He published almost 150 articles and has many more coming. He is very passionate about learning new things about Microsoft office Suite and Data analysis.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  