Note: All new – and existing – Excel Training Videos are now hosted on my main website. Follow this link to go directly to my Excel Training Section.

Let me warn you – Calculated Items in Pivot Tables can produce incorrect results! Calculated Items can be useful – in special circumstances. However, don’t use a Calculated Item to create a Quarterly Summary. It is too easy to accidentally “double” your sales! And, this will not earn you any special bonus. In fact, it may harm your reputation as an Excel expert. So be very careful before you create a Calculated Item.

Review my Excel Training Video on Grouping Data to see the preferred way to produce a Quarterly Summary in a Pivot Table.

Here are the steps to follow in this lesson:

1. You use a Formula to Calculate an Item from the values on one of the Fields in your Pivot Table.
2. Select one value in the Field where you want to create the Calculated Field.
3. In this lesson, I selected a Month and then from the Drop-Down Menu on the Toolbar I chose Formula – Calculated Item,
4. I named the Calculated Item Q1 (1st Quarter) and for the Formula I clicked = January+February+March. Click Add and then click OK.
5. As you can see, the new Q1 Item actually “doubled” the existing sales for January – March. That is not what you were looking for!
6. To remove the Calculated Item, select it and then from the drop-down select Formulas – Calculated Item. Highlight Q1 and click Delete – OK.
7. Review my video to see how to Group Data to produce the Quarterly Summary in the Pivot Table.

Search My Index of Excel Training Videos to find the topic you want to view

New! Danny’s DVD Training Series, “The 50 Best Tips for …” is now available at the online store for The Company Rocks

Note: All new – and existing – videos are hosted on my main website. Follow this link to go directly to my Excel Video Training Lessons.

During a recent Excel training class, one student asked me if was possible to create a Quarterly Summary Report in a Pivot Table. Yes, it is quite easy to do this – as you will see in the video.

In the Data Set that we are using for this Pivot Table we have four territories and 12 months of sales for each territory over a one year period. The Months (Jan, Feb, etc.) and not actual dates (e.g.1/1/08,) they are Labels.

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.

Here are the steps to follow in this lesson:

1. Create a Pivot Table using the default settings in the Pivot Table Wizard. Place the Months in the Row area, the territory in the Column area, and the Sales in the Data area.
2. Select the Labels January, February, March. On the Pivot Table drop-down menu select Group & Show Detail – Group.
3. Rename the generic label (Group1) to 1st Qtr.
4. Follow the same sequence of steps to create Groups for the 2nd, 3rd, and 4th Quarters.
5. Rename the new, generic field (Month2) Quarter.
6. Use the Toolbar Commands to Hide & Show Detail as desired.

NEW! Download the Excel Practice file that I used in this video

• grouping-by-quarters-in-pivot-tables

Search my Index of Excel Video Lessons to find each video listed by topic

New! Danny’s DVD Training Series, “The 50 Best Tips …” is now available at the online store for The Company Rocks

Note: All new – and existing – Excel Video Training Lessons are now hosted on my main website. Click here to link directly to my Excel Video Training Section.

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

News! My DVD, “The 50 Best Tips for Excel 2007″ is now available to purchase. I invite you to visit my online bookstore for more details.

Note: All new – and existing – Excel Training Videos are now hosted on my main website. Follow this link to go directly to my Excel Training Videos Section.

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 by searching my Index to 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

Note: All new – and existing – Excel Video Training Lessons are now hosted on my main website. Click this link to go directly to my Excel Video Training Section. 

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:

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

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.

Note: All new – and existing – Excel Video Training Lessons are now hosted on my main website. Please follow this link to go directly to the Excel Training Section. 

I continue to explore the many great features of Excel Pivot Tables. In this video lesson, I show 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.

NEW! - Practice in Excel 2003 – Download the Excel Workbook I used in this lesson:

• hide-show-details-in-pivot-table

Find the Excel Video Training Lesson that you want in my Index of all Excel Topics

NEW! My DVD Training Series, “The 50 Best Tips … ” is available for sale at the online store for The Company Rocks

Note: All new – and existing – Excel Training Video Lessons are now hosted on my main website. Follow this link to go directly to the Excel Training Section.

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.

NEW! – Download the Excel File that I used in this lesson:

• introduction-to-pivot-tables

Find the Excel Video Training Lesson that you want in my Index to all Excel Topics

My DVDs are now available for sale at my new online store !