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.
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.
- 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.
- Using Auto Fill to Create Data Series
- Using Paste Special
- Using Flash Fill to Convert Text Cases
- Using Flash Fill to Combine Text
- Using Flash Fill to Split Text
- Using Text Functions – LEFT / RIGHT / MID / FIND / LEN / TEXT
- Using Text to Columns Wizard
Using Quick Analysis Tools
- Formatting – Apply Conditional Formatting Rules
- Charts – Insert Recommended Chart
- Totals – Calculate totals automatically
- Tables – Insert table automatically
- Sparklines – Insert mini chart in cells
Using Conditional Formatting
- Applying Highlight Cells Rules
- Applying Top/Bottom Rules
- Creating Data Bar
- Creating Color Scales
- Inserting Icon Sets
- Using the Conditional Formatting Rules Manager
Using Sparklines to Show Data Trend
- Inserting Line Sparkline
- Inserting Column Sparkline
- Inserting Win/Loss Sparkline
- Modifying Sparklines
Using Excel Table for Data Analysis
- Creating and Formatting Table
- Delete Duplicate Record Rows
- Adding Total Row to Excel Table
- Applying AutoFilter and Custom Filter
- Applying Top 10 Filter
- Applying Search Filter
- Using Custom List to Sort Data
- Creating Calculated Columns
- Creating Chart from Excel Table
- Adding Slicer to Excel Table
Using Functions for Data Analysis
- Use Absolute References in Formulas
- Naming Cells and Ranges
- Logical Functions – IF/IFERROR/AND/OR
- VLOOKUP And HLOOKUP Functions
- INDEX and MATCH Functions
- Conditional Logical Functions – SUMIF/COUNTIF/AVERAGEIF
- Database Functions – DSUM / DCOUNT / DMIN / DMAX/ DAVERAGE
- Using Advanced Filter
Adding Automatic Subtotals and Outline
- Insert Subtotals and Grand Totals
- Create Chart from Subtotals
- Create Auto Outline
- Group and Ungroup Outlined Data
- Create Chart from Outlined Data
- Customize Chart with Chart Tools
Using What-If Analysis
- Use Goal Seek to Find A Result
- Create Scenario to Compare Results
- Create Data Table to Show Multiple Results
- Insert Forecast Sheet to Predict Data Trend
Using Data Consolidate
- Consolidating Data from Multiple Worksheets
- Consolidating Data from Multiple Workbooks
- Editing Link to Multiple Workbooks
- Building 3-D Reference Formulas
Using Data Validation and Protection
- Applying Data Validation Rules
- Adding Data Validation Messages
- Creating Drop-Down List
- Protecting Sheet
- Protecting Workbook
- 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.
- 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.”
“Valene is an effective trainer who highlights the key points and illustrate using good examples.”
- 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
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.