Mastering Excel 2019 - Advanced

Name: Mastering Excel 2019 - Advanced
Rating: 4.4 (81 reviews)

Mastering Excel 2019 – Advanced

Created byBigger Brains

Last updated 2/2025

English

What you'll learn

Use multiple worksheets and workbooks to track, retrieve, consolidate and report on data using features like cell links, Group Mode, and more!
Collaborate with others using Comments, Track Changes, Sharing, Co-Authoring, and Compare and Merge.
Prepare a document for sharing by checking for accessibility and sensitive information, as well as multiple ways to protect your worksheets and workbooks.
Keep your data consistent and accurate with Data Validation and automate tasks with Macros.
Perform complex calculations by developing a deep understanding of Excel’s Lookup functions like VLOOKUP, MATCH, and INDEX.
Quickly find and address errors in your formulas with Tracing, Evaluate Formula, and the Watch Window, as well as more!
Use your data to plan and forecast trends and explore a variety of outcomes based on variables and scenarios.
Visualize geographic data with Map Charts and 3D Map Tours.

Course content

7 sections • 47 lectures • 4h 21m total length

Introduction1:59
Explore advanced Excel 2019 techniques—link data across sheets and workbooks, use 3D formulas, consolidate, validate data, automate with macros, and apply lookups, auditing, what-if analysis, forecasting, sparklines, and mapping.
Creating Internal Links4:08
Creating External Links9:00
Learn to create external links in Excel using external references, paste links, and manage absolute vs relative references; update links and break links when sharing workbooks.
Using Paste Special5:50
Using Group Mode3:18
Use group mode to edit multiple sheets at once by selecting several sheets and applying bold, shading, and a year in B1 and B3.
Using 3D References2:32
Master three-dimensional references by using group mode to sum and average the same cell across four worksheets, creating efficient three-dimensional formulas with autoSum.
Consolidating Data6:56
Consolidate data across four quarters and multiple sheets using Excel's consolidate feature to sum quantities, matching by labels in the left column and headings, with live links to source data.
Knowledge Check

Adding Comments5:04
Tracking Changes6:04
Master track changes in Excel by enabling the legacy feature on the Quick Access Toolbar, highlight edits, list changes on a sheet, and accept or reject edits with history.
Co-Authoring, Part 16:12
Co-Authoring, Part 24:44
Explore co-authoring in Excel 2019 advanced, featuring guest contributors, color-coded edits, in-app chat, and version history with restore options to recover data.
Using Compare and Merge3:04
Use compare and merge to pull data from multiple copies of the same file into one workbook, with last change precedence, and add the feature to the quick access toolbar.
Preparing to Share7:19
Share via OneDrive or email, choose PDF or Excel, consider workbook, check accessibility by adding alt text and using the accessibility checker, and inspect document properties to remove personal information.
Adding Digital Signatures5:55
Learn how to insert and manage digital signatures in Excel 2019, including visible and invisible signatures, signature lines, certificates, and verification of document integrity.
Protecting Worksheets10:33
Protecting Workbook Structure3:01
Learn how protecting workbook structure, not sheet contents, secures sheet order and visibility by preventing adding, deleting, renaming, moving, or hiding sheets, with optional password controls.
Protecting a Workbook File7:08
Knowledge Check

Applying Data Validation7:18
Adding a Dropdown List4:56
Master data validation in excel 2019 to create drop-down lists from a named range, such as Reported_By and Regions Range, and copy the validation down a column.
Customizing Data Validation5:18
Recording a Macro8:08
Running a Macro5:32
Editing a Macro8:27
Edit the Formatsheet macro in Module 1 via the Visual Basic Editor, adjusting fonts, dates, and ranges, then save as a macro-enabled XLSM workbook.
Knowledge Check

Using VLOOKUP, Part 15:40
Use VLOOKUP to look up an employee ID in a named data table and return the corresponding name and department with an exact match.
Using VLOOKUP, Part 25:15
Consolidate large tables with advanced VLOOKUP to extract exact matches for extensions from a primary key across sheets. Set the column index and range_lookup to maintain links when data changes.
More Lookup Functions9:44
Using Array Formulas6:00
Explore how array formulas perform multiple calculations at once, using multi-cell and single-cell examples with named ranges to compute income per title and average income per contract year.
Knowledge Check

Tracing Formulas6:22
Addressing Formula Errors7:16
Address formula errors in Excel 2019 by identifying obvious issues like #REF!, #NAME?, and #VALUE!, and using error checking, trace error, and circular reference fixes.
Watching and Evaluating Formulas4:46
Adding Error Handling4:34
Master how to handle input data errors in Excel using IFNA and IFERROR. Replace #N/A and division by zero errors with clear messages or defaults.
Working with Information Functions5:12
Learn how to use information functions like ISBLANK, ISNUMBER, and ISFORMULA inside IF statements to handle blanks, validate data, and prevent errors in Excel 2019.
Knowledge Check

Using Goal Seek3:30
Explore goal seek in what-if analysis to set a formula cell, adjust a changing cell, and determine borrowing for a 500 monthly payment and break-even sales.
Forecasting with Data Tables5:04
Mastering Excel 2019 advanced teaches forecasting with data tables, showing how to build single-variable and two-variable tables to vary principal and interest for loan payments.
Using Solver6:46
Learn to use Excel solver to maximize profit by configuring the objective, variable cells, and constraints, including whole-number units and a 6 million production cap, with scenarios and analysis reports.
Forecasting with Scenarios8:16
Learn to build and compare scenarios in Excel's Scenario Manager, changing revenue and expenses to forecast profit, save originals, and view a unified scenario summary.
Forecasting Data Trends3:23
Explore Excel's forecast sheet to turn two years of monthly data into a forecast chart and table with 95% confidence bounds and automatic seasonality detection.
Knowledge Check

Inserting Symbols and Characters2:32
Insert symbols and special characters in Excel 2019 by using the insert tab and symbols, selecting fonts like Wingdings, and inserting items such as copyright and checkmark symbols into cells.
Creating Sparklines3:58
Create sparklines in a single cell to visualize data trends. Choose line, column, or win/loss, set data and location, and customize markers and color.
Creating a Map Chart3:47
Create a map chart in Excel to visualize sales by state, using country and region data, adjust map area, add labels, and customize colors and legends.
Creating 3D Maps8:27
Create and explore 3D maps in Excel to visualize state and regional sales data, with time by month, scene sequencing, and multiple map types such as bubble and area maps.
Creating Custom Lists5:34
Importing and Exporting Data3:19
Creating a Web Query3:52
create a web query to import live data from a website into Excel, choose the table via web view, load it, and set refresh options.
Working with Forms9:54
Using ActiveX Controls4:02
Explore how ActiveX controls differ from regular form controls in Excel 2019, including how to insert a combo box, set properties, and choose when to use ActiveX versus standard controls.
Ideas in Office 3652:06
Explore Excel's ideas feature in Office 365 to generate pivot charts and pivot tables from data, using the ideas pane for insights, with column headings and data size limits.
Knowledge Check

Requirements

An intermediate knowledge of Excel is recommended.

Description

Learn how to use the world’s most popular software tool for organizing and analyzing data.

When you master Excel, you have one of the most practical and valuable skillsets in modern business. You can work wonders—from organizing lists; to creating multi-layered, interactive reports; to answering critical business questions like ROI, budget allocations, expense tracking, and more. This course is your first step towards becoming an expert at using Excel.

This is our most requested training course!

Topics covered include:

Navigating the interface
Creating and saving workbooks
Selecting and entering data
Performing calculations
Formatting data
Managing multiple worksheets
Customizing your options

Objectives. You will be able to:

Navigate the interface including creating and saving workbooks, quickly accessing commands, and selecting and entering data
Perform calculations using formulas and functions
Insert and delete rows and columns
Apply formatting to text, numbers, and cells including using Cell Styles and Conditional Formatting
Control how your worksheets print using Page Layout options, Page Breaks, and Headers and Footers
Manage multiple worksheets within a workbook including adding, removing, renaming, reordering, copying, and color-coding worksheets
Use View features to compare workbooks, compare worksheets, or save Custom Views
Customize Excel by adding Groups to the Ribbon and modifying the Quick Access Toolbar

Course Goal: By the end of this course, you will be able to use to create your own workbooks and analyze your data.

Learn how to use the world’s most popular software tool for organizing and analyzing data.

Over four hours of high-quality HD content in the “Uniquely Engaging”TM Bigger Brains Teacher-Learner style!

Who this course is for:

People who have a working knowledge of Excel 2019 or Excel 365, but want to use more complex functions and features to analyze data.

Mastering Excel 2019 - Advanced

What you'll learn

Explore related topics

Course content

Linking and Consolidating7 lectures • 34min

Sharing and Protecting10 lectures • 59min

Automating6 lectures • 40min

Using Lookup Functions4 lectures • 27min

Auditing Formulas5 lectures • 28min

Forecasting Data5 lectures • 27min

More Features10 lectures • 48min

Requirements

Description

Who this course is for: