Special

Excel Functions

2003

Special

Excel Functions

2007

Excel Functions and Formulas
From Excel 1997 to Excel 2010

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

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 will not need any of the new functions created in Excel 2007. You can also replace some formulas involving: VLOOKUP

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 2003 to 2010.

The Best Lookup Formula

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 obsolete functions

Since the 1970's many new functions have been created rendering other functions obsolete. For example, if you learn about LARGE and SMALL you don't need MIN and MAX

When you click on the "Insert Function" icon on the left of the formula bar    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.

Excel Functions and Formulas Sitemap