Advanced Excel Tips Formulas

Master Advanced Excel Formulas. Solve Complex Problems. Learn Advanced Excel Skills to Save Time & Impress (Excel 2010)

Last updated 2022-01-10 | 4.7

- Be a Confident & Advanced Microsoft Excel user
- Able to solve complex problems in Excel
- Learn new techniques and formulas to tackle tasks faster and better in Excel

What you'll learn

Be a Confident & Advanced Microsoft Excel user

Able to solve complex problems in Excel

Learn new techniques and formulas to tackle tasks faster and better in Excel

Apply the right tools for a given task instead of going to your go-to tools and making them work for every situation

Reduce complexity of your spreadsheets

* Requirements

* You have basic Microsoft Excel knowledge
* You are able to write basic formulas and are familiar with the Excel spreadsheet environment
* Demonstration is done using Excel 2010. However
* the focus of this training is to teach you new methods of doing things which you can do regardless of the Excel version you have

Description

If you're a business professional, who uses (or will be using Microsoft Excel) on a daily basis, this is the course for you!

The Course will Help You Build a Strong Foundation in Excel & Solve Complex Problems Fast - This is How:

Use my top 10 tips to save time
Learn the top 10 formulas for quick and dynamic analysis

This course includes a detailed downloadable PDF of the top 10 Tips & Formulas - together over 90 pages! Now you can READ, WATCH or do BOTH!

The top 10 will increase your awareness of the most useful Excel features to work faster and smarter. Only when you know the extent and possibilities Excel offers you, only then are you able to handle any type of analysis with the best method.

In Their Own Words:

Carlo says: "Excellent course featuring loads of easily understood content. It helped me become very competent and comfortable with pivot tables along with a host of other functionality. It's easily understood and also easy to follow. Instructor speaks clearly and explains things well, very helpful so the information is easily retained."

Sahar says: "Very very helpful tips. Anyone who uses Excel should learns these to let them get more benefits of the embedded functionalities."

Steven says: "Well explained, excellent material and topics. Definitely very helpful for my everyday work."

Advanced Excel skills are highly relevant in business. No matter which role you’re currently in or you’re planning to take on. Whether you’ll be in Finance, consulting, IT, in project management, your advanced Excel skills will set you apart from the competition.

What is the difference between an Advanced Excel and Average Excel user?

An advanced Microsoft Excel user is aware of the tools and features available and can apply the right tools for the right task, instead of relying on their go-to tools and making them work for every situation. Advanced Excel users, always find the optimal solution for every task, and they find it fast!

What differentiates this Microsoft Excel course from the others?

The reason I created this specific Advanced Excel course, is because there are many advanced Excel users who are unaware of key features & formulas in Excel. I understand that when you take an Excel training it can be so broad, and a lot of courses cover too many topics. It’s easy to get lost. You might also not immediately appreciate why some features are useful and how you can practically apply them. We are creatures of habit and it’s difficult to get our head around learning new techniques. As a result you might be missing out on some features that can majorly simplify the way you use Excel.

Differentiation factor #1

Focus - The top 10 brings focus to the key Excel features which will provide you with a solid & a strong foundation in Excel.

Differentiation factor #2

Learn by doing - This Advanced Microsoft Excel course is well structured and organized. You can download my Excel Demo workbook which I use to demonstrate each feature and practice along with me. You can then do an exercise from the Excel Exercise workbook to test your knowledge. Real learning happens when you attempt to solve problems on your own. Answers are provided, but do ask any questions if you get stuck. Your questions will always be answered.

Differentiation factor #3

Engaging - I have made every attempt to keep the course interesting and engaging by mixing talking head explanations with screencast demos. Each course is split by topic & feature. This allows you to easily pick what you want to learn now or review later.

Summary

My main goal in this training is to increase your awareness of the top tools and formulas in Excel to help you solve complex problems. Users are not aware of what they don’t know. Only when you know the extent and possibilities Excel offers you, only then are you able to handle any type of analysis with the best method.

Becoming advanced in Excel takes practice, readiness to learn new methods and willingness to apply these to practical cases. Take this course to get a head start!

Who this course is for:

You currently use Microsoft Excel and want to improve your skills
You want to be sure you have the Excel knowledge necessary to officially call yourself "advanced"
You are a student, planning to take on a job that requires sound Excel knowledge
You are a financial analyst who uses Excel on a daily basis and would like to learn if there is more to it than you currently know

Course content

5 sections • 45 lectures

Introduction, Scope & Excel Files 3 lectures

Advanced Excel - Introduction & Scope Preview 06:48

Watch this lecture for a more detailed overview of the scope of my advanced Excel course.

I designed this course for anyone seeking to improve their Excel skills. What I’ve seen is that there are many advanced Excel users who are unaware of useful features & formulas in Excel. My top 10 brings focus to the most important Excel features which will provide you with a strong & solid foundation. It will save you time and frustration when you analyze data and create reports.

To use Excel, you don’t need any specific pre-knowledge. You can get by with it’s user friendly interface and basic functions. But to be able to effectively use Excel, to be fast in your reporting, to quickly solve complex problems that your business gives you, you need to be advanced. Being advanced means, you are aware of the main tools and formulas that are available, and you can apply the right tools for the right task. This sets you apart from the rest.

Note that you don’t necessarily need to take the lectures sequentially. If you want to jump to a specific topic of interest first, do so.

DOWNLOAD these Files First Preview 02:03

The files you need for this course are right here. Download the two Excel workbooks from the resources tab. One is called Excel_Advanced_Demo and the other Excel_Advanced_ExerciseBook. You will also see a text file. Download that as well. You willl need it later to complete an exercise.

You can follow my demonstrations in the Demo workbook and complete the exercises in the Exercise book. Note that the answers to the exercises are included in the same workbook, but only refer to these once you have really tried to solve them on your own first.

To actually learn and use new Excel techniques, you need to apply them at every opportunity you get in your own spreadsheets, even if that means it will take you longer the first time. In the long run, it will mean time saving and robust spreadsheets. Take notes when you watch each tip and formula and think about your own Excel files and where you could apply these.

Course Outline for Quick Reference Preview 00:08

Top 10 Excel Tips to Save You Time 20 lectures

Overview of Top Excel Tips Preview 02:19

Watch this lecture to get an overview of the top ten tips in this section. While there are a lot of functions and tools available in Excel, there are a few that are extremely helpful when you use Excel on a frequent basis. In this tips section I’ve included topics that are probably not in a basic excel training and also if you’ve taught yourself Excel and worked yourself up to advanced status, you might have missed some of these features.

If you are already quite advanced in Excel, take this tips section as a "filling in the gaps" type of exercise.

Take whichever tip you find useful and try to immediately use it to your own Excel spreadsheets.

Tip 1 - Become Faster with Excel's Keyboard Shortcuts Preview 09:35

Why are Excel keyboard shortcuts useful? Because they let you finish your tasks faster. Watch this lecture to find out which Excel shortcut keys are real time-savers. I share my most used ones. You can also print out the short-cut list from my Demo workbook and also use the link I provide to find the list of all the Excel shortcut keys.

Tip 2 - Master the Simpler Tasks: Part 1 Preview 18:12

This lecture and the next is called “Master the simpler tasks”, because there are a handful of very useful tools that can make working with Excel much easier. You might be familiar with some of these from a previous basic training, or you happened to stumble on the feature by yourself. I just had to be sure you knew these, because it’s important for any Excel user to be familiar with them.

Tip 2 - Master the Simpler Tasks: Part 2 Preview 09:23

Tip 3 - Quickly Navigate Larger Files Preview 11:38

When you work with large Excel workbooks or worksheets it’s important to know some tips and tricks on how to efficiently cruise between various sheets and work simultaneously with different workbooks. Watch this lecture to find out how.

Tip 4 - Inspect Workbooks for Hidden Information Preview 10:00

In most cases when you start to work at a new company you take over the existing Excel workbooks of your predecessor and continue to update and expand on the workbook. Normally these files are quite large and contain many tabs and it’s not always clear if there is hidden information such as comments, personal information, hidden rows etc in the file. Some hidden information can be deliberate whereas some might be unintentional. To find all this out manually can be time consuming. That’s where you can use the Inspect document feature. A great tool to use before distributing workbooks.

Tip 4 Extended - Unhide Hidden Names with VBA Preview 01:29

Tip 5 - Use Protection Appropriately Preview 16:36

Protecting an entire workbook, sheets or specific ranges within a workbook, is a good way to make sure templates and common files are not unintentionally or intentionally changed. In this lecture, you will find out the best methods to do this.

Test Your Knowledge - Top 5 Tips (Don't Skip)

Aside from the exercises, take this quiz to test your knowledge.

Tip 6 - Import Text, CSV & Web Data Properly Preview 11:55

In this lecture learn how to best import your data into Excel. Specially those extracts you get from your ERP systems. You will also learn how to import data from the web and how to automatically update your query based on your own specifications.

Tip 7 - Audit & Debug Formulas Preview 08:41

Once you have mastered Excel functions and you start to use more complex formulas and more robust spreadsheet, you are likely to run into two issues:

1. You have mistakes in your formulas but because they are too long and complex you can’t find where the problem is

2. The result of your Excel formula throws an error for some cells and you’re not sure why

In this lecture I’m going to give you a tour of the formula audit tools that are available and how they can help you track and debug your formulas

Tip 8 - Improve Presentation with Customized Number Formatting Preview 17:23

Custom formatting is a great way to manipulate the presentation of your data without actually changing the data. Many Excel users try to use intermediary steps to change the look of the data. These steps can generally be avoided with the use of custom formatting.

In the Demo workbook, I have included a table with the list of characters and what they mean and how they can be used. You can print it out and refer to it when you need to design your custom formatting.

Tip 9 - Solve Problems With Goal Seek Preview 06:33

The purpose of goal seek is when you know the end result but don’t know what the input should be to get to your result. Goal seek is one of the simplest tools to use. You just need to know how. Find that out, in this lecture.

Tip 10 - Use Excel PivotTables for Super Fast Analysis Preview 16:20

In this lecture find out how to create and work with pivot tables. They are a great tool for summarizing and analyzing data.

Tip 10 - PivotTables: Calculated Fields, Slicers & Helpful Tricks Preview 16:16

In this lecture, I will show you some more advanced tips and tricks with regard to the PivotTable. This includes how to create calculated fields, to use slicers, PivotCharts and a little taste for creating advanced charts & dashboards.

Tip 10 - PivotTables: Valuable Design Tips Preview 23:12

PivotTables are a great tool for quick analysis and also as a data feeding tool for your dashboards. People however, rarely use these as an integrated table in the actual reports that they print out. Even though the formatting of the Pivot tables has majorly improved in the past years, they’re still not seen as visually pleasant for the final report, so they’re generally integrated in the back-end and a nicer table that references the pivot is used for the front end. In this lecture I will show you great design tips on how to optimize your pivot tables for the final report.

Test Your Knowledge - Top 10 Tips (Don't Skip)

This short quiz allows you to test your understanding of the Tips section (from Tip 6 to Tip 10)

Bonus Tip - Methods to Speed up Your Excel Files Preview 05:36

In this lecture, I will take you through the steps you need to follow to improve any slow-calculating Excel workbooks.

Bonus Tip - The Possibilities & Limitations of Excel Preview 06:48

In this lecture, I share my view of Excel's potential and limitations. If you work in a bigger company that has BI systems in place, the common question that arises is usually: Which system do we use to create this dashboard? Or how do we track and report these KPIs? Do we invest in a new tool or use what we have? Sometimes the answer is sitting write on your desktop. It can be Excel.

PDF to Download - Top 10 Excel TIPS to Work Smarter (in print and in detail) Preview 00:28

The downloadable PDF is a detailed documentation (over 60 pages) of the top 10 Excel Tips we cover in this section. Together with the Excel Workbooks which can be downloaded from the Resources tab, you should have all the material you need to practice and master the techniques.

Top 10 Excel Formulas for Quick & Dynamic Analysis 16 lectures

Overview of Top Excel Formulas Preview 03:26

Excel offers an incredible range of formulas that enable dynamic analysis of data. In this section I will introduce you to the top 10 Excel functions I believe every user who deals with Excel on a frequent basis should know. With these functions you are well equipped with the knowledge you need to handle pretty much any situation that requires you to analyse data or grab a piece or pieces of information from a large table.

Formula 1 - SUMIFS, COUNTIFS, AVERAGEIFS to Account for Exceptions Preview 14:03

The inclusion of the "IFS" formulas since Excel 2007 was a major gain for Excel users. Why? Because they allow you to easily add exceptions to your sum, average and count calculations. Don’t make your life more difficult by trying to use excel filters to get your sum. Use the SUMIFS formula instead.

Formula 2 - IF (Nested Functions) Because Data Analysis is Often Not Simple Preview 12:00

You probably know the IF function from a basic Excel training, but what you might not be familiar with is to use nested formulas, meaning more than one IF in a formula. Because the reality is, there are always exceptions to the exceptions. Find out more in this lecture

Formula 3 - IFERROR for Credible Reports Preview 05:53

The IFERROR function provides an elegant and simple way to hide errors in reports. It's a must have to anyone creating reports in Excel.

Formula 4 - VLOOKUP & HLOOKUP For Dynamic Lookups Preview 11:27

Most relatively advanced Excel users are familiar with the VLOOKUP function. I have included it in this section to make sure you know it. It's a great formula because it can search for a value or criteria in a table and return a respective value from an adjacent column. HLOOKUP works exactly in the same way except that it works horizontally rather than vertically. The “V” is for vertical and “H” for horizontal lookups.

Formula 5 - INDEX & MATCH for Flexible Lookups Preview 16:23

If there is one formula that you take away with you from this training, it should be the INDEX and MATCH function. The VLOOKUP formula has its limitations and that’s where the INDEX and MATCH function comes to the rescue.

Formula 5 - INDEX & MATCH for Complex Real-Cases Preview 18:45

In real life, your Excel files are always more complex than the ones in the training. In this lecture I will show you how to tackle real-case scenarios where you have a complex table and you need to lookup values from this table to provide a comprehensive report.

Test Your Knowledge - Top 5 Formulas (Don't Skip)

This short quiz tests your knowledge on the top 5 formulas covered so far.

Formula 6 - The Most Useful Text Functions Preview 12:38

Excel Text formulas can save a great deal of time cleaning up your data and getting them in the format you need. Sometimes you need to manipulate or clean up the descriptions of your products, your customers or account descriptions. You might need to cut out, substitute or replace a part of your product codes. In this lecture I will show the most useful Excel text formulas.

Formula 6 - Advanced TEXT functions Preview 17:27

In this lecture I will show you how to use the text formulas we learnt in the previous lecture to create more flexible reports.

Formula 7 - Excel's Top Date Functions Preview 20:24

Excel's Date functions are great when you need to create a timeline for your project, or distribute a reporting timetable. I often see people refer to a calendar when they create these reports. There is a big chance of making mistakes. Use Excel's Date function instead. In this lecture, I will introduce you to the ones that consistently save me time.

Formula 8 - OFFSET for Dynamic Calculations Preview 14:47

OFFSET is an interesting formula and it’s one that can do so much more than meets the eye. Chances are you’ve been needing this formula because it can make so many calculations dynamic.

Formula 9 - FREQUENCY, SMALL & LARGE to Organize and Rank Preview 15:26

Did you ever need to organize your data into categories and then report on these categories? That's where the FREQUENCY as well as the SMALL and LARGE functions come in.

Formula 10 - SUMPRODUCT For the Gurus Preview 14:55

SUMPRODUCT is one of the most powerful formulas in Excel. Many people use it for it's classical purpose. In this lecture, I will take you beyond this and show you ways of using SUMPRODUCT that can help you avoid many "workarounds" you've been doing.

Test Your Knowledge - Top 10 Formulas (Don't Skip)

Test your knowledge of the top 6 to 10 formulas with this quiz.

PDF to Download: Top 10 Excel FORMULAS to Work Smarter Preview 00:29

The downloadable PDF is a detailed documentation (over 30 pages) of the top 10 Excel FORMULAS we cover in this section. Together with the Excel Workbooks which can be downloaded from the Resources tab, you should have all the material you need to practice and master the techniques.

Formula Combinations for Real Cases 4 lectures

YTD Calculations - SUM, SUMPRODUCT & OFFSET Preview 13:14

A real-life example where smart, dynamic formulas can help. In this lecture I show how you can calculate YTD (year to date) values for volumes and prices using SUM, SUMPRODUCT & OFFSET functions.

3 Ways to Lookup within Boundaries: SUMIFS, SUMPRODUCT & INDEX Preview 11:34

Sum Alternate Columns based on Selection - SUMIFS & INDEX Preview 10:38

In this lecture we have the task to create a dynamic sum based on user selection. The user will select the column header name and we need to provide the sum of the columns and also make exceptions.

Sum Alternate Columns - SUMPRODUCT Preview 10:00

In this lecture I will show you an alternate method to sum different columns based on user user selection. The method uses the SUMPRODUCT formula. I also compare the speed to the different formulas to see which one performs better on large data sets.

Wrap Up 2 lectures

Final Words Preview 02:22

Don't forget: To be a real master in Excel, you have to apply these methods to practical problems. This forces you to use the knowledge you learnt in the training in creative ways to help improve your own spreadsheets.