Home Archives for Excel Pivot Tables
April 14, 2011 2 Comments

Video Tutorials for Both Excel 2007 and Excel 2003

Many of my clients and viewers continue to use Office 2003 Programs (Excel 2003, Word 2003, PowerPoint 2003, etc.) and they have asked me to produce more video lessons that use these programs.

Finally, I have found the solution!

WebEx Recorded Sessions

WebEx Recorded Sessions

Extended Length Video Recordings on WebEx by Cisco

I have just started to create a series of extended length, in-depth instructional video tutorials as part of my “Master Business Skills in Minutes!” Series. I am making these recorded video lessons available on my WebEx by Cisco Systems website: http://thecompanyrocks.webex.com

My goal is to offer a “mix” of recorded video lessons where some are offered “free of charge,” while others will carry a modest fee. Each lesson that I publish will be offered in two versions – one for Office 2007 and a parallel version for Office 2003. Eventually, I will publish versions that use Office 2010.

Registration is Required – and Simple!

Each of the lessons – free or paid – will require you to register at WebEx before you can have access to download the video or to view it online. I have made the registration process as simple as possible: all that you need to supply is First Name, Last Name and a valid email address. That’s it! I value your privacy – click here to read or download the Privacy Policy for The Company Rocks.

Create a Pivot Table with Fewer than 10 Clicks of Your Mouse

Free Video Lessons on My Website, Video Podcast and YouTube

I will continue to post free Excel Video Lessons here on my website – www.thecompanyrocks.com and I will include links to my YouTube Channel – DannyRocksExcels  These videos will be short and focused – under 9 minutes!

What You Get with My WebEx Video Recordings

The videos that I publish at http://thecompanyrocks.webex.com -are designed to be longer than what I post here on my website or to YouTube. On the “extended length” videos, I will pay much more attention to the details and the “reasons why” aspects of the key steps. With WebEx, I can also offer you the opportunity to download the actual Excel Workbook that I used to create the lesson and give you a PDF of the “Step-by-step” instructions for each lesson.

I am excited about this new development. I want to hear what you think. Kindly add your comments and feedback in the section below this post.

Link to this post!
Filed Under: Excel Training, Podcast Tips & Timesavers, WebEx Recordings & Webinars Tagged: , , , , , ,
April 13, 2011 Leave a Comment

Announcing My Online Courses – Master Business Skills in Minutes

I am proud to announce that I have posted the first two recorded video lessons for my online course: “Master Excel in Minutes!”

WebEx Navigation Pane

WebEx Navigation Pane

These courses are recorded in-depth videos that you can choose to watch online or download to your computer to watch at a later time. Some of the recordings will be offered free of charge. For others – which contain premium content – I will charge a fee. The recorded videos will be found on my website at WebEx:

http://thecompanyrocks.webex.com

New Page on My Website

You can find more information about my online courses by following this link to a new page that I have created here on my website.

Here are the direct links to my first two recorded video lessons on WebEx – be sure to click the “Recorded Sessions” on the main “Attend a Session” navigation pane at http://thecompanyrocks.webex.com:

In this format – recorded, downloadable, video lessons – I am able to go into more detail on the topic than I do with my free video podcasts. I can also cover – in depth – topics that my viewers have been requesting. For example, I can now train you in MS Office 2003 (Excel 2003, Word2003, etc.)

Contents of Recorded Video Lessons

Each of the recorded video lessons includes:

  • The recorded video lesson that you can watch online
  • The recorded video lesson that you can download to your computer to view at a later time
  • The workbooks or documents that I use during the lesson – so that you can practice the concepts that I present on the video recording.
  • A PDF containing the Step-by-step instructions for each lesson – so that you can make notes as you view the recording or during your practice sessions.

I hope that you will take a few minutes to view my first offerings in this course. After you watch the lessons, please send me your feedback, questions and suggestions for future course offerings.

Thank you!

Danny Rocks

Link to this post!
Filed Under: Business Practices, Excel Training, Pivot Tables, WebEx Recordings & Webinars Tagged: , ,
April 3, 2011 Leave a Comment

How to Search For and Use Text Strings in Excel

A viewer asked for my help in creating a report that will group his customers’ email addresses by their “domain name.” For example, he wants to be group all customers with gmail addresses . Likewise, his  customers’ yahoo mail, aol.com accounts, etc.

Pivot Table for Email Domains

Pivot Table Email Domains

I immediately decided to use two powerful Excel tools to produce this report:

  1. Text-to-Columns to create two columns (name and domain) from one text value (the email address).
  2. Pivot Table Report to group and present customer emails by domain name.

Both tools work great and I produced the report with only a few mouse clicks!

The response that I got from my viewer was, “That’s great Danny. But …”

It turns out that the viewer wanted to have the Domain Names as the Field Headers and to show each customer’s email address as a row going down vertically in the corresponding field. To give my viewer the report that he wanted, I used these tools and techniques:

  • I returned to the Pivot Table to copy the unique list of domain names.
  • I used Paste Special – Transpose to convert the vertical list of domain names into a horizontal row of Field Headers.
  • I began to build my “Mega-formula” by taking “baby steps” – i.e. I created one formula and then “nested” it inside additional formulas and functions.
  • For the first “baby step” formula I used the SEARCH() Function.
  • Next, I “nested” this formula as the “logical test” inside an IF() Function.
  • In order to “hide” all of the formula error signs, I nested both of these functions inside the NEW IFERROR() Function.
  • IFERROR() was introduced in Excel 2007 and I now consider it to be “indispensable!” This function, alone, makes upgrading to either Excel 2007 or Excel 2010 a “nobrainer” decision!

I enjoy answering questions from my viewers. Many of these questions can be answered from one of the lessons on my DVD-ROM, “The 50 Best Tips for Excel 2007.” For the rest, I try to create a video lesson that demonstrates how to get the answers that you – my viewers and customers – are looking for.

Keep your questions and suggestions coming! I enjoy receiving feedback from you!

I invite you to subscribe to my RSS Feed on Feedburner to be automatically notified whenever I publish a new Excel Video Tutorial.

Watch this Excel Video in High Definition on YouTube

Here is the link to watch this video on my YouTube Channel – DannyRocksExcels

Searching for Text Strings in Excel [ 9:36 ] Play Now | Play in Popup | Download (1139)
Link to this post!
Filed Under: Excel Training, New in Excel 2007, Pivot Tables, Podcast Tips & Timesavers, Text Functions Tagged: , , , , , , , , , , ,
March 26, 2011 Leave a Comment

How to Take Advantage of Report Filters in Excel Pivot Tables

Starting with Excel 2007, Pivot Tables now include a “drop field” labeled “Report Filters.” The Report Filters area replaces the area labeled the “Pages” drop area in Excel 2003 and earlier. Report Filters is a more descriptive and intuitive name. So , this is a welcome change.

How to Organize Pivot Table

Use Report Filters in Pivot Table

In this lesson, I am responding to an additional request from one of my viewers who wants me to demonstrate how the “Report Filters” drop zone will provide incredible reporting power in a Pivot Table.

Request from a Viewer

In this lesson, my viewer is a “hydrologist” who analyzes average Water Flows by day and by month in a “Water Year.” If you use a “Fiscal Year” reporting structure, you will want to pay attention to this lesson. In this case, the “Water Year” begins on October 1 and ends on September 30. My viewer needs my help in separating out individual Pivot Table Reports organized by Water Year – placed in the Report Filters Drop Zone.The Pivot Tables show the average daily water flow by date. He told me that he is concrned that that Pivot Table Report may not be able to “account for Leap Years.”

Not to worry!

Watch this Excel Video Tutorial, as I demonstrate how to answer my viewer’s query and also give you some “solid tips” to get the results that you need when using Excel!

Watch this Excel Video Tutorial in HD Mode on my YouTube Channel

Here is the link to my YouTube video channel for this video.

Learn how to “Quickly Create Pivot Table Reports!”

Taking Advantage of Report Filters in Excel Pivot Tables [ 8:51 ] Play Now | Play in Popup | Download (959)
Link to this post!
Filed Under: Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , , , , , , ,
March 21, 2011 Leave a Comment

How to Use the Average Functions in Excel 2007 and 2010

The new AVERAGEIF and AVERAGIFS Functions were introduced in Excel 2007. In this lesson, I demonstrate how and when to use both functions. If you are using an earlier version of Excel – e.g. Excel 2003, I show you how to use a Pivot Table to produce the same results.

Average is not the Median

The Average Function is the “Arithmetic Mean” of the range of cell values. This means the Sum of the values in the range divided by the Count of the cells with numeric values in the range. Many people confuse this with the MEDIAN sale which is the “middle value” in the range. Half of the cell values are above and half of the cell values are below the result of the MEDIAN() Function.

New AverageIfs Function in Excel

AverageIfs Function

New Functions in Excel 2007

Watch the video to see how to use both the AverageIf and AverageIfs Functions. I recommend using the Function Arguments Dialog Box and Named Cell References with these functions. Pay attention to my use of “Mixed Cell References” in the AverageIfs formula. Doing so makes it easy to write the formula in one cell and then copy it across the range of cells.

Here is the link to watch this video tutorial in High Definition on my YouTube Channel.

Learn how you can “Master Excel in Minutes – Not Months!”

How to Use the Average Functions in Excel [ 8:29 ] Play Now | Play in Popup | Download (978)
Link to this post!
Filed Under: Excel Functions, Excel Training, New in Excel 2007, Podcast Tips & Timesavers Tagged: , , , , , , ,
March 11, 2011 Leave a Comment

How to Generate Multiple Reports from One Pivot Table

You can create an Excel Pivot Table with just a few mouse clicks. And, with just a few more clicks, you can change – or pivot – the design of your report. Most people who use Pivot Tables know this. However, I find that most Excel users do not know that you can generate multiple reports – each one on  a separate worksheet – with just a few clicks. This is a tremendous time-saving tip!

Generate Multiple Reports

Multiple Reports

Pivot Table Report Filters

When you place one or more fields in the “Report Filters” drop zone of your Pivot Table, you will be able to generate multiple reports from one Pivot Table. Report Filters were introduced in Excel 2007. If you use an earlier version of Excel, this drop zone is called, “Pages.” Report Filters allow you to create interactive views of your data. If you use Dashboard Reports, you will want to include Report Filters in your Pivot Table.

In this video, I demonstrate how to generate reports – each on an individual worksheet – for the values in your Report Filter Field. Whenever I demonstrate this technique in a class or to a client, I always get a “gasp of excitement.” Most people say, “That’s incredible. Do that again!”

Create a Calculated Field

I also show you how to Create a Calculated Field in a Pivot Table. You do not need to return to your source data to add additional fields and then recreate your Pivot Table. Why keep redundant data? Rather, create as many Calculated Fields as you need in your Pivot Table.

Copy Your Pivot Table

You can prevent “Pivot Table Bloat” when you make copies of a Pivot Table rather than creating brand new Pivot Tables from the same source data. Watch as I demonstrate how to do this.

Follow this link to learn how you can “Quickly Create Pivot Tables and Charts”

Here is the link to watch this video in High Definition on my YouTube Channel, DannyRocksExcels

Generate Multiple Reports from One Pivot Table [ 8:26 ] Play Now | Play in Popup | Download (3693)
Link to this post!
Filed Under: Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , ,
March 9, 2011 Leave a Comment

My Best Practice Tips for Importing External Data Into Excel

I created this Excel Training Video after helping a viewer who was having difficulty getting the correct results in his Pivot Table. The problem, it turned out, was not with the Pivot Table. Rather, it was with the way his underlying data was stored and formatted.

When to Store Numbers as Text Values

Get External Data From Commands

External Data Commands

Specifically, he had combined the records from multiple workbooks into a master table. The data came from multiple main-frame computers. However, in some workbooks he had the “Invoice Number” stored as a “Numeric” value while in other workbooks it was stored – properly – as a”Text” value. Excel will treat Invoice Number 678910 stored as a number differently from the same entry (678910) stored as a text value.

Here is the “rule of thumb” to follow: Unless you will be using the SUM, AVERAGE, PRODUCT, MIN or MAX functions in a field, store numbers as “TEXT Values.” A short list includes fields for telephone number, postal codes, customer numbers and invoice numbers.

Start with the Correct Data

When you Import External Data Into Excel, start off on the right footing. Import the fields that contain Invoice Numbers, Postal Codes and Telephone numbers as “Text” values. In my experience, I have found that trying to format these fields “after the fact” does not give me the results that I was looking for. Getting the External Data fields correctly formatted at the Import Data step is the key to producing accurate and informative Excel reports.

“The 50 Best Tips for Excel 2007” DVD-ROM

Secure Shopping Cart
Secure Shopping Cart

This is one of the 50 Video Lessons that I offer on my DVD-ROM, “The 50 Best Tips for Excel 2007.” Click here to open a Secure Shopping Cart to purchase this DVD today. I guarantee your satisfaction. I will refund your purchase price if you are not 100% satisfied with my products.

Watch in High Definition mode on YouTube

Follow this link to watch this Excel video at DannyRocksExcels on YouTube.

My Best Practice Tips for Importing External Data Into Excel [ 7:07 ] Play Now | Play in Popup | Download (952)
Link to this post!
Filed Under: Excel Training, Import Data into Excel, Podcast Tips & Timesavers Tagged: , , , , ,
March 6, 2011 1 Comment

How to Sort Pivot Table Field Values in Excel

With a Pivot Table, you have more options and more flexibility when it comes to sorting your field values than you do with a regular Excel data set. However, many of my clients and viewers seem to get “lost” when it comes to creating custom sorts for their Pivot Table Field Valuess. So, let me demonstrate four ways to sort fields to suit your needs.

Sorting Fields in Pivot Tables

Sort Pivot Table Fields

Sort the Order of Months

 

If you create a Pivot Table for a Fiscal Year or,as in this example a Water Year, you will want to sort the Month Fields to match the starting and ending months in your FY. There are at least three ways to do this:

  1. Right click on the month that you want as your first month. Choose Move from the shortcut menu and select Move (in this case) Oct to Beginning.
  2. Another way to relocate a field value is to select the bottom part of the cell. When you see the 4-heard black cross, just move the field value to the new location. You will see a Gray Horizontal Bar as you drag the field.
  3. Most amazing way to move a field value  – and seeing is believing – is to go to the cell where you want to place the field value and just “type in the name of the field value. Incredibly, Excel moves the field values to accommodate the value that you typed in! This only works with a Pivot Table.

Sort Using a Custom List

You can create a Custom List and use that for sorting the values in your Pivot Table Field. After you create your Custom List (I show you how in this video) you choose the Pivot Table Options Sort command and then choose the direction – Ascending or Descending.

In Pivot Tables, Excel remembers your custom Field Value Settings when you move – or Pivot – field from Rows to Columns for example.

 After you watch this short five minute video lesson you will know how to sort field values in Pivot Tables to suit your needs.

Pivot Table Resources

Pivot Table Training Resources

Pivot Table Training Resources

I have published 90 Minute Video Tutorials to help you to quickly master Pivot Tables. No matter which version of Excel that you are using (Excel 2010, 2007, or 2003), I offer video training that is specific to your version. You can learn more about these resources at my secure online shopping website – http://shop.thecompanyrocks.com

Learn how to “Quickly Create Pivot Tables and Charts”

Related Excel Video Lesson

My YouTube Channel – DannyRocksExcels

Click here to watch this video in High Definition, Full-screen mode

How to Sort Pivot Table Fields in Excel [ 5:01 ] Play Now | Play in Popup | Download (1124)
Link to this post!
Filed Under: Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , ,
March 4, 2011 1 Comment

Create a Pivot Table for a Water or Hydrological Year

A viewer asked for my help in creating a Pivot Table Report that is based on a Water Year – also known as a Hydrological Year. In North America, a Water Year begins on October 1 and ends on September 30. If you use a Fiscal Year in your reporting, you will want to watch this Excel Training Video Lesson.

Pivot Table for Water Year

Pivot Table for Water Year

Add Two Fields to Source Data

In order for a Pivot Table to summarize non-calendar years and quarters you will need to have two additional fields in your source data:

  1. A Fiscal Year (Water Year) Field – Use the IF(), MONTH() and YEAR() Functions to produce this.
  2. A Fiscal Quarter (Hydrological) Quarter Field – Use the CHOOSE() Function to produce this.

Now you are ready to create your Pivot Table Report.

Group Dates by Month and Year

Begin by grouping the Date Field by Month and Year – not, in this case, by Quarter. Now, remove the new Year field that you just created and add in the Water Year Field and the Hydrological Quarter to the Pivot Table. Continue to arrange the Pivot Table to suit your needs.

Since there is no direct way to change a Calender Year into a Fiscal Year or Water Year, you must prepare your data prior to beginning to create your Pivot Table. Now that you have watched this video lesson, you know how to do this!

Learn how to “Quickly Create Excel Pivot Tables and Charts”

Related Excel Video Lessons

Follow this link to watch this video in High Definition mode on my YouTube Channel – DannyRocksExcels

Create a Pivot Table for a Water or Hydrologic Year [ 9:37 ] Play Now | Play in Popup | Download (1249)
Link to this post!
Filed Under: Excel Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , , , , ,
February 22, 2011 9 Comments

Create Dynamic Chart Titles in Excel

A viewer wrote to ask for my help in creating dynamic chart titles in Excel. He has created a series of pivot table reports and pivot charts. He wants the titles for his charts to update dynamically. That is, as he selects a new filter for his pivot table, he wants the title in his pivot chart to match the value label in his filter.

Dynamic Chart Titles
Dynamic Chart Titles

 

Note: 

This technique will work for all Excel Charts and Chart Types. It is not restricted to Pivot Tables.

The solution is rather simple: You link the Chart Title to a formula.

Link Chart Title to Formula

Select a cell that is outside the range of your Pivot Table – let’s say cell H1. An example formula is:

=”Sales for Fiscal Year “&D1

In this case, cell D1 contains the label for the value that you are selecting to filter your Pivot Table Report and Chart – e.g. 2008 as your Fiscal Year.

Notice that in the formula, I include the “TEXT” inside ” ” (double quotation marks. I also use the & (ampersand) to join the text to the cell reference (D1).

Finally, select the Chart Title and then, in the formula bar type =H1 (where H1 is the cell that contains the formula that we just wrote).

As a finishing touch, you can “hide” the formula in cell H1 by changing the FONT COLOR to match the background color for that cell.

It is that easy! Try it yourself after you watch this short (6 minutes) video lesson.

Invitation to Visit My New Online Shopping Site

Shop for The 50 Best Tips for Excel 2007

Shop for The 50 Best Tips for Excel 2007

My new, secure, online shopping website – http://shop.thecompanyrocks.com – is now open. I invite you to stop by for a visit and get more information about the many resources  that I offer for sale including, “Learn how to “Master Excel in Minutes – Not Months!”

Watch Tutorial in High Definition

Follow this link to my YouTube Channel – DannyRocksExcels – where you can watch this video lesson in High Definition, Full-screen mode.

Creating Dynamic Chart Titles in Excel [ 6:09 ] Play Now | Play in Popup | Download (2578)
Link to this post!
Filed Under: Excel Charts, Excel Formulas, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , ,
« Previous Page
Next Page »