This 2-days course will train participants how to prepare source data for effective data analysis, use AutoFill to generate data series, use Text to Columns Wizard to convert dates, create dynamic tables and charts that can be updated automatically using a button, lookup and extract data from a table, consolidate multiple set of data into a summary worksheet, apply conditional rules to format data, perform What-if Analysis with Scenario Manager, Goal Seek and Data Table, using new features such as Forecast Sheet, Quick Analysis, Flash Fill, Recommended Charts and new Charting Tools.

This course will equip participants with essential skills they need for data analysis using Microsoft Excel 2016/2019/365 Users. Participants will learn how to use Excel 2016 features more effectively and wisely to analyze their company data quickly and more efficiently.

• Equip the participants with a sound grounding of using Microsoft Excel 2016/2019/365 Users for day to day analysis of data.
• Gather data and organize the data for analysis in business.
• Utilise data analytics for performance measurement.
• Skills in using Excel What-If Analysis tools for generating different scenarios of business environment and aiding decision making.
• Learn the best practices in chart generation and interpretation.
• Practicing a list of tasks that help hone data analysis skills in generating required information.

Preparing Data for Analysis

1. Using Auto Fill to Create Data Series
2. Using Paste Special
3. Using Flash Fill to Convert Text Cases
4. Using Flash Fill to Combine Text
5. Using Flash Fill to Split Text
6. Using Text Functions – LEFT / RIGHT / MID / FIND / LEN / TEXT
7. Using Text to Columns Wizard

Using Quick Analysis Tools

1. Formatting – Apply Conditional Formatting Rules
2. Charts – Insert Recommended Chart
3. Totals – Calculate totals automatically
4. Tables – Insert table automatically
5. Sparklines – Insert mini chart in cells

Using Conditional Formatting

1. Applying Highlight Cells Rules
2. Applying Top/Bottom Rules
3. Creating Data Bar
4. Creating Color Scales
5. Inserting Icon Sets
6. Using the Conditional Formatting Rules Manager

Using Sparklines to Show Data Trend

1. Inserting Line Sparkline
2. Inserting Column Sparkline
3. Inserting Win/Loss Sparkline
4. Modifying Sparklines

Using Excel Table for Data Analysis

1. Creating and Formatting Table
2. Delete Duplicate Record Rows
3. Adding Total Row to Excel Table
4. Applying AutoFilter and Custom Filter
5. Applying Top 10 Filter
6. Applying Search Filter
7. Using Custom List to Sort Data
8. Creating Calculated Columns
9. Creating Chart from Excel Table
10. Adding Slicer to Excel Table

Using Functions for Data Analysis

1. Use Absolute References in Formulas
2. Naming Cells and Ranges
3. Logical Functions – IF/IFERROR/AND/OR
4. VLOOKUP And HLOOKUP Functions
5. INDEX and MATCH Functions
6. Conditional Logical Functions – SUMIF/COUNTIF/AVERAGEIF
7. Database Functions – DSUM / DCOUNT / DMIN / DMAX/ DAVERAGE
8. Using Advanced Filter

Adding Automatic Subtotals and Outline

1. Insert Subtotals and Grand Totals
2. Create Chart from Subtotals
3. Create Auto Outline
4. Group and Ungroup Outlined Data
5. Create Chart from Outlined Data
6. Customize Chart with Chart Tools

Using Data Consolidate

1. Consolidating Data from Multiple Worksheets
2. Consolidating Data from Multiple Workbooks
3. Editing Link to Multiple Workbooks
4. Building 3-D Reference Formulas

Using Data Validation and Protection

1. Applying Data Validation Rules
2. Adding Data Validation Messages
3. Creating Drop-Down List
4. Protecting Sheet
5. Protecting Workbook
6. Adding Open File Password

• This is an intermediate level course suitable for anyone with minimal or no prior knowledge of Data Analytics and looking to acquire solid foundational skills to understand data better.
• Participants must have adequate Excel knowledge and a good working experience using Microsoft Excel 2016/2019/365 Users.
• 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.

“Course is in good pace and I learnt many useful features which I can apply to my work quickly.”
~ NEA

“Valene is an effective trainer who highlights the key points and illustrate using good examples.”
~ FairPrice

Classroom based demonstration with hands-on Exercises using Microsoft Excel 2016/2019/365 Users.

• Participants are required to bring a laptop equipped with Windows version Microsoft Excel 2016/2019/365 Users
• This course is NOT for MAC version Microsoft Excel users

Valene Ang is an experienced certified IT trainer with over 19 years of experience in training professionals from various organisations. She received her degree in Business Computing from the University of Southern Queensland, Australia. Her Professional qualifications including 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 provide 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.