# Excel – Beginner to Advanced (In-depth)

In this Excel – Beginner to Advanced – An In-depth video course, Nurture Tech Academy introduces you to the most simplest and logical way to understand Excel environment in detail.

### This Course includes:

In this Excel training course, you will learn by watching the author actually perform the operation he is instructing on, as he explains step by step how to perform these functions. The training starts with Excel Introduction and its interface. Then it will explain how to write a Formula in Excel and starts with basic formulas like Sum and Count then gradually move to the complex one like IF, IF with And, IF with OR, Nested IF. Then it will cover the whole Lookup Functionality. Financial Functions will be covered there after with Date & Time Functions.

It will also covers the basics of Excel Charts, types of charts in Excel (e.g. Column, Bar, Line, Pie, Pie of Pie, Bar of Pie, etc.)

Pivot Tables, Data Validation, What-if Analysis, Solver Add-in covers afterwards.

Then we will see step by step how to record a Macro. Then it will show you how to use show developer tab, ways to record a Macro, record a Macro, use Relative Reference, assign a Macro to a shape, how to edit or delete a Macro and finally what are the security settings of a Macro.

By the completion of this online training course, you will be fully versed, and capable of using most of the Formulas & Functions in Microsoft Excel 2007/2010/2013/2016 in a commercial Environment.

### Course Features

- Lectures 127
- Quizzes 0
- Duration 12 Hours
- Skill level All levels
- Language English
- Students 33
- Assessments Self

#### 01 Excel from Scratch (Optional)

- Lecture 1.1 0101 Touring the Excel Interface Locked
- Lecture 1.2 0102 Enter data & saving workbook Locked
- Lecture 1.3 0103 Applying Formulas Locked
- Lecture 1.4 0104 Changing a worksheet’s structure Locked
- Lecture 1.5 0105 Find, Replace & Spell Check Locked
- Lecture 1.6 0106 Applying fonts, background colors, and borders Locked
- Lecture 1.7 0107 Formatting numbers and dates Locked
- Lecture 1.8 0108 Making the pieces fit (Printing) Locked
- Lecture 1.9 0109 Inserting headers & footers and Repeating Rows Locked
- Lecture 1.10 0110 Managing Large Workbooks Locked

#### 02 Excel Essentials (Mandatory for Everyone)

- Lecture 2.1 0201 Formula Writing (Don’t miss this) Preview
- Lecture 2.2 Ex 0201 Formula Writing (Don’t miss this) Preview

#### 03 Sum/Count/Average Functionality (With Conditions)

- Lecture 3.1 0301 Sumif & Sumifs Preview
- Lecture 3.2 Ex 0301 Sumif & Sumifs Preview
- Lecture 3.3 0302 All Count Functions Locked
- Lecture 3.4 Ex 0302 All Count Functions Locked
- Lecture 3.5 0303 Averageif & Averageifs Locked
- Lecture 3.6 Ex 0303 Averageif & Averageifs Locked

#### 04 Text Functions (Manipulating Text)

- Lecture 4.1 0401 Change Case (Upper, Lower, Propper) Locked
- Lecture 4.2 Ex 0401 Change Case (Upper, Lower, Proper) Locked
- Lecture 4.3 0402 Separating Text on Symmetrical Data Locked
- Lecture 4.4 Ex 0402 Separating Text on Symmetrical Data Locked
- Lecture 4.5 0403 Separating Text on Non-Symmetrical Data (Text to Columns) Locked
- Lecture 4.6 Ex 0403 Separating Text on Non-Symmetrical Data (Text to Columns) Locked

#### 05 Logical Functions (Working with Conditions)

- Lecture 5.1 0501 If Functionality Locked
- Lecture 5.2 Ex 0501 If Functionality Locked
- Lecture 5.3 0502 If with “AND” Locked
- Lecture 5.4 Ex 0502 If with “AND” Locked
- Lecture 5.5 0503 If with “OR” Locked
- Lecture 5.6 Ex 0503 If with “OR” Locked
- Lecture 5.7 0504 IF with “AND” & “OR” / If with Countif Locked
- Lecture 5.8 Ex 0504 If with “AND” & “OR” Locked
- Lecture 5.9 0505 Nested IF (A Genie with 3 wishes) Locked
- Lecture 5.10 Ex 0505 Nested IF (A Genie with 3 wishes) Locked

#### 06 Lookup Functionality (Burn Simple find technique)

- Lecture 6.1 0601 Vlookup (Exact Match) Example # 1 Preview
- Lecture 6.2 Ex 0601 Vlookup (Exact Match) Example # 1 Locked
- Lecture 6.3 0602 Type of References (Use of $ sign) Locked
- Lecture 6.4 0603 Vlookup (Exact Match) Example # 2 Locked
- Lecture 6.5 Ex 0603 Vlookup (Exact Match) Example # 2 Locked
- Lecture 6.6 0604 Double Vlookup (Nested Vlookup) Locked
- Lecture 6.7 Ex 0604 Double Vlookup (Nested Vlookup) Locked
- Lecture 6.8 0605 Vlookup on Duplicate lookup Values Locked
- Lecture 6.9 Ex 0605 Vlookup on Duplicate lookup Values Locked
- Lecture 6.10 0606 Vlookup (Approximate Match) Locked
- Lecture 6.11 Ex 0606 Vlookup (Approximate Match) Locked
- Lecture 6.12 0607 Vlookup with IF (Conditional Vlookup) Locked
- Lecture 6.13 Ex 0607 Vlookup with IF (Conditional Vlookup) Locked
- Lecture 6.14 0608 Hlookup (Exact Match) Locked
- Lecture 6.15 Ex 0608 Hlookup (Exact Match) Locked
- Lecture 6.16 0609 Hlookup (Approximate Match) Locked
- Lecture 6.17 Ex 0609 Hlookup (Approximate Match) Locked
- Lecture 6.18 0610 Lookup (Don’t Use This) Locked
- Lecture 6.19 Ex 0610 Lookup (Don’t Use This) Locked
- Lecture 6.20 0611 Vlookup & Match (Create Magical Vlookup) Locked
- Lecture 6.21 Ex 0611 Vlookup & Match (Create a Magical Vlookup) Locked
- Lecture 6.22 0612 Match (Gives us Column & Row number) Locked
- Lecture 6.23 Ex 0612 Match (Gives us Column & Row number) Locked
- Lecture 6.24 0613 Index & Match (Made for each other) Locked
- Lecture 6.25 Ex 0613 Index & Match Exercise 1 Locked
- Lecture 6.26 Ex 0613 Index & Match Exercise 2 Locked

#### 07 Date & Time Function (A Tricky Game)

- Lecture 7.1 0701 How Excel Records Date & Time Locked
- Lecture 7.2 0702 Now, Today & Autofill Locked
- Lecture 7.3 0703 Datevalue & Timevalue Locked
- Lecture 7.4 0704 Calculate working days (Networkdays, Networkdays.Intl) Locked
- Lecture 7.5 0705 Datedif (Calculate Age) Locked

#### 08 Financial Functions (Useful for Non-Finance guys too)

- Lecture 8.1 0801 Loan Calculation (PMT, PPMT, IPMT, PER, PV, etc.) Locked
- Lecture 8.2 Ex 0801 Loan Calculation (PMT, PPMT, IPMT, PER, PV, etc.) Locked
- Lecture 8.3 0802 Creating a Loan Table Locked
- Lecture 8.4 Ex 0802 Creating a Loan Table Locked
- Lecture 8.5 0803 How to Calculate Depriciation Locked
- Lecture 8.6 Ex 0803 How to Calculate Depriciation Locked

#### 09 Sort & Filter (Common for all)

- Lecture 9.1 0901 Basic Sorting & Shortcut Keys Locked
- Lecture 9.2 Ex 0901 Basic Sorting & Shortcut Keys Locked
- Lecture 9.3 0902 Sorting using Custom List Locked
- Lecture 9.4 Ex 0902 Sorting Using Custom List Locked
- Lecture 9.5 0903 Horizontal Sorting Locked
- Lecture 9.6 Ex 0903 Horizontal Sorting Locked
- Lecture 9.7 0904 Basic Filter & Shortcut Keys Locked
- Lecture 9.8 Ex 0904 Basic Filter & Shortcut Keys Locked
- Lecture 9.9 0905 Filter Problem # 1 (Copy & Paste Filtered data without hidden Values) Locked
- Lecture 9.10 Ex 0905 Filter Problem # 1 (Copy & Paste Filtered data without hidden Values) Locked
- Lecture 9.11 0906 Filter Problem # 2 (Pasting Values on Filtered Data) Locked
- Lecture 9.12 Ex 0906 Filter Problem # 2 (Pasting Values on Filtered Data) Locked
- Lecture 9.13 0907 Filter Problem # 3 (Perform Calculation on Filtered Data) Locked
- Lecture 9.14 Ex 0907 Filter Problem # 3 (Perform Calculation on Filtered Data) Locked
- Lecture 9.15 0908 Advanced Filter (Using Complex Criteria’s) Locked
- Lecture 9.16 Ex 0908 Advanced Filter (Using Complex Criteria) Locked

#### 10 Excel Charts (Its time for Visual presentation)

- Lecture 10.1 1001 How to Create a Chart Locked
- Lecture 10.2 1002 Format Axis Locked
- Lecture 10.3 1003 Secondary Axis Locked
- Lecture 10.4 1004 Life of Pie (Charts) Locked

#### 11 Data Consolidation (Multiple Workbooks/Worksheets)

- Lecture 11.1 1101 Consolidate data from Excel inbuilt function Locked
- Lecture 11.2 Ex 1101 Consolidate data from Excel inbuilt functions Locked

#### 12 Data Validation

- Lecture 12.1 1201 Applying Data Validation Locked
- Lecture 12.2 Ex 1201 Applying Data Validation Locked
- Lecture 12.3 1202 Dependent Dropdown List Locked
- Lecture 12.4 Ex 1202 Dependent Dropdown List Locked
- Lecture 12.5 1203 Using Data Validation with Vlookup & Match Locked
- Lecture 12.6 1204 Other Validation Examples Locked

#### 13 Protection of Worksheet/Workbook

- Lecture 13.1 1301 Worksheet Level Protection Locked
- Lecture 13.2 Ex 1301 Worksheet Level Protection Locked
- Lecture 13.3 1302 Cell Level Protection Locked
- Lecture 13.4 Ex 1302 Cell Level Protection Locked
- Lecture 13.5 1303 Hiding Formulas from Formula Bar Locked
- Lecture 13.6 Ex 1303 Hiding Formulas from Formula Bar Locked
- Lecture 13.7 1304 Protecting Workbook Structure Locked
- Lecture 13.8 Ex 1304 Protecting Workbook Structure Locked
- Lecture 13.9 1305 Workbook Level Protection Locked
- Lecture 13.10 Ex 1305 Workbook Level Protection Locked

#### 14 Pivot Table & Techniques

- Lecture 14.1 1401 Understanding Pivot Table Preview
- Lecture 14.2 1402 Using Pivot Table in Real Scenario # 1 Locked
- Lecture 14.3 Ex 1402 Using Pivot Table in Real Scenario # 1 Locked
- Lecture 14.4 1403 Grouping in Pivot Table Locked
- Lecture 14.5 Ex 1403 Grouping in Pivot Table Locked
- Lecture 14.6 1404 Slicers Locked
- Lecture 14.7 Ex 1404 Slicers Locked

#### 15 What-If Analysis & Solver Add-In

- Lecture 15.1 1501 Goal Seek (A reverse approach) Locked
- Lecture 15.2 Ex 1501 Goal Seek (A reverse approach) Locked
- Lecture 15.3 1502 One Way Data Table Locked
- Lecture 15.4 1503 Two Way Data Table Locked
- Lecture 15.5 1504 Scenario Manager Locked
- Lecture 15.6 1505 Solver Add-In Locked

#### 16 Automation in Excel (Macros)

- Lecture 16.1 1601 Record your first Macro Locked
- Lecture 16.2 1602 Record another Macro Locked
- Lecture 16.3 1603 Assign a Macro to a Shape, Button, Picture etc. Locked
- Lecture 16.4 1604 Editing or Deleting a Macro Locked
- Lecture 16.5 1605 Security Setting of a Macro Locked