One of the most important questions to answer is what is Advanced Excel? Unlike the other Office products, Excel is unbelievably broad and can in fact be used to replace all the other Office products and more (you can write a document in Excel, produce slides and even create databases. You can run projects or create accounting systems. Anything you want).
What is NOT Advanced Excel
Advanced Excel is NOT the ability to do VBA and use array formulas. Although it is useful knowledge it is in no way necessary to be able to work wonders with Excel. In fact, VBA is often used as a crutch to avoid having to think through the tools and formula available in Excel. VBA programmers are often surprised to learn that the ‘process’ they have coded is available as a formula or tool, sometimes something as simple as the NETWORKDAYS formula. Where possible it is better to use Excel’s inbuilt tools. They were tested rigorously on creation and then billions of people have been testing them for years since. They are more likely to work in more cases then the code you hastily created under deadline pressure.
Excel Level 3
Advanced Excel is also NOT Excel level 3. Having attended a level 3 course does not mean much especially if it is only a 1 day course. Above the average result from all the participants in the Free Excel Skills Assessment tool we provide together with the skill levels for Excel 1,2 and 3. Note that most people are automatically above the 1,2 and 3 skill levels even though they are only self taught. Anyone who has worked on Excel for a number of years will have stumbled onto what is taught in Excel 3. Sure you may learn a few new tools or functions, but generally most people leave a level 3 class having learnt very little, sometimes teaching the teacher a thing or two!
Every Feature of Excel?
Advanced Excel is also NOT knowing everything about Excel. There is just too much to know. Look at all the MVP’s out there. They all specialise in a particular area or, if they are generalist, defer to the specialised MVP’s for key areas.
Compared to others?
Advanced Excel is NOT how you compare to the Excel guru sitting in the cubicle next door. Unfortunately, due to how much there is within Excel, people rate themselves compared to the best Excel person they know. There is no absolute measure (although the Skills Assessment will give you a good view). So if you come across a VBA programmer you may think you are only intermediate. Or if you are the only person in the company who knows VLOOKUP you may think you are advanced.
So what is Advanced Excel
In our view, Advanced Excel is the ability to use Excel to get the answers you want from Excel in the safest and quickest way. That doesn’t mean you need to know everything, and you definitely don’t need to program with VBA.
In terms of the Excel skills you need, we follow the principles of the skills assessment as shown below. The four axises are key and most people follow a clockwise progression in their Excel knowledge advancement.
The basics of Excel
So an Advanced user must be able to do the Fundamentals i.e. enter a formula, use absolute and relative referencing and understand how to find answers to their Excel questions using Excel’s inbuilt help, Google and some common sense. To make sure you are up to speed with these, look at the Free Excel Fundamentals Course.
What do you use Excel for
After that it all depends what you do with Excel. In our opinion the only attitude that is required is to understand that anything you want to do is available in Excel. You are not the first person to use Excel. Someone has emailed Bill Gates and asked for that one feature you are looking for and it has been included. Each version of Excel has new tools and functions. The chances of you discovering a requirement that cannot be handled by a current Excel tool is very rare.
Once you accept that it is there, you just need to find it. Most of the features are simple to understand and use. If it is unclear with the Excel documentation (sometimes we think that the Excel developers wrote the help files and they speak a language of their own), there is always an internet page that explains it better. Spend the time to find the perfect feature.
Tools versus Formula
Another important point is that anything you do with a tool (data sort, filter, remove duplicates) can be achieved with formula (IF, VLOOKUP, SUMIF etc). In fact, the formula would have come first and the tool is effectively a simple wizard to help people who don’t know the formula.
- So previously you could use formula to identify and remove duplicates in a list. The current version of Excel has a button that does it for you.
- In conditional formatting you could always create a formula that showed the above and below average items. The current version of Excel has a button that does it for you.
- Using formula you could always summarise a dataset. Next thing you know there are Pivot Tables that do the same thing but in an easy way (Pivot Tables are an intermediate skill, not an advanced skill)
That is why the ‘Using Tools’ axis is the second access in the Skills Assessment graph. Everyone should be able to use them. The advanced component is how you use them and that depends on what you do. If you regularly clean up datasets in Excel, do you use the Data Filter to remove the unnecessary rows? Do you convert the dates from American format (mm/dd/yyyy) to European (dd/mm/yyyy) using the the Text to Column tool? Are you building financial models? Do you use the Data Tables to run sensitivities?
The key is what are you using Excel for. We believe if you are using the best Excel feature for your particular requirement then you are an advanced user. You may not know everything about Excel, but you have the ability to learn and use all the features.
Formula and Functions
Once you start using the formula and functions you will see that Excel can do anything. By common sense you should be able to replicate any process. If you, as a human being, can achieve the expected result with a piece of paper and calculator, then Excel can do it (just faster and safer with more data).
We have found advanced users who don’t know VLOOKUP but use MATCH and INDEX (because that’s what they were taught).
We have come across so called ‘Intermediate Users’ who are able to use an OFFSET.
Super User Attributes
The last axis on the skills assessment is the Super User attributes and this is where the advanced users need to sit. But it is a difficult thing to explain. It is the ability to use the tools and/ or the formula to find a solutions. It is the ability to structure a spreadsheet in such a way that it is easy to use but also flexible enough to handle multiple situations. It is also the knowledge that mistakes can happen and making sure that error checks are built in.
The courses we offer move you through these levels to get you to a place where you are comfortable to find solutions to your own problems.