Vaibhav asks how to group a pivot table up to fiscal quarters. While this is easy if your fiscal years ends on December 31, it is not so easy for other year-ending dates. Episode 1012 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MExcel NetCast. I'm Bill Jelen.
Basically we start out with massive amounts of data.
So how we're gonna analyze as well let's fire up a pivot table.
Lets see if you can solve this problem.
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Another question today by vaibhav. Vaibhav wants to know about grouping up to months and quarters.
Let me create a pivot table here.
Dates down the left hand side revenue in the heart of the pivot table and we can come here right click and choose group and say we want to roll that up to years quarters click OK and beautiful. All Right.
It takes all that 500 rows of daily data and creates a great report with year and quarter data.
And that works great for you if your fiscal year like Microsoft ends December 31st. Great!
But if you're in one of those companies where your fiscal year doesn't end December 31st, Microsoft does not care about you.
There's just no no solution. What you have to do is you have to come back here to the original data set and we're going to insert a couple of new columns "Fiscal year" and what I use for this is I use =SUM(YEAR(C2),--(MONTH(C2)>3).
So that would be if your fiscal year ended March 31st for example.
What that does this is going to be either true or false.
the - - in front causes excel to say that trues become 1 and falses become 0 so basically it's going to allow us to insert a brand new value there and let's format that as a number.
Click "OK" so 2008. Copy it down.
Let's just check what it should see is once we get out into April.
It goes to 2009. So that's working beautifully.
All right, so that's fiscal year.
That's kind of the easy one. Fiscal quarter is a little bit harder.
I actually just spot in here I always use the choose command =CHOOSE get the month of that original date and then I hard code that anything in January February March is quarter 4 and then quarter 1, quarter 2 and quarter 3.
So what are we doing there the month is going to return a month number 1 through 12 and I'm just saying hey if it happens to be January February or March it's Q4.
Otherwise, it's Q1, Q2, Q3 and so on down the line.
Again format that as a number.
This is a really frustrating cuz I inserted columns to the left of the day to copy that formatting over.
So copy that down and we are good to go.
Now to solve the problem. We're gonna create our pivot table. "Insert" "Pivot table" "OK".
This time we're going to choose "Qtr" "Fiscal year" Square there quarter there and then revenue and we end up with data in "Fiscal years" instead of normal years.
Kind of a pain after that data the original data set but unfortunately if your fiscal year doesn't end December 31st That's what we have to do.
Well. Thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Basically we start out with massive amounts of data.
So how we're gonna analyze as well let's fire up a pivot table.
Lets see if you can solve this problem.
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Another question today by vaibhav. Vaibhav wants to know about grouping up to months and quarters.
Let me create a pivot table here.
Dates down the left hand side revenue in the heart of the pivot table and we can come here right click and choose group and say we want to roll that up to years quarters click OK and beautiful. All Right.
It takes all that 500 rows of daily data and creates a great report with year and quarter data.
And that works great for you if your fiscal year like Microsoft ends December 31st. Great!
But if you're in one of those companies where your fiscal year doesn't end December 31st, Microsoft does not care about you.
There's just no no solution. What you have to do is you have to come back here to the original data set and we're going to insert a couple of new columns "Fiscal year" and what I use for this is I use =SUM(YEAR(C2),--(MONTH(C2)>3).
So that would be if your fiscal year ended March 31st for example.
What that does this is going to be either true or false.
the - - in front causes excel to say that trues become 1 and falses become 0 so basically it's going to allow us to insert a brand new value there and let's format that as a number.
Click "OK" so 2008. Copy it down.
Let's just check what it should see is once we get out into April.
It goes to 2009. So that's working beautifully.
All right, so that's fiscal year.
That's kind of the easy one. Fiscal quarter is a little bit harder.
I actually just spot in here I always use the choose command =CHOOSE get the month of that original date and then I hard code that anything in January February March is quarter 4 and then quarter 1, quarter 2 and quarter 3.
So what are we doing there the month is going to return a month number 1 through 12 and I'm just saying hey if it happens to be January February or March it's Q4.
Otherwise, it's Q1, Q2, Q3 and so on down the line.
Again format that as a number.
This is a really frustrating cuz I inserted columns to the left of the day to copy that formatting over.
So copy that down and we are good to go.
Now to solve the problem. We're gonna create our pivot table. "Insert" "Pivot table" "OK".
This time we're going to choose "Qtr" "Fiscal year" Square there quarter there and then revenue and we end up with data in "Fiscal years" instead of normal years.
Kind of a pain after that data the original data set but unfortunately if your fiscal year doesn't end December 31st That's what we have to do.
Well. Thank you for stopping by. We'll see you next time for another netcast from MrExcel.
