Are you an individual who wants to join a public class? CLICK HERE.
Are you an office person who needs a proposal for your team?  [email protected]

Advanced MS Excel Training
Level 1

This course will delve into the essential functions and tools within Microsoft Excel to empower you with the skills needed to efficiently manage and analyze data. From mastering basic to advanced functions like text manipulation, logical operations, and lookup functions, to leveraging Excel tools for data management and analysis, this course provides a structured approach to Excel proficiency.

Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Office 365, Microsoft 365

Objectives
  • Learn a range of text, logical, and math functions from basic to advanced levels, including text manipulation (CONCATENATE, LEFT, RIGHT, MID), logical operations (IF statements, AND, OR), math functions (SUM, AVERAGE, MAX, MIN), and lookup functions (VLOOKUP, INDEX, MATCH).
  • Use Excel tools for managing data efficiently, including conditional formatting for visual cues, data validation for accuracy, subtotal tool for summarization, and pivot tables for in-depth data analysis, grouping, filtering, and presenting insights.
  • Equip learners with the skills necessary to proficiently utilize PivotTables in Excel, enabling them to analyze and present complex datasets effectively

Course Outline
Part 1: Basic to Advanced MS Excel Functions
  • Text Functions:
    • Concatenating Texts
    • Extracting Values from Texts (LEFT, RIGHT, MID)
    • Changing Case (UPPER, LOWER, PROPER)
    • Exploring Other Accessory Text Functions (TRIM, VALUE, SUBSTITUTE)
  • Logical Functions:
    • Understanding Basic IF Statements
    • Exploring Nested and Series IF Statements
    • Combining Logical Statements with AND and OR
  • Math Functions:
    • Mastering Basic Math Functions (SUM, AVERAGE, MAX, MIN, COUNT)
    • Utilizing Named Ranges
    • Exploring Conditional Math Functions (COUNTIF, SUMIF, AVERAGEIFS, etc.)
    • Shortening Long Formulas with Arrays
  • Lookup Functions:
    • Understanding VLOOKUP
    • Advanced Usage of VLOOKUP
    • Utilizing INDEX and MATCH Functions


Part 2: Managing Data Using Excel Tools
  • Conditional Formatting:
    • Implementing Basic Conditional Formatting
    • Exploring Formula-Based Formatting
  • Data Validation:
    • Setting up Basic Dropdown Menus and Cell Validations
  • Subtotal Tool:
    • Understanding its Functionality and Application


Part 3: Analyzing and Presenting Data Using PivotTables
  • Basic PivotTable Features:
    • Creating and Manipulating PivotTables
    • Refreshing and Changing Data Sources
    • Grouping Data within PivotTables
    • Using Slicers to Enhance Data Analysis
    • Utilizing PivotCharts for Visual Representation


Part 4: Introduction to Macros (if time permits)
  • Understanding Macro Security:
    • Exploring Macro Security Settings
  • Macro Recording:
    • Recording and Implementing Macros for Task Automation and Efficiency.
 
Training Approach
  • Incorporate hands-on practice sessions and real-world scenarios helps solidify theoretical concepts and fosters practical application of Excel skills.
  • Provide multiple opportunities for applying the topics they learned per chapter.

Related Courses

Advanced Excel Level 2

Extensive MS Excel

Basic MS Excel

​Macros and VBA Programming

​Excel for Data Analytics