Excel Spreadsheet Authors

You don’t Need to Know all Functions of the Excel Functions List to Become an Excel Data God

A practical framework to manage 341 Functions efficiently is dividing the Excel Functions List in two groups:

1. Real World Functions
2. Spreadsheet Functions

Let’s explore this interesting categorization…

Know the Real World Functions List

Real World Functions are established formulas in Math, Statistics, etc. For example: Sin, Cos, Square root, etc

Look at them…

1. Logical Functions
   

Used for comparison: AND, OR, etc.
Useful for evaluating two or more conditions and turn a SUMIF, COUNTIF into multiple SUMIF, multiple COUNTIF

2. Add-in and Automation functions

This group is for advanced users only. Two functions were added: CALL and REGISTER.ID; two functions were reclassified EUROCONVERT and GETPIVOTDATA (they were in external category in previous versions). They are available from a macro sheet only.

3. Engineering functions (Existing Functions)

Bessel functions, Complex numbers, number system conversions and other applications.

4. Financial functions)

Depreciation, Interest, Cash Flow, Investments, Annuities, Coupons, Securities and more…

5. Logical functions

All of them return True/False values.

Explore some examples: Excel IF AND, Excel IF OR

Important notice: Excel 2007 added the IFERROR function (useful for Error Checks).


6. Math and trigonometry functions

Math functions include: absolute value, logarithm, rounding, Sum functions and more…

See: Understand SUMPRODUCT, SUMPRODUCT Uses

Trigonometric functions include: basic trigonometric functions (sin, cos and tan) and its hyperbolic counterparts, basic trigonometric inversed functions (asin, acos, atan) and its hyperbolic counterparts; conversion degree to radians and vice versa and more.

Excel 2007 moved the COUNTIF function to the Statistical function category and also added the COUNTIFS function.

SUMIF, SUMIF Multiple, Write a SUMIF Multiple very Easily with the New SUMIFS, SUMIF Multiple – 7 Ways to sum based on multiple criteria

7. Statistical functions

Here you have Descriptive and Probability Statistics functions and more…

Explore some examples: Excel Count, COUNTIF, COUNTIF Date based on Year, Month or Day, Write a COUNTIF Multiple very Easily with the New COUNTIFS

Important Notice:

• Excel 2007 brought the COUNTIF from Math and trigonometry functions category and added the COUNTIFS function.
• Excel 2007 added two functions: AVERAGEIF and AVERAGEIFS

The Spreadsheet Functions List

Real World Functions turned Excel into a scientific calculator but you know that Excel is more than that.

How did Excel gain unlimited power?

It did it with a set of functions created to the spreadsheet environment, I call them: Spreadsheet Functions, they return information or manipulate the content of cells, retrieve data from other cells, etc.

Look at them…

1. Cube functions

These functions produce reports based on OLAP data. Cubes perform faster data analysis and display operations than relational databases.

2. Database functions

Provide Descriptive Statistics functionality.

3. Date and time functions

Insert current date, calculate intervals, convert numbers to Excel Date Time code protocol and more…

4. Information functions

It provides information about the content of cells: errors, text or numbers, the type of data. Most of them return True/False values.

Excel 2007 moved COUNTBLANK function to Statistical Functions category.

5. Lookup and reference functions

They allow you to look up values in tables and lists. You will find here the VLOOKUP Function, one of the most used and useful commands.

See also: INDEX and MATCH Revisited, Stop Wasting your Time Trying to Do Tasks that VLOOKUP Exact Match doesn’t Support

6. Text functions

Compare, modify, retrieve and extract information from text strings; Convert texts to upper and lower case and more…

Conclusion

Learn to use Excel functions is key to success in completing any work with data in Excel. Once you combine functions into formulas, you are beyond those who use Excel as a data entry and basic calculator tool only.

The good news is that you don’t need to learn them all; with a few lookup functions like VLOOKUP, INDEX and MATCH, SUMPRODUCT, Array Formulas, SUMIF, SUMIFS, COUNTIF, COUNTIFS you can become an Excel Data god.

Useful Articles and tools

1. You will have fun learning the 50 most frequently used Excel Formulas, I love the way the syntax is presented; I strongly recommend you. Click here to visit Pointy Haired Dilbert – Chandoo.org.
2. Excel Function Dictionary from Peter Noneley, 150 Function examples with explanations, syntax and informative cases, I am impressed with the quality of Peter’s job

Return from Excel Functions List to Excel Spreadsheet Authors Home

Popularity: 10% [?]