Home 2011 Archives for July 2011

Archives for July 2011

July 31, 2011 Leave a Comment

Use the Show Values As Formulas in Excel Pivot Tables

Show Values As

Show Values As in Pivot Table

I participate in a number of Group Discussion Boards on LinkedIn. In this video tutorial, I address one question that was recently asked:

“Is it possible to show a Percentage of Running Total formula in a Pivot Table?”

The answer is, “Yes, if you are using Excel 2010. If you are using earlier versions of Excel, you can use a work-around outside the Pivot Table.”

New in Excel 2010

A great new feature introduced in Excel 2010 is the drop-down menu for the “Show Values As’ in a Pivot Table Calculation. In earlier versions of Excel, the Show Values As option was not so obvious. And, I found that many of my clients did not know how to use it – or could not locate the drop-down menu.

Work-Around for Show % of Running Total

While the “Running Total in…” calculation has been available for quite some time, the “% of Running Total in …” formula is brand new in Excel 2010. The work-around that I demonstrate is to first, create the Show As “Running Total in …” formula. Then change the Font and background of these cells to “White.” Next, write a standard formula “outside of the Pivot Table” that references these cells. Be sure to turn off the “GetPivotData” Formula when you write this formula.

Download Extended Length Pivot Table Video Tutorials

Click on this link to learn about my new “extended length” video tutorials for Pivot Tables. They are availabe for Excel 2003, Excel 2007 and Excel 2010.

Watch Video in High Definition on YouTube

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

Master Excel Pivot Tables in 90 Minutes

Follow this link to learn about my new “Extended Length” – 90 Minutes – Video Tutorials

 

 

How to Use the Show As Values Formulas in Excel Pivot Tables [ 8:13 ] Play Now | Play in Popup | Download (1325)
Link to this post!
Filed Under: Excel Training, New in Excel 2010, Pivot Tables, Podcast Tips & Timesavers 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: , , , , , , ,
July 25, 2011 Leave a Comment

How to Drill Down in a Pivot Table to Display the Details for a Summary Cell

Drill Down in a Pivot Table

Drill Down Details in Pivot Table

Excel Pivot Table Reports are a great way to summarize the results of multiple records in an underlying data set.

However, at some point, someone will question the accuracy of a specific calculation in your Pivot Table – this is to be expected.

When this happens. you can quickly “drill down” to display the details for any summary cell in your Pivot Table. You simply “double-click” the summary cell and you get to review the detail on a new worksheet in your Excel workbook.

Refreshing a Pivot Table

The key points to understand about Excel Pivot Tables are:

  • You cannot change an individual value in a Pivot Table.
  • You cannot update any changes from a “drill down” worksheet in a Pivot Table.
  • After you edit your underlying data set, you must “Refresh” your Pivot Table to get the revised calculations.

Formatting “Blank Cells” in a Pivot Table

One aspect of Pivot Tables – displaying “blank cells” – is disconcerting to many people who are using or viewing Pivot Tables for the first time. In this video tutorial, I demonstrate how to customize the display of these cells that have no underlying values to calculate.

View this Tutorial in High Definition

Follow this link to watch this video tutorial in High Defintion on my YouTube Channel – DannyRocksExcels

Master Excel Pivot Tables in 90 Minutes!

Click this link to find out how to download or purchase a DVD-ROM of my extended length video tutorials for Excel Pivot Tables.

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

Create a Pivot Chart to Present a Frequency Distribution Report

Combination Chart for Frequency Report
Combination Chart for Frequency Report

Quite a few of my viewers requested that I continue my series of creating and presenting a “Frequency Distribution Report” using an Excel Pivot Table. So, here is the new Part 3 in the series!

In this Excel Video Tutorial, I demonstrate – using Excel 2010 – how to create a Pivot Chart for the Frequency Distribution Pivot Table that I created and modified in Parts 1 and 2 of this series.

Key Point: A Pivot Chart Must be based on a Pivot Table Report!

  • Any structural changes that you make to the Pivot Table are reflected in the Pivot Chart.
  • If you “filter” the Pivot Chart, those filters apply to the Pivot Table that is the basis for your Pivot Chart – and vice versa.

Focus on the Chart

As I demonstrate in this video – posted on YouTube – I prefer to focus on one task at a time. I prefer to get the formatting and structure of the Chart “right” before I think about using Filers and other “goodies to add .” This “Best Practice” helps me to get more accomplished in less time!

My bottom line is: “Focus on the task at hand. Eliminate any possible distractions from that primary task!”

A correctly designed Pivot Chart will help you to present your information accurately and interactively! For this presentation, I decided that a “Combination Chart” would be the best way to present the information that I wanted to convey. A “Combination Chart” allows you to have a Primary Axis (Revenue) that uses a Column Chart Type and a Secondary Axis (Invoice Count) that uses a Line Chart Type.

When to Use a Secondary Chart Axis

In this example, there is a wide difference between the two fields that we are charting:

  • Revenue for the Invoices in the “Bin Range” is in “the millions!
  • Count of Invoices in the “Bin Range” falls into the hundreds and low thousands.

Using a “Combination Chart” in this example – (Column Chart for Revenue in Millions) and Count of Invoices (Line Chart for Count in Hundreds) allows you to present the “relationship” between both metrics. I show you step-by-step how to create this combination chart!

 

Watch This Video in High Definition on YouTube

Follow this link to view this video tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Click this link to view Part 1 of the 3 Part series – Creating a Frequency Distribution  Report with Excel Pivot Table

Click this link to view Part 2 of the 3 Part series – Formatting a Frequency Distribution Report with Excel Pivot Table

Link to this post!
Filed Under: Excel Charts, Excel Training, New in Excel 2010, Pivot Tables, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
July 19, 2011 1 Comment

Formatting an Excel Pivot Table Frequency Report

Show % in Frequency Report

Show % of in Frequency Report

This is Part 2 of my 2-part series on Excel Pivot Table Frequency Reports. A Frequency Report is a great way to answer these questions:

  • How many invoices did we generate for values under $5,000?
  • How many invoices did we gererate for values over $15,000?

In this part, I show you how to format the Frequency Report to make it easier to present the information.

Formatting the Frequency Report

Here are the changes that I made to improve the look of the Frequency Report:

  • Reduced the number of “bins” in the frequency range by changing the “Grouping.”
  • Changed the formatting to make large numbers (millions) easier to read – e.g. as $3.2M
  • Made a copy of the Pivot Table to show values as “% of Total.”

Click on this link to go to Part 1 in this series of video tutorials for Pivot Table Frequency Reports

Learn How to Use Pivot Tables

I have created a series of video tutorials that you can download to learn how to create and modify Excel Pivot Tables. You can choose to download the recording that fits your version of Excel (2003, 2007 or Excel 2010). The cost is $9.95 for the video recording and that includes the Excel workbooks that I used and a step-by-step instructional manual. Follow this link to get more information about these recordings.

Watch this Video in High Definition on YouTube

Follow this link to watch this video tutorial on my YouTube Channel – DannyRocksExcels

Link to this post!
Filed Under: Excel Training, Format Cells in Excel, Pivot Tables, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
July 17, 2011 2 Comments

Use Excel Pivot Table to Determine Frequency Distribution of Invoice Amounts

Frequency Distribution

Frequency Distribution of Invoices

Has your manager ever asked you,  “How many small invoices do we process?” Of course, you do not know that number immediately. However, you can produce the answer within about 2 minutes when you use an Excel Pivot Table.

Amazing?

Group Fields in a Pivot Table

Yes. And, easy to produce thanks to the “Group Fields” tools in an Excel Pivot Table – in any version of Excel!

In fact, I demonstrate how to produce the frequency distribution in:

  • Excel 2010 / Excel 2007
  • Excel 2003

Download My Pivot Table Video Recordings

This give me the opportunity to announce that I have published a series of Video Tutorial Recordings for Excel Pivot Tables – on my WebEx by Cisco website –  that you can download for only $9.95 USD!

Follow this link to get more information and to follow the direct links to download these recordings.

It is so easy to produce this Pivot Table report that you will be amazed – and want to learn more. That is why I have decided to produce a sequel to this lesson. I will post Part 2 tomorrow. In that segment, I will help you to answer your manager’s question – “How many of these invoices do we process each your?”

Just make me one promise! Do not tell your manager how easy it is to produce these reports!

Of course, I am joking about this. But, most people who do not work with Pivot Tables on a regular basis have no idea how easy it is to harness the analytical and presentation power of Pivot Tables. Once you watch these lessons – and download my video recordings – you will know a lot more about Pivot Tables.

Watch This Video in High Definition on YouTube

Click here to watch this video on my YouTube Channel – DannyRocksExcels – in High Definition

Click here to go to Part 2 in this series – Formatting a Frequency Distribution Report for Presentation

Link to this post!
Filed Under: Excel Training, Pivot Tables, WebEx Recordings & Webinars Tagged: , , , , , ,
July 10, 2011 Leave a Comment

My Two Most Popular Excel Tutorials on YouTube

YouTube Logo

YouTube - DannyRocksExcels

Three years ago – in 2008 – I started to create Excel Video Tutorials, both for this website and, also, for my YouTube Channel – DannyRocksExcels.

Looking back, I am amazed to see that two of my earliest postings have been viewed over 50,000 times on YouTube!

Most Viewed Excel Videos  on YouTube

  1. The Basics of Excel 2003 – Entering Data – has been viewed 64,725 times to date! Click on this link to watch it on YouTube
  2. Introduction to Pivot Tables in Excel 2003 – has been viewed 50,477 times to date! – Click here to watch this video on my YouTube Channel

Improved Production Values Since 2008

While I may sometimes “cringe” at my production value choices from 2008, I must say that the content of each of these two viewo tutorials holds up nicely!

What I Offer Today!

I have come a long way since 2008 – both in my knowledge of Excel and How to present the Excel Training Options that you can benefit from.

Re: Excel Pivot Tables – Paradoxically, Pivot Tables are BOTH the most powerful tool in Excel AND also, one of the easiest tools to use to analyze and present the information hidden inside your data!

Here is a link to the Excel Video Tutorial Recordings that you can download for only $9.95 USD. I have customized each recording for Excel 2003, Excel 2007 and Excel 2010!

Get my DVD-ROM – “The 50 Best Tips for Excel 2007” for only $29.97!

 

Let me know what you think!

Danny Rocks

The Company Rocks

 

Link to this post!
Filed Under: Excel Basics, Excel Training, Pivot Tables, WebEx Recordings & Webinars, YouTube Videos by Danny Rocks Tagged: , , , ,
July 9, 2011 Leave a Comment

Three and a Half Ways to Average Excel Data Using Criteria

AverageIf Function in Excel

AverageIf Function in Excel

It is easy to use Excel’s AVERAGE Function to get the average for an entire range of data. But, what if you are asked to produce a report that shows the average for select groups of records – for example, average test results for the female students only; or for the male students only? What approach do you take? What Functions do you use to produce this report?

Three and a Half Ways to Produce this Report

  • SUBTOTALS – Easy to use. Remember to sort your data first using the field that you want to subtotal.
  • AVERAGEIF Function – Very easy to use. Only works in Excel 2007 and Excel 2010.
  • DAVERAGE Function – Will work in any version of Excel. Easy to add or change the Criteria.
  • PIVOT TABLE – Very easy to create. No need to write a formula. Create report with @ 6 mouse clicks!

My Preferred Approach – Create a Pivot Table Report

While Pivot Tables are the most powerful tool in Excel, they are also one of the easiest tools to use in Excel! Why?

  • Because you can create a Pivot Table Report without writing a single formula!
  • You can create this Pivot Table Report with about six clicks of the mouse.
  • You cannot possibly harm your underlying data when you create Pivot Tables!

Download a One Hour- Plus / In-depth Video Tutorial on Pivot Tables – for $9.95

I have created a series of Excel Video Tutorials that you can download for $9.95 US Dollars each. Here is a link to my informational page that gives you the details.

  • Are you Using Excel 2003? – I created a video recording for Pivot Tables using Excel 2003 that you can download for $9.95!
  • Are you Using Excel 2007? – I created a video recording for Pivot Tables using Excel 2007 that you can download for $9.95!
  • Are you Using Excel 2010? – I created a video recording for Pivot Tables using Excel 2010 that you can download for $9.95!

As with all of my products, I guarantee that you will be 100% Satisfied or I will refund your purchase price – no questions asked!

My Extended length Excel Training Video Recordings are hosted at: http://thecompanyrocks.webex.com – I use the secure shopping cart at WebEx by Cisco Systems, so you can be sure that your personal information is safe and secure.

Watch Video in High Definition on YouTube

Follow this link to watch this video tutorial in High Definition mode on my YouTube Channel – DannyRocks Excels

Get My DVD-ROM, “The 50 Best Tips for Excel 2007” for only $29.97

Three and a Half Ways to Average Excel Data with Criteria [ 9:24 ] Play Now | Play in Popup | Download (1375)
Link to this post!
Filed Under: Excel Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers, Subtotals and Outlines in Excel, WebEx by Cisco, WebEx Recordings & Webinars Tagged: , , , , , , , , , , , ,
July 8, 2011 1 Comment

Use Excel’s AutoFill Tools and Formulas to Increment Time Intervals

In my experience, many Excel users get “frustrated” when trying to enter and calculate Time Intervals. For example, setting up a schedule for meetings that begin and end in 45 minute intervals. After you watch this video tutorial, you will learn how easy it is to set up this type of schedule!

The Problem with Time in Excel

When it comes to entering Time Values, Excel will only accept a short list of Time Formats. For example, 6:00 AM is a valid Time Entry in Excel. However, Excel will treat 6:00 A.M. as a “text entry!” 6:00 AM is stored as the decimal value 0.25 in Excel. Decimals are numbers that Excel can calculate and increment. In this video, I demonstrate a shortcut that ensures that you always enter a Time Value as a Number.

How to Increment in Two-Hour Intervals

As I demonstrate in this video, use this formula to increment a series of two-hour time intervals:

=starting_cell + 2*(1/24)

In this case the (1/24) portion of the formula indicates a “one-hour segment.” 1 hour out of a 24 hour day. The +2 part of the formula indicates that you want to increment by 2 – of what? The answer is +2*(1/24) – by 2 hours.

To indicate a 15 minute increment use this formula:

=starting_cell + 15 * (1/24/60) – That is, divide each one hour segment (1/24) by 60 minutes (1/24/60) and multiply this by the number of minutes that you wish for your schedule.

Watch Part 1 of this Series – Increment Dates in Excel

Follow this link to watch Part 1 of this two-part series – “Use Excel’s AutoFill Tolls and Excel Functions and Formulas to Increment Date Intervals.”

View this Tutorial in High Defintion on YouTube

Click on this link to watch the video lesson in High Definition on my YouTube Channel – DannyRocksExcels

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007 for only $29.97

Use Excel's AutoFill Tolls and Formulas to Increment Time Intervals [ 9:02 ] Play Now | Play in Popup | Download (2003)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , , ,
July 7, 2011 1 Comment

Use Excel’s AutoFill Options and Formulas to Increment a Series of Dates

Excel AutoFill Options

Excel AutoFill Options

I begin this Excel Video tutorial by first, demonstrating how to use Excel’s AutoFill Options to increment a series of dates. Some of these AutoFill Options are:

  • Fill Weekdays Only (Monday through Friday)
  • Copy Formatting Only
  • Increment by Adding one month to the previous cell
  • Increment by Adding one year to the previous cell

Here’s a Tip: If you use your Right-mouse button to AutoFill a series, a menu of options will pop up automatically when you release the mouse. Try it!

Excel Functions and Formulas to Increment a Series of Dates

Two of my favorite Excel Date Functions are part of the “Analysis ToolPak” Add-in:

  1. The EDATE() Function – returns a “serial number” for a Month that is X number of months away from the starting date.
  2. The EOMONTH() Function – is similar to EDATE. However, it always returns the last Calendar Day of the Month that is X  number of months away from the starting date.

In Excel 2007 and Excel 2010, the Analysis ToolPak Add-in is activated by default. In this video, I demonstrate how to activate it if you are using Excel 2003 or older.

Other Functions Used in this Video Tutorial

  1. The DATE() Function. Remember that if you use this function to increment by one-year intervals that you need to “nest” the Year(), Month() and Day() functions in the arguments. This is a “tricky” function to use.
  2. The WEEKDAY() Function. In the video, I nest this function inside an IF() Function in order to get a series of dates that include Monday through Friday only!

Related Video Tutorial

My next video in this series demonstrates how to use Formulas to increment a series of times by “minutes” or by “hours.”

Watch This Video in High Definition

Follow this link to view this video tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Shop for Excel Training Resources

Shop for The 50 Best Tips for Excel 2007

Shop for The 50 Best Tips for Excel 2007

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to see all of the great training resources that I offer you.

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007” for only $39.95!

 

How to use Excel's AutoFill Options and Formulas to Increment a Series of Dates [ 11:21 ] Play Now | Play in Popup | Download (1637)
Link to this post!
Filed Under: Date Functions and Formats in Excel, Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , , , , , ,
Next Page »