While I was training a class to use Pivot Tables this week, I showed them how to Group Dates by Month, Year & Quarter. They liked that a lot - it’s a great feature! However, one student said, “My company is on a Fiscal Year. Is it possible to change the grouping of the Quarters from a Calendar Year to a Fiscal Year?”

Yes! There are several ways to do so. In this Excel Video Lesson I demonstrate the easiest way.

Here are the steps to follow in this Excel Training Video:

1. Create the Pivot Table. In this example we drag the “Date Field” to the “Row Area” and drop the “Sales Field” into the “Data Area.”
2. Select one cell in the Date field and from the Pivot Table drop=down menu choose Group & Show Detail - Group. Highlight Month & Quarter. Click OK.
3. We now have a new “Outer Row” filed - Quarter - in our Pivot Table. However, the grouping reflects a Calendar Year. We need to change this to a Fiscal Year.
4. Select each label, in turn, and edit the name in the Formula Bar.
5. To change the Order of the Quarters: Right-click the 4th Quarter label and choose Order - Move to End.
6. Edit the Field Settings for the Quarters Field. Add in the SUM Subtotal.
7. Make the Quarterly Subtotals & Grand Total rows Bold.
8. Double-click any Quarterly Label to Hide the Details. Use the Icons on the Toolbar to Show / Hide details for each group to suit your needs.

Looking for a specific Excel Video Lesson? Click here for an Index of all Excel Training Topics

Related Videos

• Use Pivot Tables to Group Dates by Quarter, Year & Month
• Hide & Show Details in Excel Pivot Tables
• Introduction to Pivot Tables in Excel 2003

When someone wants to see an additional field in your Pivot Table - e.g. to show the “Price per Units Sold,” you create a “Calculated Field.” This is a fairly simple process. In this video, Danny will show you how. This is yet another example of how you can use the power of a Pivot Table to present data the way that you - or your audience - want to see it.

Here are the steps to follow in this lesson:

1. On the Pivot Table drop-down menu select “Formulas, Calculated Field.”
2. In the dialog box, write in a Name for your new Calculated Field.
3. Enter the Formula. Use the Fields in your Pivot Table and any operators (+,-,*,/) Click OK.
4. Use the Field Settings to change any formatting, etc.

FInd the Excel Training Video you want - Index to all Excel Topics

NEW! I invite you to view a short video introduction to The Company Rocks Excels Blog

Related Videos

• Introduction to Pivot Tables
• Show & Hide Details in Pivot Tables
• Group Dates by Year, Quarter & Month in Pivot Tables
• Change the Quarterly Summary to a Fiscal Year in Pivot Tables

Can you imagine trying to use Excel Subtotals to summarize a long column sales, listed by date? It would be a nightmare! Pivot Tables, on the other hand, can give you this summary with a few mouse clicks!

Danny will show you how to produce a summary report that groups dates using a Pivot Table in this video lesson.

Here are the steps to follow in this lesson:

1. 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.
2. With 1 cell in the “Date” row selected, choose “Group & Show Detail, Group.” Select Year, Quarter and Month and click OK.
3. 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.”
4. Click the Pivot Chart icon to create a Pivot Chart on a new worksheet. Filter the data fields.
5. Move the Year field back to the Row area. Ungroup the fields to return to the starting point.
6. 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

NEW! I invite you to view a short video introduction to The Company Rocks Excels Blog

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
  

We continue to explore the many great features of Excel Pivot Tables. In this video lesson, Danny shows you how to place fields in the PAGE area of the template; how to hide details in order to see the big picture and to reveal the hidden details in various formats. You will also learn how to automatically generate multiple pivot table reports!

Here are the steps to follow in this Excel video lesson:

1. For this lesson we are moving our data sheet to a new workbook. Select Edit, Move or Copy Sheet (Click the “Copy” check box) and select “To a new workbook.
2. Create the Pivot Table (Data, Pivot Table Report). In step 3, choose the “Layout” tab.
3. On the Template, move “Territory” to the “Page” area; “Sales Reps” to the “Row” area; and move “YTD Sales” to the “Data” area twice. Click OK and then Finish.
4. Rename the new worksheet - e.g. Pivot Table.
5. Format the Numbers in the 1st Sum of YTD Sales Field - Right Mouse Click, choose Field Settings and the Number Tab.
6. For the 2nd Sum of YTD Sales, Right Mouse Click, Field Settings and then “Options.” In the Options tab select “% of Total” from the drop-down “Show Data as:” box.
7. Filter the Territories in the “Page” area.
8. Move the Territories from the “Page” area to the “Row area.” You now have 2 Row Fields. Territory is the “Outer Row” and Sales Rep is “nested” as the “Inner Row.”
9. Experiment with “Show Details” and “Hide Details” on the Pivot Table Toolbar.
10. To create individual worksheets for the territories: Move Territory back to the Page area. From the drop-down options on the Tool Bar, select Show Pages.

Find the Excel Training Video that you want - Index of all Excel Topics

NEW! I invite you to view a short video introduction to The Company Rocks Excels Blog

Related Video Lessons

• Introduction to Pivot Tables in Excel 2003
• Group Dates in a Pivot Table Summary
• Create a Calculated Field in a Pivot Table
• Change the Quarterly Summary to a Fiscal Year in Pivot Tables
  

The majority of my clients have not yet used Pivot Tables. They are curious about them, but they are also afraid to get started with them on their own.

This is the first in a series of training videos designed to guide you in your discovery of Pivot Tables.

Here are the steps to follow in this video lesson:

1. Start with an Excel data list that has clearly defined Column / Field Headers. With one cell active, Choose Data, Pivot Table Reports.
2. You can accept all of the default settings to create a Pivot Table on a new worksheet.
3. On the new worksheet you see: a) Floating Pivot Table Tool Bar b) Blank Pivot Table Template c) Pivot Table Field List
4. Experiment by dragging & dropping Fields on to either the Row or the Column areas. Remember that Ctrl+Z (Undo) and Ctrl+Y (Redo) are handy tools to help you as you experiment with the layout that you want to see.
5. To format numbers in Pivot Tables, double-click the “Sum of YTD Sales” header and in the dialog box select the Number tab to choose your format. This is different from the usual way you format cells. In Pivot Tables, you do NOT format numbers as cells. Rather you format the “Field Settings.”
6. You can drop the YTD Sales into the Data area a second time. Then in Field Settings, choose a different Summarize by function - e.g. AVERAGE.

Find the Excel Training Video that you want - Index to all Excel Topics

NEW! I invite you to view a short video introduction to The Company Rocks Excels Blog

Related Videos

• Hide & Show Details in Pivot Tables
• Group Dates by Year, Quarter & Month in Pivot Tables
  
• Create a Calculated Field in Pivot Tables
• Change the Quarterly Summary to a Fiscal Year in Pivot Tables