Md. Abdur Rahim Rasel
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Easy Steps to Create a Report That Displays Quarterly Sales in Excel
Let’s assume we have an Excel large worksheet that contains the 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 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 (with Quick Steps)
Similar Readings
- How to Make Monthly Report in Excel (with Quick Steps)
- Make MIS Report in Excel for Sales (with Easy Steps)
- How to Make Inventory Aging Report in Excel (Step by Step Guidelines)
- Generate PDF Reports from Excel Data (4 Easy Methods)
- How to Prepare MIS Report in Excel (2 Suitable Examples)
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 which have been given in the below screenshot.
Read More: How to Create a Summary Report in Excel (2 Easy Methods)
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 has been given in the below screenshot.
Read More: How to Make Sales Report in Excel (with Easy Steps)
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.
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.
Related Articles
- How to Create an Expense Report in Excel (With Easy Steps)
- Create an Income and Expense Report in Excel (3 Examples)
- How to Generate Report in PDF Format Using Excel VBA (3 Quick Tricks)
- Make Production Report in Excel (2 Common Variants)
- How to Make Daily Activity Report in Excel (5 Easy Examples)
- Make Daily Production Report in Excel (Download Free Template)
- How to Make Report Card in Excel (Download Free Template)
