Excel Advanced – Multi Edu

About Course

This course will teach students advanced concepts and formulas in Microsoft Excel. 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. This course has an assessment which may be required for a certificate to be generated.

Students will learn functions such as SUMIF, AVERAGEIF, and COUNTIF, advanced lookup functions, and complex logical and text functions
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 Content

1.Introduction

1.1 Introduction

00:00

2.Customizing Excel

3.Analyzing data with logical functions

3.1 Working with Common Logical Functions

00:00
3.2 Understanding IF Functions

00:00
3.3 Evaluating Data with the AND Function

00:00
3.4 Evaluating Data with the OR Function

00:00
3.5 Creating a Nested IF Function

00:00
3.6 Using the IFS Function

00:00
3.7 Summarizing Data with SUMIF

00:00
3.8 Summarizing Data with AVERAGEIF

00:00
3.9 Summarizing Data with COUNTIF

00:00
3.10 Summarizing Data with MAXIFS and MINIFS

00:00
3.11 Using the IFERROR Function

00:00

4.Working with lookup functions

4.1 What are Lookup Functions

00:00
4.2 Using VLOOKUP

00:00
4.3 Using HLOOKUP

00:00
4.4 Using VLOOKUP with TRUE

00:00
4.5 Using HLOOKUP with TRUE

00:00
4.6 Using the INDEX Function

00:00
4.7 Using the MATCH Function

00:00
4.8 Combining INDEX and MATCH

00:00
4.9 Comparing Two Lists with VLOOKUP

00:00
4.10 Comparing Two Lists with VLOOKUP and ISNA

00:00

5.Using text function

6.Working with date and time functions

7.Formula auditing

8.What if analysis tool

9.Worksheet and workbook protection

10.Automating with macros

11.Working with forms controls

12.Ensuring data integrity

12.1 What is Data Validation

00:00
12.2 Restricting Data Entry to Whole Numbers

00:00
12.3 Restricting Data Entry to a List

00:00
12.4 Restricting Data Entry to a Date

00:00
12.5 Restricting Data Entry to a Specific Text Length

00:00
12.6 Composing Input Messages

00:00
12.7 Composing Error Alerts

00:00
12.8 Finding Invalid Data

00:00
12.9 Editing and Deleting Validation Rules

00:00

About Course

What Will You Learn?

Course Content

1.Introduction

1.1 Introduction

2.Customizing Excel

2.1 Customizing the Ribbon

2.2 Customizing the Quick Access Toolbar

2.3 Customizing the General and Formula Options

2.4 Customizing the AutoCorrect Options

2.5 Customizing the Save Defaults

2.6 Customizing Advanced Excel Options

3.Analyzing data with logical functions

3.1 Working with Common Logical Functions

3.2 Understanding IF Functions

3.3 Evaluating Data with the AND Function

3.4 Evaluating Data with the OR Function

3.5 Creating a Nested IF Function

3.6 Using the IFS Function

3.7 Summarizing Data with SUMIF

3.8 Summarizing Data with AVERAGEIF

3.9 Summarizing Data with COUNTIF

3.10 Summarizing Data with MAXIFS and MINIFS

3.11 Using the IFERROR Function

4.Working with lookup functions

4.1 What are Lookup Functions

4.2 Using VLOOKUP

4.3 Using HLOOKUP

4.4 Using VLOOKUP with TRUE

4.5 Using HLOOKUP with TRUE

4.6 Using the INDEX Function

4.7 Using the MATCH Function

4.8 Combining INDEX and MATCH

4.9 Comparing Two Lists with VLOOKUP

4.10 Comparing Two Lists with VLOOKUP and ISNA

5.Using text function

5.1 What are Text Functions

5.2 Using CONCAT, CONCATENATE, and TEXTJOIN

5.3 Using Text to Columns

5.4 Using LEFT, RIGHT, and MID

5.5 Using UPPER, LOWER, and PROPER Functions

5.6 Using the LEN Function

5.7 Using the TRIM Function

5.8 Using the SUBSTITUTE Function

6.Working with date and time functions

6.1 What are Date and Time Functions

6.2 Using TODAY, NOW, and DAY Functions

6.3 Using NETWORKDAYS and YEARFRAC Functions

7.Formula auditing

7.1 Showing Formulas

7.2 Tracing Precedents and Dependents

7.3 Adding a Watch Window

7.4 Error Checking

8.What if analysis tool

8.1 Using the Scenario Manager

8.2 Using Goal Seek

8.3 Analysing with Data Tables

9.Worksheet and workbook protection

9.1 Understanding Protection

9.2 Encrypting Files with Passwords

9.3 Allowing Specific Worksheet Changes

9.4 Adding Protection to Selected Cells

10.Automating with macros

10.1 What are Macros

10.2 Displaying the Developer Tab

10.3 Creating a Basic Formatting Macro

10.4 Running a Macro

10.5 Assigning a Macro to a Button

10.6 Creating Complex Macros and Editing the VBA Code

10.7 Adding a Macro to the Quick Access Toolbar

11.Working with forms controls

11.1 What are Form Controls

11.2 Adding Spin Buttons and Check Boxes

11.3 Adding a Combo Box

12.Ensuring data integrity

12.1 What is Data Validation

12.2 Restricting Data Entry to Whole Numbers

12.3 Restricting Data Entry to a List

12.4 Restricting Data Entry to a Date

12.5 Restricting Data Entry to a Specific Text Length