Course Overview
This course will teach students advanced concepts and formulas in Microsoft Excel 365. 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
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
- Top-rated instructors: Our crew of subject matter experts have an average instructor rating of 4.8 out of 5 across thousands of reviews.
- Authorized content: We maintain more than 35 Authorized Training Partnerships with the top players in tech, ensuring your course materials contain the most relevant and up-to date information.
- Interactive classroom participation: Our virtual training includes live lectures, demonstrations and virtual labs that allow you to participate in discussions with your instructor and fellow classmates to get real-time feedback.
- Post Class Resources: Review your class content, catch up on any material you may have missed or perfect your new skills with access to resources after your course is complete.
- Private Group Training: Let our world-class instructors deliver exclusive training courses just for your employees. Our private group training is designed to promote your team’s shared growth and skill development.
- Tailored Training Solutions: Our subject matter experts can customize the class to specifically address the unique goals of your team.
Agenda
1 - 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 the Most 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 Functions
- What are Text Functions?
- Using CONCAT, CONCATENATE, AND TEXTJOIN
- Using Text to Columns
- Using LEFT, RIGHT, and MID Functions
- 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
- 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
- Additional Protection Features
10 - Automating with Macros
- What are Macros?
- Displaying the Developer Tab
- Creating a Basic Formatting Macro
- Assigning a Macro to a Button
- Creating Complex Macros
- Viewing and Editing the VBA Code
- Adding a Macro to the Quick Access Toolbar
11 - Working with Form Controls
- What are Form Controls?
- Adding a Spin Button 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 Specific Text Lengths
- 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
- Co-Authoring in Excel
- Tracking Changes in a Workbook
- Working with Versions
- Sharing Files via Email
14 - Importing and Exporting Data to a Text File
- Importing a Text File
- Exporting Data to a Text File
15 - Conclusion