This course will equip participants with essential skills they need for data analysis using Microsoft Excel 365. Participants will learn how to use Excel 365 features more effectively and wisely to analyze their company data quickly and more efficiently. The participants will also learn how to ask Copilot to create charts and PivotTables, or to highlight, sort, and filter your data using simple English sentences.

This 2-days course will train participants in how to prepare source data for effective data analysis using Power Query, how to consolidate multiple sets of data into a summary worksheet, how to analyze data  quickly using Quick Analysis Tools, how to create dynamic dashboard effortlessly with AI tools, how to automate repetitive task with Macro, and many more.

Immediate Takeaways:

1. 1. Equip the participants with a sound grounding of using Microsoft Excel 365 for day-to-day analysis of data.
   2. Gather data and organize data for analysis in business with AI tools.
   3. Transform and clean data efficiently with Power Query and AI tools.
   4. Building Professional dashboard automatically.
   5. Automating repetitive tasks such as filtering, sorting and adding subtotals with Excel Macros.

Chapter 1: Getting Started with AI Tools in Excel

1. 1. Introducing Copilot in Excel
   2. Exploring Key Features in Copilot
   3. Introducing ChatGPT for Excel
   4. Creating Forecast Sheet
   5. Manipulating Data with Flash Fill and Copilot

Chapter 2: Preparing Data for Analysis  

1. 1. Formatting Data with Copilot
   2. Combining and Splitting Data with Copilot
   3. Manipulating Data with CONCAT/ TEXTJOIN / TEXT Functions
   4. Importing Data Sources with Power Query
   5. Automating Data Cleaning with Power Query
   6. Automating Calculations with Power Query

Chapter 3: Performing Quick Analysis

1. 1. Formatting – Apply Conditional Formatting Rules
   2. Applying and Editing Color Scales Rules
   3. Creating New Rules Using Formula and Functions
   4. Charts – Insert Recommended Chart
   5. Totals – Calculate with summary functions
   6. Tables – Insert table and PivotTable automatically
   7. Sparklines – Insert mini chart in cells
   8. Formatting and Editing Sparklines
   9. Analyzing Data with Copilot

Chapter 4: Sorting and Filtering Table with Copilot        

1. 1. Creating and Formatting Excel Table
   2. Removing Duplicate Record Rows
   3. Creating Calculated Columns
   4. Applying Filter in Excel Table
   5. Adding Slicer to Excel Table
   6. Applying Icon Sets Rules in Excel Table
   7. Using Filter by Color & Sort by Color

Chapter 5: Adding Automatic Subtotals and Outline

1. 1. Inserting Subtotals with Copilot
   2. Creating Auto Outline with Copilot
   3. Automating Repetitive Tasks with Excel Macro
   4. Executing Macro with Buttons Form Control

Chapter 6: Data Validation and Protection with Copilot

1. 1. Applying Data Validation Rules
   2. Creating Drop-Down List
   3. Protecting Worksheet to Prevent Changes
   4. Protecting Worksheet to Allow Range Editing
   5. Protecting Worksheet to Allow Edit Ranges with Passwords
   6. Protecting Workbook Structure
   7. Adding Open File Password

Chapter 7: Creating Formulas and Functions      

1. 1. Building 3-D Reference Formulas with Copilot
   2. Logical Functions – IF / IFS / AND / OR
   3. Conditional Logical Functions è SUMIFS / COUNTIFS / AVERAGEIFS / MINIFS / MAXIFS
   4. Database Functions – DSUM / DCOUNT / DCOUNTA / DMIN / DMAX/ DAVERAGE
   5. Apply Advanced Filter with Macro Buttons
   6. Lookup Functions – VLOOKUP / XLOOKUP / INDEX and MATCH Functions
   7. Using Dynamic Array Functions è FILTER / SORT / UNIQUE

Chapter 8: Creating Professional Dashboard with AI Tools          

1. 1. Building Dashboard with Copilot
   2. Building PivotChart Dashboard with Copilot
   3. Inserting Slicer and Timeline to Filter All Pivot Charts

“Well-paced, practical, and immediately useful.”

“The course was well-paced, and I learnt many useful features that I could apply to my work right away.”

-Miss Sim, NEA

“Clear, focused, and full of real examples.”

“Valene is an effective trainer who highlights the key points and illustrates them with good examples.”

— Participant, FairPrice

“Knowledgeable and confident with every question.”

“She is very knowledgeable and answered all our questions with confidence. It’s a great course for building Excel skills.”

— Emil, SPF

“No one gets left behind.”

“Ms Ang was very experienced and knowledgeable, and she shared many practical tips. She made sure none of us got lost, and slowed down to help us catch up whenever we needed it.”

— Jenny Sim, MHA

• This is an intermediate to advanced level course suitable for anyone with knowledge of Data Analytics and looking to acquire solid foundational skills to understand data better.
• Participants must have adequate knowledge of Excel and good working experience using Microsoft Excel 365.
• This course is designed for senior officers, executives and decision makers in government, corporate and private organizations who want to learn how to use Excel data analysis skills effectively to increase the productivity of their work.
• This course is also designed for Managers, Data analysts, Business Owners, Executives and Support staff from all departments, who are assisting in preparation or presentation of charts, graphs, comparing past performance, doing forecasting, budgeting, trend analysis and decision making.

Classroom based demonstration with hands-on Exercises using Microsoft Excel 365.

• Participants are required to bring a laptop equipped with Windows version Microsoft Excel 365
• This course is NOT for MAC version Microsoft Excel users
• Learners are required to bring a wifi-enabled laptop with Microsoft 365 Copilot paid version

Microsoft Excel Expert and Microsoft Certified Trainer: Valene Ang

Valene Ang is an experienced certified IT trainer with over 23 years of experience in training professionals from various organizations. She received her degree in Business Computing from the University of Southern Queensland, Australia. Her Professional qualifications include Advanced Certificate in Training and Assessment (ACTA) and Master Instructor for Microsoft Office Specialist (MOS).

Valene has extensive training and course development experience. She customized many Microsoft Office training courses for corporate clients, assist them in business data analysis and provided dynamic report solutions. Her training focuses on 100% hands-on exercises as well as providing practical solutions to real life Excel related problems.

Valene conducted many Microsoft Excel, PowerPoint and Word training in Singapore, Malaysia and China. Her corporate clients include CPF, PUB, MOM, PSA, IRAS, DFS, MOE, NEA, DHL, SingTel, Singapore Expo, Changi Airport Group, SPRING, FairPrice and many more.