Mastering Data Presentation & Visualization
Previous | 15 / 22 | Next |
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 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
- 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
- Filtering and sorting report by values and labels
- Grouping and ungrouping labels
- Refreshing report
- Using GetPivotData to highlight the important value outside the report
- 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
- 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
- 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
- Import data from text file
- Import daa from csv file
- Load data from access into data model
- 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
- 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
- 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
- Transform all reports into Pivot Chats
- Turn the visualization into dynamic dashboard using slicer
- Refreshing new data source
- Refreshing dashboard by one click