Advanced Microsoft Excel

14 Hours (Part-time or Full-time available)

This course is intended for current Microsoft Excel users who wish to extend their knowledge and skills beyond building and formatting simple workbooks.

This course is designed for current Microsoft Excel users who can build basic workbooks. It is assumed that the users know how to create and save a workbook, create formulas and use functions, and perform simple formatting of the data.

Jennifer Lye

Unit 1 Names Ranges

• Define Name
• Name Manager
• Dynamic Name Range

Unit 2 Protecting Data

• Lock Cells
• Protect Sheet

Unit 3 Lookup & Reference Functions

• VLookup and HLookup
• Match
• Index
• Choose

Unit 4 Financial Functions

• Pmt
• Rate
• Nper
• Pv
• Fv

Unit 5 Statistical Functions

• Median, Mode, Standard deviation
• Min, Max, Large, Small
• Math & trig functions
• Round, Roundup, Rounddown

Unit 6 If Function

• If Function
• AND and OR Function

Unit 7 Conditional Formatting

• Highlight Cells Rules
• Clear Rules
• Top/Bottom Rules

Unit 8 Text Functions

• Join Strings
• Left
• Right
• Mid
• Len
• Find
• Substitute

Unit 9 Data Validation

• Create Validation Rule
• Input Message
• Error Alert
• Data Validation Result
• Reject Invalid Dates
• Budget Limit
• Prevent Duplicate Entries
• Drop-down List

Unit 10 Creating A Worksheet Template Unit 11 Pivot Tables

• Insert A Pivot Table
• Two-dimensional Pivot Table
• Multi-level Pivot Table
• Multiple Report Filter Fields

UNIT 12 Tables

• Insert a table
• Format a table
• Working with tables
• Convert to a range

Unit 13 What If Analysis

• Create a Different Scenarios
• Goal Seek
• Solver
• Load the Solver Add-in

Unit 14 Group And Consolidate Worksheets

• Group Worksheets
• Consolidate
• View Multiple Worksheets

Unit 15 Web Integration

• How to run a Web Query

Unit 16 How To Insert Hyperlink In Excel

Unit 17 Sparklines

• Insert Sparklines
• Customize Sparklines