Excel For Data Analysis & Reporting (V2016)
  • S$750
  • 2-days
  • Intermediate Level

About this course

Excel 2016 is a powerful Data Analysis and Reporting tool that provides all the data crunching power including many new features such as forecasting functions to predict future values, multi-select slicer for data filter, automatic table relationship detection, PivotChart drill -down buttons to zoom into details and etc. The proper use of Excel features can greatly enhance the design of your report and help you to analyze your organization’s data more effectively.

If you find yourself struggling with duplicated data, or require an efficient way to analyze and retrieve your data from Excel table, if you deal with budgets, P&L, Sales or other administrative data and need to display the data using interactive Excel charts and reports, if you need to present your data using impressive report format, this is the right course for you.

At the end of each session, a case study which related to the topics learned will be given. Participants will have chance to discuss the case study scenario and come out with the solution.


Who is this course for?

This course is aimed at anyone who has to analyse data of any sort and create solid professional reports for the organization.

Prerequisite:
This is a fast pace Intermediate level course and is not suitable for beginners. This course is for frequent Excel users .You MUST have a thorough understanding of spreadsheet fundamentals and be confident creating complex functions and formulas and creating simple charts.

Notice:
This course will be running and taught based on the PC Windows software and not Mac versions. (All Laptops and materials will be provided)

  • 15-16 Nov 2018
  • 25-26 Feb 2019
  • 09-10 May 2019
  • 22-23 Aug 2019
  • 11-12 Nov 2019
  • *SDF for all companies
Day 1
Building Dynamic Spreadsheet Reports

Participant will learn about relative and absolute references and how to use defined names to create user-friendly and meaningful formulas.
Participant will learn how to create table-driven reports using Excel data and external data, sort data in different ways using custom list, filter the table data to show highest cost and revenue, filter the table using Slicers and create relationships between two tables.

Enhancing Report Visualizations

Participant will learn how to enhance the report visualization by using Conditional Formatting rules to highlight duplicate records, highest values and lowest values in the report, using icons and sparklines to show trend in the report and using Data Bar to create in-cell data visualization.

Creating PivotTable Reports

Participant will learn powerful report features such as creating PivotTable report from different data sources, using the Automatic relationship detection to discover and create relationships among the tables used for your workbook’s data model, using Timeline to filter date field, using multi-select Slicer to filter multiple items, adding subtotals by grouping field, calculating sales performance and comparing two customer lists by using a PivotTable

Creating PivotChart Reports

Participant will learn how to create PivotChart to represent data in a graphical portrayal, create a line-column PivotChart to show different data series, use multi-select Slicer to filter PivotChart, use Timeline to filter PivotChart based on dates and out across groupings of time and other hierarchical structures within your data.

Case Study 1

The case studies are based on real-life business scenarios and problems encountered by Excel users in daily jobs.

Day 2
Using Flash Fill and Quick Analysis

Participant will learn how use Flash Fill to split text, change text cases, combine text, format numbers, format dates and display data automatically, and use Quick Analysis tool to analyze the data faster and using Sparklines to show trend in the report.

Using Advanced Filter

Participant will learn how to retrieve specific record rows and extract unique values from a column to another worksheet or workbook based on multiple pre-defined conditions.

Analysing Data with Functions

Participant will learn how to use absolute references and defined names in the formulas, how to calculate total sales and average sales for each region as well as how to count the number of records that match specific conditions by using Database functions (DSUM, DCOUNT, DCOUNTA, DMIN, DMAX & DAVERAGE) and Conditional Logic functions (COUNTIFS, AVERAGEIFS & SUMIFS).

Retrieving Data with Functions

Participant will learn how to extract data from a database using VLOOKUP function and how to retrieve data from a table using INDEX and MATCH functions.

Designing Interactive Form

Participant will learn how to design interactive form that includes drop-down lists and ability to reject invalid data entry automatically with error alert messages. Participant will also learn how to protect worksheet contents from editing and deleting by unauthorized users.

Case Study 2

Participants will have chance to design a user-friendly dynamic form. The case study helps participant to apply the skills and functions they learned from the training.

About Valene Ang

Valene Ang is a Certified Microsoft Training Specialist who has more than 19 years of training experience and working with companies to improve their data processes.

Her qualifications include a Bachelor’s in Business Computing, Microsoft Certified Trainer (MCT), Certified Instructor of Microsoft Certified Application Specialist (MCAS), and Master Instructor of Microsoft Office Specialist (MOS). Valene also holds an Advanced Certificate in Training and Assessment (ACTA) - a national qualification awarded by WDA (Workforce Development Authority) in Singapore.

She has trained different levels of management executives from small-medium size organisations, government offices and multinational companies such as PSA International, Rio Tinto, SingTel, DFS Galleria Singapore, Canon Singapore, HP Singapore, CPF Board, MOE, HPB, HDB, National Environment Agency (NEA), Public Utility Board (PUB), Inland Revenue Authority of Singapore (IRAS), SPRING Singapore, Temasek Polytechnic, Republic Polytechnic, Nanyang Polytechnic, Singapore Expo, Changi Airport Group, DHL, Barclays Capital, Far East Organizations, Intrack Market Service (Malaysia), DENZA (ShenZhen) and many more.

She has also conducted customized training and one-to one coaching sessions for many companies’ executives on Microsoft Office 2003/2007/2010/2013/2016 versions. She is a much-sought-after trainer, judging by the very good evaluation she received from her past participants.

 

I learnt many useful tips on using Excel for work! I didn’t know the existence of many functions until I came for this course.

Lim Jing Jun, Head, Spring Singapore

A great course that is well-structured and relevant to my work. Definitely opened my eyes to new and better ways of analysis data!

Crystal Lee, Marketing Manager, Singtel

Course notes were comprehensive and Trainer was clear.

Tang Ya Xuan Alissa, Assistant Manager, National Council of Social Service have chance to discuss the case study scenario and come out with the solution.

Unique to this Course

Free Excel Templates and Spreadsheet

Cutomised case study for In-house companies to suits your business needs

Free comprehensive list of Excel resources

After Course support