Excel For Data Analysis & Reporting

About the course

*Eligible for SkillsFuture Credit

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.

Participants will have chance to discuss the case study scenarios and come out with the best solution. These case studies also help participant to review the topics learned so that they know exactly how to apply the skills they learned in their daily work. Model answers of the case studies will be given to participants for reference.

Who is this course for?

  • This course is aimed at anyone who has to analyze business data and create impressive professional reports for their organization.
  • This is an Intermediate level course and is not suitable for beginners who only know some basic functions and seldom use Microsoft Excel at work.
  • This course is for frequent Excel users who know how to create simple PivotTable and perform simple data analysis tasks.
  • You MUST have a thorough understanding of spreadsheet fundamentals and be confident creating complex functions and formulas and creating simple charts.
  • This course is not for those who never use or create chart, PivotTable and PivotChart.

Course content

Day 1

Visualizing Data with Quick Analysis Tools

Quick Analysis tool allows you to quickly create graphs, sparklines, PivotTables, PivotCharts, and statistical functions by just clicking on a button.

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

Participants will learn how to:

• Use Data Bar to spot larger and smaller numbers quickly
• Use Color scales to show data variation
• Use Icon sets to present data
• Apply and Edit Conditional Formatting rules
• Use Sparklines to show data trend
• Use the Recommended Chart Tools
• Use Chart Element, Chart Styles and Chart Filter tools
• Add Trendline to show data trend and forecast data
• Use Table to sort, filter and summarize data

Working with Data List

Advanced Filter is helpful in 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.

Participant will learn how to:

• Sort data by multiple levels
• Sort data based on custom list
• Use Custom List in AutoFill
• Use Search box criteria to filter data list
• Use Advanced Filter to filter data list
• Use Advanced Filter to copy records to another worksheet
• Use Advanced Filter to copy only selected columns to another worksheet
• Insert automatic subtotals for related data

Consolidating Data

Data consolidate feature allows you to assemble and summarize data from separate worksheets or workbooks into a master worksheet or other workbooks, so that you can more easily update and aggregate as necessary.

Participants will learn how to:

• Consolidate Data from Multiple Worksheet
• Consolidate Data from Multiple Workbooks
• Use 3-D Reference Formulas to Consolidate Data

Using Advanced Functions

Conditional logical functions allow you to sum, average and count cells that meet multiple criteria. Database functions work with an Excel database to sum, average and count based on a given set of conditions.

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, MATCH and SUMPRODUCT functions lookup a value in a table by matching value in any column and retrieve the data from any column in the table.

Participants will learn how to:

• Use absolute references and relative references
• Use defined names to create meaningful formulas
• Use Logical functions: IF, AND, OR and IFS
• Use Statistical functions: COUNTIFS, AVERAGEIFS, SUMIFS, MAXIFS and MINIFS
• Use Math & Trig functions: RANDBETWEEN, SUMIFS, SUMPRODUCT
• Use Database functions: DSUM, DCOUNT, DCOUNTA, DMIN, DMAX and DAVERAGE
• Use Lookup functions: VLOOKUP, XLOOKUP, INDEX and MATCH

Day 2

Preparing Data for Analysis

Power Query is a user-friendly business intelligence tool that that simplifies the process of importing data from different source files and sorting them into an Excel sheet in the most convenient and usable format.

Participants will learn how to:

• Load Table Data into Power Query Editor
• Use Power Query Editor to Change Data Types
• Import data from Text/CSV file using Power Query
• Import data from Microsoft Access database using Power Query
• Combine Multiple Files from a Folder using Power Query

Cleaning Up Data for Analysis

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.

Power Query includes many pre-build transformation functions that can be used to clean up source data for analysis and reporting. These transformations can be as simple as removing a column or filtering rows, or as common as using the first rows as headers, merge columns, split column, and so on.

Participants will learn how to:

• Use Flash Fill to split text, change text cases, combine text, format numbers and dates
• Use TEXT, LEFT, RIGHT, MID, LEN, FIND function to format and split data
• Use Text To Columns Wizard to split text string and format dates
• Use CONCAT, TEXTJOIN function to combine data
• Use Power Query Editor to merge and split columns

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

Participants will learn how to:

• Format Data as Excel Table
• Remove duplicated records from table
• Create calculated columns in Excel table
• Create new Conditional Formatting Rules
• Sort data by Icon Sets
• Filter data to show the highest or lowest data
• Filter Excel table records using Slicers
• Create Chart from Excel Table

Creating Impressive PivotTable and PivotChart Report

PivotTable is one of the most powerful and widely used features of Microsoft Excel. It allows you to summarize, analyze and visualize data in various ways that can provide deep insights.

PivotChart is a visual representation of PivotTable that helps to summarize, visualize, and analyze the data in a structured and organized format. It is a wonderful way to visualize data in an effective and efficient manner.

Participants will learn how to:

• Create PivotTable and PivotChart from Excel table
• Use different PivotTable Report Layout
• Hide and show Subtotals and Grand Totals
• Create Report Filter Pages from Report Filter
• Using Column Labels and Row Labels filters
• Summarize values using Statistical functions
• Calculate Percent of Totals
• Calculate Running Totals
• Group dates and numbers
• Customize PivotChart using the new Chart Tools
• Add Slicer and Timeline to PivotChart
• Connect multiple PivotTables to Slicer and Timeline

Case Study

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


Please read carefully...

This course will be running and taught based on thePC 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 torealisewhich features may, or may not, be present on their work-based application.

Look at theFILEtab and search forACCOUNT. 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 365 subscription version for training purposes.

Valene Ang

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)…

The course has been extremely useful in building my understanding for Power BI. Will be handy for my day-to-day work.
Tan Kok Joo, Director
Mind-blowing professional training course that helped my team with techniques to structure/plan a good presentations before diving into it.
Valerie Lee, Head, Corporate Development
This training broaden my view on what value I can contribute to my company and the goals I need to set. Thank you.
Vony AgustianaKhristanti, Purchasing Officer
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
Relevant and insightful and know-how for engaging audience effectively. I like the pace of the course coupled with plenty of hands-on exercises
Daniel Ch’ng, Project Director
Great 2 days lesson to learn how to fully manipulate Powerpoint and see the application in a different light.
Melvin Kwong, Account Manager