Excel 2007 FREE one-day tutorial video course

Excel 2007 Excel 2007 Excel 2010

Free Course Session One: Basic Skills

Lesson
Book HD Video Sample
File
Low Res
Video
Contents Book      
How to Use This Course Book Video Sample File  
Introduction and Objectives Book Video Sample File  
Lesson 1 1: Start Excel and check your program version Book Video Sample File Low Resolution Video
Lesson 1 2: Maximize, minimize, re-size, move and close the Excel window Book Video Sample File Low Resolution Video
Lesson 1 3: Understand the Application and Workbook windows Book Video Sample File Low Resolution Video
Lesson 1 4: Open and navigate a workbook Book Video Sample File Low Resolution Video
Lesson 1 5: Save a workbook Book Video Sample File Low Resolution Video
Lesson 1 6: Pin a document, and understand file organization Book Video Sample File Low Resolution Video
Lesson 1-7 View, add, remove and navigate worksheet tabs Book Video Sample File Low Resolution Video
Lesson 1 8: Use the Ribbon Book Video Sample File Low Resolution Video
Lesson 1 9: Understand Ribbon components Book Video Sample File Low Resolution Video
Lesson 1 10: Customize the Quick Access Toolbar and preview the printout Book Video Sample File Low Resolution Video
Lesson 1 11: Use the Mini Toolbar, Key Tips and keyboard shortcuts Book Video Sample File Low Resolution Video
Lesson 1 12: Understand Views Book Video Sample File Low Resolution Video
Lesson 1 13: Use full screen view Book Video Sample File Low Resolution Video
Lesson 1 14: Use the help system Book Video Sample File Low Resolution Video
Session 1: Exercise Book Sample File
Session 1: Exercise answers Book      

Free Course Session Two:
Doing Useful Work with Excel

Lesson
Book HD Video Sample File Low Res
Video
Introduction and Objectives Book Video  
Lesson 2 1: Enter text and numbers into a worksheet Book Video Sample File Low Resolution Video
Lesson 2 2: Create a new workbook and view two workbooks at the same time Book Video Sample File Low Resolution Video
Lesson 2 3: Use AutoSum to quickly calculate totals Book Video Sample File Low Resolution Video
Lesson 2 4: Select a range of cells and understand Smart Tags Book Video Sample File Low Resolution Video
Lesson 2 5: Enter data into a range and copy data across a range Book Video Sample File Low Resolution Video
Lesson 2 6: Select adjacent and non-adjacent rows and columns Book Video Sample File Low Resolution Video
Lesson 2 7: Select non-contiguous cell ranges and view summary information Book Video Sample File Low Resolution Video
Lesson 2 8: AutoSelect a range of cells Book Video Sample File Low Resolution Video
Lesson 2‑9: Re-size rows and columns Book Video Sample File Low Resolution Video
Lesson 2‑10: Use AutoSum to sum a non-contiguous range Book Video Sample File Low Resolution Video
Lesson 2‑11: Use AutoSum to quickly calculate averages Book Video Sample File Low Resolution Video
Lesson 2‑12: Create your own formulas Book Video Sample File Low Resolution Video
Lesson 2 13: Create functions using Formula AutoComplete Book Video Sample File Low Resolution Video
Lesson 2 14: Use AutoFill for text and numeric series Book Video Sample File Low Resolution Video
Lesson 2 15: Use AutoFill to adjust formulas Book Video Sample File Low Resolution Video
Lesson 2 16: Use AutoFill Options Book Video Sample File Low Resolution Video
Lesson 2 17: Speed up your Auto Fills and create a custom fill series Book Video Sample File Low Resolution Video
Lesson 2 18: Use the zoom control Book Video Sample File Low Resolution Video
Lesson 2 19: Print out a worksheet Book Video Sample File Low Resolution Video
Session 2: Exercise Book Sample File
Session 2 Exercise Answers Book      
Session 2: Endpiece Book  
You've now completed the starter course and have a good grasp of the basic use of Excel. 
You're now ready to move on to:

Essential Skills Session Three:
Taking Your Skills to the Next Level

Lesson
Book HD
Video
Sample File Low Res Video
Introduction Book Video Sample File  
Lesson 3 1: Insert and delete rows and columns Download
Lesson 3 2: Use AutoComplete and fill data from adjacent cells
Lesson 3 3: Cut, copy and paste
Lesson 3 4: Cut, copy and paste using drag and drop
Lesson 3 5: Use Paste Values and increase/decrease decimal places displayed
Lesson 3 6: Transpose a range
Lesson 3 7: Use the Multiple Item Clipboard
Lesson 3 8: Use Undo and Redo
Lesson 3 9: Insert cell comments
Lesson 3 10: View cell comments
Lesson 3 11: Print cell comments
Lesson 3 12: Understand absolute and relative cell references Book Video Sample File Low Resolution Video
Lesson 3 13: Understand mixed cell references Download
Lesson 3 14: Create a template
Lesson 3 15: Use a template
Lesson 3 16: Freeze columns and rows
Lesson 3 17: Split the window into multiple panes
Lesson 3 18: Check spelling
Session 3: Exercise Book   Sample File  
Session 3 Exercise Answers        

Essential Skills Session Four:
Making Your Worksheets Look Professional

Lesson
Book HD Video Sample File Low Res Video
Introduction Book Video Sample File  
Lesson 4 1: Format dates Download
Lesson 4 2: Understand date serial numbers
Lesson 4 3: Format numbers using built-in number formats
Lesson 4 4: Create custom number formats
Lesson 4 5: Horizontally Align the contents of cells
Lesson 4 6: Merge cells, wrap text and expand/collapse the formula bar
Lesson 4 7: Vertically align the contents of cells
Lesson 4 8: Understand themes

Font sets

Color sets

Lesson 4 9: Use cell styles and change themes Effects
Lesson 4 10: Add color and gradient effects to cells
Lesson 4 11: Add borders and lines
Lesson 4 12: Create your own custom theme
Lesson 4 13: Create your own custom cell styles
Lesson 4 14: Use a master style book to merge styles
Lesson 4 15: Use simple Conditional Formatting
Lesson 4 16: Manage multiple conditional formats using the Rules Manager
Lesson 4 17: Bring data alive with visualizations
Lesson 4 18: Create a formula driven conditional format Book Video Sample File Low Resolution Video
Lesson 4 19: Use the Format Painter Download Complete Course
Lesson 4 20: Rotate text
Session 4: Exercise Book   Sample File  
Session 4 Exercise Answers      

Essential Skills Session Five:
Charts and Graphics

Lesson
Book HD
Video
Sample File Low Res Video
Introduction Book Video Sample File  
Lesson 5 1: Create a simple chart with two clicks download
Lesson 5 2: Move, re-size, copy and delete a chart
Lesson 5 3: Change the chart layout and add a data table
Lesson 5 4: Format chart element fills and borders
Lesson 5 5: Format 3-D elements and align text
Lesson 5 6: Move, re-size and delete chart elements
Lesson 5 7: Change a chart's source data
Lesson 5 8: Assign non-contiguous source data by click and drag
Lesson 5 9: Change source data using the Select Data Source dialog tools
Lesson 5 10: Chart non-contiguous source data by hiding rows and columns
Lesson 5 11: Create a chart with numerical axis
Lesson 5 12: Deal with empty data points
Lesson 5 13: Add data labels to a chart
Lesson 5 14: Highlight specific data points with color and annotations
Lesson 5 15: Add gridlines and scale axes
Lesson 5 16: Emphasize data by manipulating pie charts
Lesson 5 17: Create a chart with two vertical axis
Lesson 5 18: Create a combination chart containing different chart types Book Video Sample File Low Resolution Video
Lesson 5 19: Add a trend line Download Complete Course
Lesson 5 20: Switch chart rows/columns and add a gradient fill
Lesson 5 21: Create your own chart templates
Session 5: Exercise Book   Sample File  
Session 5 Exercise Answers        

Essential Skills Session Six:
Working With Multiple Worksheets and Workbooks

Lesson
Book HD
Video
Sample File Low Res Video
Introduction Book Video Sample File  
Lesson 6 1: View the same workbook in different windows download
Lesson 6 2: View two windows side by side and perform synchronous scrolling
Lesson 6 3: Duplicate worksheets within a workbook
Lesson 6 4: Move and copy worksheets from one workbook to another
Lesson 6 5: Hide and unhide a worksheet
Lesson 6 6: Create cross worksheet formulas
Lesson 6 7 Understand worksheet groups Book Video Sample File Low Resolution Video
Lesson 6 8: Use find and replace Download Complete Course
Session 6: Exercise Book   Sample File  
Session 6 Exercise Answers  

Essential Skills Session Seven:
Printing Your Work

Lesson
Book HD
Video
Sample File Low Res Video
Introduction Book Video    
Lesson 7 1: Print Preview and change paper orientation download
Lesson 7 2: Use page layout view to adjust margins
Lesson 7 3: Use Page Setup to set margins more precisely and center the worksheet
Lesson 7 4: Set paper size and scale
Lesson 7 5: Insert, delete and preview page breaks
Lesson 7 6: Adjust page breaks using Page Break Preview
Lesson 7 7: Add auto-headers and auto-footers and set the starting page number
Lesson 7 8: Add custom headers and footers Book Video Sample File Low Resolution Video
Lesson 7 9: Specify different headers and footers for the first, odd and even pages Download
Lesson 7 10: Print only part of a worksheet
Lesson 7 11: Add row and column data labels and grid lines to printed output
Lesson 7 12: Print several selected worksheets and change the page order
Lesson 7 13: Suppress error messages in printouts
Session 7: Exercise Book   Sample File  
Session 7 Exercise Answers        
Download sample files for the Session One sample lesson

Expert Skills Session One:
Tables, Ranges and Databases

Lesson
Book HD
Video
Sample Files Low Res Video
Contents Book      
How to Use This Course Book Video    
Introduction and Objectives Book Video    
Lesson 1 1: Check your program and operating system version download
Lesson 1 2: Apply a simple filter to a range
Lesson 1 3: Apply a top 10 and custom filter to a range
Lesson 1 4: Apply an advanced filter with multiple OR criteria
Lesson 1 5: Apply an advanced filter with complex criteria
Lesson 1 6: Apply an advanced filter with function-driven criteria
Lesson 1 7: Extract unique records using an advanced filter
Lesson 1 8: Convert a range into a table with a total row
Lesson 1 9: Format a table using table styles and convert a table into a range
Lesson 1 10: Create a custom table style
Lesson 1 11: Sort a range or table by rows
Lesson 1 12: Sort a range by columns
Lesson 1 13: Sort a range or table by custom list Book Video Sample File Low Resolution Video
Lesson 1 14: Name a table and create an automatic structured table reference download
Lesson 1 15: Create a manual structured table reference
Lesson 1 16: Use special items in structured table references
Lesson 1 17: Understand unqualified structured references
Session 1: Exercise Book   Sample File  
Session 1 Exercise Answers        
Download sample files for the Session Two sample lessons
 

Expert Skills Session Two:
Data Integrity, Subtotals and Validations

Lesson
Book HD
Video
Sample File Low Res Video
Introduction and Objectives Book Video    
Lesson 2 1: Keep data atomic using Text to Columns download
Lesson 2 2: Remove duplicate values from a range or table
Lesson 2 3: Automatically subtotal a range
Lesson 2 4: Create nested subtotals
Lesson 2 5: Consolidate data from multiple data ranges
Lesson 2 6: Use data consolidation to generate quick subtotals from tables
Lesson 2 7: Validate numerical data Book Video Sample File Low Resolution Video
Lesson 2 8: Create user-friendly messages for validation errors download
Lesson 2 9: Create data entry Input Messages
Lesson 2 10: Add a formula-driven date validation and a decimal validation
Lesson 2 11: Add a dynamic list validation based upon a table
Lesson 2 12: Use a function-driven custom validation to enforce complex business rules
Lesson 2 13: Use a custom validation to add a unique constraint to a column
Session 2: Exercise Book   Sample File  
Session 2 Exercise Answers        
Download sample files for the Session Three sample lessons
 

Expert Skills Session Three:
Advanced functions and formulas

Lesson
Book High
Res
Video
Sample File Low Res Video
Introduction and Objectives Book Video    
Lesson 3 1: Understand precedence rules and use the Evaluate feature download
Lesson 3 2: Use common functions with Formula AutoComplete
Lesson 3 3: Use the formula palette and the PMT function
Lesson 3 4: Use the PV and FV functions to value investments
Lesson 3 5: Use the IF logic function Book Video Sample File Low Resolution Video
Lesson 3 6: Use the SUMIF and COUNTIF logic functions to create conditional totals download
Lesson 3 7: Understand date serial numbers
Lesson 3 8: Understand common date functions
Lesson 3 9: Use the DATEDIF function
Lesson 3 10: Use date offsets to manage projects using the scheduling equation
Lesson 3 11: Use the DATE function to offset days, months and years
Lesson 3 12: Enter time values and perform basic time calculations 
Lesson 3 13: Perform time calculations that span midnight
Lesson 3 14: Understand common time functions and convert date serial numbers to decimal values
Lesson 3 15 Use the TIME function to offset hours, minutes and seconds
Lesson 3 16 Use the AND and OR functions to construct complex Boolean criteria
Lesson 3 17 Understand calculation options (manual and automatic)
Lesson 3 18: Concatenate strings using the concatenation operator (&)
Lesson 3 19: Use the TEXT function to format numerical values as strings
Lesson 3 20: Extract text from fixed width strings using the LEFT, RIGHT and MID functions
Lesson 3 21: Extract text from delimited strings using the FIND and LEN functions
Lesson 3 22: Use a VLOOKUP function for an exact lookup Book Video Sample File Low Resolution Video
Lesson 3 23: Use an IFERROR function to suppress error messages  
Lesson 3 24: Use a VLOOKUP function for an inexact lookup
Session 3: Exercise Book   Sample File  
Session 3 Exercise Answers  
Download sample files for the Session Four sample lessons
 

Expert Skills Session Four:
Using Names and the Formula Auditing Tools

Lesson
Book HD
Video
Sample File Low Res Video
Introduction and Objectives Book Video    
Lesson 4‑1: Automatically create single-cell range names  download
Lesson 4‑2: Manually create, single cell range names and named constants
Lesson 4‑3: Use range names to make formulas more readable
Lesson 4‑4: Automatically create range names in two dimensions
Lesson 4‑5: Use intersection range names and the INDIRECT function
Lesson 4‑6: Create dynamic formula-based range names using the OFFSET function
Lesson 4‑7: Create table-based dynamic range names
Lesson 4‑8: Create two linked drop-down lists using range names
Lesson 4‑9: Understand the #NUM!, #DIV/0! and #NAME? Error Values
Lesson 4‑10: Understand the #VALUE!, #REF! and #NULL! Error Values
Lesson 4‑11: Understand background error checking and error checking rules
Lesson 4‑12: Manually error check a worksheet
Lesson 4‑13: Audit a formula by tracing precedents
Lesson 4‑14: Audit a formula by tracing dependents
Lesson 4‑15: Use the watch window to monitor cell values
Lesson 4‑16: Use Speak Cells to eliminate data entry errors Book Video Sample File Low Resolution Video
Session 4: Exercise Book   Sample File  
Session 4 Exercise Answers  
Download sample files for the Session Five sample lessons
 

Expert Skills Session Five:
Pivot Tables

Lesson
Book HD
Video
Sample File Low Res Video
Introduction and Objectives Book Video    
Lesson 5‑1: Create a one dimensional pivot table report from a table Book Video Sample File Low Resolution Video
Lesson 5‑2: Create a grouped pivot table report download
Lesson 5‑3: Understand pivot table rows and columns
Lesson 5‑4: Use an external data source
Lesson 5‑5: Apply a simple filter and sort to a pivot table
Lesson 5‑6: Use report filter fields
Lesson 5‑7: Use report filter fields to automatically create multiple pages
Lesson 5‑8: Format a pivot table using PivotTable styles
Lesson 5‑9: Create a custom PivotTable style
Lesson 5‑10: Understand pivot table report layouts
Lesson 5‑11: Add/remove subtotals and apply formatting to pivot table fields
Lesson 5‑12: Display multiple summations within a single pivot table
Lesson 5‑13: Add a calculated field to a pivot table
Lesson 5‑14: Add a calculated item to a pivot table
Lesson 5‑15: Group by Text
Lesson 5‑16: Group by Date
Lesson 5‑17: Group by numeric value ranges
Lesson 5‑18: Show row data by percentage of total rather than value
Lesson 5‑19: Create a pivot chart from a pivot table
Lesson 5-20:  Embed multiple pivot tables onto a worksheet
Session 5: Exercise Book   Sample File  
Session 5 Exercise Answers        
Download sample files for the Session Three sample lessons
 

Expert Skills Session Six:
What If Analysis and Security

Lesson
Book HD
Video
Sample File Low Res Video
Introduction and Objectives Book Video    
Lesson 6 1: Create a single-input data table Book Video Sample File Low Resolution Video
Lesson 6 2: Create a two-input data table download
Lesson 6 3: Define Scenarios
Lesson 6 4: Create a scenario summary report
Lesson 6 5: Use Goal Seek
Lesson 6 6: Use Solver
Lesson 6 7: Hide and unhide worksheets, columns and rows
Lesson 6 8: Create custom views
Lesson 6 9: Prevent unauthorized users from opening or modifying workbook
Lesson 6 10: Control the changes users can make to workbooks
Lesson 6 11: Restrict the cells users are allowed to change
Lesson 6 12: Allow different levels of access to a worksheet with multiple passwords
Lesson 6 13: Create a digital certificate
Lesson 6 14: Add an invisible digital signature to a workbook
Lesson 6 15: Add an visible digital signature to a workbook
Session 6: Exercise Book   Sample File  
Session 6 Exercise Answers  
(No sample files are needed for the Session Seven sample lesson)

Expert Skills Session Seven:
Working with the Internet,
Other Applications and Workgroups

Lesson
Book HD
Video
Sample File Low Res Video
Introduction and Objectives Book Video    
Lesson 7‑1: Publish a worksheet as a single web page  download
Lesson 7‑2: Publish multiple web pages as a web site
Lesson 7‑3: Hyperlink to worksheets and ranges 
Lesson 7‑4: Hyperlink to other workbooks and the Internet
Lesson 7‑5: Hyperlink to an e-mail address and enhance the browsing experience
Lesson 7‑6: Execute a web query
Lesson 7‑7: Embed an Excel worksheet object into a Word document 
Lesson 7‑8: Embed an Excel chart object into a Word document 
Lesson 7‑9: Link an Excel worksheet to a Word document 
Lesson 7‑10: Understand the three different ways to share a document  Book Video Sample File Low Resolution Video
Lesson 7‑11: Share a workbook using the lock method download
Lesson 7‑12: Share a workbook using the merge method
Lesson 7‑13: Share a workbook on a network
Lesson 7‑14: Accept and reject changes to shared workbooks
Session 7: Exercise Book   Sample File  
Session 7 Exercise Answers  
Download sample files for the Session Eight sample lessons
 

Expert Skills Session Eight:
Forms and Macros

Lesson
Book HD
Video
Sample File Low Res Video
Introduction and Objectives Book Video    
Lesson 8‑1: Add group box and option button controls to a worksheet form doqnload
Lesson 8‑2: Add a combo box control to a worksheet form
Lesson 8‑3: Set form control cell links
Lesson 8‑4: Connect result cells to a form
Lesson 8‑5: Add a check box control to a worksheet form
Lesson 8‑6: Use check box data in result cells
Lesson 8‑7: Add a temperature gauge chart to a form
Lesson 8‑8: Add a single input data table to a form
Lesson 8‑9: Improve form usability  Book Video Sample File
Lesson 8‑10: Understand macros and VBA        
Lesson 8‑11: Record a macro with absolute references Book Video Sample File Low Resolution Video
Lesson 8‑12: Understand macro security download
Lesson 8‑13: Implement macro security
Lesson 8‑14: Record a macro with relative references
Lesson 8‑15: Use shapes to run macros
Lesson 8‑16: Run a macro from a button control
Session 8: Exercise Book   Sample File  
Session 8 Exercise Answers