Microsoft Excel - Advanced Excel Tricks that Impress
4.6 (72 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
474 students enrolled

Microsoft Excel - Advanced Excel Tricks that Impress

The secret Excel magic tricks used by the power users
4.6 (72 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
474 students enrolled
Created by Alan Murray
Last updated 4/2019
English
English [Auto]
Current price: $51.99 Original price: $74.99 Discount: 31% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 6.5 hours on-demand video
  • 11 articles
  • 3 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • A deep understanding of the most useful and advanced functions of Excel
  • Create dynamic and interactive charts to impress management
  • Apply charting techniques that the pros use and be the charting boss of your office
  • Apply tricks that will have you working faster, smarter and better at Excel
  • Lifetime access to the lessons, practice files, exercises, and 1-on-1 instructor support
  • Fun and effective lessons on some of the greatest Excel tricks around
Requirements
  • A basic knowledge of Excel
  • You are able to write basic formulas
  • The course is taught in Excel 2016, but the tricks will work in any version of Excel unless stated
Description

★★★★★ Course access includes the exact files I use to download and follow along, 1 on 1 Instructor support, lifetime access and a 100% money back guarantee ★★★★★

Full Course Description

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.

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.

Who this course is for:
  • You want to improve your current Excel skills and learn advanced tricks
  • You want to learn Excels most powerful functions and understand why they are so awesome
  • You'd like to create stunning charts to visualize data effectively
  • You want to learn some neat tricks to work quickly and efficiently in Excel
Course content
Expand all 79 lectures 06:37:44
+ Introduction
2 lectures 07:00

An introduction to the course and what to expect including content and resources.

Preview 06:53
Course Files - Download these files first to follow along
00:07
+ Neat Tricks to Supercharge your Productivity
8 lectures 55:45

The Paste Special tool in Excel is remarkable. It can achieve many typical everyday Excel tasks very quickly and save tons of time, yet many Excel users are unaware of what it is truly capable of.

This lesson explores 10 Paste Special tricks that you will wish you knew earlier.

Preview 11:35

In this lesson we explore the best kept secret of Excel. This tool is mega useful and this video demonstrates 3 reasons why.

Excel's Best Kept Secret - and 3 Ways to Use it
06:59

There is an unsung hero of Excel that can clean up messy data extremely quickly. It is capable of changing formulas, removing erroneous characters from cells, and even formatting every sheet of a workbook in seconds.

This lesson will demonstrate two examples of this impressive and often overlooked and forgotten tool.

The Unsung Hero of Excel for Cleaning Messy Data
11:08

Quickly format and calculate data on multiple worksheets at the same time with this little magic trick. Knowing this can save tons of time and improve the consistency of your worksheets.

Edit Multiple Sheets at the Same Time with Ease
04:18

Are formulas not calculating, or have they stopped updating on your spreadsheets. In this lesson we uncover 5 common reasons as to why this may be. Oh, and we fix them.

5 Reasons your Excel Formulas are not Calculating
05:53

In this top secret lesson we show you how to hide a sheet so that it does not appear in the list when users go to unhide sheets.

You probably know how to hide and unhide sheets using the conventional method. This is great, and can also be protected. 

But what if you do not want all of what Worksheet Protection brings, yet would like to hide the sheet deeper. This lesson will show you how.

TOP SECRET - How to Make Sheets VERY Hidden
04:55

In this lesson I show my favourite Excel shortcuts and tips that I use to complete everyday tasks. There are a combination of keyboard shortcuts, Excel features and mouse techniques mentioned.

My Favourite Excel Shortcuts
10:34

Homework exercises to recap on some of the skills learnt in Section 2.

HOMEWORK: Section 2
00:23
+ Using Wildcards with Formulas
4 lectures 10:27

In this lesson we use wildcards with the VLOOKUP function for partial matches.

VLOOKUP will typical match what it is looking for, and what it finds precisely. However you may be searching for a specific word, or phrase within a cell.

Fortunately with the use of wildcards this can be done.

Preview 04:05

In this lesson wildcard characters are used to perform partial matches with the COUNTIF and SUMIF functions.

These functions need the criteria entered as a string, so the wildcards are concatenated with the logical operators we need, and other aspects of the criteria.

Using wildcards with COUNTIF and SUMIF
02:26

The question mark wildcard character is demonstrated in this lesson. It is used to validate the number of characters in a cell.

In this example, the cells must contain exactly 6 characters. A formula is used to identify any cells that do not follow this rule.

Validate that Text is a Specific Number of Characters
03:51

A homework exercise to recap on the lessons from section 3.

HOMEWORK: Section 3
00:05
+ The Awesome SUMPRODUCT Function - The Swiss Army Knife of Functions
7 lectures 29:41

In this lecture we explain the SUMPRODUCT function with two examples of how it can be used.

This is an introduction to SUMPRODUCT, with the following videos delving deeper, so that you know this important function inside and out.

Preview 08:00

In this lesson we see the SUMPRODUCT function being used to sum and count values from a specific month. The MONTH function of Excel is also used to achieve this.

Example 1 - Count and Sum the Sales from a Specific Month
06:22

The SUMPRODUCT function is used here to count the occurrences of a specific word, or phrase, within a range of cells. This really shows the dynamism of this function.

Example 2 - Count the Occurrences of a Specific Word in a Range
04:18

Excel has many variations of count functions including COUNT, COUNTA and COUNTBLANK. But not on to count only the unique values, and exclude duplicates. SUMPRODUCT is used in this lesson to accomplish this task.

Example 3 - Count the Unique Values ONLY in a Range
03:25

Instead of summing all of the values in a range, you may just want to sum the top 10, top 5 or top 3.

This lesson sees SUMPRODUCT sum only the top 3 values in a range. This lesson also introduces the LARGE function.

The same technique could be used for the bottom 3 by using the SMALL function.

Example 4 - Sum the Top 3 Values
03:05

Can you believe that SUMPRODUCT is also capable of performing a lookup? Well you better believe it.

This lesson will show you how to use SUMPRODUCT for a two way lookup. This is when you need to search along a row, and down a column to retrieve a value from a cell.

Example 5 - Two Way Lookup Formula with SUMPRODUCT
04:18

This homework exercise will recap on some of the lessons from section 4.

HOMEWORK: Section 4
00:13
+ Advanced PivotTable Tricks
13 lectures 01:28:35

One of the most common issues people face when new to working with PivotTables, is when the PivotTable counts their values instead of summing them.

In this lesson we explain why that is happening and present 2 reasons along with the solution.

Two Reasons your PivotTable Counts instead of Sums
06:07

You must do this one thing before you create your PivotTable.

This one trick will make it easier to update your PivotTable when data in the source changes, and also simplifies the editing of that source in the future.

You Must Do this One Thing
06:48

One of the most useful tricks when working with PivotTables is the ability to group your data.

This lesson will show you how to do this using the most common type of fields to group - dates and times.

In the lesson date and time fields are split into years, quarters, months and hours respectively so that we can analyse data to that level.

Grouping Dates and Times
09:20

The GETPIVOTDATA function is used to make it easy to extract the information we want from a PivotTable. You can think of it as a PivotTable lookup.

It provides a durable way that is unaffected by updates to the PivotTable ensuring we get the information we want every time.

This lesson shows an example of the GETPIVOTDATA function being awesome. We then show an example of when you may not want to use it, and how it can be turned off.

Using the GETPIVOTDATA Function to Pull Data from a PivotTable
09:21

In this lesson we explore 3 additional calculations you can perform in your PivotTables, beyond the standard sum, count and average.

We look at viewing values as a percentage of the subtotal, ranking them and also creating running totals.

Going Beyond the Sum - 3 More PivotTable Calculations
09:42

A very common task in PivotTables is to calculate the difference between years, months and weeks.

This variance calculation is easy to perform with a PivotTable as demonstrated in this video. The difference can be shown as a value or a percentage.

Formatting is also applied to to give the data more life.

Calculating the Difference to Previous Years/Months/Weeks
05:16

Although PivotTables contain many built in calculations, it is possible to create your own calculated fields.

In this lesson we create a calculated field to create bonuses for the sales staff. We explain the benefits and limitations of using calculated fields.

Create your own Calculated Fields
03:41

It is great news that Conditional Formatting can be easily applied to PivotTables to bring increased data clarity.

This video shows you the quickest and best way to apply Conditional Formatting rules, and how to ensure they expand and update with the PivotTable.

Using Conditional Formatting with PivotTables
06:21

You can easily create top ten and bottom ten lists with PivotTables. These lists will update when the data source changes so work wonderfully in Excel reports and graphs.

This video shows you how to create a top ten lists of product sales.

Show Top Ten Results
03:20

There are many options for PivotTables. Options for formatting, layout and how data is calculated.

This lesson explores 5 useful PivotTable settings you want to know.

Five Useful PivotTable Settings
08:15

Slicers are a truly wonderful tool. This fast, dynamic and intelligent filtering tool has taken Excel reporting to another level.

This lesson walks you through how to create them, the best way to use them and their many benefits.

The Power of Slicers
10:28

This lesson continues where the previous one finished and explores 7 awesome Slicer settings to have them behave as you want them to.

These extra settings will have you understanding Slicers inside out.

7 Slicer Settings you Will Want to Change
08:53

Homework exercises to recap on lessons from section 5.

HOMEWORK: Section 5
01:03
+ The Hidden Power of the MOD Function
6 lectures 16:58

In this lesson we introduce you to the MOD function. What it does and why you would use it. The examples in this lesson are basic to build the understanding of the function. We expand in the next few videos.

An Introduction to the MOD Function
01:52

In the first example of the MOD function it is used to extract the time from a date-time cell. With the time separated it is easier to analyse.

Example 1 - Extracting the Time from a Date-Time Cell
02:21

The MOD function is used here in a Data Validation rule to only allow the entry of odd numbers.

The formula is demonstrated in a cell first before being placed in the Data Validation rule.

Example 2 - Prevent the Entry of Odd Numbers in a Range
02:43

A very niche example of the MOD function here, but a good example of its versatility. This example comes from my course on creating sports league tables in Excel.

We have a list of cricket overs on a spreadsheet and we want to calculate the total number of balls bowled. The MOD function comes to our rescue with a little help from a friend.

Example 3 - Calculate Total Balls Bowled from Overs in Cricket
03:46

In the final example see the MOD function teamed up with ROW and SUMPRODUCT to sum every third cell of a range. The MOD function is used to help identify whether a cell meets the condition to be summed.

Example 4 - Sum Every 3rd Row in a List
06:07

A homework exercise to recap on lessons from section 6.

HOMEWORK: Section 6
00:09
+ The Magic of the INDIRECT Function
6 lectures 17:42

The INDIRECT function is used in this example with named ranges to make a SUM function dynamic. The range that the SUM function uses is dependent on a cell value.

Example 1 - INDIRECT with Named Ranges
02:49

In this example the INDIRECT function is used to sum ranges on different worksheets.

Again the range that is summed is dependent on a cell value. This time though we need to build a dynamic sheet reference into the INDIRECT function.

Example 2 - Referencing other Sheets with INDIRECT
04:27

Terrific example on the wonders of the INDIRECT function here as we use it to build an R1C1 reference.

We need to return the last value from a row. This can be done using the INDIRECT and the COUNTA functions together.

Because COUNTA will return a numeric value, the R1C1 reference type is used instead of the A1 style.

Example 3 - Return the Last Value from a Row
04:23

The INDIRECT function is used with VLOOKUP in this example for a dynamic lookup. By changing cell values, the VLOOKUP function returns data from a different table.

Example 4 - INDIRECT with VLOOKUP
02:27

In the last example we create a dependent drop down list. The options for the second list are dependent upon the option chosen in the first list.

This trick is created by using named ranges, the INDIRECT function and the Data Validation tool.

Example 5 - Create Dependent Drop Down Lists
03:14

A homework exercise to recap on lessons from section 7.

HOMEWORK: Section 7
00:22
+ The Incredible INDEX Function
7 lectures 45:07

The most common use of the INDEX function is in combination with the MATCH function to create a flexible dynamic lookup formula.

The INDEX and MATCH functions together surpass the limitations of VLOOKUP that may be holding you back.

It is faster, can look in both directions and is not limited to rigid index numbers for columns.

Example 1 - Using INDEX and MATCH for an Advanced Lookup
11:08

In this lesson the INDEX and MATCH functions are used together again, but now for a picture lookup.

For this example, we write the formula into a defined name and then link a picture to that defined name.

When a customer is selected from a drop down list, our lookup formula returns the picture from a list.

Example 2 - Create a Picture Lookup
08:01

The INDEX function is used here to return the value from the last row. From a list of payments we want to return the value of the last payment made.

Example 3 - Return the Value from the Last Row
02:50

Creating dynamic named ranges is extremely advantageous to automate your charts, formulas and PivotTables to ever expanding ranges. This can be done with the OFFSET function, but INDEX is a faster and more efficient technique.

This lesson shows you how to create one-dimensional and two-dimensional named ranges using the INDEX function.

Example 4 - Create a Dynamic Named Range
12:10

Summing a column of values is awesome, but what about summing only the last 6 values, or maybe the last 10, or first 5. Well the INDEX function technique shown in this lesson can be adapted to solve all those situations.

In the video we use INDEX in an Excel formula to sum only the last 6 payments in a list.

Example 5 - Sum the Last 6 Values Only
04:42

This lesson shows a rare and unknown trick for the INDEX function. We use it to select a range of values, from a list of possible ranges, dependent upon the selection made in a cell.

This technique would work well for conditional lookup tables, and values for charts. In this video we sum the values selected by a user in a cell. The INDEX function is used to pick the correct range from a list.

Example 6 - Retrieve a Range from a List of Ranges
05:59

Homework exercises to recap on lessons from section 8.

HOMEWORK: Section 8
00:17
+ COUNTIF Function - A Mega Useful Excel Function
7 lectures 29:28

When entering criteria into the COUNTIF function, it must be entered as a string. This can be a combination those of a string and cell values.

This lesson demonstrates 2 examples including embedding a function in the criteria argument.

Using Cell Values with COUNTIF
04:57

This lesson looks at referencing two cell values to create a date range. The COUNTIFS function is used to handle multiple conditions.

Count Values Between Two Dates
02:58

Handling duplicates is a common task in Excel. Because of this, Excel contains features to highlight or remove duplicate values.

But it would be better if you could stop them in the first place. The COUNTIF function can be used with Data Validation to prevent duplicate values from being entered.

Prevent Duplicates in a Range
04:11

In this lesson we want to identify which names occur on one list, but not on another. Conditional Formatting is used to highlight the missing names.

Compare Two Lists to Identify Missing Items
04:24

This is a wonderful example of the COUNTIF function and its versatility.

The VLOOKUP function returns from the first value it finds in a column. In this lesson we ask it to return the final occurrence of a value in a column. A clever variation by using COUNTIF.

VLOOKUP for the Last Match in a List
07:40

There are functions in Excel to rank values easily. But if more than one has an equal number of points/rank, it is not so easy to separate them.

This lesson shows you how to use our friend COUNTIF to create a unique ranking.

Uniquely Rank Items
05:05

Homework exercises to recap on lessons from section 9.

HOMEWORK: Section 9
00:13
+ Introducing Custom Number Formatting
5 lectures 16:04

In this lesson we combine text and numbers in a cell using Custom Formatting. By using formatting we do not affect the cell value when we include text.

Examples of doing this include showing 50 kg, or 200 metres in a cell, but not changing those values.

Combine Text and Numbers in a Cell
03:51

In the formatting options of Excel, they do not have an option to display the weekday of a date. Fortunately, this is easy to do with Custom Formatting.

Show the Weekday of a Date
02:51

Getting Excel to retain the leading zeros in a zero is a very common problem. One common way is to format the cell as text, and this may be adequate.

But what if you need the cell to be a numeric value? Well we can get Custom Formatting to display any number of leading zeros.

Keep the Leading Zeroes of a Value
03:08

You probably want your negative values to display differently to the positive values. Custom Formatting makes this easy.

You can display negatives however you want. In this lesson we display them in red and inside parenthesis. A very common way to see negatives displayed.

Display Negative Values in Red
03:27

Excel does not really appreciate blank cells. They can hinder our shortcuts and understanding of the table structure.

Entering 0 instead of nothing is a typical way around this. But if you do not want to see the zeros, Custom Formatting can hide them making the cell appear blank.

Show Zero Values as Blank Cells
02:47