Course Code: msexcelbspk
Duration: 10 hours
Prerequisites:

To benefit the most from this training program, participants should have:

  • Basic computer skills, including familiarity with using a mouse and keyboard.
  • A general understanding of basic mathematical concepts.
  • Some experience with navigating and using Microsoft Office applications is helpful but not required.

 

Overview:

This Excel training program is designed to equip participants with the essential skills needed to effectively use Microsoft Excel for data analysis, management, and presentation. The program covers fundamental and advanced topics, ensuring that participants can confidently utilize Excel's powerful features to improve their productivity and decision-making processes. Through a series of structured lessons, hands-on exercises, and real-world examples, participants will gain practical experience in using Excel to solve everyday business problems.

Course Outline:

This course is divided into seven comprehensive lessons, each focusing on specific Excel functionalities and techniques:

Lesson 1: Using Formulas

  • Objective: Learn to create and use formulas to perform calculations and manipulate data efficiently.
  • Key Topics:
    • Understanding Excel references and creating formulas.
    • Techniques for re-using formulas with AutoFill and Flash Fill.
    • Introduction to common statistical functions like SUM, COUNT, COUNTA, and PRODUCT.

Lesson 2: Working with Functions

  • Objective: Explore a variety of specialized functions to perform complex calculations and data analysis.
  • Key Topics:
    • Working with ranges and named ranges.
    • Using specialized functions like COUNTIF, SUMIF, and AVERAGEIF.
    • Employing logical functions (IF, AND, OR, NOT) for decision-making.
    • Manipulating text with text functions such as CONCAT, LEFT, RIGHT, MID, LEN, UPPER, LOWER, PROPER, TEXTJOIN, and TEXTSPLIT.

Lesson 3: Working with Lists

  • Objective: Master the techniques for managing and analyzing data lists.
  • Key Topics:
    • Sorting and filtering data to organize and isolate specific information.
    • Querying data with database functions like DSUM, DCOUNT, and DAVERAGE.
    • Outlining and subtotaling data for better data summarization.

Lesson 4: Using Lookup Functions and Formula Auditing

  • Objective: Utilize advanced lookup functions to search and retrieve data efficiently.
  • Key Topics:
    • Implementing powerful lookup functions such as INDEX MATCH and XLOOKUP.

Lesson 5: Using PivotTables and PivotCharts

  • Objective: Gain proficiency in summarizing and presenting data using PivotTables and PivotCharts.
  • Key Topics:
    • Creating and configuring PivotTables.
    • Analyzing data with PivotTables.
    • Presenting data visually with PivotCharts.
    • Filtering data with timelines and slicers.

Lesson 6: Sharing and Protecting Workbooks

  • Objective: Learn to share workbooks securely and protect sensitive data.
  • Key Topics:
    • Protecting worksheets and workbooks to prevent unauthorized changes.

Lesson 7: Using External Data

  • Objective: Import and manage data from various external sources to enhance data analysis capabilities.
  • Key Topics:
    • Importing data from CSV files, web pages, and database tables.