Analyses with pivot tables becomes much easier and can save a lot of your time as it presents data into a summarize way. Pivot tables remove all duplicate label. Still the data can be bulky enough in pivot table which may reduce the efficiency of user if not summarized more.

Hmm…What’s the solution!! Ever thought that grouping things together helps!! Yess, it helps! So let me tell you that you can group dates by month and year in a pivot table…Wow!! Let’s see how to convert dates into months/ quarter/ years in pivot table with just one click.

How to convert dates into months/ quarters/ years in pivot table

Pivot tables has the one of the most useful features to group the items which is can be used on items of row label or column label. We can group items in a following way.

  1. Group by dates
  2. Group by numbers

1. Group by dates

Let’s understand how to convert dates into months/ quarters/ years in pivot table with example.

Here is the Sales Register containing columns of Date, Branch, customer name, item, quantity sold, selling price and sales amount of around 50 line item.
Sales Register for analysis

  1. Insert a pivot table.inserting a pivot table
  2. Drag the date field in the rows label or columns label. (For our example we are continuing with rows label.)
  3. Drag the customer field to rows label and Quantity and Amount to Values label. (You can skip this step. However we have taken this steps to make data analysis and groups by date more logical and understanding.)
  4. Right click on the any date and select group.Convert dates months quarters years pivot table
  5. Now you will get options to group the dates by month/ quarter/ years etc.Convert dates months quarters years pivot table
  6. Select months to group dates by month and boom!!
    Note: If you are working on more than 1 calendar year data, it is recommended to select years with months or quarter to have a clear view of the data. Here we will select months and years. Convert dates months quarters years pivot table
  7. Press the enter to get date group by months and years.

Bingo!! Now you learnt how to convert dates into months/ quarters/ years in pivot table.

2. How to Group numbers in excel pivot table?

  1. Insert a pivot table.
  2. Drag the Amount field in the rows label or columns label. (For our example we are continuing with rows label.)
  3. Now drag the customer field to the rows label. (After amount field) (You can skip this step. However we have taken this steps to make data analysis and groups by date more logical and understanding.)
  4. Right click on any data of amount and select group.
  5. You will get a pop of grouping. You can use this grouping pop up to define your range. (For our example, we would be taking starting from 0 and ending on the maximum data.)
    Components of Grouping by Range:

    Starting at : defines start value of first group
    Ending at : defines end value of last group
    By : Defines the range of data to be used for grouping.

  6. Press the enter and you will have your data grouped by range in pivot table.

Now you got both. But how to ungroup dates into pivot table? Let’s understand the same.

How to ungroup dates into Pivot table?

  1. Go to grouped data in pivot table.
  2. Right click on any cell of grouped data.
  3. Select ungroup and your grouped data will be ungrouped.

If you loved post, don’t forget to share and subscribe our blog via email.
Have any Questions/ suggestions/ recommendations? Leave us a comment.