Advanced Excel: Top Excel Tips & Formulas
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.
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!
Introduction, Scope & Excel Files
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.
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.
Top 10 Excel Tips to Save You Time
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.
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.
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.
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.
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.
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.
Aside from the exercises, take this quiz to test your knowledge.
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.
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
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.
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.
In this lecture find out how to create and work with pivot tables. They are a great tool for summarizing and analyzing data.
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.
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.
This short quiz allows you to test your understanding of the Tips section (from Tip 6 to Tip 10)
In this lecture, I will take you through the steps you need to follow to improve any slow-calculating Excel workbooks.
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.
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
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.
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.
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
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.
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.
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.
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.
This short quiz tests your knowledge on the top 5 formulas covered so far.
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.
In this lecture I will show you how to use the text formulas we learnt in the previous lecture to create more flexible reports.
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.
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.
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.
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 of the top 6 to 10 formulas with this quiz.
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
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.
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.
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.
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.