Home Archives for Excel Pivot Tables
December 30, 2009 Leave a Comment

Announcing the Online Store for The Company Rocks

Danny's DVDs "The 50 Best Tips" Series Available in Online StoreToday, I launched my new online bookstore for The Company Rocks. I am offering my Instructional DVDs for sale individually or as a set at attractive prices. Follow this link to enter the store to learn more about the DVDs in “The 50 Best Tips for …” Series.

“The 50 Best Tips for Excel 2007” contains over 5 1/2 hours of training. Each video lesson lasts @ 6 – 7 minutes. Each lesson focuses on one productivity tip. So you can get an answer quickly when you need it. I follow a step-by-step approach to teach you how to be more productive when you use Excel 2007. You can see how to enter and edit the formulas because I use an innovative “pan-in / pan-out” camera technique to focus your attention.

And, as a bonus: The actual spreadsheets that I use for each lesson are included on the DVD!

“The 50 Best Tips for PowerPoint 2007” packs over seven hours of training onto two DVDs! Unlike many instructional packages the teach you PowerPoint from a technical angle, I teach you how to incorporate PowerPoint into your total presentation. I am a professional speaker and trainer. I am not a “techie.” You will learn the technical side of PowerPoint 2007 – but only as it helps you to be more effective in presenting your ideas or products. I think that you will enjoy this difference!

The focus of each DVD is on the Intermediate to Advanced user of Excel or PowerPoint. The focus is on helping you to save time and take advantage of the great presentation elements in both programs. There are 10 videos each at the Beginning Level and at the Advanced Level. There are 30 videos for the Intermediate Level user.

Please visit my new online bookstore to learn more about each DVD. I welcome your feedback!

Thank you for your kind words of support as I have built my business these past three years. I wish you a very happy new year in 2010!

Danny Rocks

danny@thecompanyrocks.com

Link to this post!
Filed Under: Business Communications, Online Store for The Company Rocks, PowerPoint Video Training, Video Training Courses Tagged: , , , , , , , , ,
April 8, 2009 Leave a Comment

Be Careful with Calculated Items in Pivot Tables

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

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

Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , , ,
December 20, 2008

I Have 21 Excel Videos Rated 5-Stars on YouTube

YouTube Logo

YouTube Logo

Here is a listing of my 21 Excel Video Lessons that are rated “5-Stars” on YouTube.

I hvae organized the videos by category. The First Hyperlink will take you to to the videos on this site. The “indented” Hyperlink will take you to the videos on my YouTube site –  DannyRocksExcels.

I hope that you find a few tips to save you time or answer a question. I welcome your feedback. Enjoy!

Pivot Tables

“What-if” Analysis

Consolidation and SubTotals

Filter & Sort Lists in Excel

Financial Functions in Excel

Logical & Lookup Functions in Excel

Text Functions

Formula Auditing

Formatting and Conditional Formatting

Paste Special Options

Excel Charts

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.

Related Videos

Link to this post!
Filed Under: Excel Training, Most popular articles, Most Viewed Excel Videos, YouTube Videos by Danny Rocks Tagged: , , , , , , , , ,
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.

Link to this post!
Filed Under: Excel Training, Pivot Tables, Video Response to Viewers 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”

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

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 !

Link to this post!
Filed Under: Excel Training, Pivot Tables Tagged: , , , ,
« Previous Page