Unit 01 - Getting Started
Topic A: Spreadsheet Terminology
Spreadsheet Components
Topic B: The Excel Environment
Excel Window Components
Enhanced ScreenTips
Demo - B-1: Examining Excel Window Components
Topic C: Getting Help
The Excel Help Window
Demo - C-1: Getting Help with Using Excel
Topic D: Navigating a Worksheet
Worksheet Navigation Methods
Demo - D-1: Navigating a Worksheet
Unit 01 Review
Unit 02 - Entering and Editing Data
Topic A: Entering and Editing Text and Values
Spreadsheet with Text and Values
Demo - A-1: Entering Text and Values
Editing Text and Values
Demo - A-2: Editing Cell Contents
Using AutoFill
Using AutoFill to Fill a Month Series
Demo - A-3: Using AutoFill to Fill a Series
Topic B: Entering and Editing Formulas
Formulas
Operators
Demo - B-1: Creating a Basic Formula
Entering Formulas
Entering Cell References with Mouse
Demo - B-2: Entering Cell References with the Mouse
Demo - B-3: Editing a Formula
Topic C: Working with Pictures
Add an Image to a Worksheet
Worksheet with a Picture
Demo - C-1: Inserting and Modifying a Picture
Topic D: Saving and Updating Workbooks
Saving Workbooks
Demo - D-1: Saving a New Workbook
Saving a Worksheet as a PDF
Demo - D-2: Saving a Worksheet as a PDF File
Demo - D-3: Editing and Updating a Workbook
Unit 02 Review
Unit 03 - Modifying a Worksheet
Topic A: Moving and Copying Data
Moving Data in Worksheets
Demo - A-1: Moving Data in a Worksheet
Copying Data
Demo - A-2: Copying Data in a Worksheet
Moving Data by Dragging It
Copying Data by Dragging It
Demo - A-3: Moving and Copying Data by Using Drag-and-Drop
The Office Clipboard
Demo - A-4: Using the Clipboard Task Pane
Topic B: Moving and Copying Formulas
Relative References
Demo - B-1: Moving a Formula
Demo - B-2: Copying a Formula
Demo - B-3: Using AutoFill to Copy a Formula
Topic C: Absolute and Relative References
Demo - C-1: Observing the Limitations of Relative References
Absolute References
Mixed References
Demo - C-2: Applying Absolute References
Topic D: Inserting and Deleting Ranges, Rows, and Columns
Inserting a Range
Demo - D-1: Inserting a Range of Cells
Inserting Rows or Columns
Demo - D-2: Inserting Rows
Deleting a Range
Demo - D-3: Deleting a Range of Cells
Unit 03 Review
Unit 04 - Functions
Topic A: Entering Functions
Function
Arguments
Range Reference
The Trace Error Button
Syntax Errors
Demo - A-1: Entering a SUM Function
Demo - A-2: Using the Mouse to Enter a Function Argument
Inserting Functions
Demo - A-3: Inserting a Function
Topic B: AutoSum
AutoSum Button
Demo - B-1: Using AutoSum
Topic C: Other Common Functions
AVERAGE Function
Demo - C-1: Using AVERAGE
MIN Function
Demo - C-2: Using MIN
MAX Function
Demo - C-3: Using MAX
COUNT Function
Demo - C-4: Using COUNT and COUNTA
Unit 04 Review
Unit 05 - Formatting
Topic A: Text Formatting
Demo - A-1: Formatting Text
Selecting a Non-Contiguous Range
Demo - A-2: Formatting a Non-Contiguous Range
Formatting Cells
Demo - A-3: Using the Format Cells Dialog Box to Format Text
Topic B: Row and Column Formatting
Changing Column Widths
Demo - B-1: Changing Column Width and Row Height
Demo - B-2: Applying Color to a Row
The Merge & Center Button
Demo - B-3: Setting Alignment
The Borders Menu
Demo - B-4: Applying Borders to Cell Ranges
Using the Border-Drawing Pencil
Demo - B-5: Using the Border-Drawing Pencil
Topic C: Number Formatting
Number Formatting
Demo - C-1: Using the Number Group to Format Numbers
The Number Tab
Demo - C-2: Exploring the Number Tab
Topic D: Conditional Formatting
Conditional Formatting Menu
New Formatting Rule Dialog Box
Demo - D-1: Creating a Conditional Format
Demo - D-2: Editing and Deleting a Conditional Format
Topic E: Additional Formatting Options
Copying and Clearing Formats
Demo - E-1: Copying Formats
Demo - E-2: Using AutoFill to Copy a Format
Applying a Cell Style
Applying a Table Format
Demo - E-3: Applying Cell and Table Styles
Demo - E-4: Using Find and Replace to Change Formatting
Unit 05 Review
Unit 06 - Printing
Topic A: Preparing to Print
Using the Spelling Checker
Demo - A-1: Checking Spelling in a Worksheet
Using Find and Replace
Demo - A-2: Finding and Replacing Text
Page Layout View
Demo - A-3: Previewing a Worksheet
Topic B: Page Setup Options
Changing Scaling Settings
Demo - B-1: Setting Page Orientation and Scaling
Setting Custom Margins
Demo - B-2: Adjusting Margins
Adding Headers and Footers
Demo - B-3: Creating Headers and Footers
Topic C: Printing Worksheets
When You?re Ready to Print…
Printing a Selection
Demo - C-1: Printing a Selected Range
Unit 06 Review
Unit 07 - Charts
Topic A: Chart Basics
Creating a Chart
Demo - A-1: Creating a Chart
Demo - A-2: Moving a Chart Within a Workbook
Chart Elements
Demo - A-3: Examining Chart Elements
Demo - A-4: Creating and Editing a Pie Chart
Topic B: Formatting Charts
Changing the Chart Type
Demo - B-1: Applying Chart Types and Chart Styles
Adding Axis Labels
Demo - B-2: Modifying Chart Elements
Unit 07 Review
Unit 08 - Managing Large Workbooks
Topic A: Viewing Large Worksheets
Freezing Rows and/or Columns
Demo - A-1: Locking Rows and Columns
Splitting a Worksheet into Panes
Demo - A-2: Splitting a Worksheet into Panes
Hiding a Column
Unhiding Columns
Hidden Columns
Demo - A-3: Hiding and Unhiding Columns and Worksheets
Demo - A-4: Minimizing the Ribbon
Topic B: Printing Large Worksheets
Set Print Titles for a Worksheet
Demo - B-1: Setting Print Titles
Page Break Preview
Demo - B-2: Adjusting Page Breaks
Topic C: Working with Multiple Worksheets
Demo - C-1: Navigating Between Worksheets
Renaming a Worksheet
Formatting Worksheet Tabs
Demo - C-2: Naming Worksheets and Coloring Tabs
Inserting a Worksheet
Moving a Worksheet
Deleting a Worksheet
Demo - C-3: Working with Multiple Worksheets
Printing Multiple Worksheets
Demo - C-4: Previewing and Printing Multiple Worksheets
Unit 08 Review
Unit 09 - Using Multiple Worksheets and Workbooks
Topic A: Using Multiple Workbooks
Switch Between Workbooks
Demo - A-1: Switching Between Workbooks
The Move or Copy Dialog Box
Demo - A-2: Copying a Worksheet to Another Workbook
Topic B: Linking Worksheets with 3-D Formulas
Inserting a 3-D Reference
Demo - B-1: Creating 3-D Formulas
Adding a Watch Window
Demo - B-2: Adding a Watch Window
Topic C: Linking Workbooks
Demo - C-1: Examining External Links in a Worksheet
Syntax for External Links
Creating External Links
Demo - C-2: Creating External Link Worksheet
Redirecting Links
Demo - C-3: Editing Links
Topic D: Managing Workbooks
Creating a Workspace
Demo - D-1: Creating a Workspace
Unit 09 Review
Unit 10 - Advanced Formatting
Topic A: Using Special Number Formats
The Format Cells, Number Tab
Demo - A-1: Applying Special Formats
Hiding Zero Values
Demo - A-2: Controlling the Display of Zero Values
Customizing Number Formats
Custom Number Formats
Demo - A-3: Creating Custom Formats
Topic B: Using Functions to Format Text
Text Functions
Demo - B-1: Using PROPER, UPPER, and LOWER
The SUBSTITUTE Function
Demo - B-2: Using SUBSTITUTE
Topic C: Working with Styles
The Cell Styles Gallery
Creating Styles
Demo - C-1: Creating and Applying Styles
Modifying Styles
Demo - C-2: Modifying Styles
Topic D: Working with Themes
Theme Colors
Demo - D-1: Changing to a Different Theme
Creating New Theme Colors
Topic E: Other Advanced Formatting
Changing Orientation of Text
Demo - E-1: Merging Cells
Demo - E-2: Changing the Orientation of Text in a Cell
Demo - E-3: Splitting Cells
Transposing Data
Demo - E-4: Transposing Data During a Paste
Adding Backgrounds
Demo - E-5: Adding and Deleting Backgrounds
Adding a Watermark
Demo - E-6: Adding a Watermark
Unit 10 Review
Unit 11 - Outlining and Subtotals
Topic A: Outlining and Consolidating Data
The Expanded Outline Form
The Collapsed Outline Form
Demo - A-1: Creating an Outline
The Consolidate Dialog Box
Demo - A-2: Using the Consolidate Command
Topic B: Creating Subtotals
The Subtotal Dialog Box
Demo - B-1: Creating Subtotals in a List
Demo - B-2: Using Multiple Subtotal Functions
Unit 11 Review
Unit 12 - Cell and Range Names
Topic A: Creating and Using Names
Defining Names
Demo - A-1: Naming and Selecting Ranges
Using Names in Formulas
Demo - A-2: Using Names in Formulas
Using Create from Selection
Demo - A-3: Using the Create from Selection Command
Applying Names
Demo - A-4: Applying Names to Existing Formulas
Topic B: Managing Names
The Name Manager Dialog Box
Demo - B-1: Modifying and Deleting Named Ranges
Creating a 3-D Name
Demo - B-2: Defining and Applying 3-D Names
Unit 12 Review
Unit 13 - Tables
Topic A: Sorting and Filtering Data
Structure of Organized Data
Sorting Data Based on a Cell
Sorting by Multiple Columns
Demo - A-2: Sorting Data
Filtering Data
Demo - A-3: Filtering Data by Using AutoFilter
Topic B: Advanced Filtering
Custom AutoFilter Dialog Box
Demo - B-1: Using Custom AutoFilter Criteria
Creating a Criteria Range
Demo - B-2: Using the Advanced Filter Dialog Box
Copying the Filtered Data
Topic C: Working with Tables
Creating a Table
Demo - C-1: Creating a Table
Demo - C-2: Formatting a Table
Demo - C-3: Adding and Deleting Rows and Columns
Structured References
Demo - C-4: Using Structured References
The [@] Argument
Unit 13 Review
Unit 14 - Web and Sharing Features
Topic A: Saving Workbooks as Web Pages
Customizing the Quick Access Toolbar
Saving a Workbook as a Web Page
Demo - A-2: Saving a Workbook as a Web Page
Publishing a Web Page
The Publish as Web Page Dialog Box
Topic B: Using Hyperlinks
Inserting a Hyperlink
Demo - B-1: Inserting and Editing Hyperlinks
Topic C: Sharing Workbooks
File Save & Send Page Options
Demo - C-1: Examining Workbook Sharing Options
Sharing Workbooks by Email
Unit 14 Review
Unit 15 - Advanced Charting
Topic A: Chart Formatting Options
Format Axis: Axis Options
Demo - A-1: Adjusting the Scale of a Chart
Labeling a Data Point
Demo - A-2: Formatting a Data Point
Topic B: Combination Charts
Combination Chart: Changing Chart Type
Combination Chart: Adding a Secondary Axis
Demo - B-1: Creating a Combination Chart
Adding a Trendline
Demo - B-2: Creating a Trendline
Inserting Sparklines
Sparklines in a Worksheet
Demo - B-3: Inserting Sparklines
Topic C: Graphical Elements
Adding Shapes to Charts
Demo - C-1: Adding Graphical Elements
Formatting Graphical Elements
Demo - C-2: Formatting a Graphical Element
Inserting a Picture from a File
Unit 15 Review
Unit 16 - Documenting and Auditing
Topic A: Auditing Features
Dependent and Precedent Cells
Demo - A-1: Tracing Precedent and Dependent Cells
Tracing Errors in a Worksheet
Demo - A-2: Tracing Errors
Topic B: Comments in Cells and Workbooks
Viewing Comments
Demo - B-1: Viewing Comments in a Worksheet
Adding Cell Comments
Demo - B-2: Adding a Comment to a Cell
The Document Panel
Topic C: Protection
Protecting a Worksheet
Demo - C-1: Password-Protecting a Worksheet
Protecting Parts of a Worksheet
Demo - C-2: Unlocking Cells and Protecting Part of a Worksheet
Using Digital Signatures
Topic D: Workgroup Collaboration
Sharing a Workbook
Demo - D-1: Sharing a Workbook
Share Workbook: Advanced Tab
Tracking Changes
Accepting and Rejecting Changes
Demo - D-3: Tracking Changes in a Workbook
Using the Document Inspector
Marking a Workbook as Final
Demo - D-5: Marking a Workbook as Final
Unit 16 Review
Unit 17 - Templates and Settings
Topic A: Application Settings
The Excel Options Dialog Box
The Customize Ribbon Page
Topic B: Built-in Templates
Available Templates
Using the Sales Invoice Template
Demo - B-1: Using a Downloaded Template
Topic C: Creating and Managing Templates
Creating a Template
Demo - C-1: Creating a Template
Modifying a Template
Demo - C-2: Modifying a Template
Using an Alternate Template Location
Unit 17 Review
Unit 18 - Advanced Functions
Topic A: Logical Functions
The IF Function
Demo - A-1: Using the IF Function
AND, OR, and NOT Functions
Demo - A-2: Using OR, AND, and NOT as Nested Functions
Nested IF Functions
Demo - A-3: Using Nested IF Functions
The IFERROR Function
Demo - A-4: Using the IFERROR Function
Topic B: Math and Statistical Functions
The SUMIF Function
Demo - B-1: Using SUMIF
The COUNTIF Function
Demo - B-2: Using COUNTIF
The AVERAGEIF Function
Demo - B-3: Using AVERAGEIF
SUMIFS, COUNTIFS, AVERAGEIFS
Demo - B-4: Using SUMIFS, COUNTIFS, and AVERAGEIFS
The ROUND Function
Evaluate Formula Dialog Box
Demo - B-5: Using ROUND
Topic C: Financial Functions
The PMT Function
Demo - C-1: Using the PMT Function
Topic D: Displaying and Printing Formulas
Displaying Formulas in Cells
Hiding Formulas from Users
Showing Hidden Formulas
Demo - D-1: Showing, Printing, and Hiding Formulas
Unit 18 Review
Unit 19 - Lookups and Data Tables
Topic A: Using Lookup Functions
The HLOOKUP Function
The VLOOKUP Function
Demo - A-1: Examining VLOOKUP
VLOOKUP for Exact Matches
Demo - A-2: Using VLOOKUP to Find an Exact Match
VLOOKUP for Approximate Matches
Demo - A-3: Using VLOOKUP to Find an Approximate Match
HLOOKUP for Exact Matches
Demo - A-4: Using HLOOKUP to Find Exact Matches
HLOOKUP for Approximate Matches
Demo - A-5: Using HLOOKUP to Find Approximate Matches
Topic B: Using MATCH and INDEX
The MATCH Function
Demo - B-1: Using the MATCH Function
The INDEX Function
Demo - B-2: Using the INDEX Function
Topic C: Creating Data Tables
One-Variable Data Tables
Demo - C-1: Creating a One-variable Data Table
Two-Variable Data Tables
Demo - C-2: Creating a Two-variable Data Table
Unit 19 Review
Unit 20 - Advanced Data Management
Topic A: Validating Cell Entries
Topic A: Validating Cell Entries
Data Validation
Demo - A-1: Observing Data Validation
Setting Data Validation Rules
Demo - A-2: Setting Up Data Validation
Using Date Criteria
Demo - A-3: Setting Date and List Validations
Topic B: Exploring Database Functions
Structure of Database Functions
Demo - B-1: Examining the Structure of Database Functions
DSUM and DAVERAGE
Demo - B-2: Using the DSUM Function
Unit 20 Review
Unit 21 - PivotTables and PivotCharts
Topic A: Working with PivotTables
A Sample PivotTable
Creating a PivotTable
Demo - A-1: Creating a PivotTable
Adding Fields
Demo - A-2: Adding Fields to a PivotTable
Inserting a Slicer
A PivotTable with Slicers
Demo - A-3: Using Slicers to Filter PivotTable Data
Topic B: Rearranging PivotTables
Moving Fields
Demo - B-1: Moving Fields
Hiding Details
Expanding a Collapsed Range
Demo - B-2: Collapsing and Expanding Fields
Refreshing Data
Demo - B-3: Refreshing the Data in a PivotTable
Topic C: Formatting PivotTables
Using Styles
Demo - C-1: Applying a PivotTable Style
Changing Field Settings
Value Field Settings Dialog Box
Demo - C-2: Changing Field Settings
Topic D: PivotCharts
Creating PivotCharts
A Sample PivotChart
Demo - D-1: Creating a PivotChart
Unit 21 Review
Unit 22 - Exporting and Importing
Topic A: Exporting and Importing Text Files
Using the Save As Command
Demo - A-1: Exporting Excel Data to a Text File
Importing Data
Demo - A-2: Importing Data from a Text File into a Workbook
The Text Import Wizard
Converting Text to Columns
Demo - A-3: Converting Text to Columns
Removing Duplicates
Demo - A-4: Removing Duplicate Records
Topic B: Exporting and Importing XML Data
The XML Maps Dialog Box
The XML Source Pane
Importing XML Data
Exporting Data to an XML File
Deleting XML Maps
Topic C: Querying External Databases
Using Microsoft Query
Demo - C-1: Getting External Data from Microsoft Query
Web Query
Retrieving Data from a Web Page
Demo - C-2: Using a Web Query to Get Data from the Web
Unit 22 Review
Unit 23 - Analytical Tools
Topic A: Goal Seek and Solver
Using the Goal Seek Utility
Demo - A-1: Using Goal Seek to Solve for a Single Variable
Activating Add-Ins
The Add-Ins Dialog Box
The Solver Parameters Dialog Box
Demo - A-3: Using Solver to Solve for Multiple Variables
Topic B: The Analysis ToolPak
Using the Sampling Analysis Tool
Demo - B-1: Using the Sampling Analysis Tool
Topic C: Scenarios
Creating a Scenario
Demo - C-1: Creating Scenarios
Switching Among Scenarios
Merging Scenarios
A Sample Scenario Summary
Topic D: View
Creating Views
Demo - D-1: Creating Views
Switching Among Custom Views
Demo - D-2: Switching Among Views
Unit 23 Review
Unit 24 - Macros and Custom Functions
Topic A: Running and Recording a Macro
Running Macros
Demo - A-1: Running a Macro
Recording Macros
Demo - A-2: Recording a Macro
Assigning Macros to Buttons
Demo - A-3: Assigning a Macro to a Button
Topic B: Working with VBA Code
VBA Code
Observing VBA Code
Demo - B-1: Observing a VBA Code Module
Example of Editing VBA Code
Demo - B-2: Editing VBA Code
Topic C: Creating Functions
Function Procedures
Demo - C-1: Creating a Custom Function
Unit 24 Review
Unit 25 - Conditional Formatting and SmartArt Graphics
Topic A: Conditional Formatting with Graphics
Data Bars
Conditional Formatting Rules Manager
Demo - A-1: Creating Data Bars
Color Scales
Demo - A-2: Using Color Scales
Icon Sets
Demo - A-3: Creating Icon Sets
Topic B: SmartArt Graphics
Creating SmartArt Graphics
Demo - B-1: Inserting a SmartArt Graphic
Quick Style and Bevel Effects
Demo - B-2: Modifying a SmartArt Graphic
Unit 25 Review
Course Closure