Skip to main content

Microsoft Excel's Power Features

ID : 40303   
« back to classes page
Course Description
Many advanced Excel users such a Mr. Excel have called Power Pivot the Best Thing to Happen to Excel in 20 Years.
Class will begin with a quick overview or refresher on how to create a Table and a basic PivotTable.  We will then move into more advanced PivotTable topics such as creating Measures, Calculated Fields and Calculated Items. And we will look at PivotTables settings.
 
Next, we will learn how to take multiple tables, add them to Excel’s Data Model, relate them and then create a Power Pivot.  We will look at the Get and Transform feature also called Power Query.  Power Query is often called a self-service Business Intelligence (BI) tool.  In newer versions of Excel, Power Query has been renamed Get & Transform because it allows you to connect to a source, combine and clean up data and then analyze the data.
 
We will learn how to write formulas both in Power Query and the Data Model. Many of these formulas will be like what you have used in Excel.  However, these formulas have a different structure and are often more powerful and work better with large data sets. And a major advantage is the ability to get new data or change data and then then simply refresh your query.  If you write formulas using a VLOOKUPS, INDEX and MATCH or IF statement in Excel, with large data sets you will be amazed at the speed of these newer features.  And if you write macros to clean up data, you might find these new features will work much better than a macro.
 
CREATING TABLES AND PIVOTTABLES REFESHER
  • Best Practices for Arranging Data for use with Tables or PivotTables
  • Understand the difference between Filtering data and using Tables
  • Learn the power of Tables
  • PivotTables Basics
  • Learn to Refresh and Modify PivotTables
  • Work with Slicers and Understand How Slicers can help with Dashboards
  • Understand PivotTable Cache
  • Work with PivotTable Timelines
 
INTRODUCTION TO POWER PIVOT
  • What is Power Pivot
  • Importing Tables into the Data Model
  • Linking Tables
  • Using the Related() Function
  • Basic Calculations in the Data Model
  • Creating a PivotTable using Multiple Data Sheets
POWER QUERY aka GET AND TRANSFORM
  • What is Power Query and where is it located in 2013, 2016 and Office 365
  • Types of Data Connections and Power Query Editor Window
  • Review and Change Data Types
  • Loading to the Data Models
  • Data Specific Editing Tools such as Text, Numbers, and Date Tools
  • Filling Data Up and Down
  • Splitting and Combining Columns of Data
  • Adding Conditional Columns
  • UnPivoting Data
  • Merging Data and working with Joins
  • Appending Multiple Data Sets
A BREIF LOOK AT POWER PIVOT DAX FUNCTIONS
  • Creating a Measure using AutoSum
  • Deleting a Measure
  • Working with the New Measures Dialog Box
  • Understanding DAX Syntax
  • DAX Operators
  • DAX Functions such as COUNT ROWS AND COUNTA
  • Logical DAX Functions like IF, OR, AND

Class Details

1 Sessions
Weekly - Tue

Location
Larry Hanna Training & Outreach Facility

Instructor
Darla Cloud 

Tuition: 

$99.00


Registration Closes On
Friday, May 8, 2020 @ 12:00 PM

Schedule Information

Date(s) Class Days Times Location Instructor(s) Instructional Method
5/12/2020 - 5/12/2020 Weekly - Tue 8:30 AM - 4:30 PM Hobbs, Larry Hanna Training & Outreach Facility  Map Darla Cloud  ClassRoom