Home Archives for Pivot Tables
October 29, 2008 6 Comments

Change Pivot Table Grouping to a Fiscal Quarter

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

Do you want to see a list of all of my Excel Pivot Table Video Lessons? Click here to go to my Pivot Table Archive.

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.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Training, Pivot Tables, Video Response to Viewers Tagged: , , , ,
October 6, 2008 Leave a Comment

Top Excel Posts in September 2008

Here is a classified listing of the most popular postings and Video lesson blog entries on my site during the month of September, 2008:

Information about The Company Rocks Excels

Filtering Excel Data

Time-Savers in Excel

Pivot Tables in Excel 2003

50 Best Tips for Excel 2007

Excel Tips

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.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Training, Most Viewed Excel Videos, Pivot Tables Tagged: , , , , , , , ,
August 24, 2008 3 Comments

Create a Calculated Field in Your Pivot Table

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

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.

Get a Free 28 Minute Excel Video Tutorial

Follow this link for information to download – “Introduction to Pivot Tables in Excel 2003”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , ,
August 22, 2008 16 Comments

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?  At every change in date?

This would be a nightmare! This would produce a useless report!

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

Additional Pivot Table Resources

Pivot Table Training Resources

Pivot Table Training Resources

I have published a 90 minute focused video tutorial for Pivot Tables. It is available for each version of Excel -2010, 2007, and 2003. You can purchase it as either a DVD-ROM that I will ship to you or as a Downloadable version that you can work with immediately.

In addition to the 90 minutes of video instruction on Pivot Tables, the package includes:

  • The Excel Practice Files that I used while filming the video tutorial – so that you can practice your new skills using the same files that are on the video.

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to learn about the many training resources that I offer for sale.

  • A Step-by-step Instructional Guide that you can print out – so that you can use it to take notes as you watch my video tutorial.

Related Video Lessons

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , , , , , ,
August 19, 2008 6 Comments

Hide and Show Details in Excel Pivot Tables

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!

Watch This Video on YouTube

Here is the link to watch this video tutorial on YouTube .

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

  1. For this lesson I am moving the 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! My DVD Training Series, “The 50 Best Tips … ” is available for sale at the online store for The Company Rocks

Learn How to Quickly Create Pivot Tables – With Real World Business Examples!


Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , , , ,
August 18, 2008 8 Comments

Introduction to Pivot Tables in Excel 2003

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.

Free Excel 2003 Video Lesson, Workbook and Manual

 I invite you to download a free 28 minute Excel Video Tutorial, Workbook and Instructional Manual for Pivot Tables in Excel 2007 – Follow this Link to my WebEx by Cisco site.

Find the Excel Training Video that you want –

Index to all Excel Topics

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , , ,
« Previous Page