PMI North Carolina Chapter

On Demand Learning

Session 10 – Introducing the Excel Power BI tools: Power Query and Power Pivot

Description:
Participants will be introduced to two of the Excel Power BI tools. They will learn Power Query to pull data into Excel from many different data sources. Then, they will learn how to create Power Pivot Tables to summarize and analyze large chunks of data.

Objectives:
*Introduction to Excel Power BI

*The Power Query Tool

*The Power Pivot Tool

Price: $10

Requires:
NCPMI Excel Analytics Session 10 recording
Excel Manual 10
Session 10 Exercise Files

Download

Session 9 – Dashboard Visualizations Part 2: Pivot Tables, Pivot Charts, Slicers

Description:
Participants will learn to create and modify pivot tables, as well as add pivot charts and slicers to create a data analytics dashboard within Excel.

Objectives:
*Creating and Modifying Pivot Tables

*Adding and Modifying Pivot Charts

*Adding and Modifying Slicers

Price: $10

Requires:
NCPMI Excel Analytics Session 9 recording
Excel Manual 9
Session 9 Exercise Files

 

Download

Session 8 – Dashboard Visualizations Part 1: Charts and Graphs, Sparklines, Conditional Formatting

Description:
Participants will learn to create data visualizations by using charts and graphs, sparklines, and conditional formatting tools.

Objectives:
*Creating and Formatting Charts and Graphs

*Creating Sparklines

*Using Conditional Formatting to Provide Visual Cues

Price: $10

Requires:
NCPMI Excel Analytics Session 8 recording
Excel Manual 8
Session 8 Exercise Files

Download

Session 7 – Troubleshooting: Data Validation, Auditing tools, IFERROR, Locating Formulas, Worksheet and Workbook Protection

Description:
Participants will be able to troubleshoot Excel in the areas of data entry, formulas and functions, and user misstep.

Objectives:

*Ensuring Correct Data Entry with Data Validation

*Finding Mistakes with the Auditing Tools

*Using IFERROR to Display Custom Error Messages

*Finding Formulas in a Worksheet Easily

*Protecting Worksheets and Workbooks

Price: $10

Requires:
NCPMI Excel Analytics Session 7 recording
Excel Manual 7
Session 7 Exercise Files

 

Download

Session 6 – Finding and Displaying Relevant Data: VLOOKUP, HLOOKUP, MATCH and INDEX, Goal Seek

Description:
Participants will be able to find and display data of interest by using the Lookup tools, Match and Index. Participants will also be able to back into a calculation using Goal Seek.

Objectives:
*Creating a Drop Down Pick List in Excel

*Displaying and Manipulating Data via VLOOKUP and HLOOKUP

*Finding Intersecting Data with Match and Index

*Backing into Calculations via Goal Seek

Price: $10

Requires:
NCPMI Excel Analytics Session 6 recording
Excel Manual 6
Session 6 Exercise Files

Download

Session 5 – Conquering Data: Custom Sorting, Subtotals, Filters and Advanced Filters

Description:
Participants will learn to extract and view data in many different ways, using sorting, subtotaling and filtering, making data analytics faster and easier.

Objectives:
*Arranging Data Effectively with Sorts and Custom Sorts

*Changing Sort Order with Custom Lists

*Creating Automatic Subtotals with Grouping Elements

*Showing Pertinent Data with Filters and Advanced Filters

Price: $10

Requires:
NCPMI Excel Analytics Session 5 recording
Excel Manual 5
Session 5 Exercise Files

Download

Session 4 – Combining Data: Workbook Concepts with 3D Calculations, Linking External Workbooks, Consolidation

Description:
Participants will learn to combine different data sets both within a single workbook and across different workbooks/files.

Objectives:
*Impacting Data on Multiple Workbook Sheets Easily

*3-D Calculations Within a Workbook

*Linking External Workbooks/Files to Combine Analytics

*Using Consolidation to Summarize Like Workbooks

Price: $10

Requires:
NCPMI Excel Analytics Session 4 recording
Excel Manual 4
Session 4 Exercise Files

Download

Session 3– Fun with Functions Part 2: Concatenation, Text to Columns, Substring Functions, Date Calculations

Description:
Participants will learn how to prepare text, date and time fields to be used in a data analytics framework.

Objectives:
*Concatenating Fields to Form New Analytics

*Using Text to Columns to Create New Data Analytic Elements

*Date and Time Entry Options

*Performing Date Formulas

*Performing Time Formulas

Price: $10

Requires:
NCPMI Excel Analytics Session 3 recording
Excel Manual 3
Session 3 Exercise Files

 

Download

Session 2 – Fun with Functions Part 1: Naming Cells and Ranges, IF, Boolean IF, Nested IF, SUMIF, SUMIFS

Files:
NCPMI Excel Analytics Session 1 recording
Excel Manual 1

Description:
Participants will learn how to use all facets of the Excel IF function, and will know how to use forms of IF to enable decision-making within the data analytics framework.

Objectives:
*Using Cell and Range Names to Simplify Formulas and Functions
*The IF Function
*Boolean IF Statements
*Nested IF Statements
*The SUMIF and SUMIFS Functions

Requires:
NCPMI Excel Analytics Session 2 recording
Excel Manual 2
Session 2 Exercise Files

Price: $10

Download

Session 1 – Smart Worksheet Creation: Best Practices, Tips, Tricks and Shortcuts

Files:
NCPMI Excel Analytics Session 1 recording
Excel Manual 1


Description:
Participants will learn how to put together any type of Excel worksheet quickly, accurately, and in a way that enables Excel to work more efficiently. This session is full of hidden shortcuts and little-known tips.

Objectives:

The Seven Steps to Spreadsheet Success
Seven Shortcuts to Work Smarter, Not Harder
Data Validation Tools to Insure Reliable Data
Basic Functions and Formulas – Made Easy

Price: $10

Download