Home Archives for Excel Video Training
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: , , , , , , , , , , ,
June 29, 2011 Leave a Comment

Working with Custom Lists in All Versions of Excel

Edit Custom Lists in Excel

Edit Custom Lists in Excel

Custom Lists in Excel are great because the help to ensure accuracy and consistency when entering data on a worksheet. Excel comes with several Custom Lists built-in to the program – e.g. Days of the Week and Months of the Year. Now, you can quickly use Excel’s AutoFill handle to add Jan, Feb, Mar, Apr, etc. in any direction (vertical or horizontal) on the active worksheet.

Edit Custom Lists

You can quickly create – or edit – your own Custom List. Follow these steps:

  1. Type your list in a contiguous group of cells – either vertically or horizontally.
  2. Select the cells with the values that you just entered.
  3. Spell check this list – use the F7 Keyboard Shortcut
  4. Open up the Edit Custom List Dialog Box. (Watch this video to see how this is differs between Excel 2010, Excel 2007 and Excel 2003.)
  5. With your new Custom List selected, click the “Import” button to add your Custom List to the current version of Excel on this computer.

Sort Data Using a Custom List

On this video tutorial, I demonstrate how to sort a list of data using a Custom List – e.g. to get the “Month” field sorted in chronological order (January, February, etc.) This will save you a great deal of time!

Create a Custom List for Letters of Alphabet

Having a Custom List for the 26 letters of the alphabet comes in handy on many occasions. I show you how to AutoFill down the initial list using =Char(Row() + 64) beginning in Row 1 – a really usefuly function!

View This Excel Video Tutorial in High Definition

Follow this link to watch this video tutorial 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!

Working with Custom Lists in All Versions of Excel [ 8:03 ] Play Now | Play in Popup | Download (1477)
Link to this post!
Filed Under: Excel Basics, Excel Functions, Excel Training, Tips and Time Savers Tagged: , , , , , , , ,
June 28, 2011 2 Comments

How to Embed an Interactive Excel Workbook from a Sky Drive in Windows Office Live!

For the past two or three months, I have been uploading the Excel Workbooks that I use during my video tutorials to my Sky Drive at Windows Office Live! – www.office.live.com Just this past week, I learned that I can also “embed” those stored files here – in a blog post – on my website! So, here is my first attempt to share an “interactive Excel Workbook” with you.

Embedded Excel Workbook

This Excel worksheet is one of the basic data sets that I use when I demonstrate how to create Excel Pivot Tables. Scroll horizontally or vertically to see the Pivot Table. Notice that you can use the drop-down filters in the Pivot Table!

Click the Icon in the lower right corner of the Excel WebApps frame and you will be taken to my Sky Drive online where you can view or manipulate the worksheet in Full Screen More. Or, you can choose to download this file to your computer to work on it with the full range of Excel commands.

Download Excel Video Tutorial Recordings for $9.95

If you want to learn more about Pivot Tables, click on this link to gather information on my new series of Extended Length (one hour +) Excel Video Tutorials. You can download these videos to your computer for only US $9.95 from my WebEx by Cisco site – http://thecompanyrocks.webex.com – I store all of the Excel Workbooks that I use in the lessons on my Sky Drive online!

Watch This Video Tutorial in High Definition on YouTube

Follow this link to watch my step-by-step instructions to accomplish this task – in High Definition – on my YouTube Channel, DannyRocksExcels.

Link to this post!
Filed Under: Download Files from Video Lessons, Excel Training, Interactive Webpages, Pivot Tables, WebEx Recordings & Webinars, YouTube Videos by Danny Rocks Tagged: , , , , , ,
June 27, 2011 Leave a Comment

How a Single Blank Cell Affects an Excel Pivot Table Report

As you will see in this Excel Video Tutorial, having a single blank cell in a data set will greatly affect how Excel Functions and Pivot Tables are designed and calculated.

Single Blank Cell in Data

Single Blank Cell in Data

Imagine, how a single blank cell, in a field of 848 records, can change the way your Pivot Table views the data type (text vs. the expected numeric data type) and subtotal function (Count vs. the expected SUM function).

Seeing is believing!

Preventing a Blank Cell from “Creeping into” your data set

Use the Keyboard Shortcut Ctrl + Down Directional Arrow to go to the last cell that contains data in a column. This is a great way to isolate “stray” blank cells that will have a great impact on your Pivot Tables and other calculations in Excel.

Also, in Excel 2007 and Excel 2010, look for where a “numeric” field is placed by default. A single blank cell will cause an (intended) numeric field to be placed in the Row (text) labels area. Even dragging this field into the VALUES Drop zone does not solve the problem because the COUNT Function will be used to Subtotal this field. This is a “disaster waiting to happen!”

Introducing My Extended Length Excel Video Recordings

This is my opportunity to introduce you to my new, extended-length (one-hour +) Excel Video Tutorials that you can download for only US $9.95!

Here is the link that gives you more information about this exciting new service:

http://www.thecompanyrocks.com/pivot-table-tutorial-videos-to-download-practice-files-instructional-manual-included/

I offer you multiple options:

Let me know what you think!

Let me know which topics you would like me to record for you to download.

Send me an email with your comments and suggestions: danny@thecompanyrocks.com

Watch This Video in High Definition

Follow this link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

How a Single Blank Cell Affects an Excel Pivot Table Report [ 7:21 ] Play Now | Play in Popup | Download (1129)
Link to this post!
Filed Under: Excel Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers, WebEx by Cisco, WebEx Recordings & Webinars Tagged: , , , , ,
« Previous Page
Next Page »