What Truly Are the Different Levels of 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

By nature, these three activities reflects the progression of task complexity.

The A1 are the basic clerical activities that every Excel users would use it for. The A2 activities are what professional, technical, and management position people would do, and it comprises the A1 activities as its foundation. The A3 activities comprise the A2 activities in its core, and are performed by people geared towards more analytical and technical positions. 

One cannot perform A3 activities within Excel without the ability of A2 activities. As well, one cannot perform A2 activities without the ability of A1 activities. 

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 operations 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.

Majority 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 the first group of activities - storing and presenting data?

Level 1

At this level, people know how to navigate around the Excel application. 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 Excel 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

Level 1

They understand the concept of relative reference, absolute reference, mixed reference. They definitely use VLOOKUP/XLOOKUP, SUMIFS, COUNTIFS, 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 also 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/XLOOKUP.

When they use VLOOKUP or HLOOKUP or XLOOKUP, they know how to lookup values in a range to return a corresponding value which level 1 people have no clue about even after using VLOOKUP for 10 years. Level 1 people may do a VLOOKUP and get a #N/A, even though the formula is written correctly, the data are all in place, but for "no reason" refusing to cooperate. Level 2 people would understand right away, what the common cause for that problem is, and just nest another function inside the VLOOKUP, the formula would work and return appropriate results.

They love to use the incredible ampersand “&” symbol in their VLOOKUP/XLOOKUP to lookup based on multiple criteria, instead of just one criteria that level 1 people understand. 

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/XLOOKUP, 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 not difficult 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, LEN, SEARCH, SUBSTITUTE LEN, 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)”.

They 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 periodic reports into models with formula, they have no need of Macro. They may not know about array formulas, or if they have heard about array formulas, or even used SUMPRODUCT function, but still largely oblivious of array formulas principles.

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/XLOOKUP 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 containing a nested array formula. Using the “&” for multiple criteria VLOOKUP without supporting column 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 handle 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.

They are aware of the Dynamic Array Formulas introduced in Microsoft 365 version of Excel in the year 2020. They are happy for the relief brought about by the new functions, and use them wherever possible, 

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 relatively simpler in terms of Excel skills. 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. Some 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 semi-manual, not very efficient.

Level 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. But recording macros 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 Courses At?

The above diagram gives you a rough view where each Super Excel course fits in that landscape.

Again, I'd like to say, by no means, this diagram covers all or even most of Excel's capabilities. As a matter of fact, all those mentioned in this diagram combined, probably only account for 20% (or less) of Excel's total capabilities. However those are the ones contributing to 80% of all Excel users' work.

Since all Super Excel courses are designed from real life usage perspective, and they are taught in a different way too, so it’s hard to compare them with the conventional Excel courses designed from software development perspective. 

SE1-A Super Excel Tables & Formatting course focuses on how to store and present data in tables format.

SE1-B Super Excel Pivot Tables & Charts course focuses on how to summarize data quickly to gain business insights and present data visually in charts.

SE2 Super Excel Data Processing & Analysis course focuses on how to process and analyze large amount of various type of data effectively and efficiently.

SE3 Super Excel Modeling & Array Formulas course focuses on more complex data analysis and business planning with Excel models.

With the Super Excel Training & Coaching, ordinary people can learn practical Excel skills quickly with the ongoing coaching support, and become Excel superheroes at workplace in relatively short time.