New user? Register as member

Excel Pivot Table Masterclass: Excel Reporting & Dashboards

Responding to market conditions in the shortest possible time

Objective

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

• Create a dashboard using the functions in Pivot Table andPower Pivot
• Visualize your data using Pivot Chart and Conditional Formatting
• Apply all functions of Pivot Table

Pre-requisite
Participants must

• Have experience using a pivot table
• own a worksheet of data with at least 50 rows

Outline

Pivot Table Compliant Data Set

1. Required Data Layout
2. 3 ways to connect to your data sources
3. Have your pivot table data layout reviewed

Analyze and Summarize your data with Pivot Table

1. Create a Pivot Table using Tables
2. Create a Pivot Table using a CSV file
3. Create a Pivot Table using the files in a folder
4. Using more than one summation value in Pivot Table
5. Changing Summary Calculations
6. Sorting Data in Pivot Table
7. Filtering Data in Pivot Table
8. Grouping values in Pivot Table
9. Grouping dates in Pivot Table
10. Counting unique records
11. Add and use Slicers for presentation
12. Using a timeline in Pivot Table
13. Creating trend in Pivot Chart

Mastering presentation of data

1. Creating a bar chart in Pivot Table
2. Controlling more than one Pivot Table with slicers
3. Attract your audience with professional-looking pivot charts
4. Effective use of visuals to attract attention.

Who should attend

The training is specially designed for managers and executives who want to summarize and present their data in a dashboard.

Profile 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