Excel 2010 FREE one-day tutorial video course

Excel 2007 Excel 2010 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      
Introduction and Objectives Book Video    
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: Understand common file formats Book Video Sample File Low Resolution Video
Lesson 1 7: Pin a workbook and understand file organization Book Video Sample File Low Resolution Video
Lesson 1 8: View, move, add, rename, delete and navigate worksheet tabs Book Video Sample File Low Resolution Video
Lesson 1 9: Use the Versions feature to recover an unsaved Draft file Book Video Sample File Low Resolution Video
Lesson 1 10: Use the Versions feature to recover an earlier version of a workbook Book Video Sample File Low Resolution Video
Lesson 1 11: Use the Ribbon Book Video Sample File Low Resolution Video
Lesson 1 12: Understand Ribbon components Book Video Sample File Low Resolution Video
Lesson 1 13: Customize the Quick Access Toolbar and preview the printout Book Video Sample File Low Resolution Video
Lesson 1 14: Use the Mini Toolbar, Key Tips and keyboard shortcuts Book Video Sample File Low Resolution Video
Lesson 1 15: Understand Views Book Video Sample File Low Resolution Video
Lesson 1 16: Use full screen view Book Video Sample File Low Resolution Video
Lesson 1 17: 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      
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    
Lesson 3 1: Insert and delete rows and columns Download Complete Course
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 Complete Course
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    
Lesson 4 1: Format dates Download Complete Course
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 theme
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: Insert a Sparkline into a range of cells Book Video Sample File Low Resolution Video
Lesson 4 20: Apply a common vertical axis and formatting to a Sparkline group Download Complete Course
Lesson 4 21: Apply a date axis to a Sparkline group and format a single Sparkline
Lesson 4 22: Use the Format Painter
Lesson 4 23: 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    
Lesson 5 1: Create a simple chart with two clicks Download Complete Course
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  
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    
Lesson 6 1: View the same workbook in different windows Download Complete Course
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  
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 Complete Course
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 Complete Course
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        


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      
Introduction and Objectives Book Video    
Lesson 1 1: Check your program and operating system version Download Complete Expert Course
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 Book Video Sample File Low Resolution Video
Lesson 1 9: Format a table using table styles and convert a table into a range Download Complete Expert Course
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 Complete Expert Course
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        
 

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: Split fixed width data using Text to Columns Download Complete Expert Course
Lesson 2 2: Split delimited data using Text to Columns
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 Complete Expert Course
Lesson 2 9: Create data entry Input Messages
Lesson 2 10: Add a formula-driven date validation and a text length validation
Lesson 2 11: Add a table-based dynamic list validation
Lesson 2 12: Use a function-driven custom validation to enforce complex business rules
Lesson 2 13: Remove duplicate values from a range or table
Lesson 2 14: Use a custom validation to add a unique constraint to a column
Session 2: Exercise Book   Sample File  
Session 2 Exercise Answers        
 

Expert Skills Session Three:
Advanced functions

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 Complete Expert Course
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 Complete Expert Course
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 Book Video Sample File Low Resolution Video
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  
 

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  Book Video Sample File Low Resolution Video
Lesson 4 2: Manually create, single cell range names and named constants Download Complete Expert Course
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 check a worksheet for errors
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  
 

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 Complete Expert Course
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: Filter a pivot table visually using slicers Book Video Sample File Low Resolution Video
Lesson 5 8: Use slicers to create a date-driven interface Download Complete Expert Course
Lesson 5 9: Use report filter fields to automatically create multiple pages
Lesson 5 10: Format a pivot table using PivotTable styles
Lesson 5 11: Create a custom PivotTable style
Lesson 5 12: Understand pivot table report layouts
Lesson 5 13: Add/remove subtotals and apply formatting to pivot table fields
Lesson 5 14: Display multiple summations within a single pivot table
Lesson 5 15: Add a calculated field to a pivot table
Lesson 5 16: Add a calculated item to a pivot table
Lesson 5 17: Group by Text
Lesson 5 18: Group by Date
Lesson 5 19: Group by numeric value ranges
Lesson 5 20: Show row data by percentage of total rather than value
Lesson 5 21: Create a pivot chart from a pivot table
Lesson 5 22:  Embed multiple pivot tables onto a worksheet
Lesson 5 23: Use slicers to filter multiple pivot tables
Session 5: Exercise Book   Sample File  
Session 5 Exercise Answers        
 

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 Complete Expert Course
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 workbooks
Lesson 6 10: Control the changes users can make to workbooks
Lesson 6 11: Restrict the cells users are allowed to change Book Video Sample File Low Resolution Video
Lesson 6 12: Allow different levels of access to a worksheet with multiple passwords Download Complete Expert Course
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  

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 
Lesson 7 2: Publish multiple web pages as a web site
Lesson 7 3: Hyperlink to worksheets and ranges  Book Video Sample File Low Resolution Video
Lesson 7 4: Hyperlink to other workbooks and the Internet Download Complete Expert Course
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 Complete Expert Course
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 Sample File
Session 7 Exercise Answers  
 

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 Download Complete Expert Course
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 appearance and usability  Book Video Sample File Low Resolution Video
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 Complete Expert Course
Lesson 8 13: Implement macro security
Lesson 8 14: Understand Trusted Documents
Lesson 8 15: Record a macro with relative references
Lesson 8 16: Use shapes to run macros
Lesson 8 17: Run a macro from a button control
Lesson 8 18: Show and hide Ribbon tabs Book Video Sample File Low Resolution Video
Lesson 8 19: Add custom groups to standard Ribbon tabs
Lesson 8 20: Create a custom Ribbon tab
Session 8: Exercise Book   Sample File  
Session 8 Exercise Answers