Advanced Excel: Top Excel Tips & Formulas
- 7 hours on-demand video
- 5 articles
- 8 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
- 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
- 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
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!
- 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
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.
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.