While working with large Microsoft Excel, sometimes we need to create a report that displays quarterly sales. Creating a report that shows quarterly sales in Excel is an easy task. This is a time-saving task also. Today, in this article, we’ll learn two quick and suitable ways to create a report that displays quarterly sales in Excel effectively with appropriate illustrations.
How to Create a Report That Displays Quarterly Sales in Excel: 3 Easy Steps
Let’s assume we have a Excel large worksheet that contains information about several sales representatives of Armani Group. The name of the sales representatives, the Product Name, the Saling Date of the products, and the revenue Earned by the sales representatives are given in Columns B, C, D, and E respectively. We can easily create a report that displays quarterly sales in Excel by using the CHOOSE, MONTH, SUMIF Functions, and so on. Here’s an overview of the dataset for today’s task.
Step 1: Combine CHOOSE and MONTH Functions to Determine Quarterly Sales
In this step, we will apply the CHOOSE and MONTH functions to create a report that displays quarterly sales. This is an easy task. Let’s follow the instructions below to create a report that displays quarterly sales!
- First of all, select cell F5.
- After selecting cell F5, type the below formula in that cell. The function is,
=CHOOSE(MONTH(C5),1,1,1,2,2,2,3,3,3,4,4,4)Formula Breakdown:
- C5 is the serial_num of the MONTH function.
- MONTH(C5) is the index_num of the CHOOSE function, and 1,1,1,2,2,2,3,3,3,4,4,4 is the value of month number.
- After typing the formula in the Formula Bar, simply press Enter on your keyboard, and you will get 1 as the output of the functions.
- Hence, autoFill the CHOOSE and MONTH functions to the rest of the cells in column F.
Read More: How to Make Daily Sales Report in Excel
Step 2: Apply SUMIF Function to Get Total Quarterly Sales
Now, we will apply the SUMIF function to sum up the total sales in terms of the quarter. Let’s follow the instructions below to create a report that displays quarterly sales!
- First, select cell D17, and write down the SUMIF function of that cell.
=SUMIF(F5:F14,C17,E5:E14)
- Further, simply press Enter on your keyboard, and you will get $131,385.00 as the output of the SUMIF function.
- Hence, autoFill the SUMIF function to the rest of the cells in column D.
- After that, sum up the sales in the different quarters in cell D21.
- After completing the above process, you will be able to create a report that displays quarterly sales, as shown in the below screenshot.
Step 3: Create a Pie Chart to Display Quarterly Sales Report in Excel
After creating a report that displays quarterly sales, we will create a 2-D Pie Chart to understand the sales in different quarters. A 2-D Pie Chart helps you to understand the results. Let’s follow the instructions below to do that!
- To create a 2-D Pie Chart, first of all, select the data range. We will select cells D17 to D20 for the convenience of our work. Hence, from the Insert tab, go to,
Insert → Chart → Insert Pie
- Further, select 2-D Pie.
- As a result, you will get a 2-D Pie chart, which is given in the below screenshot.
Read More: How to Make Sales Report in Excel
Things to Remember
➜ While a value can not found in the referenced cell, the #N/A error happens in Excel.
➜ When the cell reference is not valid, then Excel shows you #REF! error.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to create a report that displays quarterly sales will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
