How to Make Monthly Sales Report in Excel (with Simple Steps)

A monthly sales report represents sales activity within a company by monitoring, evaluating, and analyzing sales trends on a monthly basis. It is a must for a sales manager to make a monthly sales report at the end of the month. If you are looking for some easier ways and tricks to make a monthly sales report in excel then you are at the right place. Stay tuned.


Download Practice Workbook

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


2 Quick Steps to Make Monthly Sales Report in Excel

In this article, I am going to show you 2 quick steps to make a monthly sales report in excel.

Suppose we have a dataset of an Electronics shop. In the dataset, we have the sales of January, February, and March in different sheets. Now we will calculate the total 3 months of sales in a new sheet taking data from each sheet.

  • The first picture shows the Sales for January.

Make Monthly Sales Report in Excel

  • The following represents Sales for February.

Make Monthly Sales Report in Excel

  • This screenshot represents the Sales in March.

  • We took a new worksheet to calculate the monthly sales report for each item every month

Make Monthly Sales Report in Excel


Step 1: Define Range for Each Month

Firstly, you have to define the range from each sheet so that you can write the formula in the final sales report sheet easily.

  • Select cells (C4:F10).
  • While the data is selected type “January” in the name box as we are selecting the data range from “Sales of January”.
  • Hit the Enter button to continue.

Define Range for Each Month

  • Now go to the next sheet and select the data range. Here I have selected cell (C4:F10).
  • Type “February” in the name box and press Enter to continue.

  • Choose the next worksheet “March” from the workbook and follow the previous steps.

Define Range for Each Month

  • To check or edit the selected range you can travel to the “Formulas” option and click on “Name Manager”.

Define Range for Each Month

  • A new window will appear named “Name Manager”.
  • You can edit the range or edit names from this new window.

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


Step 2: Apply Formula to Make Monthly Sales Report

As our data range is selected properly. Let’s calculate the monthly sales report with a formula in our final sheet. To do so follow the below steps-

  • Select a cell to apply the formula. Here I have selected cell (D5).
  • Put the formula down in the selected cell-
=VLOOKUP($C5,IF(D$4="January",January,IF(D$4="February",February,IF(D$4="March",March))),4,FALSE)

Where,

  • The VLOOKUP function is a premade function that looks for information in a data range or string.
  • The IF function returns one value if true and another value if false within a given condition.
  • In the Table Array (D$4=”January” – if it’s true it will collect data from “Sales of January”. If false then it will go to “February” or “March”.
  • Col_index_num is 4. It is the column from which we will get the total sale of each item.
  • As we want the exact match for our lookup value, we will select FALSE.

Apply Formula to Make Monthly Sales Report

  • Press Enter to get the output. From the screenshot below we can see that we have got the price of the Air Condition for the month of January.

Apply Formula to Make Monthly Sales Report

  • Drag down the “fill handle” down to see the result of all items in the column.
  • Now drag to the right side to get the result for February and March too.

Apply Formula to Make Monthly Sales Report

  • We have successfully made our monthly sales report in excel just by using a simple formula.

Apply Formula to Make Monthly Sales Report

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


Things to Remember

  • Sometimes after applying the formula #N/A error may occur which generally indicates that a formula can’t find what it’s been asked to look for. In that case, check the formula properly whether the range and references are selected properly or not.

Conclusion

In this article, I have tried to cover the simple and quickest steps to make a monthly sales report in excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


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.

2 Comments
  1. HELLO, HOW TO SOLVE THESE PROBLEMS IN EXCEL?

    1.Five girls are in bakery business, and they want to check how they have done sales in every month.

    2.They have different products like Cake, Pies, Sandwich, Bread, Burger, and Donuts.

    3.Please create pie chart to understand which product is sold high and low by all the ladies

    4.If the sales of cakes are more than 100, that lady is announcing 10% discount for the next month

    5.Ladies with only 5 letters in their name are allowed to use data validation for their cake products

  2. Dear FODAY,

    Thank you for your response. As per your query-

    1. You can create a Pie Chart to visualize the sales from the “Insert” option.

    Imagine a dataset with multiple Bakery Products and their Total Sales. Now we will create a pie chart using the information from the dataset.

    First, select the products and total sales column and then press “Recommended Chart” from the “Insert” option.

    Second, from the new dialog box choose “Pie Chart” and press OK to continue.

    Finally, within a moment your final pie chart will be in your hands showing sales of multiple products.

    2. As per your second query-
    We will use the IF function to calculate the “10%” discount price if the sales of cake is more than 100.
    To do that choose a cell (G5) and write the following formula down-

    =IF(E5>100,(D5-(D5*0.1)),D5)

    Hence, hit Enter and the final output will be in your hands.

    3. Now coming to the last query you wanted to use the data validation for ladies with only 5 letters in their name. Well, below I have shared the simplest solution.
    Suppose we have a dataset with some Lady Names. Now we will use data validation for the name list.

    First, selecting the names from the list click the “Data Validation” option from the “Data” feature.

    Second, click “Settings” and choose criterias according to the screenshot and press OK to continue.

    Finally, we have applied data validation over the name list. To check try editing any names not fulfilling the condition and you will get a notification about “This Value doesn’t match the data validation restrictions defined for this cell.

Leave a reply

ExcelDemy
Logo