Course: Advanced Microsoft Excel 2019
Contact Hours: 18
Pre-requisite: Basic to Intermediate Knowledge of Microsoft Excel
Abstract
This course is designed to introduce participants with basic to intermediate knowledge of
Microsoft Excel 2019 to the advanced features in Microsoft Excel 2019. Participants will learn to
calculate data using advanced formulas and functions, analyzing data and other advanced tools
in Microsoft Excel.
Target Audience
Executives and Office Managers
Administrators, Secretaries and General Office Staff
Sales and Marketing Personnel
Accounts and Human Resources Staff
Secondary and Tertiary Level Students
Self-taught users desirous of enhancing their knowledge
Interested members of the general public
Learning outcomes
On completion of this course, learners will be able to:
1. Calculate data using advanced formulas and functions; troubleshoot and solve function
errors; assign names to cells and ranges; format values and create custom number types;
enforce data validation.
2. Analyze and present worksheet data using Charts, Sparklines, Diagrams, PivotTables,
PivotCharts, What-if Analysis, and features such as Quick Analysis Lenses, Chart
Animations and Recommendations generated by Excel from sheet data.
3. Collaborate and import data into Excel.
Course Content
1. Working with Ranges
Fill a range with the same data, a series of Values
Flash fill a range
Freeze Rows or Columns
Transpose Rows and Columns
2. Using Range Names
Understanding the benefits of using Range Names
Define a Range Name
Using Worksheet text to define a Range Name
Navigate a Workbook using Range Names
Change a Range Name.
Add cells to a range name
3. Building Formulas
Understanding Excel Functions
Using Functions; Financial, Logical, Text, Date, Time, Lookup, Reference, Math &
Statistical.
Switch to Absolute Cell References
Troubleshoot Formula Errors
Using a Range Name in a Formula
Reference Another Worksheet Range in a Formula
4. Printing Workbooks
Insert a Page Break
Set the Print Area
Configure Titles to Print on Each Page
5. Working with Tables
Get to Know Table Features
Convert a Range to a Table
Apply a Table Style
Build a Custom Table Style
Create and manage PivotTable and Pivot Charts
Using PowerPivot
6. Analyzing Data
Sort a Range or Table
Filter a Range or Table
Create advanced filters
Set Data Validation Rules
Create a Data Table
Summarize Data with Subtotals
Group Related Data
Analyze Data with Goal Seek
Analyze Data with Scenarios
Remove Duplicate Values from a Range or Table
Highlight Cells That Meet Some Criteria
Highlight the Top or Bottom Values in a Range
Create a Custom Conditional Formatting Rule
Using functions to format cells
Consolidate Data from Multiple Worksheets
Load the Excel Analysis ToolPak
7. Visualizing Data with Charts
Change the Chart Source Data
Add trendlines to Charts
Creating a dual axis chart
Add a Sparkline to a Cell
Create a Fill Series of Charts
8. Importing Data into Excel
Understanding External Data
Import Data from a Data Source
Import Data from an Access Table
Import Data from a Word Table
Import Data from a Text File
Import Data from a Web Page
Import Data from an XML File
Separate Cell Text into Columns
9. Collaborating with Others
Add a Comment to a Cell
Protect a Worksheet’s Data
Protect a Workbook’s Structure
Share a Workbook with Other Users
Track Workbook Changes, Accept or Reject Workbook Changes
Save a Workbook to Your OneDrive
Send a Workbook as an E‐Mail Attachment
Save Excel Data as a Web Page
Make a Workbook Compatible with Earlier Versions of Excel
Collaborate on a Workbook Online
Assessment Criteria
In order to achieve Learning Outcome… The Learner must…
1. Working with Ranges Know how to fill a range with values, sort a
range of data, transpose rows and columns
and freeze rows or columns.
2. Using Range Names Understand how to define, edit, and use range
names.
3. Building Formulas Build versatile worksheet functions in Excel
and incorporate them into formulas.
4. Printing Workbooks Be capable of printing specific or general parts
of a worksheet(s) or workbook.
5. Working with Tables Knowledgeably be aware of how a table is like
a database, the advantages of tables, and how
tables help with data analysis.
6. Analyzing Data Know how to apply the tools and techniques
to organize, study, and reach conclusions
about a specific collection of information.
7. Visualizing Data with Charts Utilize a wide variety of chart types, and a
large number of chart options to use the
appropriate chart to make the data easier to
understand and analyze.
8. Importing Data into Excel Able to use the number of tools that allows
external data types to be imported into Excel.
9. Collaborating with Others Know how to add comments, share a
workbook, work on a spreadsheet online,
protect worksheet data and track the changes
that others make.
Essential Learning Resources:
Websites
www.microsoft.com/learning
