Microsoft Excel - Advanced Excel Tricks that Impress
The secret Excel magic tricks used by the power users
Do you want to learn the advanced Excel tricks used by the power users?
This course is all about learning the advanced techniques you do not get taught.
This course will give you a deep understanding of the most powerful formulas and functions in Excel, including multiple examples to demonstrate what makes them so blooming AWESOME.
It also delves into advanced PivotTable, chart, and custom formatting tricks to take your skills to another planet.
During the course you will learn;
The very best functions of Excel including SUMPRODUCT, INDEX, COUNTIF, MOD and more in over 50 examples that others do not teach.
Advanced charting techniques to create interactive and visually powerful charts.
Deep PivotTable settings and features to have them working exactly as you need.
Awesome custom formatting techniques for stunning visuals and learn why it can be better than Conditional Formatting.
Mega useful tricks you can perform with day to day Excel features to supercharge your productivity.
Introduction to the Course
Download your Exercise Files
10 Paste Special Tricks used by the Pros
Excel's Best Kept Secret - and 3 Ways to Use it
The Unsung Hero of Excel for Cleaning Messy Data
Edit Multiple Sheets at the Same Time with Ease
5 Reasons your Excel Formulas are not Calculating
TOP SECRET - How to Make Sheets VERY Hidden
My Favourite Excel Shortcuts
HOMEWORK: Chapter 2
Using Wildcards with VLOOKUP
Using wildcards with COUNTIF and SUMIF
Validate that Text is a Specific Number of Characters
HOMEWORK: Chapter 3
The Definitive Guide to the SUMPRODUCT Function
Example 1 - Count and Sum the Sales from a Specific Month
Example 2 - Count the Occurrences of a Specific Word in a Range
Example 3 - Count the Unique Values ONLY in a Range
Example 4 - Sum the Top 3 Values
Example 5 - Two Way Lookup Formula with SUMPRODUCT
HOMEWORK: Chapter 4
Two Reasons your PivotTable Counts instead of Sums
You Must Do this One Thing
Grouping Dates and Times
Using the GETPIVOTDATA Function to Pull Data from a PivotTable
Going Beyond the Sum - 3 More PivotTable Calculations
Calculating the Difference to Previous Years/Months/Weeks
Create your own Calculated Fields
Using Conditional Formatting with PivotTables
Show Top Ten Results
Five Useful PivotTable Settings
The Power of Slicers
7 Slicer Settings you Will Want to Change
HOMEWORK: Chapter 5
An Introduction to the MOD Function
Example 1 - Extracting the Time from a Date-Time Cell
Example 2 - Prevent the Entry of Odd Numbers in a Range
Example 3 - Calculate Total Balls Bowled from Overs in Cricket
Example 4 - Sum Every 3rd Row in a List
HOMEWORK: Chapter 6
Example 1 - INDIRECT with Named Ranges
Example 2 - Referencing other Sheets with INDIRECT
Example 3 - Return the Last Value from a Row
Example 4 - INDIRECT with VLOOKUP
Example 5 - Create Dependent Drop Down Lists
HOMEWORK: Chapter 7
Example 1 - Using INDEX and MATCH for an Advanced Lookup
Example 2 - Create a Picture Lookup
Example 3 - Return the Value from the Last Row
Example 4 - Create a Dynamic Named Range
Example 5 - Sum the Last 6 Values Only
Example 6 - Retrieve a Range from a List of Ranges
HOMEWORK: Chapter 8
Using Cell Values with COUNTIF
Count Values Between Two Dates
Prevent Duplicates in a Range
Compare Two Lists to Identify Missing Items
VLOOKUP for the Last Match in a List
Uniquely Rank Items
HOMEWORK: Chapter 9
Combine Text and Numbers in a Cell
Show the Weekday of a Date
Keep the Leading Zeroes of a Value
Display Negative Values in Red
Show Zero Values as Blank Cells
Display Symbols using Custom Formatting to Show Change
Conditional Colour with Custom Formatting
Setting Thresholds for your Custom Formatting
HOMEWORK: Chapter 11
The Easiest Way to Create a Dynamic Data Range
Drop Down Menu to Select a Chart
Get Creative by Building a Dynamic Chart Title
Highlight the Max and Min Values Differently
Create a Scrollable Chart
Use Cell Values for Data Labels to Show what YOU want
Rolling Chart for the Last 6 Months Only
Visualise Targets v's Actuals
Using Check boxes to Show/Hide Chart Data
HOMEWORK: Chapter 12
Why learn from me?
I have been training business all around the world on how to get the most out of Excel for over 20 years.
Learning and teaching Excel is my passion. I am a full-time Excel trainer and consultant, so every day I am in the thick of the action teaching others to master Excel.
I set up the Computergaga blog and YouTube channel 8+ years ago where I am lucky enough to have taught millions of people across the globe to increase their skills and overcome everyday real world Excel problems.
You can download the files I use in the lessons to follow along and receive 1 on 1 instructor support every step of the way.