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

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.

create a report that displays quarterly sales in excel


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.

create a report that displays quarterly sales in excel

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

create a report that displays quarterly sales in excel

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


Similar Readings


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.

create a report that displays quarterly sales in excel

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

create a report that displays quarterly sales in excel

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.

create a report that displays quarterly sales in excel

  • As a result, you will get a 2-D Pie chart which has been given in the below screenshot.

create a report that displays quarterly sales in excel

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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo