IIRF Online > Office Productivity > Microsoft > Pivot Tables > Pivot Tables, Power Pivot Tables, Power Query & Power BI
Pivot Tables, Power Pivot Tables, Power Query & Power BI by Udemy
5 Course Excel Bundle: Microsoft Excel, Pivot Tables, Power Pivot Tables, Power Query & Power BI (Excel 2007-2019, O365)
Course Highlights
- Become more PRODUCTIVE at using Microsoft Excel which will SAVE YOU HOURS per day & ELIMINATE STRESS at work!
- We teach you how to use Microsoft Excel with CONFIDENCE that will lead to greater opportunities like a HIGHER SALARY and PROMOTIONS!
- Increase your Microsoft Excel SKILLS and KNOWLEDGE within HOURS which will GET YOU NOTICED by Top Management & prospective Employers!
- COURSE #1: Learn ALL the Pivot Table features: Slicers, Pivot Charts, Filtering, Grouping, Sorting, Dashboards, Data Analysis, Calculated Fields plus MORE!
- COURSE #2: Learn Power Pivot, DAX Formulas, Measures, Calculated Columns & Table Relationships!
- COURSE #3: Learn Power Query (Get & Transform), import & transform your data, consolidate from multiple workbooks, folders, worksheets plus MORE!
- COURSE #4: Learn Power BI and create interactive Dashboards that tell a story about your data!
- COURSE #5: 333 Excel Keyboard Shortcuts explained, covering Formatting, Ribbon, Formulas, VBA, Pivot Tables, Power BI plus MORE!
- 7 COURSE BONUSES: Excel PDF Cheat Sheets, 101 Ready Made Excel Templates, plus MORE!!!
- ADDED BONUS: This entire courses can be DOWNLOADED for offline viewing on the Udemy mobile app (Android & iOS)!
- DOWNLOADABLE Excel workbooks so you can keep on your desktop & practice!
- Test your skills with various real life Quizzes & Homework Practice Activities & Assignments!
- Personal Excel support from Professional Excel Trainers with 30+ Years of combined Excel Training!
Skills you will learn!
Curriculum
8 Topics
COURSE INTRODUCTION
A MESSAGE FROM YOUR INSTRUCTOR - JOHN MICHALOUDIS
EXCEL WORKBOOKS - IMPORTANT INSTRUCTIONS (PLEASE READ)
HANDOUT #1: COURSE TRACKER EXCEL WORKBOOK [FREE EXCEL WORKBOOK DOWNLOAD]
HANDOUT #2: PIVOT TABLE CHEAT SHEET [FREE PDF DOWNLOAD]
HANDOUT #3: 5 SPEEDY STEPS TO AN EXCEL PIVOT TABLE [FREE PDF DOWNLOAD]
HANDOUT #4: 101 READY MADE EXCEL TEMPLATES [FREE EXCEL WORKBOOKS DOWNLOAD]
HANDOUT#5: 50+ EXCEL KEYBOARD SHORTCUTS IN A KEYBOARD LAYOUT [FREE PDF DOWNLOAD]
50 Topics
WORKBOOKS
ARRANGING YOUR DATA SET ~ Tabular Format
ARRANGING YOUR DATA SET ~ No Gaps
ARRANGING YOUR DATA SET ~ Formatting
ARRANGING YOUR DATA SET ~ Tables
ARRANGING YOUR DATA SET ~ TIP: Clean Your Data Set
CREATING A PIVOT TABLE ~ Inserting a Pivot Table
CREATING A PIVOT TABLE ~ Field List - Activate move resize & layout
CREATING A PIVOT TABLE ~ Field List & Areas
CREATING A PIVOT TABLE ~ Drill down to audit
CREATING A PIVOT TABLE ~ Sort Field List from A to Z
CREATING A PIVOT TABLE ~ TIP: Double click on any labels to show more Fields
CREATING A PIVOT TABLE ~ TIP: Defer Layout Update
OPTIONS > DATA ~ Pivot Cache explained
OPTIONS > DATA ~ Refresh
OPTIONS > DATA ~ Refresh All
OPTIONS > DATA ~ Refresh External Data
OPTIONS > DATA ~ Import from Access database
OPTIONS > DATA ~ Change Data Source
OPTIONS > ACTIONS ~ Clear Filters & Clear Pivot
OPTIONS > ACTIONS ~ Select & format
OPTIONS > ACTIONS ~ Move a Pivot Table
DESIGN > STYLES ~ Pivot Table Styles
DESIGN > STYLES ~ Customising a Pivot Table Style
DESIGN > STYLES ~ Use a customised style in another workbook
DESIGN > LAYOUT ~ Subtotals
DESIGN > LAYOUT ~ Grand Totals
DESIGN > LAYOUT ~ Report Layouts
DESIGN > LAYOUT ~ Blank Rows
DESIGN > LAYOUT ~ TIP: Show the Classic Pivot Table Layout
OPTIONS > SHOW ~ Expand & Collapse buttons
OPTIONS > SHOW ~ Move & Remove Fields and Items
OPTIONS > SHOW ~ Show/Hide Field List
OPTIONS > SHOW ~ Show/Hide Field Headers
CUSTOMISATION ~ Change "Count of" to "Sum of"
CUSTOMISATION ~ Number formatting
CUSTOMISATION ~ Field name formatting
CUSTOMISATION ~ TIP: Predetermined number formatting
CUSTOMISATION ~ TIP: Change Sum views in Label areas
LAYOUT & FORMAT ~ Indent rows in compact layout
LAYOUT & FORMAT ~ Change the layout of a report filter
LAYOUT & FORMAT ~ Format error values
LAYOUT & FORMAT ~ Format empty cells
LAYOUT & FORMAT ~ Keep column widths upon refresh
LAYOUT & FORMAT ~ TIP: Automatic Refresh a Pivot Table
LAYOUT & FORMAT ~ TIP: Printing a pivot table on two pages
LAYOUT & FORMAT ~ TIP:Show report filter on multiple pages
CUSTOMIZE YOUR PIVOT TABLE
Course Review
HOMEWORK: SET UP A PIVOT TABLE
16 Topics
WORKBOOKS
Create multiple subtotals
Count
Average
Maximum
Minimum
Product
Count Numbers
Std Dev
Varp
PRO PIVOT TABLE TIP: Show various Grand Totals
PRO PIVOT TABLE TIP: Shortcuts to Field & Value Field Settings
PRO PIVOT TABLE TIP: See all pivot Items
PRO PIVOT TABLE TIP: Show a unique count
VALUE FIELD SETTINGS > SUMMARIZE VALUES BY
HOMEWORK: SUMMARIZE VALUES BY
20 Topics
WORKBOOKS
% of Grand Total
% of Column Total
% of Row Total
% Of
% of Parent Row Total (NEW IN EXCEL 2010)
% of Parent Column Total (NEW IN EXCEL 2010)
% of Parent Total (NEW IN EXCEL 2010)
Difference From
% Difference From
Running Total in
% Running Total in (NEW IN EXCEL 2010)
Rank Smallest to Largest (NEW IN EXCEL 2010)
Rank Largest to Smallest (NEW IN EXCEL 2010)
Index
PRO PIVOT TABLE TIP: Shortcuts to Show Values As
CASE STUDY ~ ACCOUNTING: % of Revenue Margins
CASE STUDY ~ FINANCIAL: Actual v Plan Variance Report
VALUE FIELD SETTINGS > SHOW VALUES AS
HOMEWORK: SHOW VALUES AS
20 Topics
WORKBOOKS
Group by Date
Group by Months
Group by Quarters & Years
Group by Sales ranges
Group by Text fields
Group by Time
PRO PIVOT TABLE TIP: Shortcuts to Grouping
PRO PIVOT TABLE TIP: Grouping by Half Years
PRO PIVOT TABLE TIP: Group by a Date that starts on a Monday
PRO PIVOT TABLE TIP: Grouping by a custom date
PRO PIVOT TABLE TIP: Group by fiscal years & quarters
PRO PIVOT TABLE TIP: Errors when grouping by dates
PRO PIVOT TABLE TIP: Group two pivot tables independently
PRO PIVOT TABLE TIP: Fixing the problem of counting grouped sales
PRO PIVOT TABLE TIP: Display dates that have no data
CASE STUDY ~ ACCOUNTING: Quarterly Comparative Report
CASE STUDY ~ FINANCIAL: Min & Max Bank Balance
GROUPING
HOMEWORK: GROUPING
12 Topics
WORKBOOKS
Sorting by Largest or Smallest
Sort an Item Row (Left to Right)
Sort manually (drag write right click)
Sort using a Custom List
Override a Custom List sort
Sort row from A-Z and sales from Z-A
PRO PIVOT TABLE TIP: Sort new items added to your data source
PRO PIVOT TABLE TIP: Clear a sort
PRO PIVOT TABLE TIP: Sort Largest to Smallest Grand Totals
SORTING
HOMEWORK: SORTING
22 Topics
WORKBOOKS
Filter by Dates
Filter by Labels - Text
Filter by Labels - Numerical Text
Filter by Values
Filter by Values - Top or Bottom 10 Items
Filter by Values - Top or Bottom %
Filter by Values - Top or Bottom Sum
Filter by Report Filter
PRO PIVOT TABLE TIP: Shortcuts to filters
PRO PIVOT TABLE TIP: Keep or hide selected items
PRO PIVOT TABLE TIP: Filter by Text wildcards * and ?
PRO PIVOT TABLE TIP: Filter by multiple fields
PRO PIVOT TABLE TIP: Apply multiple filters
PRO PIVOT TABLE TIP: Filter by multiple values
PRO PIVOT TABLE TIP: Include new items in manual filter
PRO PIVOT TABLE TIP: Clear filters with one click
PRO PIVOT TABLE TIP: Add a filter for the column items
CASE STUDY ~ ACCOUNTING: Top 5 Expenses report
CASE STUDY ~ FINANCIAL: Top 25% of Channel Partners
FILTERING
HOMEWORK: FILTERING
17 Topics
WORKBOOKS
Insert a Slicer
Slicer Styles
Creating a custom style
Copy a custom style into a new workbook
Slicer Settings
Slicer Size & Properties
Slicer Connections for multiple pivot tables
PRO PIVOT TABLE TIP: Different ways to filter a Slicer
PRO PIVOT TABLE TIP: Use one slicer for two pivot tables
PRO PIVOT TABLE TIP: Lock the workbook but not the slicer
PRO PIVOT TABLE TIP: Interactive employee photos with Slicers! FUN!
CASE STUDY ~ ACCOUNTING: Select a Monthly P&L report with a Slicer
CASE STUDY ~ FINANCIAL: Base Best & Worst case Forecast
SLICERS
Course Review
HOMEWORK: SLICERS
19 Topics
WORKBOOKS
Creating a Calculated Field
Use an existing Calculated Field in a new calculation
Editing a Calculated Field
Excel formulas & Calculated Fields
Creating a Calculated Item
Use an existing Calculated Item in a new calculation
Editing a Calculated Item
Excel formulas & Calculated Items
Calculated Item on Column Labels
Shortcomings of Calculated Items
PRO PIVOT TABLE TIP: Solve Order for Calculated Items
PRO PIVOT TABLE TIP: List Calculated Field & Item formulas
PRO PIVOT TABLE TIP: Remove a Calculated Field temporarily
PRO PIVOT TABLE TIP: Order of operations
CASE STUDY ~ ACCOUNTING: Creating a P&L Pivot Table Report
CASE STUDY ~ FINANCIAL: Actuals v Plan with Calculated Fields
CALCULATED FIELDS & ITEMS
HOMEWORK: CALCULATED FIELDS
24 Topics
WORKBOOKS
Insert a Pivot Chart
Insert a Slicer with a Pivot Chart
Pivot Chart Designs
Pivot Chart Layouts
Pivot Chart Formats
Limitations of Pivot Charts & workarounds
Saving a Pivot Chart template
PRO PIVOT TABLE TIP: Shortcuts to formatting a Pivot Chart
PRO PIVOT TABLE TIP: Link chart title to a pivot cell
PRO PIVOT TABLE TIP: Copying a second chart
PRO PIVOT TABLE TIP: Put a chart on a separate page with F11
PRO PIVOT TABLE TIP: Insert Pivot Chart straight from the data source
PRO PIVOT TABLE TIP: Paste Pivot Chart to your email as a picture
PRO PIVOT TABLE TIP: Paste Pivot Chart to PowerPoint & make live updates
PRO PIVOT TABLE TIP: Printing a Pivot Chart
PRO PIVOT TABLE TIP: Include a Sparkline with your pivot table
PRO PIVOT TABLE TIP: Charts Do´s & Don'ts
PRO PIVOT TABLE TIP: Change Chart Type with Slicers! FUN!!!
PRO PIVOT TABLE TIP: Workaround to creating an interactive Scatter graph
CASE STUDY ~ ACCOUNTING: P&L Pivot Table report with Graphs
CASE STUDY ~ FINANCIAL: Pivot Table Slicer & Chart Dashboard
PIVOT CHARTS
Create a Pivot Table Dashboard
19 Topics
WORKBOOKS
Intro to Conditional Formatting
Highlight Cell Rules based on values
Highlight Cell Rules based on text labels
Highlight Cell Rules based on date labels
Top & Bottom Rules
Data Bars Color Scales & Icon Sets (NEW IN EXCEL 2010)
PRO PIVOT TABLE TIP: Format only cells that contain - For Bonuses
PRO PIVOT TABLE T Format only Top or Bottom ranked values - Top 3 sales per year
PRO PIVOT TAB Format values that are above or below the average - For Promotions
PRO PIVOT TABLE TIP: Use a formula to determine which cells to format
Use selected cells to format multiple fields
All cells showing values to format multiple fields
Control Conditional Formatting with Slicers
Show text in the Pivot Table Values area
Cond Format blank cells or cells
CASE STUDY ~ ACCOUNTING: Accounts Receivable Ageing Report Matrix
CASE STUDY ~ FINANCIAL: Conditionally Format your sales results
CONDITIONALLY FORMATTING
10 Topics
WORKBOOKS
Intro to GETPIVOTDATA
Create a custom report with GETPIVOTDATA
Reference Dates with GETPIVOTDATA
Data validation with GETPIVOTDATA
Shortfalls of GETPIVOTDATA
PRO PIVOT TABLE TIP: Grand Totals to the left of the pivot table
CASE STUDY ~ ACCOUNTING: Live forecasting with GETPIVOTDATA
CASE STUDY ~ FINANCIAL: Channel Analysis with GETPIVOTDATA
GETPIVOTDATA
8 Topics
WORKBOOKS
Adding the Developer tab & disabling macros
Record a simple macro to Refresh a pivot table
Date filter macro
Different pivot table views macro
Top 10 macro
Add macro to quick access toolbar
MACROS
8 Topics
WORKBOOKS
Reducing file memory by copying existing pivot table
Reducing file memory by deleting the data source
Reducing file memory by saving file as Excel Binary Workbook
Reducing file memory by keeping data source in MS Access
Compatibility Issues with Excel 2007 and Excel 2010
Sharing a Pivot Table via OneDrive
DATA MANAGEMENT
9 Topics
WORKBOOKS
Sales Forecasting with Calculated Fields
Consolidate with a Pivot Table
Frequency distribution with a Pivot Table
Break Even Model
Several Slicer custom styles for you to use
Interactive Balance Sheet Pivot Table
Monthly Sales Manager Performance Report
Reconciling customer payments
6 Topics
WORKBOOKS
Cosmetic changes in the PivotTable Tools Ribbon Menu
Recommended Pivot Tables
Distinct Count
Timelime Slicers
Data Models
5 Topics
WORKBOOKS
Group Periods
Multi-Select Slicer items
Pivot Chart - Expand and Collapse fields
3D Maps
7 Topics
DOWNLOADABLE WORKBOOKS
Which Excel version do you have?
Default Pivot Table Layout
Automatic Relationship Detection
Automatic Time Grouping
Search in a Pivot Table
Course Review
6 Topics
Power Pivot & DAX Formulas Course Introduction
[Free PDF Download] Power Pivot Cheat Sheet
[JULY 2018 UPDATE] Power Pivot is now available in all versions of Excel
Installing Power Pivot in Excel 2010
Enabling Power Pivot in Excel 2013 2016 Excel 2019 and Office 365 Subscription
How do you enable Power Pivot in Excel 2016 2019 and Office 365
5 Topics
DOWNLOADABLE WORKBOOKS
Import From Excel File (Workbook)
Import From Excel Table (Worksheet)
Import From Multiple Excel Files (Workbooks)
Which external data sources can you import data from?
5 Topics
DOWNLOADABLE WORKBOOKS
Create Relationships
Create Relationships via the Diagram View
When creating a Relationship using Power Pivot where do the "unique" values go?
Course Review
4 Topics
DOWNLOADABLE WORKBOOKS
Create a Pivot Table
Insert a Slicer
How do you insert a Slicer?
14 Topics
Intro to DAX Formulas
Intro to DAX Formulas: Measures vs Calculated Columns - Part 1
Intro to DAX Formulas: Measures vs Calculated Columns - Part 2
DAX Syntax
Date and Time Functions
Time Intelligence Functions
Filter Functions
Information Functions
Logical Functions
Math and Trig Functions
Statistical Functions
Text Functions
Other Functions
Where can you write DAX Formulas?
13 Topics
DOWNLOADABLE WORKBOOKS
DISTINCTCOUNT() - Create a "Unique Order Dates" Measure
SUM() - Create a "Total Sales" Measure
Combine 2 Measures to get "Average Sales Per Date"
CALCULATE() - Filter & Show Individual Product Sales
CALCULATE() - Show Largest Sales Values
Calculated Column - Extract Years from Order Date
CALCULATE() - Group Years
Link a Calendar Table
Sort Month Dates Into The Correct Order
Add a Hierarchy to the Calendar
Combine a Calculated Column with a Measure - Net Profit Analysis
How do you create a Measure?
13 Topics
DOWNLOADABLE WORKBOOKS
[Free PDF Download] Power Query Cheat Sheet
Introduction to Power Query
Installing Power Query in Excel 2010
Installing Power Query in Excel 2013
Power Query and Query Editor Ribbon
Split Text Fields
Trim
Format Dates and Values
Parsing URLs
Group By
Import From Folder
In which Excel versions do you need to manually install the Power Query Add-In?
4 Topics
DOWNLOADABLE WORKBOOKS
Filtering Rows
Sorting Columns
What is the main strength of Power Query?
5 Topics
DOWNLOADABLE WORKBOOKS
Extract Based on 2 Criteria
Compiling Data From Multiple Worksheets
How do you consolidate multiple worksheets that have the same layout?
Compile Data from Multiple Worksheets
3 Topics
DOWNLOADABLE WORKBOOKS
Merge
With a merge query you can achieve similar goals to the Excel VLOOKUP function…
4 Topics
DOWNLOADABLE WORKBOOKS
Intro to Joins
Full Outer Join
Right Anti-Join
3 Topics
DOWNLOADABLE WORKBOOKS
Doing Auto Cleanup
Power Query Data Types
9 Topics
DOWNLOADABLE WORKBOOKS
Extract Data from Forms
Unpivoting Columns
Pivoting Columns
Split Columns Into Other Columns
Transform and Add Columns
Modulo
Convert Reports into Pivot Tables
Converting your Pivot Table to a tabular format for analysis
6 Topics
DOWNLOADABLE WORKBOOKS
[Free PDF Download] M Cheat Sheet
M Overview
M Function List using #Shared
Using M Functions
What does M stand for?
11 Topics
DOWNLOADABLE WORKBOOKS
Viewing M Code
Simple M Expressions
Simple M Expressions - Nested Expressions
M Variables
M Functions
M Functions - Reusable Functions
M Functions - Invoking
Passing M Functions
Passing M Functions - Using the "each" keyword
What are the advantages of M?
8 Topics
Overview of the Course
[Free PDF Download] Power BI Cheat Sheet
Power BI Introduction
Power BI High Level Flow
Installing and Signing Up for Power BI
Power Query Introduction
Power Pivot Introduction
Power View Introduction
13 Topics
DOWNLOADABLE EXERCISE FILES
The Big Picture
Get Data
How to Get More Data
Modeling Data
Adding Relationships Manually
Visualization
Visualization Customization
More Visualizations
Visualization Format and Analytics
Ask a Question in Power BI
Assignment
Your Assignment - Create a Power BI Dashboard
8 Topics
DOWNLOADABLE EXERCISE FILES
Real World Example
Get Data
Modeling Data
Visualization with Multiple Charts
Publishing
Power BI Website
Power BI Quiz
17 Topics
Course Introduction - 333 Excel Keyboard Shortcuts
Free PDF Cheat Sheet Listing 333 Excel Keyboards Shortcuts for Windows & Mac
Downloadable Practice Workbook
Cell Formatting
Drag & Drop
Formulas & Functions
Frequent keyboard shortcuts
Function Key
Making selections
Ribbon Tab
Cell Navigation
Inside Ribbon
Macros & VBA
Pivot Tables
Power BI
Power Pivot
Other Keyboard Shortcuts
2 Topics
Thank You & Course Review!
BONUS EXCEL RESOURCES!
Pivot Tables, Power Pivot Tables, Power Query & Power BI
Related Pivot Tables Courses
Thank You!
Your review has been submitted successfully.