Mastering Data Presentation & Visualization
Mastering Data Presentation & Visualization
Course Duration: 2 DaysIntroduction
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
- 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
- Filtering and sorting reports by values and labels
- Grouping and ungrouping labels
- Refreshing report
- Using GetPivotData to highlight the important value outside the report
- 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
- 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
- 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
- Import data from a text file
- Import data from CSV file
- Load data from access into the data model
- 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
- 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
- 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
- Transform all reports into Pivot Chats
- Turn the visualization into a dynamic dashboard using a slicer
- 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 ExcelInquiry - Mastering Data Presentation & Visualization