COURSE
Simon Sez IT
8 hrs

COURSE
Simon Sez IT
8 hrs

Course Overview

Get beyond the basics and supercharge your Excel 2019 capabilities. With this 8-hour, expert-led video training course, you’ll gain an in-depth understanding of the most business useful advanced features of Microsoft Excel that delve into high-level consolidation, analysis and reporting of financial information.

Target Audience

Data Analysts, Data Scientists, Statisticians, people in Financial Services, Accountancy, Audit, Tax

Learning Objectives

In this course you will learn:

• What’s new/different in Excel 2019
• Advanced charting and graphing in Excel
• How to use detailed formatting tools
• Lookup and advanced lookup functions
• Financial functions including calculating interest and depreciation
• Statistical functions
• Connecting to other workbooks and datasets outside of Excel e.g. MS Access and the web.
• How to create awesome visualizations using sparklines and data bars
• Mastery of PivotTables and Pivot Charts
• Scenario Manager, Goal Seek and Solver
Learning
Chapter 1: Introduction to Excel 2016 Advanced Course
Prerequisites of the Course and Version of Excelvideo
Course Structurevideo
Course Exercise Filestext
Course Demonstration Filestext
Chapter 2: Functions
Functions - Part 1video • 14 mins
Functions - Part 2video • 7 mins
Autosumvideo • 9 mins
Chapter 2 Quizquiz
Chapter 3: Date and Time Functions
How Date and Time Worksvideo • 10 mins
Basic Date and Time Functionsvideo • 10 mins
Complex Date and Time Functionsvideo • 11 mins
Chapter 3 Quizquiz
Chapter 4: Text Functions
Using Text Functions - Part 1video • 13 mins
Using Text Functions - Part 2video • 13 mins
Chapter 4 Quizquiz
Chapter 5: Logical Functions
Using Logical Functionsvideo • 10 mins
Exercise 01video
Chapter 5 Quizquiz
Chapter 6: Lookup Functions
HLOOKUP Functionvideo • 15 mins
VLOOKUP Functionvideo • 8 mins
Exercise 02video
Chapter 6 Quizquestion
Chapter 7: Financial Functions
Financial Functions and Terminologyvideo • 9 mins
Personal Financial Functionsvideo • 10 mins
Professional Financial Functionsvideo • 8 mins
Depreciationvideo • 6 mins
Exercise 03video
Chapter 7 Quizquiz
Chapter 8: Statistical Functions
Statistical Functions for Description - Part 1video • 14 mins
Statistical Functions for Description - Part 2video • 14 mins
Statistical Functions for Forecasting - Part 1video • 12 mins
Statistical Functions for Forecasting - Part 2video • 12 mins
Exercise 04video
One Click Forecastingvideo • 9 mins
Statistical Functions for Inferencevideo • 12 mins
Chapter 8 Quizquiz
Chapter 9: Connecting to External Data
Connecting to Other Workbooksvideo • 14 mins
Connecting to Access Databasesvideo • 13 mins
Connecting to Web Data Sourcesvideo • 8 mins
Get and Transformvideo • 15 mins
Exercise 05video
Chapter 9 Quizquiz
Chapter 10: Tables
Introduction to Tablesvideo • 13 mins
Working with Tablesvideo • 11 mins
Table Referencesvideo • 9 mins
Table Stylesvideo • 6 mins
Exercise 06video
Chapter 10 Quizquiz
Chapter 11: Pivot Tables
Introduction to Pivot Tablesvideo • 10 mins
Working with Pivot Tablesvideo • 13 mins
Filters and Slicersvideo • 13 mins
Pivot Chartsvideo • 9 mins
Exercise 07video
Chapter 11 Quizquiz
Chapter 12: Data Analysis
What If Analysisvideo • 11 mins
Scenario Managervideo • 12 mins
Goal Seekvideo • 8 mins
Solvervideo • 12 mins
Exercise 08video
Chapter 12 Quizquiz
Chapter 13: Graphs and Charts
Area Chartsvideo • 7 mins
Surface Chartsvideo • 5 mins