What do you need to know to be considered advanced in excel?

Posted by:

What do you need to know to be considered advanced in excel?

Need to know to be considered advanced in excel. Excel does a lot and most training companies and trainers break up Excel into three levels: Basic, Intermediate, and Advanced. Here is what I would say makes you advanced. This list is not all-inclusive:

After surpassing main formatting features in Excel, you will come across Excel formulas & functions part, which is the major feature of Microsoft Excel. Honestly speaking, without Excel formulas knowledge, it impossible to become an Excel expert. If you are dealing with numbers, analysis and so on, then Excel functions will save your life!

KNOW THE HANDY FUNCTIONALITY THAT EXCEL HAS

Others have listed these … and yes they’d include pivot tables, filters, sorting, graphs, sparklines and importing CSV files amongst other goodies.

KNOW WHAT GOOD PRESENTATION IS

I’ve heard that design is a bit like french kissing and driving – you really should ask around before your certain you might be any good at it!

My point is that most people are pretty bad at design even though they think they are pretty good.

The design isn’t just about aesthetics. It encourages user adoption and increases information absorption and in turn affects decision making.

Again, I won’t go into details but a great place to start would be to google search Stephen Few – he wrote a book that is a bit of a benchmark in this area. Also google Robin Williams (no, not that one; she is a designer) and I think her book as called Design for Non-designers … or something like that. There is also a good book by Jon Moon … I forget the name … something about business document design principles.

KNOW AND IMPLEMENT GOOD STRUCTURE

Good structure helps create a useful powerful formula and eases the use of maintenance of a spreadsheet. Here are some tips –

  • Have a separate page for each of outputs, inputs, lookups, OPTIONAL: calcs, and data. There may be some other categories, but you get the idea. Try to have one sheet do one thing. Sometimes this won’t apply but often it does
  • Have your data sources look like data sources. That means in a table with headers and continuous (or continuous-ish) data going down and across the page. It should not look like a report … that just makes things difficult
  • Highlight where you want users to make inputs. I use a lemon color for this but anyway to flag this would help
  • Add a ‘how to use this spreadsheet’ tab. Oops. Missed that from my first bullet point!

Anyway … that’ll probably do for now. Notice what’s not in my answer? Any reference to macros or VBA. Sure … a full-on Excel guru will probably know how to program macros but my experience is that powerful, well designed macro-less spreadsheets can get you a long, long way before you need to think about VBA style automation. And there are downsides to using macros, some of which I’ve outlined here – Paul Spiteri’s answer to Are skills in Excel VBA useful to have?

Before I go – I’ve been capturing what I believe to be some key Excel skills and best practices on a site I own and run which is located here It’s Like Netflix for Learning Excel. There is plenty of free content (and some not) so you may find a tidbit that will make you just that little better in Excel.

If you find yourself yawning by the time you get to article number 9 and can do everything he teaches blindfolded with one arm tied behind your back, then you are an Excel Jedi (that is, advanced).

So, it’s hard to answer. I’d suggest being advanced is knowing 80 percent or more of the functionality, and that includes strong VBA skills too.

But knowing the tool is not enough, even if you can write VBA code in your sleep. Those skills need to be easily used in nearly every business problem thrown at you that can be addressed in a spreadsheet. So practical needs to trump theoretical. Otherwise, your knowledge of the tool is essentially useless

And those little bits add up.

The reality is that the term “advanced” has become diluted beyond repair. Some people would try to wow me on a CV that they can do a VLOOKUP, or a SUMIFS/COUNTIFS, but the reality is I would consider advanced as something like being able to connect to a SQL server and pull information back and creating an analysis model using data tables meaning that what would take someone 4–5 hours to set up if they were “okay” at Excel, I can do with an automatic refresh. VBA understanding is also in there, but I would then “grade” VBA with a different level to Excel. You can be intermediate Excel and set up some basic VBA.

If you have already been using Microsoft Excel then you know that the calculation, graphing and tables available help you track, manipulate and report important data. There is more to excel than a few SUMs, though. There are more than 450 formulas and functions in Excel and advanced excel shows you how to put them to their best use.

  1. Beginner Level:

2. Medium Level:

3. Advanced Level:

So, if you want to get more into Excel formulas & functions, than I suggest you to take a look at Sonka’s Excel Training Templates – Practice Workbooks for Self Learning, prepared considering Practice Yourself & Learn Yourself approach.

Good Luck!

0

Add a Comment