New user? Register as member

Intermediate Excel Training

Microsoft Excel is used for different purposes and one of the common purposes is to use downloaded data for reporting. If you are working with downloaded data and wish to know how to effectively summarize the data and present it in a professional looking Pivot Table report, this is the course for you.

Pre-requisite
Participants must have experience in

• Working with one spreadsheet with a few hundred rows of data
• basic Auto-filter function
• Sorting data
• Creating simple worksheet functions

Objective

At the end of this course, you will be equipped with the skills to

• “Clean” and prepare your data for reporting
• Summarize your data with Pivot Table
• Apply Data Visualization with Pivot Table and Chart

Outline

Mastering Worksheet functions

1. Extract useful data from a column using LEFT, RIGHT and MID
2. Beautify your text with the UPPER, LOWER and PROPER
3. Combine 2 data ranges into 1 using VLOOKUP
4. Summarize data with SUMIFS and COUNTIFS
5. Organize data into multiple groups using IF and Nested IF
6. Absolute and Relative Referencing
7. Calculating Duration and Anniversaries using DATEDIF
8. Extract Year, Month and Day with Date functions

Save time with Excel functions

1. Master the Remove Duplicates functions
2. Convert dates to multiple formats using Text-to-Columns
3. Remove duplicates

Analyze and Summarize your data with Pivot Table

1. Framework of a Pivot Table
2. Create a Basic Pivot Table Report
3. Rearranging a Pivot Table Report
4. Customizing a Pivot Table
5. Changing Summary Calculations
6. Adding and Removing Subtotals
7. Sorting in a Pivot Table
8. Filtering in a Pivot Table
9. Create Report Filters

Mastering presentation of data

1. Visual presentation of your data using Pivot chart
2. Impress your audience with customize charts
3. Effective use of visuals to attract attention.

Who should attend

The training is ideal for those (such as Directors, Head’s, Leaders, Managers and executives across all industries and all professionals) who deal with large volume of data & excel in their day to day work for reporting, and need excel tools for further enhancing their efficiency and productivity

Particular of Jason Khoo

Jason Khoo is a Microsoft Certified Trainer (MCT), ACTA certified trainer of 20 years. He is a Microsoft Certified Power BI Data Analyst Associate and a data analytics practitioner of 30 years. During his time of employment, he has worked in Tibs (currently SMRT), MobileOne Ltd, 3M and Virgin Mobile.

During his tenure, he has worked with multiple databases, extracted and downloaded information from systems and run numerous analysis. These analyses spanned across many departments such as Finance, Sales, Marketing, Human Resources, Payroll, etc.

After his employment, he worked as a data analytics consultant for many companies, including Discovery Asia, 3M, Tanah Merah Country Club, Johnson and Johnson Vision Care, National Environmental Agency, Timberland, etc.
His data analytics skills combined with Excel allowed him to deliver many reports to the clients without them having to incur software cost. These reports include Dashboards, Top 10 charts, Risk Analysis, Business Models, KPIs, etc.

As a trainer, he is passionate about transferring his knowledge and imparting his skills to his participants. To day, he has trained thousands in his face to face workshops and had conducted talks for thousands as well.

Trans-Island Bus Services Ltd (Tibs) –Operations and Revenue Analytics, Passengers Travelling Behaviour Analytics
• Daily tracking of revenue,
• Prepare KPI for LTA,
• Fare revision analysis

Mobile One Asia Ltd (M1) – Financial analytics, Customer Call Usage Analytics
• Full company budgeting and business planning,
• Monthly Analysis,
• KPI reporting

3M – Sales, Cost and Inventory analytics
• Daily and Monthly Sales Forecasting,
• Analysis of Marketing Budget,
• Analysis of Inventory and shipments.
• Cost Analysis for new products (BOM analysis)

Virgin Mobile – Retail, Marketing, Payroll, Admin, IT, Finance Analytics
• Daily KPI reporting,
• Monthly Analysis for Finance, Payroll,
• Developed Fully Automated Business Model driven by KPI