Course Information

Date21 - 22 Nov 2019, Thu - Fri
Time9:00 AM - 5:00 PM
VenueRoyal Plaza on Scotts Singapore Hotel Including Tea breaks and International Gourmet Buffet Lunch at Award winning Carousel Restaurant (Halal Certified)
FeeS$ 850 (Nett)
ContactJaslyn
9767 9686 / 6278 9785
jaslyn@cbsgroup.com.sg
Register

Registration is on a 1st come 1st serve basis. Register early to avoid disappointment.

Testimonials

I've been making full length films and documentaries for many years. We use professional equipment and…
Nay Win Tun, Video Editor
Perfect Your Elevator Pitch through Digital Storytelling
Very through and comprehensive
– NAFA
1 day Workshop on The Motivational Conversation - Rodney Woulfe
I will recommend this to my colleagues who have negative attitude towards their life, so that they can…
– Nanyang Technological University
1 day Influencing Difficult People in the Workplace by Ms Nancy Yeo
Nancy is very clear, engaging and insightful. She is able to tap on her vast experience to share with…
– WWW Cargo Pte Ltd
1 day Influencing Difficult People in the Workplace by Ms Nancy Yeo
‹ back to previous page
21
&
22
Nov 2019

Microsoft Excel 2016 Essential Skills for Data Analysis & Interpretation

- Master Data Interpretation & become expert data analyst using Microsoft Excel 2016

Data Analysis is the process of systematically inspecting, cleansing, transforming, and modeling data with the goal of discovering useful information and conclusions, as well as supporting decision-making. Microsoft Excel 2016 provides useful tools needed in doing data cleansing, transforming and analyzing such as Text to Columns, remove duplicates, Flash Fill, Quick Analysis and many more.

COURSE OBJECTIVES

COURSE OBJECTIVES

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. Participants will learn how to use Excel 2016 features more effectively and wisely to analyze their company data quickly and more efficiently.

IMMEDIATE TAKEAWAYS

IMMEDIATE TAKEAWAYS
  • Equip the participants with a sound grounding of using Microsoft Excel 2016 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.

COURSE OUTLINE

COURSE OUTLINE
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

WHO SHOULD ATTEND

WHO SHOULD ATTEND
  • 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.
  • 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.

TESTIMONIALS

TESTIMONIALS

“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

TRAINING METHODOLOGY

TRAINING METHODOLOGY
Classroom based demonstration with hands-on Exercises using Microsoft Excel 2016.

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

ABOUT THE TRAINER

ABOUT THE TRAINER

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.

Join Our Mailing List

10 Years of successful track records

Our clients share our passion for people development. We take a long term view of relationships and the
deep and sustained impact has been greatly fulfilling. We have trained over 26,000 learners to date.

AIRBUS
AVIVA
CISCO
DHL
ExxonMobil
mapletree
NUS - National University of Singapore
PSA - The World's Port of Call
PUB - Water for All Conserve, Value, Enjoy
sembcorp
Singapore Airlines
Singapore Tourism Board
Standard Chartered
StarHub
WDA - Singapore Workforce Development Agency