Excel Functions and Formulas

Excel has not changed much since the 1980's (including the 2007 version) but the computer on your desk has changed dramatically. It is hundreds of times faster and more powerful than 10 years ago. It is also connected to multiple sources of data within your enterprise and to data sources from around the world through the Internet.

Due to these changes to your computer, Excel is limitless as a data analysis and reporting program.

Let me offer you my 30 years of experience in accounting and finance and my expertise in Excel certified by Microsoft to help you use all of this data and transform it into sound business decisions.

Peter Leclerc

To develop formulas in any version of Excel you can use 21 signs (+, -, =, etc...). Excel (1997 to 2006) also offers you 329 functions and 5 new functions have been added to Excel 2007 for a grand total of 334 functions.

When you click on the "Insert Function" icon Excel shows all these functions to you grouped in 10 categories. Only 15.6% of the functions are useful for most users. All the other functions are rarely used by business people (accounting, finance, manufacturing, sales, etc..) because they are too specialized (Engineering and Financial) or redundant.

Category	Useful	Rarely Used	New in 2007	Total
Signs	21			21
Database Excel Functions		12		12
Date and Time Excel Functions	12	9		21
Engineering Excel Functions		40		40
Financial Excel Functions		53		53
Information Excel Functions	2	15		17
Logical Excel Functions	4	2	1	7
LookUp and Reference Excel Functions	5	13		18
Mathematical Excel Functions	13	45	1	59
Statistical Excel Functions	6	74	3	83
Text Excel Functions	10	14		24
Grand Total (excepting signs)	52	277	5	334
Percentages (excepting signs)	15.6%	84.4%
Category	Interesting	Rarely Used	New in 2007	Total

New Excel Functions in 2007

The five new functions in Excel 2007 are: AVERAGEIF, AVERAGEIFS, SUMIFS, COUNTIFS and IFERROR

IMPORTANT NOTE: If you use any of the 5 new functions created by Microsoft in Excel 2007 you may not send your spreadsheets to colleagues who are using earlier versions of Excel. The good news is that you can do what 4 of these "new" functions do (AVERAGEIF, AVERAGEIFS, SUMIFS, COUNTIFS) and even more with a single function that has been in Excel forever and that still works in Excel 2007. This extraordinary single and powerful function is SUMPRODUCT

As for IFERROR you can use the IF/ISERROR formula to do the same as IFERROR and it works in all versions of Excel

SUMPRODUCT: The most powerful and useful Function in Excel since 1980

When you start using SUMPRODUCT you will not need many other functions like: SUMIF, COUNTIF, and SUM

You can also replace some formulas involving: VLOOKUP

When you add INDEX/MATCH formulas to your arsenal other functions become obsolete: VLOOKUP, HLOOKUP and LOOKUP altogether.

The forgotten Excel function

Since 1997 and even in the 2007 version of Excel, Microsoft has failed to add to the "Insert Function" drop down list a very useful function if you have to work with dates: DATEDIF

The MIN, MAX, SMALL and LARGE Functions in Excel

Excel Functions and Formulas Sitemap

Downloadable Tutorials, Examples and Templates