MS-MO-10-EX
This is a comprehensive course covering all of the functions and features of Excel 2010 for students of any skill level. The course begins with basic concepts and gradually progresses to more advanced topics. After an introduction to spreadsheet terminology and Excel\'s window components, students will learn how to use the Help system and navigate worksheets and workbooks. Then they will enter and edit text, values, formulas, and pictures, and they will save workbooks in various formats. Students will also move and copy data, learn about absolute and relative references, and work with ranges, rows, and columns. This course also covers simple functions, basic formatting techniques, and printing. Students will also create and modify charts, and learn how to manage large workbooks.
Next, students will learn how to use multiple worksheets and workbooks efficiently, and they will start working with more advanced formatting options including styles, themes, and backgrounds. They will also learn how to create outlines and subtotals, how to create and apply cell names, and how to work with tables. Students will save workbooks as Web pages, insert and edit hyperlinks, and learn to share workbooks by email. This course also covers advanced charting techniques, use of trendlines and sparklines, worksheet auditing and protection, file sharing and merging, and workbook templates.
Finally, Students will work with advanced formulas, as well as lookup functions such as VLOOKUP, MATCH, and INDEX. In addition, students will learn about data validation and database functions such as DSUM. They will learn how to work with PivotTables and PivotCharts, how to import and export data, and how to query external databases. Students will also learn about the analytical features of Excel (such as Goal Seek and Solver), running and recording macros, SmartArt graphics, and conditional formatting with graphics.
Course Outline Index
Excel 2010: Basic
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
Order of Operations
Demo - B-2: Entering Cell References with the Mouse
Demo - B-3: Editing a Formula
Demo – B-4: Working with the Order of Operations
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
Demo - D-2: Saving a File as an Excel 97-2003 Workbook
Saving a Worksheet as a PDF
Demo - D-3: Saving a Worksheet as a PDF File
Demo - D-4: 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
Demo - B-4: Using Paste Link
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 Error Checking Button
The Trace Error Button
Syntax Errors
Demo - A-1: Entering a SUM Function
Demo - A-2: Using the Mouse to Enter a Function Argument
Demo - A-3: Entering a Function in the Formula Bar
Inserting Functions
Demo - A-4: 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
The Font Group
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
Paste Special Dialog Box
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
Rules Manager
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
The Cell Styles Gallery
Format a Table
Demo - E-3: Applying Cell Styles
Demo - E-4: Applying Table Styles and Sorting Data
Demo - E-5: 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
Demo - B-4: Formatting Headers and Footers
Demo - B-5: Printing Gridlines and Headings
Topic C: Printing Worksheets
Sheet Options in Page Setup
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
Arranging Windows
Demo - A-1: Locking Rows and Columns
Demo - A-2: Opening and Arranging New Windows
Splitting a Worksheet into Panes
Demo - A-3: Splitting a Worksheet into Panes
Hiding a Column
Unhiding Columns
Hidden Columns
Demo - A-4: Hiding and Unhiding Columns and Worksheets
Demo - A-5: 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
Demo - B-3: Inserting Different Even and Odd Headers
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 - Graphics and Screen-shots
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
Topic C: Screenshots
Insert a Screenshot
Demo C-1: Inserting a Screenshot
Clip a Portion of a Screen
Demo C-2: Modifying a Screenshot
Unit 09 Review
Course Closure
Excel 2010: Intermediate
Unit 01 - 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 Links in a Worksheet
Redirecting Links
Demo - C-3: Editing Links
Topic D: Managing Workbooks
Creating a Workspace
Demo - D-1: Creating a Workspace
Unit 01 Review
Unit 02 - 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
Merging Cells
Wrap Text in a Cell
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
Use Paste Special to Add Values
Demo - E-5: Using Paste Special to Add Values
Adding Backgrounds
Demo - E-6: Adding and Deleting Backgrounds
Adding a Watermark
Demo - E-7: Adding a Watermark
Unit 02 Review
Unit 03 - Outlining and Subtotals
Topic A: Outlining and Consolidating Data
The Expanded Outline Form
The Collapsed Outline Form
Demo - A-1: Creating an Outline
Create a Custom View
Switch Among Custom Views
Demo - A-2: Creating Custom Views
The Consolidate Dialog Box
Demo - A-3: 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 03 Review
Unit 04 - 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 04 Review
Unit 05 - 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
Sort and Filter by Color
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
Table Tools | Design Tab
Adding to 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
Table Names
The [@] Argument
Unit 05 Review
Unit 06 - 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 a Hyperlink
Modify a Hyperlink
Demo - B-2: Modifying and Deleting a Hyperlink
Topic C: Sharing Workbooks
File Save & Send Page Options
Demo - C-1: Examining Workbook Sharing Options
Save & Send Options
Sharing Workbooks by Email
Unit 06 Review
Unit 07 - 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
Downloading a Chart Template
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
Demo - C-3: Adding a Picture to a Worksheet
The Adjust Group
Demo - C-4: Modifying a Picture
Unit 07 Review
Unit 08 - 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
Password-Protect a Worksheet
Demo - C-1: Password-Protecting a Worksheet
Protect Parts of a Worksheet
Demo - C-2: Unlocking Cells and Protecting Part of a Worksheet
Protect the Workbook Structure
Demo - C-3: Protecting the Workbook Structure
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 08 Review
Unit 09 - Templates and Settings
Topic A: Application Settings
The Excel Options Dialog Box
Demo - A-1: Changing Application Settings
The Customize Ribbon Page
Demo - A-2: Customizing the Ribbon
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 09 Review
Unit 10 - PivotTable and PivotCharts
Topic A: Working with PivotTables
A Sample PivotTable
Create a PivotTable
Demo - A-1: Creating a PivotTable
Add Fields
Demo - A-2: Adding Fields to a PivotTable
Insert 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
Hide Details
Expand a Collapsed Range
Demo - B-2: Collapsing and Expanding Fields
Refresh Data
Demo - B-3: Refreshing the Data in a PivotTable
Topic C: Formatting PivotTables
PivotTable Styles
Demo - C-1: Applying a PivotTable Style
Change Field Settings
Value Field Settings Dialog Box
Demo - C-2: Changing Field Settings
Topic D: Using PivotCharts
Create a PivotChart
A Sample PivotChart
Demo - D-1: Creating a PivotChart
Unit 10 Review
Course Closure
Excel 2010: Advanced
Unit 01 - Logical & Statistical Functions
Topic A: Logical Functions
The IF Function
Demo - A-1: Using the IF Function
Demo - A-2: Using a Formula to Apply Conditional Formatting
OR, AND, and NOT Functions
Demo - A-3: Using OR, AND, and NOT as Nested Functions
Nested IF Functions
Demo - A-4: Using Nested IF Functions
The IFERROR Function
Demo - A-5: 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
Unit 01 Review
Unit 02 - Financial & Date Functions
Topic A: Financial Functions
The PMT Function
Demo - A-1: Using the PMT Function
Topic B: Date and Time Functions
Date Functions
Demo - B-1: Using Date Functions
Calculating Time
Demo - B-2: Using Time Functions
Topic C: Array Formulas
Array Formulas
Creating an Array Formula
Demo - C-1: Using an Array Formula
Applying Arrays to Functions
Demo - C-2: Applying Arrays to Functions
Modify an Array Formula
Demo - C-3: Modifying the Array Formula
Topic D: Displaying and Printing Formulas
Displaying Formulas in Cells
Hide Formulas from Users
Show Hidden Formulas
Demo - D-1: Showing, Printing, and Hiding Formulas
Automatic Recalculation
Edit Iteration Calculation Options
Demo - D-2: Setting Calculation Options
Unit 02 Review
Unit 03 - 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 03 Review
Unit 04 - Advanced Data Management
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 04 Review
Unit 05 - 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 05 Review
Unit 06 - 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
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
Add a Scenario Manager Button
Merging Scenarios
A Sample Scenario Summary
Unit 06 Review
Unit 07 - 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
Add Macro Buttons to the Ribbon
Insert a Button
Change Button Properties
Demo - A-4: Inserting a Macro Button
Create Auto_Open Macro
Demo - A-5: Creating an Auto_Open Macro
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
Creating a Custom Function
Demo - C-1: Creating a Custom Function
Unit 07 Review
