Excel Chapter 8 of 24: Introduction to 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 chapter 24 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.

So there are 8 chapters (15 to 22) showing you examples of formulas built with the most important functions in each category (except engineering and database).

3 functions have their own chapter because they are the most powerful and important: SUMPRODUCT (chapter 13) and INDEX and MATCH (that always work together as show in chapter 14).

There is a chapter on ISERROR (10) that becomes very useful to avoid error messages, there is a chapter on SUBTOTAL (12) that is really the only function that you will use within dynamic analysis (11 functions in one)

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

When you start developing more complex or long formulas you need to master the concept of absolute/relative references. Lean the easy way to do this with the F4 key in chapter 11.

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.

On functions and formulas start with chapter 8A on general tips on functions and formulas.

Next Chapter: Excel Functions Tipsl

Excel Table of Contents
or use the quick links below

Discover Even More in 50 Excel spreadsheets

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Excel Functions and Formulas