Excel functions and formulas           

 

 

Excel tips on functions and formulas

Excel Functions and Formulas contents

  Excel functions worksheets

Excel and VBA Expert

 

 

Excel Functions and Formulas Sitemap

From Excel 1997 to Excel 2013

This website has been created to help my colleagues business data analyst (finance, accounting, marketing, sales, inventory, etc) become more creative with Excel. With the proper functionalities and functions (very few) a good analyst can extract data from any database and develop automated, simple and complex reports.

With the proper knowledge of Excel an analyst can expect a better job and a better salary and can develop reports without the help of the overworked computer specialists from the Information Technology Department.


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


Excel SUMPRODUCT

The most powerful and useful function in Excel since 1980 and still in 2011. SUMPRODUCT is the ultimate tool to automate reports based on data stored in any database on your network.


A better Excel Lookup Formula

The functions INDEX and MATCH are useless by themselves. But when you combine them within INDEX/MATCH formulas they become the most powerfull lookup formulas available. The INDEX/MATCH formula allows you to assemble data from different databases to create a relational database.


Less than 10% of the Excel functions will cover 90% of the needs of most users.

When you click on the "Insert Function" icon on the left of the formula bar  Excel Insert Function  Excel shows all these functions to you grouped in 10 categories. 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 by 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

9

15

 

24

Grand Total (excepting signs)   

52

277

5

334

Since 1997 and even in the 2010 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


Other Websites on Excel

Excel VBATutorial     Excel Databases


Excel Functions and Formulas Sitemap

 

 

 

Excel tips on functions and formulas

Excel Functions and Formulas contents

Excel functions worksheets

Excel and VBA Expert

 

SUMPRODUCT    INDEX/MATCH    SUBTOTAL    ISERROR    HLOOKUP    LOOKUP    VLOOKUP
AVERAGEIF    AVERAGEIFS   COUNTIF    COUNTIFS    SUMIF    SUMIFS    IFERROR