Excel For Data Analysis & Reporting (2016/365 version)
  • S$750
  • 2-days
  • Intermediate Level

About this course

Excel is a powerful Data Analysis and Reporting tool. You could use Excel to gain new insights into the information and data that you work with in your job. 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.

This course will train participants how to use Excel better and design the spreadsheet report to be more interactive and effective for data analysis purposes.


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)

*Important disclaimer for participants attending the 2016 version:
Participants are encouraged to inquire which version they have at work to realise which features may, or may not, be present on their work-based application.

Look at the FILE tab and search for ACCOUNT. It will either highlight ‘PRODUCT ACTIVATED’ or ‘SUBSCRIPTION’. Product Activated applications are typically a one-time purchase license and will not carry the same updated features as the Subscription versions. Companies may vary in polices of software purchase depending on the suitability of products. Subscription versions tend to be continually updated

As an example, one the features not available in the Product Activated version is IFS and MAXIFS Functions. Impress Training will be using the Subscription version for training purposes.

  • 09-10 May 2019
  • 15-16 Jul 2019
  • 22-23 Aug 2019
  • 11-12 Nov 2019
  • *SDF for all companies
Day 1
Data Cleaning with Flash Fill and Power Query

Flash Fill is a time-saving feature which fills in data automatically when a pattern is provided. You can use it to extract data, insert data, format text and concatenate text. With Power Query, you can search for data sources, make connections, and then transform that data in ways that meet your needs.

Data Analysis 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).

Data Filtering with Advanced Filter

Advanced Filter is really helpful when it comes to finding data that meets two or more complex criteria such as extracting matches and differences between two columns, filtering rows that match items in another list, finding exact matches including uppercase and lowercase characters.

Data Extracting with Lookup Functions

VLOOKUP function lookup a value in a table by matching on the first column and retrieve the data (result values) in any column to the right. INDEX and MATCH functions lookup a value in a table by matching value in any column and retrieve the data from any column in the table.

Data Visualization

Quick Analysis tool allows you to quickly create graphs, sparklines, PivotTables, PivotCharts, and summary functions by just clicking on a button. Conditional formatting is a feature which allows you to apply a format to a cell or a range of cells based on certain criteria such as highlight duplicate records, highest values and lowest values. A Sparkline is a tiny chart in a worksheet cell that allows you to quickly visualize the overall trend of a set of values or to indicate maximum and minimum values.

Data Validation and Protection

Data validation rule is used to restrict the type of data or the values that users enter into a cell and create drop-down list. Hyperlink is a reference to a specific location (cell or worksheet), document or web-page that the user can jump to by clicking the link. With worksheet protection, you can make only certain parts of the sheet editable and users will not be able to modify data in any other region in the sheet.

Day 2
Building Interactive Spreadsheet Report

Excel table enables you to manage and analyze a group of related data easier. You can create calculated columns, add slicer to Excel table and create relationships between two tables.

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.

Creating Dynamic PivotTable Report

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 Impressive PivotChart Report

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

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

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