Comprehensive MS Excel

This training includes a thorough review of the basics and a thorough discussion of the advanced topics. There are more exercises and participants will be exposed to various practical and complicated scenarios.

1. Basic to Advanced MS Excel Functions

• Text Functions
• Combining Texts (CONCATENATE)
• Extracting Values from Texts (LEFT, RIGHT, MID)
• Changing Case (UPPER, LOWER, PROPER)
• Accessory Text Functions (TRIM, VALUE, SUBSTITUTE)
• TEXTJOIN, TEXTSPLIT, TEXTBEFORE, etc.*
• Logical Functions
• Basic IF Statements
• Nested and Series IF Statements
• Logic Statements with AND and OR
• Math Functions
• Basic Math Functions (SUM, AVERAGE, MAX, MIN, COUNT)
• Named Ranges
• Conditional Math Functions (COUNTIF, SUMIF, AVERAGEIFS, etc.)
• Handling Date and Time Problems
• Calculating Working Days
• Calculating Age and Other Date-Related Problems
• Shortening Long Formulas with Arrays
• Lookup Functions
• VLOOKUP
• Advanced Usage of VLOOKUP
• INDEX and MATCH
• XLOOKUP

2. Data Tab and Other Tools

• Worksheet Formatting Tools (Sort, Filter, Format, Merge, etc.)
• Text-to-Columns
• Conditional Formatting
• Basic Conditional Formatting
• Formula-Based Format
• Data Validation
• Basic Dropdown Menus and Cell Validations
• Advanced Dropdown Menus
• Advanced Data Validation Scenarios
• Subtotal Tool
• Worksheet Security: Locking Cells and File Passwords

3. Data Visualizations with Charts

• Basic Charts
• Combination Charts
• Sparklines
• Using INDIRECT to Create Dynamic Charts
• Special Chart Types
• 4. Analyzing and Presenting Data Using PivotTables and Pivot Charts
• Basic PivotTable Features
• Refresh and Change Data Source
• Creating Calculated Fields
• Constructing Reports using PivotTables
• Grouping Data
• Using Slicers
• Creating Pivot Charts
• Creating Dashboards using PivotCharts

4. Error Handling

• Understanding Errors in Formulas
• Solving Errors in Formulas

5. Introduction to Macros

• Understanding Macro Security
• Macro Recording
• Assigning Macros to Images and Buttons

6. Data Modeling using PowerPivot (Combining and Analyzing Multiple Data Sources)

• Getting External Data using Power Query
• Getting Data from File
• Consolidating Files in a Folder
• Data Modeling in PowerPivot
• Creating PivotTables from PowerPivot