Excel For Data Analysis & Reporting (V2013)
  • 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 allow the participants to learn some powerful new features of Excel 2013 and train the participants how to convert raw data into Excel Table that will enable effective data filtering and sorting, format data in Excel table or PivotTable to highlight important figures using different colors based on pre-defined rules, extract useful information from the database using VLookup & Lookup functions, and advanced filter, group data into category to calculate subtotals and grand totals automatically, outline complex worksheet data to show and hide details, validate data entry to accept only valid data, protect confidential contents from unauthorized access using passwords, linking worksheet formulas and consolidate data from multiple worksheets and workbooks. Participants will also learn how to use PivotTables to analyze large database more efficiently, use PivotCharts to summarize important figures, drop-down list, filter and database functions to create interactive and impressive reports for data analysis.

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. These case studies will also help participant to have better understanding on the topics they learned and know exactly how to apply the skills in their work. Model answers of the case studies and samples Excel worksheets will be given to participants for reference and revision after the course.

This course will be running on PC Based windows software and not Macintosh. 


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)


  • 08-09 Jan 2018
  • 05-06 Mar 2018
  • 19-20 Mar 2018  (Malaysia-Kuala Lumpur)
  • 10-11 May 2018
  • 09-10 Jul 2018
  • 17-18 Sep 2018
  • *SDF for all companies
Day 1
Introducing Excel 2013 Features

Get started by learning about new and improved features in Excel 2013. Using Flash Fill to display data
automatically, Quick Analysis tool, Recommended Chart tool and Powerview for interactive 3D map chart.

Building Table-Driven Reports

Create table-driven reports using Excel data and external data imported from Access database, how to sort data in different ways using custom list, how to filter the table data to show highest cost and revenue, how to filter the table data using Slicers, how to create relationships between two tables and how to create PivotTable from related tables.

Creating PivotTable Reports

Creating cross-tabular PivotTable report from a data source, using the Create Relationship tool to build a
PivotTable from multiple data sources, using the Timeline Slicer to filter records based on dates, drilling
into PivotTable data, adding subtotals by grouping field, calculating sales performance and comparing two
customer lists by using a PivotTable.

Creating PivotChart Reports

Create PivotChart to represent data in a graphical portrayal, how to create a line -column PivotChart to
show different data series, how to build a standalone PivotChart from a PivotTable and how to use Timeline
Slicer to filter PivotChart based on dates.

Enhancing Report Visualization

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.

Case Study 1
Day 2
Understanding Cell References

Use relative references, absolute reference, structured references and defined names to create formulas.

Using Advanced Filter

Learn how to retrieve specific record rows and extract unique values from a column using advanced filter.

Analysing Data with Functions

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 & Daverage) and Conditional Logic functions (Countifs, Averageifs & Sumifs). When an item in a drop-down list is selected, all the related formulas will be updated automatically

Retrieving Data with Functions

Learn how to extract data from a database using VLookup functions, how to create an interactive form using drop-down list and functions. When a product code is selected from the drop -down list, the description and price will be retrieved automatically.

Building Interactive Reports

Develop powerful report that includes features such as auto fill-down formulas, auto calculating subtotals
when conditions are met, applying cell formats based on predefined rules using a drop-down list, update
formula results automatically when data changes, auto rejecting invalid data entry and displaying interactive error alert messages. Participant will also learn how to protect worksheet contents from editing and deleting by unauthorized users.

Case Study 2

About Valene Ang

Valene Ang is a Certified Microsoft Training Specialist who has more than 10 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 and medium sized organisations, government offices and multinational companies such as PSA International, Komatsu Asia Pte. Ltd., DFS Galleria Singapore, Neptune Orient Lines Limited (NOL), National Environment Agency (NEA), Inland Revenue Authority of Singapore (IRAS), CPF Board, Public Utility Board (PUB), Canadian High Commission, British High Commission, Temasek Polytechnic, Republic Polytechnic, Baxter Healthcare Pte. Ltd, and many more.

She has also conducted customized training and one-to one coaching sessions for many companies’ executives on Microsoft Office 2003 and Microsoft Office 2007. 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

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