Microsoft Excel - Advanced Excel Tricks that Impress
- 6.5 hours on-demand video
- 11 articles
- 3 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.Try Udemy for Business
- 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
- 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
★★★★★ 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.
- 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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.