Free Course Session One: Basic Skills
|
|
|
Lesson
|
Book |
HD Video |
Sample
File |
Low Res
Video |
|
Contents |
|
|
|
|
|
How to Use This Course |
|
|
 |
|
|
Introduction and Objectives |
|
|
 |
|
|
Lesson 1 1: Start Excel and check your program version |
 |
|
 |
|
|
Lesson 1 2: Maximize, minimize, re-size, move and close the Excel window |
|
|
|
|
|
Lesson 1 3: Understand the Application and Workbook windows |
|
|
 |
|
|
Lesson 1 4: Open and navigate a workbook |
|
|
 |
|
|
Lesson 1 5: Save a workbook |
|
|
 |
|
|
Lesson 1 6: Pin a document, and understand file organization |
|
|
 |
|
|
Lesson 1-7 View, add, remove and navigate worksheet tabs |
|
|
 |
|
|
Lesson 1 8: Use the Ribbon |
|
|
 |
|
|
Lesson 1 9: Understand Ribbon components |
|
|
 |
|
|
Lesson 1 10: Customize the Quick Access Toolbar and preview the printout |
|
|
 |
|
|
Lesson 1 11: Use the Mini Toolbar, Key Tips and keyboard shortcuts |
|
|
 |
|
|
Lesson 1 12: Understand Views |
|
|
 |
|
|
Lesson 1 13: Use full screen view |
|
|
 |
|
|
Lesson 1 14: Use the help system |
|
|
 |
|
|
Session 1: Exercise |
|
|
 |
|
|
Session 1: Exercise answers |
|
|
|
|
Free Course Session Two:
Doing Useful Work with Excel
|
|
|
Lesson
|
Book |
HD Video |
Sample File |
Low Res
Video |
|
Introduction and Objectives |
|
|
|
|
|
Lesson 2 1: Enter text and numbers into a worksheet |
|
|
 |
|
|
Lesson 2 2: Create a new workbook and view two workbooks at the same time |
|
|
 |
|
|
Lesson 2 3: Use AutoSum to quickly calculate totals |
|
|
 |
|
|
Lesson 2 4: Select a range of cells and understand Smart Tags |
|
|
 |
|
|
Lesson 2 5: Enter data into a range and copy data across a range |
|
|
 |
|
|
Lesson 2 6: Select adjacent and non-adjacent rows and columns |
|
|
 |
|
|
Lesson 2 7: Select non-contiguous cell ranges and view summary information |
|
|
 |
|
|
Lesson 2 8: AutoSelect a range of cells |
|
|
 |
|
|
Lesson 2‑9: Re-size rows and columns
|
|
|
 |
|
|
Lesson 2‑10: Use AutoSum to sum a non-contiguous range
|
|
|
 |
|
|
Lesson 2‑11: Use AutoSum to quickly calculate averages
|
|
|
 |
|
|
Lesson 2‑12: Create your own formulas
|
|
|
 |
|
|
Lesson 2 13: Create functions using Formula AutoComplete |
|
|
 |
|
|
Lesson 2 14: Use AutoFill for text and numeric series |
|
|
 |
|
|
Lesson 2 15: Use AutoFill to adjust formulas |
|
|
 |
|
|
Lesson 2 16: Use AutoFill Options |
|
|
 |
|
|
Lesson 2 17: Speed up your Auto Fills and create a custom fill series |
|
|
 |
|
|
Lesson 2 18: Use the zoom control |
|
|
 |
|
|
Lesson 2 19: Print out a worksheet |
|
|
 |
|
|
Session 2: Exercise |
|
|
 |
|
|
Session 2 Exercise Answers |
|
|
|
|
|
Session 2: Endpiece |
|
|
|
|
Essential Skills Session Three:
Taking Your Skills to the Next Level
|
|
|
Lesson
|
Book |
HD
Video |
Sample File |
Low Res Video |
|
Introduction |
|
|
 |
|
|
Lesson 3 1: Insert and delete rows and columns
|
 |
|
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
|
|
|
|
 |
|
Lesson 3 13: Understand mixed cell references
|
 |
|
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 |
|
|
 |
|
|
Session 3 Exercise Answers |
|
|
|
|
Essential Skills Session Five:
Charts and Graphics
|
|
|
Lesson
|
Book |
HD
Video |
Sample File |
Low Res Video |
|
Introduction |
|
|
 |
|
|
Lesson 5 1: Create a simple chart with two clicks |
 |
|
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
|
|
|
 |
 |
|
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 |
|
|
 |
|
|
Session 5 Exercise Answers |
|
|
|
|
Essential Skills Session Seven:
Printing Your Work
|
|
|
Lesson
|
Book |
HD
Video |
Sample File |
Low Res Video |
|
Introduction |
|
|
|
|
|
Lesson 7 1: Print Preview and change paper orientation
|
 |
|
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 |
|
|
 |
 |
|
Lesson 7 9: Specify different headers and footers for the first, odd and even pages |
 |
|
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 |
|
|
 |
|
|
Session 7 Exercise Answers |
|
|
|
|
Expert Skills Session One:
Tables, Ranges and Databases
|
|
|
Lesson
|
Book |
HD
Video |
Sample Files |
Low Res Video |
|
Contents
|
 |
|
|
|
|
How to Use This Course
|
 |
|
|
|
|
Introduction and Objectives
|
 |
|
|
|
|
Lesson 1 1: Check your program and operating system version |
 |
|
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 |
 |
|
 |
 |
|
Lesson 1 14: Name a table and create an automatic structured table reference |
 |
|
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
|
 |
|
 |
|
|
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 |
 |
|
|
|
|
Lesson 2 1: Keep data atomic using Text to Columns |
 |
|
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 |
 |
|
 |
 |
|
Lesson 2 8: Create user-friendly messages for validation errors |
 |
|
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
|
 |
|
 |
|
|
Session 2 Exercise Answers |
|
|
|
|
Expert Skills Session Three:
Advanced functions and formulas
|
|
|
Lesson
|
Book |
High
Res
Video |
Sample File |
Low Res Video |
|
Introduction and Objectives |
 |
|
|
|
|
Lesson 3 1: Understand precedence rules and use the Evaluate feature
|
 |
|
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
|
 |
|
 |
 |
|
Lesson 3 6: Use the SUMIF and COUNTIF logic functions to create conditional totals
|
 |
|
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
|
 |
 |
|
 |
|
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 |
 |
|
 |
|
|
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 |
 |
|
|
|
|
Lesson 4‑1: Automatically create single-cell range names
|
 |
|
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
|
 |
|
 |
 |
|
Session 4: Exercise |
 |
|
 |
|
|
Session 4 Exercise Answers |
|
Expert Skills Session Five:
Pivot Tables
|
|
|
Lesson
|
Book |
HD
Video |
Sample File |
Low Res Video |
|
Introduction and Objectives |
 |
|
|
|
|
Lesson 5‑1: Create a one dimensional pivot table report from a table
|
 |
|
 |
 |
|
Lesson 5‑2: Create a grouped pivot table report
|
 |
|
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 |
 |
|
 |
|
|
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 |
 |
|
|
|
|
Lesson 6 1: Create a single-input data table
|
 |
|
 |
 |
|
Lesson 6 2: Create a two-input data table
|
 |
|
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 |
 |
|
 |
|
|
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 |
 |
|
|
|
|
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
|
|
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
|
 |
|
 |
 |
|
Lesson 7‑11: Share a workbook using the lock method
|
 |
|
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 |
 |
|
 |
|
|
Session 7 Exercise Answers |
|
Expert Skills Session Eight:
Forms and Macros
|
|
|
Lesson
|
Book |
HD
Video |
Sample File |
Low Res Video |
|
Introduction and Objectives |
 |
|
|
|
|
Lesson 8‑1: Add group box and option button controls to a worksheet form
|
 |
|
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
|
 |
|
 |
|
|
Lesson 8‑10: Understand macros and VBA
|
|
|
|
|
|
Lesson 8‑11: Record a macro with absolute references
|
 |
|
 |
 |
|
Lesson 8‑12: Understand macro security
|
 |
|
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 |
 |
|
 |
|
|
Session 8 Exercise Answers |
|