Comprehensive MS Excel (Basic to Advanced)

This program develops participants’ ability to use Microsoft Excel as a powerful analytical and reporting tool rather than just a spreadsheet. Starting with essential functions and progressing toward advanced tools such as PivotTables, macros, and PowerPivot, the course equips learners with practical techniques for cleaning data, automating calculations, and presenting insights effectively. Participants will work through real-world examples to strengthen their ability to transform raw data into meaningful information for decision-making.

Training Duration: 2 days

Objectives

• Strengthen participants’ mastery of Excel formulas and functions for solving common business problems.
• Develop skills in organizing, cleaning, and validating datasets using Excel tools.
• Enable participants to analyze large datasets using PivotTables, charts, and dashboards.
• Introduce automation techniques using macros to improve efficiency.
• Provide foundational knowledge of data modeling and external data connections using Power Query and PowerPivot.

​
Who the Topic Is For
This course is intended for professionals who regularly work with spreadsheets and want to improve their ability to analyze and present data. It is particularly suitable for analysts, supervisors, finance staff, operations personnel, administrative professionals, and anyone responsible for reporting, tracking, or decision support using Excel.

Methodologies

1. Instructor-Led Demonstration and Guided Practice
   The facilitator demonstrates techniques step-by-step while participants replicate them using prepared datasets. Concepts are immediately reinforced through guided exercises and short challenges.
   
2. Hands-On Data Analysis Activities
   Participants apply Excel tools to realistic business scenarios such as sales tracking, performance reporting, and data consolidation. Exercises emphasize problem-solving, interpretation of results, and best practices in spreadsheet design.
   

Inclusions:

• Join in Cubao: The training includes fast food lunch, AM and PM snacks, printed handout, printed certificate, unlimited coffee and biscuits
• Join via Zoom: The training includes PDF certificate and PDF handout.

Outline

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

5. Error Handling

• Understanding Errors in Formulas
• Solving Errors in Formulas

6. Introduction to Macros

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

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