How to Group Data by Month in Excel (2 Useful Methods)

Get FREE Advanced Excel Exercises with Solutions!

Grouping data in Excel is a very important task for practical work. We need to group data by month or by date or by year. You can easily group data by month in Excel by exploring the methods of grouping data by month. In this article, we’ll try to discuss how to group data by month in Excel.

We can group data by using PivotTable both automatically and manually or by using simple Excel formulas. We can organize the PivotTable into groups to assist us create the desired data.  Let’s examine how to group a pivot table in Excel by month.


1. Using Pivot Table to Group Data by Month in Excel

You can automatically categorize cells with date formatting in PivotTables into Days, Months, Years, etc. In actuality, this is a useful function that helps you save time when you have short time to prepare your database. Check out the procedure in more detail.


1.1 Grouping Data by Month Automatically

Let’s say we have the following dataset of the Sales Chart of Mobile Phone of Huawei Company from January to March. The first column in this table indicates the Store Number, the second column the Delivery Date, third column the Quantity of Sales and the fourth column the Selling Price in USD.

How to group data by month in Excel automatically

You need to follow below steps to group data automatically.

Step 01: Create a Pivot Table

Click the Insert ribbon at the top to get started.

Then select the From Table/Range option from the PivotTable drop-down menu.

How to group data by month in Excel automatically

Then, as seen in the following image, choose the following options from the dialog box.

How to group data by month in Excel automatically

Step 02: Group the Data by Month

In order to display the table below, drag the field items into the Rows and Values fields.

You need to rename the table heads in the formula bar as Date & Month of Selling like below.

How to group data by month in Excel automatically


1.2 Grouping Data by Month Manually

PivotTable allows you to manually or in a certain format organize Dates. So just keep going.

Step 01: Go to Excel Option

You must first turn off the function that automatically groups dates.

To do this, select File from the top menu.

How to group data by month in Excel manually

Secondly, navigate to Excel Options at the page’s bottom.

How to group data by month in Excel manually

Next, choose the Data tab and tick the box next to the option that is seen below.

How to group data by month in Excel manually

Step 02: Create a Pivot Table Grouped by Dates

Create a PivotTable in the same manner as previously described.

Secondly, drag the objects into the appropriate fields as shown in the screenshot below.

How to group data by month in Excel manually

Additionally, choose the Date; in this case, 10-Jan.

After that, pick Group Selection from the drop-down menu on the PivotTable Analyze ribbon.

How to group data by month in Excel manually

The start date (Starting at), end date (Ending at), and grouping (By) options are then presented in a box as shown below. Please be aware that you can choose numerous choices from the grouping list.

Here, you need to choose Months to group data by month, and lastly click OK.

How to group data by month in Excel manually

As a result, you’ll eventually discover the table as output.

How to group data by month in Excel manually

Read More: How to Use Excel Pivot Table to Group Dates by Month and Year


Similar Readings


2. Grouping Data by Month Combining Excel Functions

You can also use simple formulas to group data. To implement the formula you first need to write in cell G5 like this.

=MONTH(C5)+((YEAR(C5)-2022)*12)

Here, C5 refers to the date which the serial needs to calculate.

The MONTH (C5) function takes the value of the month from the C5 cell. Here, it is 1. YEAR(C5) function takes the value of the year from the C5 cell. It is 2022. The output is subtracted by 2022 by the argument YEAR(C5)-2022. This output is zero. Then it is multiplied by 12. The whole formula MONTH (C5)+((YEAR(C5)-2022)*12) gives the output 1.

using MONTH and YEAR function

After clicking ENTER, you’ll find the output as 1 i.e. the date in the C5 cell is the number 1 month of the year. By using Fill Handle from cells G6 to G15  you’ll find all the serials of months of the cells C6 to C15.

using MONTH and YEAR function

Now, you need to put the following formula in the H5 cell.

=SUMIF($G$5:$G$15,G5,$E$5:$E$15)

using MONTH, YEAR and SUMIF function

Similarly, after pressing ENTER, you will find the output as 1380.

Finally, you need to use the Fill Handle to find all the outputs.

using MONTH and YEAR function

Notice: You can use any method of removing duplicate values from the output. so we’ll get only the 3 rows for 3 months instead of duplicate values.

Read More: How to Group Pivot Table by Month in Excel (2 Methods)


Things to Remember

  • You need two different reference datasets to group data by month both automatically and manually. If you make the same dataset as a reference, after disabling automatic grouping the excel sheet will change to manual grouping mode and the corresponding grouping will change too.
  • In the PivotTable Fields bar, you don’t need to fill both rows and columns. Rather we need to fill rows and columns according to requirement.

Download Practice Workbook


Conclusion

By studying this article you can easily apply the methods of grouping data by month in practical fields. Please don’t forget to visit our official Excel learning page ExcelDemy for any query.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo