facebook-pixel
$29.99
Certification

Industry recognized certification enables you to add this credential to your resume upon completion of all courses

Need Custom Training for Your Team?
Get Quote
Call Us

Toll Free (844) 397-3739

Inquire About This Course
Instructor
Intellezy Trainers, Instructor - Excel 2019 Advanced

Intellezy Trainers

Improving individual or company performance begins with the right training resources. Keep your team’s Microsoft and Adobe skills fresh with our engaging, short-segment videos. Intellezy’s training is delivered by certified instructors and industry experts. Their friendly, engaging style breaks down complex ideas into bite-sized learning nuggets. And, because they never leave the screen, the learner’s attention is held longer. Start watching and find out how valuable 3 minutes can be.

Instructor: Intellezy Trainers

Students will learn functions such as SUMIF, AVERAGEIF, and COUNTIF, advanced lookup functions, and complex logical and text functions.

This course will teach students advanced concepts and formulas in Microsoft Excel 2019. 

Students will also learn about Excel's many collaboration features and import and export data to and from their workbooks.

Course Description

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.

What am I going to get from this course?

  • Customizing Excel
  • Analyzing Data with Logical Functions
  • Working with Lookup Functions
  • Using Text Functions
  • Working with Date and Time Functions
  • Formula Auditing
  • What-If Analysis
  • 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 

Prerequisites and Target Audience

What will students need to know or do before starting this course?

Excel 2019 Introduction and Intermediate courses or equivalent experience. 

Who should take this course? Who should not?

Those wishing to learn advanced concepts and formulas in Microsoft Excel 2019.

Curriculum

Module 1: Introduction

Lecture 1 Introduction

Module 2: Customizing Excel

Lecture 2 Customizing the Ribbon
Lecture 3 Customizing the Quick Access Toolbar
Lecture 4 Customizing the General and Formula Options
Lecture 5 Customizing the Save Defaults
Lecture 6 Customizing Advanced Excel Options

Module 3: Analyzing Data with Logical Functions

Lecture 7 Working with Common Logical Functions
Lecture 8 Understanding IF Functions
Lecture 9 Evaluating Data with the AND Function
Lecture 10 Evaluating Data with the OR Function
Lecture 11 Creating a Nested IF Function
Lecture 12 Using the IFS Function
Lecture 13 Summarizing Data with SUMIF
Lecture 14 Summarizing Data with AVERAGEIF
Lecture 15 Summarizing Data with COUNTIF
Lecture 16 Summarizing Data with MAXIFS and MINIFS
Lecture 17 Using the IFERROR Function

Module 4: Working with Lookup Functions

Lecture 18 What are Lookup Functions?
Lecture 19 Using VLOOKUP
Lecture 20 Using HLOOKUP
Lecture 21 Using VLOOKUP with TRUE
Lecture 22 Using HLOOKUP with TRUE
Lecture 23 Using the INDEX Function
Lecture 24 Using the MATCH Function
Lecture 25 Combining INDEX and MATCH
Lecture 26 Comparing Two Lists with VLOOKUP
Lecture 27 Comparing Two Lists with VLOOKUP and ISNA

Module 5: Using Text Function

Lecture 28 What are Text Functions?
Lecture 29 Using CONCAT, CONCATENATE, and TEXTJOIN
Lecture 30 Using Text to Columns
Lecture 31 Using LEFT, RIGHT, and MID
Lecture 32 Using UPPER, LOWER, and PROPER Functions
Lecture 33 Using the LEN Function
Lecture 34 Using the TRIM Function
Lecture 35 Using the SUBSTITUTE Function

Module 6: Working with Date and Time Functions

Lecture 36 What are Date and Time Functions?
Lecture 37 Using TODAY, NOW, and DAY Functions
Lecture 38 Using NETWORKDAYS and YEARFRAC Functions

Module 7: Formula Auditing

Lecture 39 Showing Formulas
Lecture 40 Tracing Precedents and Dependents
Lecture 41 Adding a Watch Window
Lecture 42 Error Checking

Module 8: What-If Analysis Tools

Lecture 43 Using the Scenario Manager
Lecture 44 Using Goal Seek
Lecture 45 Analyzing with Data Tables

Module 9: Worksheet and Workbook Protection

Lecture 46 Understanding Protection
Lecture 47 Encrypting Files with Passwords
Lecture 48 Allowing Specific Worksheet Changes
Lecture 49 Adding Protection to Selected Cells

Module 10: Automating with Macros

Lecture 50 What are Macros?
Lecture 51 Displaying the Developer Tab
Lecture 52 Creating a Basic Formatting Macro
Lecture 53 Running a Macro
Lecture 54 Assigning a Macro to a Button
Lecture 55 Creating Complex Macros and Editing the VBA Code
Lecture 56 Adding a Macro to the Quick Access Toolbar

Module 11: Working with Form Controls

Lecture 57 What are Form Controls?
Lecture 58 Adding Spin Buttons and Check Boxes
Lecture 59 Adding a Combo Box

Module 12: Ensuring Data Integrity

Lecture 60 What is Data Validation?
Lecture 61 Restricting Data Entry to Whole Numbers
Lecture 62 Restricting Data Entry to a List
Lecture 63 Restricting Data Entry to a Date
Lecture 64 Restricting Data Entry to a Specific Text Length
Lecture 65 Composing Input Messages
Lecture 66 Composing Error Alerts
Lecture 67 Finding Invalid Data
Lecture 68 Editing and Deleting Validation Rules

Module 13: Collaborating in Excel

Lecture 69 Working with Comments
Lecture 70 Printing Comments and Errors
Lecture 71 Sharing a Workbook
Lecture 72 Tracking Changes in a Workbook
Lecture 73 Working with Versions
Lecture 74 Sharing Files

Module 14: Importing and Exporting Data to a Text File

Lecture 75 Importing a Text File
Lecture 76 Exporting Data to a Text File

Module 15: Conclusion

Lecture 77 Course Recap

Module 16: Final Assessment Quiz - Excel 2019 Advanced

Quiz 1 Final Assessment Quiz - Excel 2019 Advanced

350