Mastering Data Presentation & Visualization

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.

Pre-requisites

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

• 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 Power Pivot

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 Charts
• 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.