Microsoft Excel Training

Lesson 1: Excel Essentials

Starting Excel
The Excel Interface
Opening a Workbook
Navigating around a Workbook
Finding Data
Entering Data
Saving and Closing a Workbook

Lesson 2: Creating Worksheets

Types of Data
Entering Text
Adjusting Column Width
Entering Numbers
Types of Formulas
Entering Formulas
About Functions
Inserting Functions
Copying and Moving Data
Copying Formulas
Absolute and Relative References

Lesson 3: Formatting

Text Formatting
Number Formatting
Alignment
Text Wrapping
Merging Cell Data
Borders and Highlighting
Styles and Themes

Lesson 4: Manipulating Data

Data Entry Shortcuts
Fill and Auto Fill
Replacing Data
Paste Options
Inserting and Deleting Rows and Columns
Hiding Rows and Columns

Lesson 5: Charts

About Charts
Creating Charts
Chart Types and Elements

Lesson 6: Output

Managing Worksheet Windows
Freezing Panes
Printing Worksheets
Print Setup Options
Print Areas and Titles
Headers and Footers
Sharing Workbooks

Lesson 7: Settings and Templates

Workbooks Options and Properties
Working with Templates

Lesson 1: Managing Workbooks

Inserting and Deleting Worksheets
Rearranging Worksheets
Adding Hyperlinks
Linking to other Workbooks
Customizing Excel

Lesson 2: Named Ranges

Using Names in Formulas
Other ways to use Named Ranges
Naming Shortcuts
Managing Named Ranges

Lesson 3: Filtering and Sorting Data Tables

Sorting Data
Custom Sorts
Filtering Tables
Using Auto-Filter
Removing Duplicate Rows
Working with Structured References
Data Validation
Transposing Rows and Columns

Lesson 4: Summarizing Data

Consolidating Data
Using Sub-totals

Lesson 5: PivotTables

About PivotTables
Creating PivotTables
Formatting PivotTables
Manipulating PivotTables
Viewing Subsets of PivotTable Data
Adding Slicers
Creating PivotCharts

Lesson 6: Presentation Features

Conditional Formatting
Graphical Conditional Formatting
Custom Formats
Adding Graphics
Adding Smart Art

Lesson 7: Advanced Charts

Special Chart Types
Inserting Trendlines
Combination Charts
Chart Templates
Inserting Sparklines
Quick Analysis Options

Lesson 8: Collaboration

Controlling Access with Permissions
Protecting and Securing Workbooks
Shared Workbooks
Adding Comments
Tracking Changes
Merging Shared Workbooks

Lesson 1: Logical and Lookup Functions

The IF Function
Conditional Calculations
Using SUMIF
VLOOKUPS and HLOOKUPS
Range LOOKUPS

Lesson 2: Advanced Formulas

Auditing and Error Trapping
Formula Options
Array Functions
Using TRANSPOSE

Lesson 3: Special Functions

Date and Time Functions
TODAY
NOW
DATE
Date Calculations
Text Functions
Using CONCATENATE and TRIM
Using UPPER, LOWER and PROPER
Extracting Text
Statistical Functions
Using MIN and MAX
Using COUNT, COUNTA and COUNTBLANK
Financial Functions
Using FV
Using PMT

Lesson 4: Importing and Exporting

The Power Pivot Data Model
Importing Data
Using Power Pivot
Exporting Data

Lesson 5: Data Analysis

What-if Analysis
Scenarios
Using Goal Seek
The Analysis Toolpak

Lesson 6: Macros and Forms

Recording Macros
Running Macros
Running Macros when Workbook is Open or Closed
Using Forms to Record Input

Lesson 1: VBA Concepts

The Object Model Hierarchy
Understanding Object Properties, Methods and Events
VBA Module and Procedure Types

Lesson 2: Accessing VBA Functionality

Macro Security Options
Using the Macro Recorder
Writing and Editing Macros
Essential VB Editor Components
Exploring the Object Browser
Using VBA Comments and Formatting to Write Solid Code

Lesson 3: Debugging and Error Handling

Types of Programming Errors
VBE Debugging Tools
Error Handling Code

Lesson 4: Using Variables to Store Data

VBA Data Types and Naming Rules
Declaring Variables

Lesson 5: VBA Looping and Decision Structures

For...and Do...Loops for Repetitive Actions
If..then Statements
Select Case Statements

Lesson 6: User-Interactive Worksheets

Capturing User Input
Dialog Box Types

Lesson 7: Performing Calculations with VBA

Using VBA’s Built-in Functions
Writing User-Defined Functions for Custom Calculations
Code to Automate Inserting SUM Functions