Data analysis is a process of inspecting, cleaning, transforming, and modelling data with the goal of discovering useful information, suggesting conclusions, and supporting decision making. The use of powerful Excel data analysis tools that allow you to speed up your day-to-day work as well as assist the decision makers in making sound decision.

Objective

This 2-day course will train participants how to import external data using Microsoft Query, prepare source data for the use of data analysis, convert dates and perform date calculations, 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, perform What-if Analysis with Scenario Manager, Goal Seek and Data Table, using new features such as Flash Fill, Recommended Charts. Recommended PivotTables and new Charting Tools.

Outline

Chapter 1: Linking And Consolidating Data
1.1 Building Link Worksheets Formula
1.2 Consolidating Data From Multiple Worksheets
1.3 Building 3-D Reference Formulas
1.4 Building Link Workbooks Formula
1.5 Consolidating Data From Multiple Workbooks
1.6 Editing Link To Multiple Workbooks

Chapter 2: Cleaning Up Data Using Functions
2.1 Convert Dates Using Text To Columns Wizard
2.2 Split Content Of A Cell Into Different Columns
2.3 Convert Text Case
2.4 Copy Characters
2.5 Using Flash Fill
2.6 Perform Date Calculations

Chapter 3: Using Advanced Functions To Analyse Data
3.1 Using Cell References
3.2 Naming Cells And Ranges
3.3 Using Database Functions
3.4 Using Conditional Logic Functions
3.5 Using Vlookup And Hlookup Functions
3.6 Using Index And Match Functions

Chapter 4: Using Data Validation
4.1 Understanding Data Validation Settings
4.2 Understanding Data Validation Messages
4.3 Using Data Validation To Restrict Data Entry
4.4 Creating Drop-Down List

Chapter 5: Implementing Protection
5.1 Protecting Worksheet
5.2 Protecting Workbook Structure
5.3 Protecting Workbook

Chapter 6: Using What-If Analysis
6.1 Using Goal Seek
6.2 Using Scenario Manager
6.3 Using Data Table

Chapter 7: Using Excel Table
7.1 Creating And Formatting Table
7.2 Sorting And Filtering Data
7.3 Delete Duplicate Record Rows
7.4 Adding Total Row To Excel Table
7.5 Adding Calculated Column To Excel Table
7.6 Creating Dynamic Chart From Excel Table
7.7 Formatting Chart With New Charting Tools

Chapter 8: Using Conditional Formatting
8.1 Understanding Conditional Formatting
8.2 Applying Duplicate Values Rule
8.3 Applying Top/Bottom Rules
8.4 Applying Data Bar Rules
8.5 Applying Icon Sets Rules
8.6 Managing Conditional Formatting Rules
8.7 Sort By Color
8.8 Filter By Color
8.9 Deleting Conditional Formatting Rules

Chapter 9: Using Advanced Filter To Copy Data
9.1 Filter The List In Place
9.2 Copy Filtered Records To Another Location
9.3 Copy Unique Records To Another Location

Chapter 10: Creating Slicer-Driven Report
10.1 Using Recommended Pivottables
10.2 Formatting Numbers In Pivottable
10.3 Creating A Pivottable From Another Pivottable
10.4 Filtering Data In Pivottable
10.5 Sorting Values In Pivottable
10.6 Creating And Customizing Pivotchart
10.7 Creating Pivottable To Group Dates
10.8 Creating And Formatting Slicer
10.9 Connecting Pivottable Report

Who should attend

Methodology

This is a 2-day hands-on course. The trainer will walk through the topic step-by-step. You will be provided with exercise files on every topic to effectively apply what have been taught.

Testimonial

"The workshop was well-planned and designed. Well-versed in the subject taught."

"One of the most useful course I have attended; my teammates and I will try to sign up for similar courses conducted by Ms Valene. She is an excellent trainer, She is patient, explains concepts well and paces the class well. Excellent facilitator."

"Trainer's teaching is excellent."

"The workshop is very good, practical examples and exercises. One of the best Excel courses I've ever attended thus far. The trainer is very knowledgeable and has a very good rapport with the group. "

"Perfect pace. I have learned a lot - Everything is useful. Enjoyed your lessons, Valene. Thank you."

"Learned an in-depth with regards to excel as my works use a lot of excel. Overall is good. The trainer is very knowledgeable and experienced."

"Pace is fast, but I am able to follow and I have achieved the outcome of the course! The workshop is good for managing excessive data! The trainer is very knowledgeable and experienced. Able to address all questions posed."

“All the course Learning objectives are met. The trainer has great knowledge in how to apply the skills in real-life setting”

"The training manual was very effective and I could follow them step by step without fail, I can Apply the skill learnt at work"

"The training manual was very detailed and I could refer to it and well attend the class all time."

"The trainer was helpful and clear in her explanation."

Valene Ang's Profile

Valene Ang is a Microsoft Certified Trainer (MCT) with a degree in Business Computing. Her Professional qualifications including Advanced Certificate in Training and Assessment (ACTA) and Master Instructor for Microsoft Office Specialist (MOS). She has broad experience in corporate IT training and course materials development.

Valene has a broad experience in customizing Microsoft Office training programs, developing customized course outlines and course materials, assisting corporate clients in business data analysis and providing dynamic report solutions. Her training focuses on providing practical solutions to real life Excel problems.

Valene conducted many Microsoft Office training in Singapore, Malaysia and China. Her corporate clients include NOL, PSA, IRAS, DFS, CPF, PUB, MOM, MOE, NEA, DHL, SingTel, Singapore Expo, Changi Airport Group, SPRING Singapore, Nanyang Polytechnic, Singapore Polytechnic, Republic Polytechnic, Denza (ShenZhen) and etc..