Excel Skills 5Most people think of Excel skills as of the common known three levels, i.e. Basic, Intermediate, Advanced.

Actually that does not tell employers what you are capable of with Excel, nor does it tell you what employers are really looking for in terms of Excel capability.

Let me help you to understand what the different levels of Excel skills are, so you know where you are, and what actions you need to take to gain the skills to achieve your career goal.

First of all, purely completing the three levels (basic, intermediate, advanced) of Excel courses, one is still only at the BASIC level in the grand scheme of Excel skills. To avoid confusion, here I will not use those three terms to label the different level of Excel skills.

When interviewing candidates for analyst position, an Excel expert would not ask which level they were at. Instead one would ask which functions or features they have already used, and how they used them. And that tells a lot about their Excel skills.

People Use Excel to Perform Three Types of Activities

  • A1 – Storing and Presenting Data

  • A2 – Processing and Analyzing Data

  • A3 – Modeling and Planning Business Operations

For each of the three activities, there are three levels of skills involved. Let’s call them Level 1, Level 2, and Level 3.

A1 – Storing and Presenting Data

You use Excel to store the data, sales data, marketing data, production, data, HR data, admin data, financial data, as well to present data, in a table format or chart format with various shapes, size, and colors.

A2 – Processing and Analyzing Data

There are data everywhere. They come from various sources, in all kinds of layout/format, with numerous fields. However there are connections with those data. The marketing data has connection with the sales data, production data, logistics data. The employee salary data has connection with operational expenses data, net profit data. You use Excel to process those data and analyze your business, so you understand what is going on with the business and why it is going in the way it has been going.

A3 – Modeling and Planning Business Operations

You are planning the operation according to the business strategy, or according to a solution that senior executives figured out to turn the business around. You are doing business forecasting based on the marketing program your company have launched; you are budgeting employee salaries based on sales projection or new markets the company is planning to enter; you are planning business operation during Christmas to ensure you have the right product at the right time at the right amount to the right customers. Many of those planning take place within Excel regardless how big or small your company is.

I’m not saying all those three types of activities happening within Excel. Definitely not.

Excel Skills 4Majority of those activities should take place in an enterprise system, which would be handled by your IT staff and system consultants. The minority is not a small few, instead it is still a large amount of small activities as the long tail (as in the long tail theory), which are handled within Excel by all those managers, analysts, coordinators, specialists, and many other positions.

A1 – Storing and Presenting Data

So what does each level skills look like in storing and presenting data?

Level 1

This level of people know how to navigate through the Excel software. They know how to enter data into various Excel workbooks, worksheets, tables and find them. They can present final summary of the data in a nice tables with fancy colors and filter feature. They can also present the data summary with line charts, bar charts, and incredible pie charts, they can also print the results on paper and formatting looks fine.

Level 2

They probably use the new Table feature, all types of text formatting and alignment, very fine color setting in cells and fonts. They can use the Sum, Count, Average functions to summarize the data in the table. They know about various controls to do drop down list, combo box. They can customize the cell formatting so even though the number appears as $907 but the actual value is $907,486 as they are in ‘$000. Their spreadsheet cell fill color, font color, border style will change automatically when the data in the cells change due to the conditional formatting feature. Their charts are much more complicated than level 1’s. They do secondary axis, both lines and bars in the same chart. They can adjust almost any element of a chart at ease.

Level 3

Their data may not even be stored in the Excel workbook, rather they are stored in the server in text file, or Access database, or SQL servers. Excel is only the interface to retrieve the data, or retrieve the processed results when using Power Pivot. Their charts are not charts. They are called Dashboard. They are dynamic, clean, simple, to the point, and their charts involves very complicated formulas and multiple layers of charts.

A2 – Processing and Analyzing Data

Excel Skills 1Level 1

They understand the concept of relative reference, absolute reference, mixed reference. They definitely use Vlookup, Sumif, Sumifs, Countif, If, as well as some text functions like Left, Right, Mid, Search functions.  They are proud of their ability to use those functions to process and analyze large volume of data otherwise would be impossible to accomplish. Pivot Table is a great tool to them. When they enter a formula, typically they use the Insert Function wizard, which presents a box for each argument with explanation.

Level 2

They use the mixed reference to allow the range to change in dynamic ways that level 1 people never thought about. Of course they use those functions level 1 people use. They have many more functions at their disposal. They may use the add-in data analysis tool like regression. They handle dates and times with functions at ease. They know the difference and commonality between Index & Match combination and Vlookup/Hlookup. When they use Vlookup or Hlookup, they know how to look up a range instead of an exact value which level 1 people have no clue about even after using Vlookup for 10 years. They love to use the incredible “&” symbol in their Vlookup to look up based on any criteria. Using the named ranges helped them to organize various type of data.

They cannot work without If function. They will do seven or eight layers nested If, and each of those true or false output arguments of the If are also nested Sumifs, Vlookup, Countifs, Iferror functions insides. And and Or functions are integral part of their nested If functions. They will actually draw a flowchart before they start doing the long nested formulas. At the end, this is a logic game. They love and enjoy it. Formula like this “IF(M15>20000,IF(OR(G15=”AL”,G15=”CT”,G15=”IN”,G15=”IA”,G15=”TX”),IF(K15=”Dry Goods”,”Con-Way”,”IFS”), IF(K15=”Dry Goods”,”Vitran”,”Schneider”)),IF(K15=”Dry Goods”,”Yellow”,”Transx”))” is relative simple to them. They know how to break it down to small pieces and how to put the pieces back together.

They definitely also use those text functions like Left, Right, Mid, Search, Substitute, Len, Rept, but more importantly, their formula in one cell is insanely long, something like “=MID(O40,SEARCH(“^”,SUBSTITUTE(O40,”-“,”^”,LEN(O40)-LEN(SUBSTITUTE(O40,”-“,””))),1)+1,50)”.

Excel Skills 2They would question level 1 people, why do you need to use Macro while you can automate everything with formulas? True. They have built most of their regular repeating reports into models with formula, they have no need of Macro. They used to use Sumproduct or array Sum functions with a pride, but now with Sumifs, Countifs, Averageifs, everyone can accomplish same objectives without even knowing the concept of Array Formula.

Level 3

Their formulas are much more versatile. They frequently use Row, Rows, Column, Columns, Offset, Indirect to create dynamic ranges in their formulas. These people, they are pros in terms of using array formula. They would Vlookup based on multiple criteria of <>, >, < without leaving a trace of any supporting cells of intermediate steps. All the steps are self supported and encapsulated in one cell. Using the “&” for multiple Vlookup is just child play for them. They love boolean logic, and use multiplication, #DIV/0, FALSE strategies to force those not meeting criteria being filtered out in the final calculation.

They are enthusiastic about writing beautiful formula to extract unique list from a data set, or even have the unique list to be returned in a sorting order. Do not get me wrong, they are not really using a new set of more advanced functions in the array formulas. No. They are still using mostly the same function as Vlookup, Index, Match, Sum, If, Sumifs, Countifs, but they are using them to handel arrays. The most deceiving part of those array formula is that the arrays are there, but you do not see it. Because they mostly exist in Excel’s memory, and not appear in an array of cells. You really need to have a big brain to hold all those arrays visually calculating in your memory. To them, nothing is impossible. If you can think of a problem, they can figure out an array formula to handle that.

A3 – Modeling and Planning Business Operations

Level 1

They build models with formulas to plan the business operations, but due to their limited knowledge and skills in doing complex functions/formulas, these models are relative simple in terms of Excel. They have the structure in place though. Input sheets are separated from output sheets, and processing sheets. The formulas in all three group of sheets lack of connection. Many of them need to be updated manually each time running the model. When I did the quarterly five-year forecast in the early stage of my analyst career, my function/formula skills was only at level 1 in terms of processing and analyzing data. I did the planning, but it’s very low efficiency, semi-manual, not good at all.

Excel Skills 3Level 2

They are building highly complex planning model, with clear structure in terms of model building. They either used simple functions but very creative logic to achieve the optimization, or used complex nested formulas to consider all those constraints and inputs in the model. The array formulas come in very handy for the modelers, however function/formula still have their limitations, and may not be the best way to build model for every circumstance. In that case, VBA (Visual Basic Application) will be required at Level 3.

Level 3

People would be writing VBA code to handle part of those models or whole models. Macro is actually VBA code in the back end. it’s just that users do not need to understand the VBA, they just need to record a macro and run the macro. But recording has lots of limitations. They are static and rigid, many functionality can only be accomplished through writing the VBA code. This requires somebody with strong knowledge in VBA programming.  Typically, the person who wrote the program, is not the only person using this program. There will be many other users using this tool to conduct the planning, and they do not need to have VBA knowledge. They just need to know where to enter input data, which buttons to click and in which order, then the program will produce the planning output accordingly. Depending on how much time they spending on programming, they are at the edge to cross from a business professional to IT professional.

Which Levels Are the Super Excel Training Courses At?

Since all Super Excel courses are designed from real life usage perspective, so it’s hard to compare them with the conventional Excel courses designed from software development perspective. If we really have to compare:

SE1-A Super Excel Tables & Formatting course helps you to gain the conventional Basic level Excel skills and more. It focus on how to store and present data in tables format.

SE1-B Super Excel Pivot Tables & Charts course helps you to gain the conventional Intermediate level Excel skills and more. It focus on how to summarize data quickly to gain business insights and present data visually in charts.

SE2 Super Excel Data Processing & Analysis course helps you to gain the conventional Advanced level Excel skills and much more. It focus on how to process and analyze large amount of various type of data effectively and efficiently.

SE3 Super Excel Modeling & Array Formulas course helps you to gain the Very Advanced Excel skills that most conventional Excel courses do not even teach. It focus on more complex data analysis and business planning with Excel models.

With the Super Excel training approach, we can help ordinary people to become Excel Superheros at workplace in very short time.