Use Pivot Tables to Summarize by Year, Quarter and Month
Can you imagine trying to use Excel Subtotals to summarize a long column sales, listed by date? What would you subtotal – every change in date? It would be a nightmare!
Pivot Tables, on the other hand, can provide a summary of dates by month, quarter, and year with just a few mouse clicks!
In this video lesson I show you how to produce that summary. The Pivot Table will transform a long list of data into information that you can use to analyze trends.
Here are the steps to follow in this lesson:
- Create a Pivot Table using all of the default settings. Drag the “Date” field into the ROW area and the “Sales” field into the DATA area of the Template.
- With 1 cell in the “Date” row selected, choose “Group & Show Detail, Group.” Select Year, Quarter and Month and click OK.
- You now have 3 ROWS in your Pivot Table – Year, Quarter & Month. Drag the Year field from the ROW up to the COLUMN area. Hide on of the “Grand Totals.”
- Click the Pivot Chart icon to create a Pivot Chart on a new worksheet. Filter the data fields.
- Move the Year field back to the Row area. Ungroup the fields to return to the starting point.
- To Group by WEEK: Choose “Group & Show Detail, Group” and select DAY. Then select 7 for the number of days in the week.
Find the Excel Training Video that you want – Index of all Excel Topics
News! My DVD, “The 50 Best Tips for Excel 2007″ is now availabe to purchase. I invite you to visit my online bookstore for more details.
Related Video Lessons
- Introduction to Pivot Tables
- Hide & Show Details in Pivot Tables
- Create a Calculated Field in Pivot Tables
- Change the Quarterly Summary to a Fiscal Year in Pivot Tables
- Take a Video Tour of My DVD – “The 50 Best Tips for Excel 2007″
October 27th, 2008 at 7:04 PM
[...] Use Pivot Tables to Summarize by Year, Quarter and Month [...]
March 21st, 2009 at 3:36 PM
[...] Group Dates by Year, Quarter & Month in Pivot Tables [...]
April 6th, 2009 at 6:37 AM
[...] In an earlier video, I showed you how easy it is to create new fields for Months, Quarters & Years from a Date field. Because our months are Labels, we will GROUP the Months to create the 1st, 2nd, 3rd, and 4th Quarters in our Pivot Table. [...]
April 26th, 2009 at 3:25 PM
cool sitename man)))
————————
ad: http://xabul.ru/
October 18th, 2009 at 6:22 AM
[...] Use Pivot Tables to Group Dates by Quarter, Year & Month [...]
November 2nd, 2009 at 1:23 PM
Thanks this is exactly what I needed!
November 2nd, 2009 at 2:40 PM
Thank you for taking the time to give me your feedback – greatly appreciated!
I am pleased that this was exactly what you were looking for.
Best,
Danny
December 30th, 2009 at 3:25 AM
[...] Use Pivot Table to Group Dates by Month, Quarter & Year Share and Enjoy: [...]
December 30th, 2009 at 1:42 PM
[...] Use Pivot Tables to Summarize by Year, Quarter and Month [...]
December 30th, 2009 at 2:15 PM
[...] Group Dates in a Pivot Table Summary [...]
May 21st, 2010 at 12:36 PM
[...] Review my Excel Training Video on Grouping Data to see the preferred way to produce a Quarterly Summary in a Pivot Table. [...]
September 24th, 2010 at 8:28 AM
[...] Use Pivot Tables to Summarize by Year, Quarter and Month [...]
December 16th, 2010 at 9:24 AM
[...] Use Pivot Tables to Summarize by Year, Quarter and Month [...]
December 16th, 2010 at 1:49 PM
[...] Group Dates by Year, Quarter & Month in Pivot Tables [...]