Microsoft Excel 2016/2019/365 Users Essential Skills For Data Analysis & Interpretation

– Master Data Interpretation & become expert data analyst using Microsoft Excel 2016/2019/365 Users

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/2019/365 Users 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

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.

Immediate Takeaways
  • 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.
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?
  • 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.
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

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
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.

SPF

The course is very informative and was very well delivered by the lecturer.

SPF

SPF

The course contents were clear and precise. The instructor was able to keep the class highly motivated and engaged.

SPF

ICA

From this course it clears up my misconception regarding counseling in terms of the outcome that shouldn’t come from me. I realized my role of counseling people. Thank you for the valuable sharing.

ICA

IDA

Mr. Praga is very good and thus makes one thinks hard and deep. Thank you.

IDA

Singapore Customs

The role-play was helpful. MICA This was a very interactive and engaging workshop. I get to learn a lot.

Singapore Customs

Singapore Customs

Has gained several insights into the topic and has been beneficial with the several coaching model available to work with.

Singapore Customs

MCYS

Good trainer, enjoyed his course.

MCYS

SPF

A very good course with spiritual enlighten and very pleasing to my soul. Well done! Brilliant lecturer!

SPF

HAS

Trainer is good who love to share his knowledge and methods.

HAS

NUHS

Rodney has provided alternatives to handle employees in different work context. Through effective communication tactics and K.S.A approach, employees faces can be understood and furthermore improve on their performance.

NUHS

NUHS

Rodney was clear with his presentation and was able to provide comfortable alternative solutions to manage difficult situations.

NUHS

Anonymous

Dr Gabriel, thank you so much. Will live by your parting advice: It is not knowledge but consciousness of body language! Thanks again. Great course! Gabriel is really an experienced person.

Anonymous

SEAP

I was greatly interested in this course. Thank you.

SEAP

AIA

This is an excellent program to improve the aesthetics and impact of PowerPoint without making it looks cheap or amateurish. I’m amazed at how my skills improved by almost 300%.

AIA

MOM

This course is very engaging delivery.

MOM

ITE

Will recommend to colleagues to pick up this course.

ITE

SPF

Very enlightening and enjoyable session. Thank you for the knowledge.

SPF

NYP

Previously attended michael’s course. Finds that he constantly updates materials and customizes to suit class needs. Addresses case studies brought up in class to make it appreciable throughout.

NYP

PT Bukit Makmur Mandiri Utama

This course is good with the role play to practice.

PT Bukit Makmur Mandiri Utama

Training Strategies

A good course.

Training Strategies

MCYS

This course has added value to my current work.

MCYS

Ngee Ann Polytechnic

The course is useful and has a number of useful conflict resolution skills that I will most certainly use.

Ngee Ann Polytechnic

Ministry of Education

Comprehensive with good and realistic practical tips, stimulating and thoughts provoking.

Ministry of Education

Ministry of Manpower

Trainer is engaging, enthusiastic and knowledgeable.

Ministry of Manpower

Republic Polytechnic

This course is good and fun.

Republic Polytechnic

Jurong Town Council

This course has been excellent and an eye-opener. It is most definitely very informative and appropriate. It is also a reality check for all of us.

Jurong Town Council

Flotech Controls Pte Ltd

Very good course – practical!

Flotech Controls Pte Ltd

Singapore Prisons

Enjoyed the entire course and the rainer is very good in applying examples relevant to the workplace.

Singapore Prisons

Ideal Foods Pte Ltd

Very practical and interesting.

Ideal Foods Pte Ltd

People’s Association

Nancy is a wonderful instructor. She is very knowledgeable.

People’s Association

Institute of Education

Nancy is already a motivator. Allows me to see things at different angles. Totally agree with her that a lot of situation is about managing oneself. Enjoy her course totally.

Institute of Education

KK Hospital

Great course. Exceptional Facilitator.

KK Hospital

Singapore Prisons

Overall is good. I have learnt a lot. Thank you so much Nancy.

Singapore Prisons

Thye Hua Kuan Hospital

Good sharing of experiences by the trainer. I have enjoyed this 1 day seminar.

Thye Hua Kuan Hospital

Nanyang Technological University

I will recommend this to my colleagues who have negative attitude towards their life, so that they can maybe change their mindset after that.

Nanyang Technological University

WWW Cargo Pte Ltd

Nancy is very clear, engaging and insightful. She is able to tap on her vast experience to share with us ideas and ways to deal with different kinds of people. Great!

WWW Cargo Pte Ltd
VOCO Orchard / Holiday Inn, Orchard
05 - 06 Feb 2024
VOCO Orchard / Holiday Inn, Orchard
20 - 21 Jun 2024

Time

  9:00 am - 5:00 pm

Duration

2 Days

Cost

  S$ 850 (Nett)

VOCO Orchard / Holiday Inn, Orchard

Course Fees Including Tea Breaks and Buffet Lunch

Submit a Comment

RSS
Follow by Email