Home Archives for Excel Video Training
August 19, 2008 6 Comments

Hide and Show Details in Excel Pivot Tables

I continue to explore the many great features of Excel Pivot Tables. In this video lesson, I show you how to place fields in the PAGE area of the template; how to hide details in order to see the big picture and to reveal the hidden details in various formats. You will also learn how to automatically generate multiple pivot table reports!

Watch This Video on YouTube

Here is the link to watch this video tutorial on YouTube .

 Here are the steps to follow in this Excel video lesson:

  1. For this lesson I am moving the data sheet to a new workbook. Select Edit, Move or Copy Sheet (Click the “Copy” check box) and select “To a new workbook.
  2. Create the Pivot Table (Data, Pivot Table Report). In step 3, choose the “Layout” tab.
  3. On the Template, move “Territory” to the “Page” area; “Sales Reps” to the “Row” area; and move “YTD Sales” to the “Data” area twice. Click OK and then Finish.
  4. Rename the new worksheet – e.g. Pivot Table.
  5. Format the Numbers in the 1st Sum of YTD Sales Field – Right Mouse Click, choose Field Settings and the Number Tab.
  6. For the 2nd Sum of YTD Sales, Right Mouse Click, Field Settings and then “Options.” In the Options tab select “% of Total” from the drop-down “Show Data as:” box.
  7. Filter the Territories in the “Page” area.
  8. Move the Territories from the “Page” area to the “Row area.” You now have 2 Row Fields. Territory is the “Outer Row” and Sales Rep is “nested” as the “Inner Row.”
  9. Experiment with “Show Details” and “Hide Details” on the Pivot Table Toolbar.
  10. To create individual worksheets for the territories: Move Territory back to the Page area. From the drop-down options on the Tool Bar, select Show Pages.

NEW! My DVD Training Series, “The 50 Best Tips … ” is available for sale at the online store for The Company Rocks

Learn How to Quickly Create Pivot Tables – With Real World Business Examples!


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: , , , ,
August 14, 2008 3 Comments

4 reasons to use Excel's Formula Auditing tools

In this Excel training video I will show you how to use the Formula Auditing Toolbar to:

  1. Learn about the relationships among your formulas and cells by tracing precedents & dependents.
  2. Finding the source of your formula errors – and correcting them!
  3. Evaluate your formulas step-by-step to better understand how they are built.
  4. Setting up a small window to “watch” your key formulas change as you enter and edit data in cells.

Here are the steps to follow in this video training lesson:

  1. Select Tools,  Formula Auditing, Show Formula Auditing Toolbar.
  2. Select a cell containing a formula and experiment with the icons to Trace Precedents and Trace Dependents. Click the icon several times to trace back as far as you can go.
  3. Select a cell and Evaluate its Formula. Step In and Step Out to learn how the formula is built.
  4. Paste a list of all of the Names in your workbook. Select Insert, Name, Paste, Paste List.
  5. Select a cell with a formula and click the Show Watch Window icon to see how the formula results change as you enter and edit data in cells that feed into that formula.

Find the Excel Training Video that you want – Index to all Excel Topics

News! 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.

Related Video Lessons

Link to this post!
Filed Under: Excel Formulas, Excel Training, Tips and Time Savers Tagged: , ,
August 12, 2008 5 Comments

3 ways to minimize data entry errors in Excel

Excel is a great program for organizing and analyzing the information that you have in your system. But what if you have lots of data entry errors and inconsistencies?

In this Excel Training Video, I show you 3 ways to minimize data entry errors. You will learn how to employ Excel’s Data Validation feature to prevent accidental and intentional data entry errors!

These are the steps for this Excel Video Training Lesson:

  1. Choose the cell or range of cells that you want to apply Data Validation to.
  2. Select Data, Validation and start with the “Settings” tab.
  3. To control a range of dates, select “Date” in the Allow drop-down menu. Choose the range of dates that you wish to control. In this video I chose <= =TODAY()
  4. To create a “Pick from list” validation: 1st step is to create the list (in a separate workbook) and then “Name” the list. In the Allow menu, choose “List” and in the Source, use the F3 shortcut to paste in your named list.
  5. To prevent Duplicate entries, in the Allow menu choose “Custom” – This allows you to write a Formula. E.g. = COUNTIF($E$ :$E$30,E2)=1 where E2 is the 1st cell in your Validation range.
  6. In the “Error Alert” tab, you can pick from 3 different “Styles. Only the “Stop” style will prevent a data entry that violates your Data Validation rules. The other 2 styles are warnings and information – you can over-ride the validation rules with either of these 2 styles.

Find the Excel Training Video that you want – Index to all Excel Topics

News! 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.

Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training Tagged: , , , ,
August 11, 2008 4 Comments

Use Vlookup function to prepare a payroll statement

A viewer wrote to me, asking for help in preparing his company’s payroll statement. I found an Excel Template using the =VLOOKUP() function to help him. In this Excel Video Training lesson, I demonstrate how to effectively use Lookup functions from 2 different worksheets.

Here are the steps to follow in this Excel Video Training Lesson:

  1. For the 1st part of this lesson I use an Excel Template called Payroll which I downloaded from the MS Excel website.
  2. The Payroll Statements draw information from two different worksheets via the =VLOOKUP() Function.
  3. Make sure that your “table arrays” have the key values in the 1st (leftmost) column.
  4. To return an “exact match” type in FALSE for the 4th argument.
  5. If you want to copy your =VLOOKUP() formulas, use “Absolute Cell” references for the table array.

Find the Excel Video Training Lesson that you want – Index of all Excel Topics

News! 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.

Link to this post!
Filed Under: Excel Functions, Excel Training, MS Excel Training, Video Response to Viewers Tagged: , , ,
August 10, 2008 4 Comments

Use Excel's =VLOOKUP() function to produce an invoice

Excel’s =VLOOKUP() function is used in almost every business form that we encounter – Customer invoices or Employee Payroll statements, etc. This short training video will show you how and when to use the Vertical Lookup function.

Here are the steps to follow in this Excel Video Training Lesson:

  1. The key to understanding the =VLOOKUP() function is to look for a “value” in the 1st column (leftmost) of a table array. So bear this in mind when you construct tables for Price lists, Employee Information, Inventories, etc.
  2. 3 Arguments are required. The 1st argument – “What value do you want to look up?” You can either enter a value or point to a cell reference.
  3. The 2nd argument asks for the location of the table array. Select the entire table, but NOT the column headers.
  4. The 3rd argument asks, “Once I have found the value in the table, what information do you want returned in your formula?” This is the Column indes i.e. counting from left to right, what is the 3 of the column that contains the information.
  5. The 4th argument is optional – enter “FALSE” if you require an exact matching value.

Find the Excel Video Training Lesson that you want – Index to all Excel Topics

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 Video Lessons

Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: , , ,
August 6, 2008 1 Comment

Create Advanced Filters for your data list

In this Excel Video Lesson we will teach you to use Advanced Filtering of your data list.

Here are the steps to follow in this video lesson:

  1. Apart from your data table write your Criteria. Criteria must consist of at least 2 rows – a Header and at least 1 row of Criteria.
  2. With an active cell selected in the data table, click Data, Filter, Advanced Filter.
  3. Accept the default setting to Filter the Data in-place and also the range for the data list. Select the cells for your Criteria and click OK.
  4. The Advanced Filter applies the Criteria to show only those records that meet your Criteria.
  5. When you write your Criteria on 1 row, ALL of the criteria must be met – i.e. the “AND” option.
  6. When you write your Criteria on multiple rows, each criteria is met independently – i.e. the “OR” option.

Find the Excel Video Lesson that you want – Index to all Excel Topics

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 Video Lessons

  • AutoFilter your list
  • Apply Custom Filters to your data list
Link to this post!
Filed Under: Data Filters and Queries, Excel Data Tables and Lists, Excel Training Tagged: , , , ,
July 20, 2008 1 Comment

Name a cell and use it in a formula

Here are the steps to follow in this lesson:

  1. A “named cell” creates an Absolute cell reference.
  2. One way to name a cell is to select the cell and type the name you want in the “Name Box” (Do not use any spaces) and hit Enter
  3. To apply the Named Cell in an existing formula, highlight the part of the formula and select Insert, Name, Paste (the named cell) and click OK.
  4. Consider creating a “Named Constant” for numbers you use frequently in a formula e.g. Sales Tax or Inflation Rate.
  5. To create a Named Constant choose Insert, Name, Define and in the “Refers to” box type (for example) = 0.0715
  6. Follow the same procedure to use the Constant in your formula: Insert, Name, Paste (named Constant) and click OK.
  7. Edit the Named Constant if e.g. the Sales Tax Rate changes. All formulas that use the Named Constant (in this workbook) will update automatically!

Click Here to Find the Excel Training Video in My Index

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 Functions, Excel Training, Tips and Time Savers Tagged: , , , ,
July 16, 2008 4 Comments

Learn the powerful SUMIF Function

Here are the steps to follow in this lesson:

  1. Type =sumif( and then click the FUNCTION WIZARD to enter the ARGUMENTS.
  2. Enter the range of data that you wish to SUM as the 1st Argument.
  3. Identify the Criteria to evaluate e.g. “>500″ – (enclose the Argument inside ” “) as the 2nd Argument.
  4. The 3rd Argument is OPTIONAL. You can leave this empty if you want to SUM the same range of data from your 1st Argument.

NEW! Download the Practice File from this Lesson:

Find the video lesson that you want – Index to all Excel Topics

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 Functions, Excel Training Tagged: , , ,
« Previous Page