Microsoft Excel 2016/19
Intermediate/Advanced

Course Overview

Expand and enhance your skills using Microsoft Excel 2016/19 to increase your productivity, learn many advanced functions and how to manage lists of data in Excel. Introduction to Excel is a prerequisite for this course.

Course InformationRelated Training

Duration: Two-Day Course
Platform: Windows Only
Fee: Call for information

We recommend one to eight people for this course.

Course Content Download PDF (57kb)

Productivity

Applying the new themes and styles to help you create a unified design within your workbook.
Validating or restricting the data that is placed in a cell.
Creating an absolute cell reference.
Using range names to quickly go to a range or using the range name in a formula.
Identifying trends in your data using the improved conditional formatting features.
Inserting cell comments to make your work easier to review.
Using the Flash Fill feature to eliminate repetitive tasks such as extracting the first name from a list rather than using a formula.
Easily sharing a workbook online using OneDrive.

List Management

Analyzing your data more efficiently using the improved sorting command and creating a custom sort order list.
Quickly extracting data using the Filter command.
Using the Subtotal command.
Creating an automatic Outline to see only the subtotal and grand totals.
Quickly removing duplicate records.
Using the many features associated with Table Format command.

Workbook Management

Working with multiple worksheets and consolidating data.
Protecting parts of or the entire worksheet and protecting the workbook.

Get and Transform Data

Downloading data from another source.
Change or transform your data.
Automatically creating a Forecast chart.

Charts and Sparklines

Showing your data with the new enhanced chart layouts and styles to make charts more lively and informative.
Using the new Combo chart type.
Creating the new chart types, including Waterfall,
Statistical chart, Histogram, Pareto, and Box and Whisker.
Creating tiny charts called Sparklines that provide a visual representation of your data.
Using the new Quick Analysis tool to do everything simpler: Formatting, Formulas, charts and more.

Introduction to Pivot Tables

Learn how to quickly create a pivot table to summarize, organize, analyze and compare large amounts of data using the Recommended PivotTablecommand.
Adding slicers to quickly analyze your data.

Advanced Functions

Using the IF, COUNTIF, SUMIF, ROUND, TODAY and VLOOKUP functions.
Nesting functions such as the SUM function with the IF function.

Macros and Templates

Creating and using a simple Macro which records repetitive tasks in Excel.
Creating a template for files you use frequently.

Application Overview: Microsoft Excel 2016/19

Microsoft Excel is a powerful tool to create and format spreadsheets and to analyze, evaluate, and calculate data. You can use advanced features to manage, track, and chart critical business data to create rich data visualization, PivotTable views, and professional-looking charts. You can compile your company’s data into Microsoft Office Excel by retyping or by copying and pasting. But importing is most effective when you're working with large amounts of data that would be time-consuming to enter manually or too large to copy and paste. In addition, if you import your data, Excel can automatically update your reports and summaries whenever the original source database is updated. Your data is always current.

Users: IT, Database, Engineering, Finance, Legal, Marketing and Admin Professionals as well as anyone who needs create and edit spreedsheets that can be searched, sorted, analysed, reported, updated and shared with other users.

Microsoft Excel 2016/19 Intermediate/Advanced