详情

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 functions using arithmetic operators
Able to create simple lookup using the VLOOKUP function
Understand or use functions like SUM, COUNT, AVERAGE, MAX, MIN, SUMIF, COUNTIF, AVERAGEIF, IF
Visualize simple reports into charts like columns, line, pie & bar chart

Course Objectives

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

Understand what tools to use to sort out and filter out useful data and learn what steps should be taken in order 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 sources
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 space, 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 a report with row, column, value, and filter field

Lesson 2.2: Facelift the Report

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

Lesson 2.3: Organizing and Sort Out Useful Info

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

Lesson 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 the current report
Setting up a connection to slicer another report
Hiding data with no values using the 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 displaced in the labeled axis
Labeling charts with their 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 requirements 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 the data model
Paste append & paste replace

Lesson 5.2: Importing Data from Delimited File

Import data from a text file
Import data from CSV file

Lesson 5.3: Importing Data from Database File

Load data from access into the data model

Lesson 5.4: Talk about the Relationship

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

Module 6: Adding Calculated Column Using Existing Information From Tables

Lesson 6.1: Inserting a calculated column

Using simple arithmetic operations to create a calculated column

Lesson 6.2: Using the DAX function

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

Lesson 6.3: Define Hierarchy

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

Module 7: It's Time to Put Everything Together

Lesson 7.1: Create Reports by Connecting to External Data Sources

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

Lesson 7.2: Visualize Report

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

Lesson 7.3: Say No More to Reporting Routine

Refreshing a new data source
Refreshing dashboard with one click

Target Audience

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