Adv.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 Like: + - / * ^ %.
7. Introduction to the Data and Data Formats.
8. Copy, Cut, Paste, Hide, Unhide, Link the Data in Rows, Columns and Sheet.
9. Inserting, Deleting, Moving, and linking the data in between the multiple sheets.
10. Introduction to the Paste Special all Option. (Formulas, Values, Formats, Comments, Validation, All using source themes, All except Borders, Column Widths, Formula and Number formats, Values and Number Formats, None, Add, Subtract, Multiply, Divide, Skip Blanks and Transpose)
11. Format Cells, Rows, Columns and Sheets.
12. Protection of Cells, Rows, Columns and Sheets.
13. Password protection to the Worksheet, Sheets, Rows, Columns, and Cells.
14. Printer Properties and Page Setup (Page, Margin, Header/Footer and Sheet) for Printing.
15. Insert Logo to your worksheet while printing.

TEXT FUNCTIONS:

1. Concatenate, Dollar, Left, Mid, Right, Lower, Upper, Proper, Replace, Rept, Find, Search, Substitute, Trim, Trunc, Convert.

DATE AND TIME FUNCTIONS: 

1. Date, Datevalue, Day, Day360, Minutes, Hours, Now, Today, Month, Year, Yearfrac, Time, Weekday, Workday, Networkdays.

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.

ADVANCED LEVEL IF FORMULAS:

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 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: Discussion on Name Ranges and Apply the Name Ranges on Cell and the combination of Cells.
7. Use of Name Manager: Creating, Editing, and Deleting of Names.
8. Create Name Ranges Automatically.

MATH & TRIG FUNCTIONS: 

1. ABS, Aggregate, Power, Product, Rand, Randbetween, Round, Rounddown, Roundup, Subtotal, Sum, Sumif, Sumifs, Sumproduct, Trunc.

STATISTICAL FUNCTIONS:

1. Average, AverageA, Averageif, Averageifs, Count, CountA, Countblack, Countif, Countifs, MAX, MAXA, MIN, MINA, Small, Large. 
   Use of Sumif, Sumifs, Count if, Countifs, Averageif, Averaefis with the Name Ranges.

INFORMATIVE AND OTHER FUNCTIONS:

1. Cell, Info, ISBLANK, ISERROR, ISEVEN, ISODD, Rank.

USE OF TOOL FIND & SELECT: DISCUSSION ON EXCEL’S VERY IMPORTANT TOOL “FIND & SELECT” (GO TO SPECIAL) INCLUDING ALL OPTIONS:

1. Comments, Constants, Formulas, Blanks, Current region, Current array, Objects, Row differences, Column differences, Precedents, Dependents, Last cell, Visible cells only, Conditional formats, Data validation.

BASIC LEVEL LOOKUP FORMULAS:

1. Discussion on Lookup Functions, Use of Lookup, Vlookup, Hlookup, Index, Indirect Match, Offset. Choose.
2. Vlookupwith Name Range.
3. Hlookup with Name Range.

ADVANCED LEVEL LOOKUP FORMULAS:

1. Vlookup with Match, Hlookupwith 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. Vlookupwith arrays.
3. Hlookup with arrays.
4. Vlookup with other Multiple Lookup functions.
5. Hlookupwith other Multiple Lookup functions.
6. Creation of Hyperlink.

MAGIC WITH ARRAY FORMULAS:

1. Detailed Discussion on Arrays:
2. What are the Array Formulas, How Array Formulas work and Use of the Array Formulas, Acceptance of Array Formulas in today’s scenario.
3. Basic Array Formulas Example.
4. Linking of Spread sheet with the help of Array
5. Array in Multiple formulas.
6. Array with Lookup functions.
7. Advanced Use of formulas with Array.

STRUCTURING OF THE DATA & 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. Use of Filter to extract the unique and desired data.
4. Use of Custom Filter to fulfil the desired conditions.
5. Use of Advance Filter to fulfil the multiple desired conditions.
6. Import the data from the multiple applications to Excel.
7. Use of Text to Columns for Rearrangement of Data.
8. Remove Duplicates from Data.
9. Use of Data Validation and Consolidation.
10. Use of Data Validation as a magical tool.
11. Data of Grouping, Ungrouping and Subtotal.
12. What if Analysis: Detailed Discussion On What if Analysis. Analysis of Data by using Scenario Manager and Data Table and Goad Seek.

CONDITIONAL FORMATTING AND WORKING WITH CHARTS:

1. Conditional Formatting: Detailed discussion on conditional formatting.
2. Conditional Formatting with multiple cell rules and Top/Bottom Rules.
3. Conditional Formatting with Data Bars. Colour Scales and Icon Sets.
4. Conditional Formatting on Desired Output, Create New rules, Manage the rules.
5. Apply any formula to Conditional formatting.
6. Choose Formatting as Table and different Cell Style.
7. Working With Charts: Detailed discussion on graphically presentation of Data by using Charts.
8. Presentation with different kind to Basic charts like Column Charts, Line Charts, Pie Charts, Bar Charts, Scatter Charts
9. Preparation of Advanced Level of Charts:Gnatt Chart, Bubble Chart, Speedo Meter Chart. Pareto Chart
10. Introduction to Multiple switches and buttons: Like Form Control, Combo Box, Check Box, Spin Box, List Box and Option Box.
11. How To Use Switches with Offset function effectively in Charts.
12. Preparation of the Interactive Charts.
13. Preparation of the Dashboards: Different of Kind of Advanced level of Dashboards
14. Use of Formulas Like Offset, Match, Sumif, Sumifs and many more to prepare the Dashboards.
15. Use of Data Validation in Charting.
16. Use of Sparkline to your Sheet, Interactive Sparklines
17. Working with 2axis and 3axis charts.

PIVOT TABLE AND PIVOT CHARTS WITH SLICER AND HANDLING OF ERRORS IN EXCEL :

• Table and Work on layout of Pivot Table.
• Creating Groups, Insert additional Start With Pivot Table, Do the Multiple Field Setting in Pivot Table.
• Pivot form the Multiple Source of Data.
• Pivot from the Multiple Data Ranges.
• Pivot from the Name Range.
• Do the Juggling of Data in Pivot Calculated Field in Pivot Table.
• Perform the % calculation on the basis of multiple fields.
• Start-up with Pivot charts.
• Insert slicer in Pivot Table and Pivot charts.
• Use of Tool “Error Checking”.
• Removing Duplicates.
• Trace Precedents, Trace Dependents.
• Discussion of Errors and handling of multiple errors like. #DIV/0! , #N/A, # REF, #NAME, #VALUE, # NULL, #NUM and ########Error.
• What is Circular References error? How to rectify Circular Reference?