Below, I offer you a table of levels of knowledge that I compare to ways of travelling. The third column shows you the material that you should know about for each level.

In this third column, there is a lot of material that is absent for many reasons:

- a lot of functions in Excel have been reviewed, replaced and enhanced and it would be useless to learn about the old ways unless this web site was about the history and the evolution of Excel,

- a lot of functions are redundant and I only mention the functions that I personally use. For example, INDEX/MATCH is very similar to LOOKUP, VLOOKUP and HLOOKUP but I find INDEX/MATCH to be superior and more powerfull than the latter. So you wont find anything on LOOKUP functions in this list or in this web site,

- a lot of functions are very specialized, they are not indicators of level of knowledge so they are absent from this list,

- finally, there are a lot of functions that I find totally useless so I am not burdening you with them.

A special word on Pivot Table. Pivot tables are great tools to analyze the data but a loosy tool for reporting. If you need to produce reports you need to know about SUMPRODUCT and INDEX/MATCH formulas. If you have questions and are looking for answers within the data, Excel's database functions (sort, filter, subtotals) and conditional formatting are tools that you should know about. As for pivot tables, if you are trying to develop new reports, if you are looking for smart questions, if you are looking for a dynamic way of analysing data, pivot tables are THE tools.

Level 1 is sufficient when a user enters data manually and when his task is limited to producing reports.

At level 5, I consider a user an expert. He can maintain most of the applications that I design except for the programming in VBA. A level 5 user can transform a set of data coming from any database into a report by just replacing the old data by new data. A copy/paste.

Levels 6 to 10 include knowledge of VBA (Visual Basic for Application, Excel). VBA serves to automate manual tasks. With VBA you do the same thing that you would do manually but you do it 10, 100, 10,000 times faster. You HONESTLY CANNOT pretend to have reached levels 6 to 10 if you do not master the knowledge of levels 1 to 5. In my career as Excel VBA Developer, I have often have to destroy applications developed by "VBA Programmers" that didn't know Excel. Some of these applications had taken months to develop, I had no choice but to throw them in the garbage and redevelop something else in 2 to 3 WEEKS.

I is interesting to have in each department of an organization a person who has reached level 6. A lot of small VBA procedures (macros) that save a lot of time and errors can be transferred easily from one workbook to the other.

With VBA, almost anything is possible. You can develop applications and reporting systems that are fully automated. You can accomplish in minutes tasks that even a group of users couldn't perform in a year. You can have hundreds of reports generated without any human intervention. The difference between levels 7 and 10 is 1,000 lines of code running in 2 hours versus 100 lines of code running in 2 minutes and doing the same thing.

Level

Like

Knowledge

1

Walking

Basic functions (+, -, *, /), fonts (color, type), sizing columns and rows, adding/deleting sheets, cell borders, number format, page setup, copy/paste.

2

Running

Charts, Formulas (using many functions), complex functions (AND,AVERAGE,CHOOSE,CONCATENATE,IF, INT, ISERROR, LEFT, RIGHT, MID, MAX, MIN, RANK, ROUND, TRUNC, VALUE.

3

Bike

Conditional formatting, protection, dates and times, data validation.

4

Motorcycle

Database Functions: sort, filter, forms, subtotals.

5

Car

SUMPRODUCT and INDEX/MATCH formulas.

6

Small plane

VBA Recorder, VB Editor

7

Helicopter

Moving around (working with addresses in the workbook)

8

Jet

Using variables

9

Shuttle

Select Case, Loops, application Functions

10

Beam me up, Scotty

Variant Arrays

Whatever level you want to reach, this web site and the
25 Resource Workbooks
will prove to be invaluable tools.