Mastering Data Presentation & Visualization

Course Duration: 2 Days

Introduction

Microsoft PowerPoint - Mastering Data Presentation & Visualization helps to transform your company data into rich visuals that facilitate new ways of thinking about and organizing your data, so that you can focus on what is important to achieving your goals.

Prerequisites

Basic knowledge of Microsoft Excel is essential with the following pre-requisites:

• Have attended Microsoft Advanced Level OR
• Any pivot table/ excel reporting classes
• Construct basic function using arithmetic operators
• Able to create simple lookup using VLOOKUP function
• Understand or use functions like SUM, COUNT, AVERAGE, MAX, MIN, SUMIF, COUNTIF, AVERAGEIF, IF
• Visualize simple report into charts like column, line, pie & bar chart

Course Objectives

Upon completion of this module, participant should be able to:

• Understand what tools to use to sort out and filter out useful data and learn what are the steps should be taken in orer to create a report
• Extract information and construct a report using the most powerful feature in Excel
• Visualize the pivot report using Pivot Chart
• Understand Power Pivot and not getting confuse Power Pivot with Pivot Table
• Import data from various souces
• Differentiate the differences between Calculate Column and Measure
• Summarize and visualize using Pivot Table by connecting the data source to Power Pivot

Key Content

Module 1: Things You Should Know Before Getting into Reporting

Lesson 1.1: What to do when a big data bomb drops onto your hand?

• Sorting, filtering and cleaning up data
• Say no to blank sapce, alignment and merge and center
• Keep data consistent
• How your data should be arranged in order to be able to pivot

Module 2: Building Your First Report with Pivot Table

Lesson 2.1: 3 steps in creating a report

• Step 1: Convert range into a dynamic table object
• Step 2: Inserting Pivot Table
• Step 3: Understanding how to craft report with row, column, value and filter field

Lesson 2.2: Facelift the Report

• Adjusting the format displayed in the field
• Changing the way value field summarizing the data
• Adding and removing subtotal and grand total
• Trying out different layout for the report
• Styling report with different styles options

Lesson 2.3: Organizing and Sort Out Useful Info

• Filtering and sorting report by values and labels
• Grouping and ungrouping labels
• Refreshing report

Lesoon 2.4: Using Pivot Table Value Outside Pivot Table

• Using GetPivotData to highlight the important value outside the report

Lesson 2.5: Slicing and Dicing Data Using Slicer and Timeline

• Inserting slicer using current report
• Setting up connection to slicer other report
• Hiding data with no values using slicer setting
• Insert timeline to view the report over a certain period

Module 3: Visualizing Reports Using Pivot Chart

Lesson 3.1: Displaying the Trends and Comparison of Data with Pivot Chart

• Converting report into a Pivot Chart
• Changing Chart type

Lesson 3.2: Formatting Pivot Charts

• Hiding, unhiding field button in Pivot Chart
• Turning static chart title into dynamic chart title
• Changing the formats and value displace in label axis
• Labelling charts with its value

Module 4: Ice Breaking Session with PowerPoint

Lesson 4.1: What is Power Pivot?

• Power Pivot 101
• Difference between Power Pivot and Pivot Table
• Who needs Power Pivot

Lesson 4.2: Get Your Power Pivot

• Software and hardware requirement for Power Pivot
• Step by step enabling Power Pivot
• Compatibility problem with Power Pivot

Module 5: Where Should You Begin with Power Pivot

Lesson 5.1: Importing Data into Data Model through Excel

• Link data model from Excel Table
• Copy and paste data into data model
• Paste append & paste replace

Lesson 5.2: Importing Data from Delimited File

• Import data from text file
• Import daa from csv file

Lesson 5.3: Importing Data from Database File

• Load data from access into data model

Lesson 5.4: Talk about Relationship

• Import data from a relational database
• Create relationship between tables with Diagram View

Module 6: Adding Calculated Column Using Exesting Information From Tables

Lesson 6.1: Inserting calculated column

• Using simple arithmetic operation to create calculated column

Lesson 6.2: Using DAX function

• Summarizing data with SUM, COUNT, AVERAGE, MAX & MIN
• Get unique content with DISTINCTCOUNT Function
• Using CALCULATE function to summarize data with different filters
• Using FORMAT, DAY, MONTH & YEAR function to extract date
• Workaround with ROUNDUP, IF to extract fiscal year and quarters

Lesson 6.3: Defnie Hierarchy

• Create hierarchy
• Editing and removing hierarchy
• Use hierarchy in reports to create a drill down effects

Module 7: It's Time to Put Everything Together

Lesson 7.1: Create Reports by Connecting to External Data Source

• Import relational data source from Power Pivot to Pivot Table
• Create a report from multiple tables

Lesoon 7.2: Visualize Report

• Transform all reports into Pivot Chats
• Turn the visualization into dynamic dashboard using slicer

Lesson 7.3: Say No More to Reporting Routine

• Refreshing new data source
• Refreshing dashboard by one click

Target Audience

This course is designed for clerks, officers, executives, supervisors, administrators, managers of all level who want to further enhance their knowledge and practical uses of Microsoft Excel