1

Text Functions

2

Combine multiple formulas

3

Writing of the Complex formulas for improvement in formulas writing skills.

4

Date and Time Functions

1

If, If with OR, If with AND, If with AND OR, If with OR AND.

2

If with Trim, If with Concatenation, If with Left, Mid, Right.

3

If with Other formulas, Complex formulas writing in If.

1

Nested If (For Multiple Conditions), If condition used more than one time in the same formula.

2

Production Store Management template with the help of Nested If. (Example)

3

Nested if with left, Mid, Right

1

Nested if with Multiple Text Functions in a single formula.

2

Preparation of the TAX Calculation Sheet by Nested if (Based on Indian and US Tax)

3

Nested if with Other Critical Lookup Formulas (To be discussed after Super Advanced Look-up Class).

4

Nested if with Look-up (To be discussed in Super Advanced Look-up Class).

5

Other Complex Nested if to be discussed in Class.

6

Introduction to Name Manager

1

Math and Trig Functions

2

Use of Sum product as lookup and in various activities.

3

Statistical Functions

4

Use of Sum if, Sumifs, Count if, Counties, Average if, Averaefis with the Name Ranges.

5

Informative AND Other Functions

1

Lookup Functions

2

Basic Level Lookup Formulas

1

Vlookup with Match, Hlookup with Match.

2

Lookup from the Left side of Data. (Lookup, Index and Match)

3

Vlookup with Left, Mid and Right.

4

Vlookup with Multiple Text Functions.

5

Hlookup with Multiple Text Functions.

1

Vlookup from multiple Data Ranges. (With the help of Logical and Lookup Functions).

2

Vlookup with arrays.

3

Hlookup with arrays.

4

Vlookup with other Multiple Lookup functions.

5

Hlookup with other Multiple Lookup functions.

6

Creation of Hyperlink.

1

What are the Array Formulas, How Array Formulas work and Use of the Array Formulas, Acceptance of Array Formulas in today’s scenario?

2

Basic Array Formulas Example.

3

Linking of Spreadsheet with the help of Array

4

Array in Multiple formulas.

5

Array with Lookup functions.

6

Advanced Use of formulas with Array.

7

Use of Sum product in critical situations.

1

Use of Sorting to arrange the data in ascending and descending order. Addition and deletion of levels to sort the data on multiple parameters.

2

Use of Sorting to arrange the data in Left to Right Order. Addition and deletion of levels to sort the data on multiple parameters.

3

What if Analysis: Detailed Discussion On What if Analysis. Analysis of Data by using the Scenario Manager and Data Table and Goad Seek.

1

Working With Charts: Detailed discussion on graphical presentation of Data by using Charts.

2

Presentation with a different kind to Basic charts like Column Charts, Line Charts, Pie Charts, Bar Charts, Scatter Charts

3

Preparation of Advanced Level of Charts: Gantt Chart, Bubble Chart, Speedo Meter Chart. Pareto Chart, Waterfall Chart.

4

Introduction to Multiple switches and buttons: Like Form Control, Combo Box, Check Box, Spin Box, List Box and Option Box.

5

How to Use Switches with Offset function effectively in Charts?

6

Preparation of the Interactive Charts.

7

Preparation of the Dashboards: Different of Kind of Advanced level of Dashboards

8

Use of Formulas Like Offset, Match, Sum if, Sumifs and many more to prepare the Dashboards.

9

Use of Sparkline to your Sheet, Interactive Sparkline’s

10

Working with 2axis and 3axis charts.

11

Use of Data Validation in Charting.

1

Start with Pivot Table, Do the Multiple Field Setting in Pivot Table.

2

Pivot form the Multiple Source of Data.

3

Pivot from the Multiple Data Ranges.

4

Pivot from the Name Range.

5

Insert slicer in Pivot Table and Pivot charts.

6

Use of Tool “Error Checking”.

7

Removing Duplicates.

8

Trace Precedents, Trace Dependents.

9

Discussion of Errors and handling of multiple errors like.

10

What is Circular References error? How to rectify Circular Reference?

1

Introduction of VBA

2

Display of Developers Tab and VB Editor.

3

Macro Security.

4

Introduction to Macro Recordings

5

Working on Various Examples of Macro Recorder.

6

Introduction to the writing of basic VBA codes.

7

Working in the Visual Basic Editor

8

Saving Off Your Macro.

9

Introduction to Personal Macros.

1

Defining Variables to Macro.

2

Variable Naming rules, Declaring, Initializing Variables, Option Explicit, Object Variables

3

Scope and lifetime of variables.

4

Discussion on different Types of Variables.

5

Use of Local Window.

6

Working on Programming, Understanding on VBA Language.

7

Introduction of Message Box in Macro.

8

Introduction of Input Box in Macro.

9

Use of decision making statements in Macro by using: If and Else conditions, And /OR conditions, Select-Case and Not statements.

1

Introduction of the Loops in Macro.

2

Use of Different type of loops

3

Working with Range Objects: Selecting, Copying and Pasting the Ranges and extract the data on the same Worksheet level.

4

Selecting, Copying and Pasting the Ranges and extract the data on different Worksheet level in the same Workbook.

5

Selecting, Copying and Pasting the Ranges and extract the data on different Workbook Level.

6

Consolidation from the multiple worksheets to a single worksheet by using range object and multiple loops.

7

Consolidation from the multiple workbooks to a single workbook by using range object and multiple loops.

8

Use of Multiple Inner Loops in complicated situations.

1

Introduction of programming concepts for writing the Excel Formulas in to the Program.

2

Difference between the A1 and R1C1 style of writing a formula in VBA.

3

Look at the style A1 and R1C1 and decide which is better.

4

Find out the better solution to write the formulas in VBA.

5

Writing of the basic formulas in to VBA.

6

Writing of the complex formulas in to the VBA.

7

Writing of Logical formulas and Lookup formulas in VBA.

8

Freezing of the formula in VBA.

9

Other useful formulas to be write in Class.

10

Types of Errors: Design Errors, Compile Errors, Runtime Errors. Logical Errors.

11

How to Debug the Errors?

1

User Define Function (UDF)

2

ADD-IN: Discussion on ADD-IN: What is ADD-IN, Use of ADD-IN and How to create ADD-IN.

3

Working with VBA Events: Discussion on VBA Events, what are Events, How and when to use the VBA Events.

1

Using User Form Controls

2

User Form Techniques and Tricks

3

Form Control vs Active X control, Accessing Your Macros through the User Interface. VBA User Forms