Excel 2019 - Advanced

Duration
3 Hours, 58 Minutes

 

Delivery Method
Self-Paced

Educate 360 Pro from New Horizons

This class is part of a 12‑month Educate 360 Pro Subscription

Course Overview

This course will teach students advanced concepts and formulas in Microsoft Excel 2019. Students will learn functions such as SUMIF, AVERAGEIF, and COUNTIF, advanced lookup functions, and complex logical and text functions. Additionally, students will experiment with auditing formulas and error checking, use the What-If Analysis tools, learn the options for worksheet and workbook protection, review advanced use of PivotTables and PowerPivot add-in, work with Macros, use form controls, and ensure data integrity in their worksheets and workbooks. Students will also learn about Excel's many collaboration features and import and export data to and from their workbooks.

Course Objectives

Introduction , Customizing Excel , Analyzing Data with Logical Functions , Working with Lookup Functions , Using Text Function , Working with Date and Time Functions , Formula Auditing , What-If Analysis Tools , Worksheet and Workbook Protection , Automating with Macros , Working with Form Controls , Ensuring Data Integrity , Collaborating in Excel , Importing and Exporting Data to a Text File , Conclusion

Course Prerequisites

The recommended pre-requisite for this course is Basic Microsoft Windows and Intermediate Microsoft Excel 2019 training or equivalent experience.

Agenda

1 - Introduction

  • Introduction

2 - Customizing Excel

  • Customizing the Ribbon
  • Customizing the Quick Access Toolbar
  • Customizing the General and Formula Options
  • Customizing the AutoCorrect Options
  • Customizing the Save Defaults
  • Customizing Advanced Excel Options

3 - Analyzing Data with Logical Functions

  • Working with Common Logical Functions
  • Understanding IF Functions
  • Evaluating Data with the AND Function
  • Evaluating Data with the OR Function
  • Creating a Nested IF Function
  • Using the IFS Function
  • Summarizing Data with SUMIF
  • Summarizing Data with AVERAGEIF
  • Summarizing Data with COUNTIF
  • Summarizing Data with MAXIFS and MINIFS
  • Using the IFERROR Function

4 - Working with Lookup Functions

  • What are Lookup Functions?
  • Using VLOOKUP
  • Using HLOOKUP
  • Using VLOOKUP with TRUE
  • Using HLOOKUP with TRUE
  • Using the INDEX Function
  • Using the MATCH Function
  • Combining INDEX and MATCH
  • Comparing Two Lists with VLOOKUP
  • Comparing Two Lists with VLOOKUP and ISNA

5 - Using Text Function

  • What are Text Functions?
  • Using CONCAT, CONCATENATE, and TEXTJOIN
  • Using Text to Columns
  • Using LEFT, RIGHT, and MID
  • Using UPPER, LOWER, and PROPER Functions
  • Using the LEN Function
  • Using the TRIM Function
  • Using the SUBSTITUTE Function

6 - Working with Date and Time Functions

  • What are Date and Time Functions?
  • Using TODAY, NOW, and DAY Functions
  • Using NETWORKDAYS and YEARFRAC Functions

7 - Formula Auditing

  • Showing Formulas
  • Tracing Precedents and Dependents
  • Adding a Watch Window
  • Error Checking

8 - What-If Analysis Tools

  • Using the Scenario Manager
  • Using Goal Seek
  • Analyzing with Data Tables

9 - Worksheet and Workbook Protection

  • Understanding Protection
  • Encrypting Files with Passwords
  • Allowing Specific Worksheet Changes
  • Adding Protection to Selected Cells

10 - Automating with Macros

  • What are Macros?
  • Displaying the Developer Tab
  • Creating a Basic Formatting Macro
  • Running a Macro
  • Assigning a Macro to a Button
  • Creating Complex Macros and Editing the VBA Code
  • Adding a Macro to the Quick Access Toolbar

11 - Working with Form Controls

  • What are Form Controls?
  • Adding Spin Buttons and Check Boxes
  • Adding a Combo Box

12 - Ensuring Data Integrity

  • What is Data Validation?
  • Restricting Data Entry to Whole Numbers
  • Restricting Data Entry to a List
  • Restricting Data Entry to a Date
  • Restricting Data Entry to a Specific Text Length
  • Composing Input Messages
  • Composing Error Alerts
  • Finding Invalid Data
  • Editing and Deleting Validation Rules

13 - Collaborating in Excel

  • Working with Comments
  • Printing Comments and Errors
  • Sharing a Workbook
  • Tracking Changes in a Workbook
  • Working with Versions
  • Sharing Files

14 - Importing and Exporting Data to a Text File

  • Importing a Text File
  • Exporting Data to a Text File

15 - Conclusion

  • Course Recap

What is Educate 360 Pro?

Educate 360 Pro is a 12-month subscription that provides unlimited access to full-day or short-duration live classes, on-demand video courses, class recordings, and quick reference cards. New Horizons offers more learning solutions for Microsoft Office than any other training provider and they are all included in your Educate 360 Pro subscription.

  • Unlimited enrollments into over 90 full-day and short-duration live training classes
  • Over 750 hours of on-demand video based content
  • A full library of digital quick reference cards for your favorite Office applications
  • Access to class recordings so you never have to worry about missing a scheduled class

Benefits of an Educate 360 Pro Subscription

Our Educate 360 Pro subscription is built to fit in and around your schedule. It will change the way you think about training.

  • Ask questions and get immediate answers during our full-day and short-duration live classes
  • Learn how to perform specific tasks instead of wasting time learning things you already know
  • Collaborate with fellow students and gain insight into new ways to solve problems
  • View on-demand courses with instructors that never leave your screen
  • Utilize course assessments to validate new skills
  • Take full day training classes on your schedule by accessing our class recordings
  • Revisit a prior class to further enhance your skills

Need more information? Click here to learn more about Educate 360 Pro.