1.Basics of Excel:
Revisiting Excel Basics, Writing Basic Formula like Add, Subtract, Multiply, Divide, Logical &Text formulas.Excel Operators. BODMAS/Formula error checking.Excel Sheet Formatting. Working with Dates – Concept,Correction techniques Date formulas.
2.Relative,Absolute & Mixed Referencing:
Creating Absolute/Mixed References. Problems with Absolute/Relative Cell Referencing.
3.Advanced Charting:
Creating Multiple Chart Type,Primary, Secondary Chart ,Trend line Charting, Spark Line,Dynamic Charting Techniques.
4.Logical Function Or Booleans:
Understanding basic logical functions like IF,OR,AND,COUNTIF,IFS,IFERROR to building complex logical formulas by nesting two or more functions.
5.Look up Functions:
VLOOKUP() – a practical perspective.VLOOKUP() w. TRUE – When & Why? VLOOKUP() with MATCH() 2-D lookup.INDIRECT() + Range Naming for 3-D lookup INDEX() and 2 MATCH() – reverse lookup.
6.Pivot Tables:
Creating, Formatting Simple PivotTables.Page Fieldin a PivotTable.Formatting a PivotTable.Creating/ Modifying a PivotChartIF(), Nested IFs, AND(), OR() Combo Logics; IFERROR().Building Complex Formulas.
7.MIS Reporting & Dashboard Techniques:
Automatic row-wise Subtotal,Gridlines,Data Validation (list),Cell-Range Naming,Grouping.Build Business reports like Sales,Inventory,Salary report,dashboards etc.
8.Data Cleaning:
Data cleaning is first step to do on every report.Techniques like identifying error and blanks,finding and removing duplicates,text to column methods,left,right and mid formulas,trim,case change are included in this topic.
9.Macros Application:
Developer tab; Visual Basic Editor (VBE) Record & Run a Macro.Introduction to VBA code.