Other Links

Section 3: Excel Functions and Formulas

To become an expert you don't need to learn about all the functions in Excel you need to learn how to assemble THE FUNCTIONS THAT YOU NEED into useful Excel spreadsheet formulas.

I work on hundreds of projects each year and I use only 20% of the 328 functions and 17 signs offered by Microsoft Excel.

Learning about Excel functions and formulas is like learning a new language so that you can talk  to Excel. In this new language the FUNCTIONS are words and the FORMULAS are sentences. If you can say it in English there is a formula for it. You might know only 4 words in English " Be" , " Not" , " To" and " Or" but your words become very powerful and meaningful when you write:To be or not to be

So there are 328 functions in Excel and they are all listed in the appendixes together with what they do. Some functions are specialized and used only by specialists (engineering, financial and statistical). Some other functions become obsolete when you learn about the IMPORTANT ones. For example you will drop MAX and MIN when you discover LARGE and SMALL. You will stop using SUMIF and COUNTIF when you discover SUMPRODUCT and you will forget about LOOKUP, HLOOKPUP and VLOOKUP when you start using the more powerful and less limited INDEX/MATCH and also SUMPRODUCT. You will also forget about all the database functions when you start using SUMPRODUCT.

To really become efficient with Excel functions study lessons 3-1-to 3-5 then discover the most important functions that deserve their own lessons 6, 7 and 8

Next there are 8 lessons (9 to 17) showing you examples of formulas built with the most important functions in each category (except engineering and database).

You will also find lesson 18 on functions that are powerful (SUMIF, COUNTIF, VLOOKUP, HLOOKUP and LOOKUP) but that have become obsolete when you discover the much more powerful SUMPRODUCT and INDEX/MATCH.

So where should you start?

If you ask me what 4 functions I would keep in a simplified version of Excel I would say SUM, SUMPRODUCT, INDEX and  MATCH. With these 4 functions and the understanding of absolute/relative references you can build automated reports, a production planning application, a permanent inventory, an automated invoice, a management by sub-projects tool and even an accounting application WITHOUT ANY MACROS (VBA).

As a matter of fact when visitors ask me how they should approach learning about macros (VBA) I tell them to learn  about SUMPRODUCT and INDEX/MATCH first.

The lessons in this section are: