Microsoft Excel Basic to Advanced Level

Getting Start with Excel

• Basic Calculation
• Case Change
• Cell Reference: (Relative & Absolute)
• Joint Text
• Text Apart
• Formula Bar
• Rows, Columns, Cells
• Sheets, Zoom
• Hiding/Unhiding Columns, Rows
• Wrap & Orientation
• Alignment

Data Formation

• Format Painter
• Formatting Cell by Fill and Border
• Formatting Cell by Font
• Cell Merging
• Replace All
• Data Validation
• Find and Replace
• Paste Specials
• Freeze Panes
• Subtotal

Data Summarizing

• Advanced Filtered
• Filter Function
• Color Filtered
• Conditional Formatting
• Sum Function
• Consolidation
• Sum Product Function
• Ascending Descending
• Format Table
• Dynamic Slicer In Table
• Data Group/Ungroup

Working With Dynamic Formula

• Protect Sheet/Unprotect Sheet
• Column Function
• Column Array Function
• Picture Reference
• Using Data Bars, Color, Scales and Icon sets for great visual
• Dynamic VLOOKUP
• HLOOKUP
• IF Function
• Autofill Options
• Flash Fill
• Nested IF with other functions

Advance Analysis Tool & Reporting

• LEFT, RIGHT
• Using Data Bars, Color Scales and Icon Sets for great visuals
• Use of AND, OR, NOT
• SUMIF, SUMIFS
• COUNTIFS, AVERAGEIFS
• Pivot Table
• Table, Slicer
• Average Value, Top/Bottom Analysis
• 3D Sum Function
• Concatenate
• Trim

Use Essential Functions For Data Transformation

• Transformation using Excel’s built-in AI
• Joining multiple cells using TEXTJOIN, CONCAT
• Conversion1 – UPPER, LOWER, PROPER, TRIM
• Conversion2 – FIND, SEARCH, SUBSTITUTE
• Extraction – LEFT, RIGHT, MID
• Rounding – ROUND, ROUNDUP, ROUNDDOWN
• Converting a Text number to a computable pure number or date
• Join a text with formatted number or date
• Logical Functions And Operators
• IF() Condition and expression of logical test
• IFS() function in new way
• Using IF with AND, OR
• Using nested IF with other functions
• Dynamic Array Functions For Dynamic Reporting
• Using SORT function
• Using FILTER function
• Using UNIQUE function
• Using SEQUENCE function
• Using multiple array functions in one formula
• #SPILL! errors in Excel

Advanced Techniques Of Data Sorting, Filtering

• Various professional tips on sorting
• Setting custom sorting with different orders
• Sorting left to right
• Using Custom Filter with logic settings