Home Archives for Calculated Fields in Pivot Tables
December 13, 2012 1 Comment

Excel Pivot Tables Tutorials: My YouTube Playlist

I have been organizing the video tutorials on my YouTube Channel – DannyRocksExcels – into Playlists.

I quickly realized that I have more videos (70 +) that focus on Excel Pivot Tables than on any other topic. So, having a playlist for all 70 (and growing) Pivot Table Tutorials is a great help when you need to learn about Pivot Tables. Or, simply to refresh your memory about how to use a specific Pivot Table Tool.

Scope of My Pivot Table Tutorials

I posted my first Pivot Table Tutorial on YouTube back on August 1, 2008. So far, that 7 minute Introduction to Pivot Tables in Excel 2003 has received over 90,000 views! Since then, I have created Pivot Table videos for Excel 2007 and Excel 2010.

Front Page of Online Shopping SiteIn-Depth Focused Training for Excel Pivot Tables

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

 

Link to My Excel Pivot Tables Playlist

Follow this link to go to my Excel Pivot Tables playlist on YouTube. If you like this Playlist, I encourage you to share it with your friends and colleagues.

Excel Pivot Tables Playlist on YouTube

Link to this post!
Filed Under: Excel Training, Pivot Tables, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
July 28, 2011 Leave a Comment

3 Work-Arounds for Shortcomings in Calculated Fields in Excel Pivot Tables

Excel Work-arounds

3 Work-Arounds for Calculated Fields

Don’t get me wrong. Excel Pivot Tables are a great way to summarize, analyze and present the information in your data. And, creating Calculated Fields in Pivot Tables is a terrific way to extend your analysis.

However, there are a few “quirks” that you should be aware of when using a Calculated Field”

A Calculated Field is always performed against the SUM of your data!

Well, what if you want to calculate against the MIN or the MAX of your data? Or perform some other calculation?

3 Work-Arounds for Calculated Field Shortcomings

I encourage you to look at a Pivot Table as “a means to an end.” Let your Pivot Table take you to the 90% completion point – and a Pivot Table does this very well! Then, use one of these 3 strategies to help you to complete your analysis.

  1. Sometimes, you simply must return to your source data and add an additional field so that your Pivot Table has more information to work with.
  2. You can make a copy of your Pivot Table and then Paste Special the Values to another location. From there, you can complete your analysis with more flexibility.
  3. You can create formulas outside of the Pivot Table – as I demonstrate in this video, deselect the “pesky” GetPivotData Function first. There are some limitations here:
  1. Remember that all cell references in your formula must appear in your Pivot Table
  2. Your Pivot Table must essentially remain “static” – e.g. no more fields added or subtracted, etc.

Watch Video Tutorial in High Definition on YouTube

This video lesson is longer in length than normal. To watch it in High Definition, click on this link to go to my YouTube Channel – DannyRocksExcels

Download Extended Length Video Tutorials for Pivot Tables

I have created extended length video tutorial recordings for Pivot Tables in Excel 2003, Excel 2007, and Excel 2010. You can download them for only $9.95 USD.

Click on this link to get more information about my Excel Extended Length Video Recordings.

 

Link to this post!
Filed Under: Excel Training, Pivot Tables, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
May 24, 2011 Leave a Comment

My Extended Length Pivot Tables Video Recordings Available for Excel 2003, 2007 and Excel 2010

On my WebEx site – http://thecompanyrocks.webex.com – I have posted three, one-hour-plus video recordings to help you to learn how to create and customize Excel Pivot Tables:

Download “Free of Charge” Resources for Each Recording

Click on the hyperlinks above to go to a special landing page for each Excel version. From the landing page, you will find links to download the resources that accompany each version:

  • The Excel Workbook – in each version of Excel (2003, 2007, 2010) that I use during the video recording.
  • The Step-by-Step Instructional Manual that I created for each Excel version of my recordings.
    • Available as an Adobe Acrobat PDF or as a Word Document

Very Attractive Pricing for Recordings

I have decided to price each of these recordings – which you can download to your computer – at US$9.95

  • Imagine what you would have to pay to hire a trainer – per hour –  to teach you these Pivot Table lessons!
  • Imagine how many pages in a book you would have to wade through to read about Pivot Tables!
  • Now, imagine being able to:
    • Watch a video demonstration of the steps to take to create or customize a Pivot Table.
    • Read – and make notes in – a written document that follows the steps that I demonstrate.
    • Practice the techniqes that I demonstrate on the video with the actual Excel worksheets that I use.
    • Skip back and forth to the chapter that you want to watch or review on the video recording.
    • Download the recording for the exact version of Excel that you are using.
    • Get all of this for only US$9.95

What You Will Learn on My Video Recordings

I have taken care to organize each of these topics “by chapter” – with starting points and ending points – on the recordings:

  • Create a Pivot Table in the Excel version that you are using.
    • Spotlight the uniqe features of each version
  • Filter and Sort the Fields in your Pivot Table
  • Change the Data Source & Refresh your Pivot Table
  • Use Multiple Functions and Views to Calculate your Pivot Table
  • Create a Calculated (“virtual”) Field in your Pivot Table
  • Group Fields – e.g. turn daily transactions into a Month, Quarter, Year Pivot Table Report
  • Create and Filter Pivot Charts
Download WebEx Recording

Download WebEx Recording

Download My Recordings at WebEx

  • Go to http://thecompanyrocks.webex.com and on the left side of the screen click on “Attend a Session – Recorded Sessions” to find the recording that you wish to download.
  • Register and enter your payment information – this is a secure shopping site.
  • Download the recording – also download the WebEx Recording Player so that you can play the ARF recording.
    • I recommend that you “Save” your recording on the download page. Play it from your directory of choice after it has downloaded
    • From the ARF Player for the WebEx Recording, you can convert the recording to another video format (MP4, AVI, WMV or SWF)

I welcome your feedback. Send me your questions, comments and suggestions for future recordings.

Link to this post!
Filed Under: Excel Training, MS Excel Training, Pivot Tables, Video Training Courses, WebEx by Cisco, WebEx Recordings & Webinars Tagged: , , , , , , , , ,
May 22, 2011 2 Comments

Still Using Excel 2003? Download My Video Tutorial Pivot Tables 2003 to Summarize, Analyze and Present Data

Video Tutorial for Excel 2003 Pivot Tables

Excel 2003 PivotTables Video

By popular demand, I have created an “Extended Length” ( 69 minutes) video tutorial in Excel 2003 that you can download – for US$9.95  – “Create Pivot Tables in Excel 2003 toe Summarize, Analyze and Present the Information in Your Data.”

Quite a few of my customers and viewers are still using Excel 2003. Are you one of them? If so, this is welcome news for you! If you can even find a video tutorial for Excel 2003, chances are it was created “ages ago.” I incorporate current video technology to view online or to download  69 minute video tutorial that will teach you everything that you need to know to create, modify and present a PivotTable or PivotChart Report using Excel 2003.

What You Will Learn from this Video Recording

Here are the topics that I cover – each as an individual chapter on the recording:

  • Creating a PivotTable in Excel 2003
  • Filtering and Sorting Fields in an Excel 2003 Pivot Table
  • Refreshing your PivotTable; Changing the Source Data for your Excel 2003 PivotTable
  • Changing the Functions and Type of Calculations in your Excel 2003 PivotTable
  • Creating a Calculated Field in an Excel 2003 PivotTable
  • Grouping an Excel 2003 PivotTable Field (e.g. Month, Quarter, Year from a series of Daily Transactional Data)
  • Creating and Modifying and Excel 2003 PivotChart

Skip to Individual Chapters on the Video Recording

If you have ever purchased a recording of a “live” Excel Webinar, you know that one frustration is: “How do I get to the specific part of the recording that I want to learn or repeat?”

The last thing that I want you to worry about is “getting stuck” with watching my video recording from start to finish! NO! NO! NO!

That is why I recorded this video webinar with you in mind – I created an easy to follow Table of Contents on the recording so tht you can skip to or repeat an indivual chapter or starting point!

I am confident that you will enjoy this feature!

How to Get Started

  1. Go to http://thecompanyrocks.webex.com and click on “Recorded Sessions” to see the list of all of my Recorded Video Lessons on WebEx by Cisco.
  2. Click on this link to open the Video Recording, “Excel 2003 Pivot Tables to Summarize and Analyze Data”
  3. Register for this recording (First Name, Last Name and email address) so that I can assist you quickly if you need help with downloading the recording or accessing the support materials.
  4. Enter your payment information US$9.95 using PayPal or a Credit Card – This is a SECURE WEBSITE.
  5. Download this video recording. I recommend that you: 
    1. Download the WebEx by Cisco ARF Recorder/ Player. Once you have opend the Video Recording, you can convert it to a different format (e.g. WMV (Windows Media), SWF (Flash Format) or MPEG4 (MP4 Format)
    2. Save the File to a Folder on your hard disk drive.
  6. Watch the video recording.

Download the Support Material for this Video Recording

Here is a link to the web page that I created for this video recording. Follow the hyperlinks to view online or download the Excel Workbook that I used in this recording as well as the Step-by-Step Instructional Manual that accompanies this recording.

Give Me Your Feedback

As I develop these “Extended Length” video recordings, I seek your feedback. Tell me what you liked. Tell me what you did not like – any why. Send me your suggestions for future topics for my video tutorial recordings.

Link to this post!
Filed Under: Excel 2003 Video Lessons, Excel Training, Pivot Tables, WebEx by Cisco, WebEx Recordings & Webinars Tagged: , , , , , , , , , , , , , ,
May 18, 2011 Leave a Comment

Download My 75 Minute Video Tutorial on Excel 2007 Pivot Tables

Topics Covered

Table of Contents for Recording

I have just recorded and published a 75 Minute Video Tutorial titled, “How to Create and Modify a Pivot Table in Excel 2007 to Summarize, Analyze and Present the Information in your Data Set.”

It is published on my WebEx by Cisco site: http://thecompanyrocks.webex.com Follow this link to go directly to this recording.

There is a charge for this recording – US$9.95 In addition to the recording you get:

  • A Step-by-step instructional manual
  • Access to the Excel workbook files that I used for this recording

Not to brag , but I think that this video tutorial is excellent! I spent many hours planning out the exercises, working through the timing of each chapter of the video and practicing my delivery. I am happy with the result. And, more important, I think that you will also be satisfied with what you will learn from this recording!

What You Will Learn on My Recording

My goal is to give you a comprehensive understanding of Excel 2007 Pivot Tables. But to give you the opportunity to learn this in “bite-size chunks” that can replay when necessary. I created realistic exercises to illustrate each topic.  Also notice that I have created a Table of Contents for the recording so that you can go to a specific chapter on the recording. You can stop and start at any point.

How to Download this Recording

  1. Go to http://thecompanyrocks.webex.com or click on this link to go to the main menu.
  2. Click on “Recorded Sessions.”
  3. Click the link for the recording, “Excel 2007 Pivot Tables to Summarize Data.”
  4. Register for this session (First Name, Last Name and email address).
  5. Enter payment information (PayPal or Credit Card). This is a secure site!
  6. Follow the instructions to Download my recording.
  7. Recommended – you can also download the ARF Recorder to view the recording.
  8. At this point you can decide to convert the recording to a different format. E.g. WMV (Windows Media Player), SWF (Flash) or MP4 (MPEG4)
  9. At the end of the recording, you are taken to a web page with links to download the Instructional Manual and to download the Excel Workbook that I used during this recoring.

Give Me Your Feedback

I welcome your comments and suggestions for future video tutorial recordings. You can add your comments below or send them to me via email.

Link to this post!
Filed Under: Excel Training, Pivot Tables, WebEx by Cisco, WebEx Recordings & Webinars 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: , , , , , ,
April 27, 2010 1 Comment

Create a Calculated Field in an Excel 2007 Pivot Table

Pivot Tables are very flexible. Yes, they are based on the fields in your source data, but you can also created new “virtual fields” – Calculated Fields.

In this lesson I show you how to create a new, Calculated Field, in a Pivot Table. This will be a “Price-per-Case” field that does not reside in the original source data. You will also learn how to hide the Formula Error #DIV/0! that results when we create this new field.

If you enjoyed this tip and my style of training, I invite you to visit my online store to purchase my DVD, “The 50 Best Tips for Excel 2007.”

You can watch this Excel Video Lesson on YouTube

Related Excel Video Lessons:

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

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