Course Information
Excel Tables, PivotTables, Power Query and Power Pivot
Parking for SAO, Professional Development courses is in Garage B (1511 San Jacinto Blvd.). The Garage signage may read 1511 San Jacinto or Garage B. The elevator in Garage B is not reliable. If you are unable to walk the stairs, please contact the professionaldevelopment@sao.texas.gov for alternate parking arrangements. Handicapped parking is free at the meters around the downtown area.
A course coordinator will email you a parking permit prior to the course start date. A permit must be displayed or you will be ticketed.
Course Description
Excel users often use tables for filtering and sorting data. But a table is actually a structured data set that has more abilities than just a normal filter/range. In this class you will discover the real power of tables. PivotTables are used by auditors for many reasons but primarily to summarize data. Take your PivotTables to a new level by learning more than just the basics of a PivotTables.
Course Objectives
Use Excel Table and create PivotTables. Users will also be able to use Power Query to connect, import and clean up data and use Power Pivots to create Pivot Tables using multiple data sets. This class will also teach shortcuts that will improve your efficiency when using Excel.
Outline:
CREATING TABLES
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
Work with Tables Styles and Elements
Table Structured Reference Syntax
Table Formulas and Absolute References
Converting Tables
CREATING PIVOTTABLES
Creating PivotTables
Learn to Refresh and Modify PivotTables
Work with Slicers and Understand How Slicers can help with Dashboards
Understand PivotTable Cache
Work with PivotTable Timelines
Changing PivotTable Options
Grouping and ungrouping data in a Pivot
Calculated Fields and Items
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?
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
Prerequisites
Intermediate Advanced Excel Features Beneficial for Auditors & Accountants or an equivalent course.
Instructors
Darla Cloud has been teaching computer classes for over 27 years. She has earned the various Microsoft Office certifications acknowledging her expertise in Microsoft products. Darla is also a Certified Public Accountant and a Certified Technical Trainer. In addition to Darla’s years of teaching, she has over seven years of accounting experience. Darla’s accounting experience and love of teaching help make her an excellent trainer. She has spent years learning tips, tricks and shortcuts that she will pass on during her classes.
Additional Information
TAC Rule 523.142(g) requires the CPE Sponsor to monitor individual attendance and assign the correct number of CPE credits. Participants will be asked to document their time of arrival and departure in compliance with this Rule. Additionally, attendance will be monitored throughout the day and CPE certificates will reflect actual attendance of each participant.
If you are making travel plans to come to Austin, we recommend making "refundable" air and hotel reservations or waiting until 14 days before the class to actually book your reservations. Courses are occasionally canceled or rescheduled due to low enrollment. We determine whether a course has enough participants 16 days prior to the course date. If we cancel or reschedule, we will email the participant and his or her billing contact no later than 14 days before the original class date.
To see answers to our Frequently Asked Questions, visit Texas State Auditor's Office - Professional Development FAQs