# Microsoft Excel

### Introduction and Basic Brush-up of MS Excel and Excel Back-End Operations

1
Start-up with MS Excel, Quick review on MS Excel Customize Ribbon, Quick Access Toolbar, Mini Toolbar.
2
Conversion of Excel files to PDF.
3
Introduction of Excel shortcut keys: Chart will be provided by Advanced Excel.
4
Introduction to Excel Worksheet, Row, Column, Cells etc.
5
Detailed discussion on Excel design, Back-End working structure and Excel Options.
6
Use of Basic Operators
7
Introduction to the Data and Data Formats.
8
Inserting, Deleting, Moving, and linking the data in between the multiple sheets.

### Start-up with Formulas: Part-I AND Working With Name Ranges

1
Text Functions
2
Combine multiple formulas
3
Writing of the Complex formulas for improvement in formulas writing skills.
4
Date and Time Functions

### Basic If Formulas

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

### Super Advanced Level If Formulas: (Huge Formula Writing)

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
• Use of Name Manager.
• Create Name Ranges Automatically.

### (Go To Special)

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

### Start-up With Formulas: Part-III: Lookup Functions

1
Lookup Functions
2
Basic Level Lookup Formulas
• Discussion on Lookup Functions, Use of Lookup, Vlookup, Hlookup, Index, Indirect Match, Offset, Choose, Rows, Columns.
• VLookup with Name Range.
• Hlookup with Name Range.

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.

### Super Advanced Level Lookup Formulas: (Huge Formula Writing)

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

### Magic with Array Formulas

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
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.

### Structuring Of The Data and What If Analysis

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.

### Conditional Formatting and Working with Charts

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.

### Pivot Table and Pivot Charts with Slicer and Handling of Errors In Excel

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?

### Start with VBA Macro Programming: Recording of Macros and Writing of Macro

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
9
Introduction to Personal Macros.

### VBA Macro Programming: Programming Writing Concepts and Data Types

1
Defining Variables to Macro.
2
Variable Naming rules, Declaring, Initializing Variables, Option Explicit, Object Variables
3
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.

### VBA Macro Programming: Working with Loops and Range Objects

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.

### VBA Macro Programming: Writing of Formula in VBA and Error Handling

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?

### VBA Macro Programming: Advanced VBA Functions

1
User Define Function (UDF)
2
3
Working with VBA Events: Discussion on VBA Events, what are Events, How and when to use the VBA Events.

### VBA Macro Programming: Form Vs. Active X Controls and Advanced User Forms

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