Mastering Excel 2019 - Advanced

Learn to add, reply, edit, and delete comments in Excel 2019, view them via the Review tab, and print them at end of sheet.

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.

Master co-authoring in Excel 2019 by saving workbooks to OneDrive or SharePoint, sharing with specific people or your organization, and editing simultaneously with real-time presence and change visibility.

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.

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.

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.

Learn how to insert and manage digital signatures in Excel 2019, including visible and invisible signatures, signature lines, certificates, and verification of document integrity.

Protect worksheets by setting an optional password, locking and hiding formulas, and controlling edits. Use allow edit ranges to let specific data be changed.

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.

Learn to protect an Excel 2019 workbook by applying read-only and final marks, encrypting with a password, and securing sheets, ranges, and the workbook structure.

Apply data validation rules in Excel 2019 to ensure data accuracy and consistency, restricting quantities to whole numbers greater than zero, decimals within a range, and dates within the year.

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.

Learn to customize Excel data validation with input messages and error alerts, set numeric rules, choose stop or warning or information styles, and circle invalid data.

Learn to create and record macros in Excel using the Developer tab, format headers and numbers as currency, and save macros for later playback.

Learn to run macros from the developer tab, assign them to buttons or shapes, and manage macro security settings to control enabling macros across workbooks.

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.

Use VLOOKUP to look up an employee ID in a named data table and return the corresponding name and department with an exact match.

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.

Use the match function to identify the vlookup column for salary in a wide pay data table, and use index with match to return a manager by employee id.

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.

Explore Excel's auditing features to show formulas, trace precedents and dependents, and identify external references, data sources, and how values propagate.

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.

Learn to diagnose complex formulas in Excel using Evaluate Formula, Step In, and the Watch Window. See how data changes affect functions across sheets, including nested ifs and sums.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Master custom lists in Excel to sort data in geographic or custom orders and to auto-fill sequences. Learn to use built-in lists, import from cells, and delete created lists.

Import data from text and csv formats using delimited options with tabs or comma separators, and export to csv, text, or pdf in Excel 2019 advanced.

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.

Learn to use Excel's data form and worksheet form, add quick access toolbar button, and build a custom input form with named ranges and combo boxes, list boxes, and check box.

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.

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.