Hey, welcome back to the Mr. Excel Net Cast. I'm Bill Jelen. Amazing, we start out with massive amounts of data as to, how are we going to analyze this. Well, let’s fire up the pivot table and see if we can solve this problem.
Welcome back to the Mr. Excel Net Cast, I'm Bill Jelen. Another question today by Wybath. Wybath wants to know about the grouping up to months and quarters. Let me create a pivot table here. Dates on 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 it up to the years quarters. Click OK and beautiful, alright, 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 fiscal year doesn’t end December 31st, Microsoft does not care about you. There is just no solution. What you have to do is after come back here, the original data set and we’re going to insert a couple of new columns, fiscal year and what are use for this? I use equal sum. The year of the original date, comma and then minus, minus and then in parenthesis the month of the original date greater than three. So, that would be if your fiscal year added March 31st for example. What that does this is going to be either true or false. The minus, minus in front causes a excel to say that true is become one and false is becoming zero so basically its going to allow to insert a brand that new value there and lets format that as a number. Click OK, so 2008 copied down let us check. What I should see is once we get out in the April it goes to 2009. So that’s working beautifully. Alright, that’s fiscal year that’s kind of the easy one.
Fiscal quarter is little bit harder. I actually just punt here, I always use the choose command, equal choose, get the month of that original date and then I hard go that anything in the January for March is quarter four and then quarter one, quarter two and quarter thee. So what we are doing there. The month is going to returning the months number one thru twelve and I'm just saying, hey, if it happens to be January or March is Q4 otherwise its Q1, Q2, Q3 and so on down the line.
Again, format that as a number. It is really frustrating is kind of inserted columns of the left of date that copy that formatting over, so copy that down and we are good to go. Now, to solve the problem, were going to create our pivot table, insert pivot table, okay. This time I'm going to choose quarter fiscal year, fiscal year there, quarter there and then revenue and we end up with data in fiscal years instead of normal. It is kind of paying, after that data the original data said but unfortunately, if your fiscal year does end December 31st, that’s what we have to do.
Thank you for stopping by. We’ll see next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services