Shajratul Alam Towhid
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.
Download Practice Workbook
2 Methods 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. Grouping Data by Month Using Pivot Table 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.
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.
Then, as seen in the following image, choose the following options from the dialog box.
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.
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.
Secondly, navigate to Excel Options at the page’s bottom.
Next, choose the Data tab and tick the box next to the option that is seen below.
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.
Additionally, choose the Date; in this case, 10-Jan.
After that, pick Group Selection from the drop-down menu on the PivotTable Analyze ribbon.
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.
As a result, you’ll eventually discover the table as output.
Read More: How to Use Excel Pivot Table to Group Dates by Month and Year
Similar Readings
- Excel Pivot Table Auto Grouping by Date, Time, Month, and Range!
- How to Group Dates by Filter in Excel (3 Easy Methods)
- How to Group Dates in Excel Slicer (4 Ways)
2. Grouping Data by Month Without Using Pivot Table in Excel
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.
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.
Now, you need to put the following formula in the H5 cell.
=SUMIF($G$5:$G$15,G5,$E$5:$E$15)
Similarly, after pressing ENTER, you will find the output as 1380.
Finally, you need to use the Fill Handle to find all the outputs.
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.
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.
