M365 Pivot Tables & Data Modelling Training Course

Course

In this virtual, Instructor-Led course, you use the more advanced features of Excel with PivotTables and Data Modelling. This course may be used as a prelude to Power BI as it uses the Power Query Editor and Measures with DAX formulas.

Course Aim

Enable delegates to examine PivotTables and Data Modelling and allow for better use of the application and manipulation of data.

Target Student: Anyone requiring a more in-depth knowledge of PivotTables and Data Modelling.

Prerequisites: Delegates should have an advanced knowledge of Excel before entering onto this course.

Objectives:

•Create and use PivotTables, Slicers, PivotCharts and Timelines
•Create Data Models
•Install and use the PowerPivot Add-in

Creating PivotTables

• Understand PivotTables and the benefits
• Create a Recommended PivotTable Report
• Create a blank PivotTable Report
• PivotTable Contextual Tabs & Options
• Amend the fields in a PivotTable
• Change PivotTable Name
• Format Numbers
• Group Data
• Change the Data Section
• Expand or Contract Data
• Refresh the PivotTable
• Clear the PivotTable
• Select the whole PivotTable, Labels or Values
• Move a PivotTable
• Switch Field Headers and Field List on or off
• Show Page data on a separate worksheet
• Display/Hide Subtotals and Grand Totals
• Change the PivotTable layout
• Insert or Remove blank rows
• PivotTable Style Options
• Format a PivotTable
• Edit Default Layout
• Add Calculations to a PivotTable
• Pivot Tool Gear Settings Create and use PivotTable Slicers
• What are Slicers?
• Create and use Slicers
• Stop the Slicers from Filtering
• Move the Slicer
• Change the Slicer Size and Button Size
• Report Connections
• Delete the Slicer Create and use PivotTable Charts
• What is a PivotTable Chart?
• Create a PivotTable Chart
• PivotTable Chart Analyse, Design and Format Contextual Tabs
• Use the Chart Filters
• Move the Chart
• Chart Selection Pane
• Filter Connections
• Delete the Chart Create and use PivotTable Timelines
• What are PivotTable Timelines?
• Create a PivotTable Timeline
• PivotTable Timeline Contentual Tab
• Make Changes to the Timeline
• Change the Timeline Caption & Style
• Rearrange the Timeline
• Add/Delete another Timeline
• Timeline Selection Pane
• Move the Timeline
• Align and Group/Ungroup the Timeline
• Resize the Timeline
• Show/Hide Headers, Scrollbar, Selection Label, Time Level
• Report Connections
• Delete the Timeline
Data Modelling & the PowerPivot Add-in
• What is the Data Modelling Feature in Excel and its benefits?
• What is the PowerPivot Add-in and what are its benefits?
• Installing the PowerPivot Add-in
• PowerPivot Tab explained
• Create a PowerPivot with related tables
Formatting Columns, Sorting Data and Add Calculations Sum, Average etc.
Switch the Calculation Area on or off
Add a Calculated Column (Measures using DAX Formulas)
Creating a new Measure
Create KPI’s
• Close the PowerPivot window
• Import (Get Data) from various sources
• Transform Data Using Power Query Editor
• Power Query Editor Tabs Explained
• Power Query Properties and Applied Steps
• Close and Load Data from the Power Query Editor
• Add More Tables to the Data Model
• Queries & Connections
• Create Tables and PivotTables
• Build/Edit Relationships in Diagram View (Creating Hierarchy)
• Delete, Activate/Deactivate a Relationship
• Add More Data to the Data Model
• Use Workbooks with no Tables or Relationships

Duration: 1 Day

Cost: £195 + Vat