You have a pivot table data source with daily dates.
You would like to filter your pivot table by Quarter.
Follow these steps:
1) Build a new pivot table
2) Put Dates in the Rows area
3) Group daily dates to Quarters
4) Move Date to the Report Filter
5) Build the rest of your pivot table
6) Optionally, add a slicer by quarter
Thanks to Lindsey at my Nashville Seminar for the Lincoln Trail Council of the IMA.
To download this workbook: https://www.mrexcel.com/download-ce...-quarter-in-pivot-table-with-daily-dates.xlsx
List of upcoming seminars: Excel Seminar Schedule
You would like to filter your pivot table by Quarter.
Follow these steps:
1) Build a new pivot table
2) Put Dates in the Rows area
3) Group daily dates to Quarters
4) Move Date to the Report Filter
5) Build the rest of your pivot table
6) Optionally, add a slicer by quarter
Thanks to Lindsey at my Nashville Seminar for the Lincoln Trail Council of the IMA.
To download this workbook: https://www.mrexcel.com/download-ce...-quarter-in-pivot-table-with-daily-dates.xlsx
List of upcoming seminars: Excel Seminar Schedule
Transcript of the video:
Learn Excel from MrExcel podcast episode 2200.
Filter by Quarter in Pivot Table with Daily Date.
Hey, well come back to MrExcel netcast.
I'm Bill Jelen.
Today a great trick from Lindsey in Nashville.
I was in Nashville. Doing a seminar fro the Lincoln Trail Council of IMA.
It's seminar season.
So, I bring all these great tricks from the road back to you.
Lindsey has daily dates in her Pivot Table data and she wants to be able to filter by quarters.
So, here's how we do that, Insert, Pivot Table, OK.
All right now, first step some pre-work before you build the Pivot Table.
Take that date field to the rows area.
All right and see I am getting daily dates.
If you are getting a grouped date immediately press control+Z.
I don't like when they are automatically group because it have weird rules, If it spans in the entire year, they group one way, but if it doesn't see, my data starting from January 2nd, not January 1st because it was a holiday.
And so, they give a different grouping.
All right, I just sorry Excel, wanna and turn this off.
File, Option, Data, that's a new category in Office 365, right here disable automatic grouping of the Date/Time columns in PivotTable.
All right, if you don't have a check box, then you just have to press control+Z.
If you are the in professional version 2016.
So, we choose our first date field go to Group Field choose just quarters.
Now, this data only spans 1 year, so quarters is right, click OK.
We get our quarters there and now there's a cool thing take that up to the filters area.
All right now, we are gonna build our Pivot Table.
Whatever Pivot Table you need to do.
I am gonna to build a report of revenue by sector. I'll take revenue twice.
Click on this heading and change the values to percentage of the column total.
So, percentage of total.
All right, may be add a little bit of number formatting here, right click, Number Format, choose Currency with 0 decimal places, click OK.
All right so, there is our Pivot Table, but now we can filter open the drop down.
Here's the answers for Q1 and suppose once you have this it'll really be better.
To insert a slicer by the date field.
The date field now contains quarters and I can very quickly update the report using one of the slicers.
Like that.
All right, Tips like this are in my new book, MrExcel LIV, The 54 Greatest Excel Tips of All Time.
This is actually the book that we use in those live seminars.
So, if you can't make it through one of the seminars, next best thing is buy the book.
See all the tips we talk about.
Unfortunately, without the jokes and actually the audience.
Like this conversation with Lindsey in natural.
All right so, you have a Pivot Table, Data source with daily dates you like to filter by quarters.
Follow these steps, build a new Pivot Table, put dates in a row area.
If automatically groups, control+Z to undo.
Group those daily dates to quarters, move the date to the report filter.
Build the rest of the Pivot Table and then optionally add a slicer by the quarters.
Down in the YouTube description is the link to today's article where you can download workbook for today.
If you like to try along, also list of our coming seminars down in that area.
Oh hey, I wanna thank Lindsey for showing up in my seminar in Nashville and wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Filter by Quarter in Pivot Table with Daily Date.
Hey, well come back to MrExcel netcast.
I'm Bill Jelen.
Today a great trick from Lindsey in Nashville.
I was in Nashville. Doing a seminar fro the Lincoln Trail Council of IMA.
It's seminar season.
So, I bring all these great tricks from the road back to you.
Lindsey has daily dates in her Pivot Table data and she wants to be able to filter by quarters.
So, here's how we do that, Insert, Pivot Table, OK.
All right now, first step some pre-work before you build the Pivot Table.
Take that date field to the rows area.
All right and see I am getting daily dates.
If you are getting a grouped date immediately press control+Z.
I don't like when they are automatically group because it have weird rules, If it spans in the entire year, they group one way, but if it doesn't see, my data starting from January 2nd, not January 1st because it was a holiday.
And so, they give a different grouping.
All right, I just sorry Excel, wanna and turn this off.
File, Option, Data, that's a new category in Office 365, right here disable automatic grouping of the Date/Time columns in PivotTable.
All right, if you don't have a check box, then you just have to press control+Z.
If you are the in professional version 2016.
So, we choose our first date field go to Group Field choose just quarters.
Now, this data only spans 1 year, so quarters is right, click OK.
We get our quarters there and now there's a cool thing take that up to the filters area.
All right now, we are gonna build our Pivot Table.
Whatever Pivot Table you need to do.
I am gonna to build a report of revenue by sector. I'll take revenue twice.
Click on this heading and change the values to percentage of the column total.
So, percentage of total.
All right, may be add a little bit of number formatting here, right click, Number Format, choose Currency with 0 decimal places, click OK.
All right so, there is our Pivot Table, but now we can filter open the drop down.
Here's the answers for Q1 and suppose once you have this it'll really be better.
To insert a slicer by the date field.
The date field now contains quarters and I can very quickly update the report using one of the slicers.
Like that.
All right, Tips like this are in my new book, MrExcel LIV, The 54 Greatest Excel Tips of All Time.
This is actually the book that we use in those live seminars.
So, if you can't make it through one of the seminars, next best thing is buy the book.
See all the tips we talk about.
Unfortunately, without the jokes and actually the audience.
Like this conversation with Lindsey in natural.
All right so, you have a Pivot Table, Data source with daily dates you like to filter by quarters.
Follow these steps, build a new Pivot Table, put dates in a row area.
If automatically groups, control+Z to undo.
Group those daily dates to quarters, move the date to the report filter.
Build the rest of the Pivot Table and then optionally add a slicer by the quarters.
Down in the YouTube description is the link to today's article where you can download workbook for today.
If you like to try along, also list of our coming seminars down in that area.
Oh hey, I wanna thank Lindsey for showing up in my seminar in Nashville and wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
