Data Analytics Excel Training

January 18, 2024 9:00 am - 4:00 pm MST

Wells Fargo Connections Center

CPE Hours - 6

Join us on January 18th to expand your excel and power query skills! Register early and save!!! Early bird pricing ends 12/18.

In person and virtual options available

Registration Closes January 15th

Wells Fargo Connections Center

2800 S Price Road

Chandler AZ

9:00 am - 4:00 pm

6 Hours

Pracitical Skills a Data Worker Needs

This course was developed to teach real world tech skills in Excel to the people who encounter data in spreadsheets in their day-to-day work. This course gives you a foundation for working with data. We will dive into the practical skills needed by a data worker and we will show you how to approach and handle data with Microsoft Excel. This course is not for an Excel beginner.

Functions & Formulas

Basic date & time functions (TODAY, NOW, DAY/MONTH/YEAR)
Advanced date functions
Text conversion functions
Parse and cleaning functions
Logical functions
IF function
Absolute/relative referencing
VLOOKUPs and other fun functions

Conditional Formats and Data Commands

Work with conditional formats
Data commands
Sort and filter data
Remove duplicates

PivotTables

Understand PivotTables
Create a PivotTable
Connect data to a PivotTable
Build a pivot
Filter pivot data
Drilldown on a pivot for filtered data
Format pivots
Create a PivotChart
Visualize pivot data with PivotCharts
Update the data
Linking data from PivotTables to worksheet

This course was developed to teach real world tech skills in Excel to people who encounter data in spreadsheets in their day-to-day work. If you work with macros, clean data, or use multiple workbooks to create reports, you need to know about Power Query! We dive into practical skills and show you how to approach and handle data with Power Query. This course is not for an Excel beginner.

Get Data

Queries & connections
Access queries & connections
Navigate Power Query Editor

Data Cleaning & Applied Steps

Delete unnecessary columns
Use Choose Columns to remove columns
Change column order
Change data case
Replace values
Convert data types
Remove spaces & non-printable characters
Use gear for adjustments
Change step order
Name steps
Delete steps

Sort and filter data in Power Query

Sort data in Power Query
Filter data in Power Query

Custom Columns & Calculations

Index columns
Custom columns
Conditional columns
Duplicate columns
Merge columns
Split columns

Working with Applied Steps and adjustments

Use the gear for adjustments
Change the step order
Name a step
Delete a step

Advanced Editor

Access Advanced Editor
Understanding “M” code

Getting Data into Excel Sheet

Loading data to workbook
How to disconnect data from the source

Refresh and changing the data source

Refresh inside of the Power Query Editor
Refresh the data from the sheet
Change the data source

Thank you for your interest in the Data Analytics Excel Training. Registration is currently closed. Please use the following information to contact us if you have any questions:

IIA Phoenix Chapter: Chapter80@iiachapters.org

Phoenix Chapter Home Page Contact Us Privacy Policy