Video Lessons

(Click on the topic to go directly to the video lesson)

(Several Excel Practice files are available for you to download)

Now on sale – My DVD “The 50 Best Tips for Excel 2007″

I invite you to watch a short video tour of my DVD – “The 50 Best Tips for Excel 2007″

Basics

First Steps in Understanding Excel

The videos in this section offer a quick review of the basic building blocks for creating spreadsheets in Excel

• Data Entry – Explore Several Options to Simplify This Task
• Data Forms
• Format cells before entering data -  Especially Important for Zip Codes, Dates, Times & Phone Numbers

Basic Calculations

Working with Discounts

• How to calculate a discounted price
• What % of Discount Did I Receive?

Charting Data

Create and Modify Excel Charts

• Create a chart with one keystroke
• Click and Drag a Data Series in Your Chart to Seek a New Goal
• How to Create a Combination Chart in Excel – When there is a large “gap” between your data series values
• How to Explode a Pie Slice in Your Chart

Commands

Keyboard Shortcuts

• Alt+; – Edit, Go to, Special, Visible cells only
• Ctrl+a single letter – Ctrl+A through Ctrl+Z
• Ctrl+tilde (Ctrl+˜) – To reveal all of the formulas in a worksheet
• Navigate to worksheets w/ Hyperlinks and Named Ranges
• Select Data w/ Keyboard and Mouse-click Shortcuts – Practice file available to download: select-data-w-shortcuts
• The Function Keys – F1 through F12
• Undo and Redo using Ctrl+Z and Ctrl+Y
• Use MS Office’s AutoCorrect Options to Create Shortcuts

Paste Special

• Add

• Multiply

• Transpose- Change data from vertical to horizontal and vice versa

• Validation

• Values – When you want to paste the results of a formula

  • • An Amazing Shortcut courtesy of Bill Jelen, Mr. Excel

Data

Consolidation

• Consolidate data by category
• Consolidate data by position
• Consolidate data as a quick alternative to Subtotals

Entry

• Data Forms

Filters

• Advanced Filter
• AutoFilter
• Custom Filter
• Filter and copy Unique Records to another location
• Lists – A new feature added in Excel 2003

Lists

• Custom Lists – To Use with AutoFill and Custom Filter

Pivot Tables

• Introduction to Pivot Tables – Practice file to download: introduction-to-pivot-tables
• Introduction to Pivot Tables – Part 2 – Practice file to download: hide-show-details-in-pivot-table
• Create a Calculated Field in a Pivot Table
• Be Careful with Calculated Items in a Pivot Table
• Group Dates to Produce a Summary by Year, Quarter & Month
• Group Months into Quarters in a Pivot Table
• Change a Quarterly Summary to a Fiscal Year in Pivot Tables

Sorting

• Sort data using a Custom List
• Rearrange Your Data – Sort Across Columns – Practice file to download: rearrange-columns

Subtotals

• Create Subtotals – To summarize your data.
• Format the Subtotal Lines – To make your subtotals stand out!
• Nest Subtotals – Nest the 2nd subtotal inside your 1st subtotal
• Use Consolidate as a Quick Alternative to Subtotals

Validation

• Use Data Validation to minimize data entry errors
• Use =INDIRECT() to Join Two Pick-Lists with Data Validation
• Use =COUNTIF() to ensure that only “unique values” are accepted
• Save Time and Maintain Consistency When Entering Data in Excel

What-if” Analysis

• Create a 2-Input Data Table – See multiple results by changing 2 variables – Practice file to download: cr-tutorial-20b-2-input-table
• Goal Seek – “Back into” the result you want by changing one variable at a time
• Click & Drag a Data Series to Goal Seek in a Chart
• Scenarios
• Solver

Formatting

Formulas

• Audit your formulas – to better understand how the formula is built

Cell References

• Absolute Cell References
• Link to cells in other worksheets or other workbooks
• Mixed Cell References
• Relative Cell References

Functions

Arithmetic Functions

• • COUNTIF() – To see how many name tags have been claimed
  • Running Total
  • Subtotals
  • SUMIFf()  – a great way to SUM only the values that meet a single criterion
    • Practice file to Download: sumif-function
  • Use SUMIF() to total Year-to-Date Sales

Common (“Most Popular”) Functions

• AutoSum
  • Additional AutoSum Functions
  • QuickSum Calculator on Status Bar

• =TODAY()
  • Use =TODAY() Function to find past due invoices

Conditional Functions

• COUNTIF()

Date & Time Functions

• DATE()
• DAY() MONTH() and YEAR()
• NOW() – Returns the Current Date & Time
• Time() – Returns the Current Time
• TODAY() – Returns the Current Date
• Viewer Request: How does Excel calculate Future Dates?
• WEEKDAY()

Financial Functions

• FV() – Calculate the Future Value of Your Investment – Practice file to download: future-values
• • Click & Drag to Goal Seek a New Future Value in a Chart
• PMT() – Calculate Your Monthly Payment for a Loan

Logical Functions

• IF()
• AND()

Lookup Functions

• Hlookup()
• Indirect()
• Simplify Data Lookups with Intersection Operator – Practice file to download: simplify-data-lookups
• Vlookup()
  • Vlookup() in a Payroll Statement form

Math & Trig

• Randbetween() – Create Random Numbers within a Range of Values

Text Functions

• • Ampersand – To Join Text Strings – or cell contents – Together

  • Concatenate()
  • Istext()
  • Left()
  • Lower()
  • Mid()
  • Proper()
  • Right()
  • Text to Columns
  • Text to Numbers
  • Trim()
  • Upper()

Named Cells, Ranges & Constants

• Named Constant
  

• Use a Named Constant to Save Time & Maintain Consistency in Excel Data
  

• Use Names in Formula – Make your formulas more intuitive – Practice file to download: use-named-cells-in-formulas

Sharing Excel Data with Other Applications

• Paste, Link & Embed Data in PowerPoint

Publish Worksheets as Webpages

• How to Enable Interactivity for a Workbook
• How to Publish an Excel Workbook as a Webpage