Character data
Numeric data
Alphanumeric data
Charts and images
Excel functions
Since this book is about Excel functions, in this chapter we will look into what Excel functions are.
What Is an Excel Function?
An Excel function is a set piece of code built in Excel itself that performs certain predefined actions.
How to Use Excel Functions
All Excel formulas begin with an = or a + sign. An Excel formula can contain text and one or more Excel functions.
Here, FunctionName would be replaced by the actual name of the function. Arguments are the values that are passed to the functions. Multiple arguments are separated from one another by a comma (,). In some countries, the semicolon (;) is used to separate function arguments.
a literal value of the correct data type,
an Excel function returning the correct data type, or
a cell reference where the cell contains the correct data type.
Relative reference - A relative reference is one where the row and column coordinates are not preceded by a $ sign, like A1 or A1:D100. By default, in Excel, cell references are relative. This is helpful when we move or copy formulas across multiple cells, as the relative references will change depending on the relative positions of the rows and columns. You will use relative references when you want to repeat the same calculation across multiple rows or columns.
- Absolute reference - An absolute reference is the one with the dollar sign ($) in the row coordinate, the column coordinate, or both the row and the column coordinates, like $A$1 or $A1 or A$1. An absolute cell reference remains unchanged when copying the formula to other cells. Absolute references are useful
when you want to perform calculations with a value in a specific cell, or
when you want to copy a formula to other cells without changing references.
Absolute References
Absolute Reference Type | Comment |
|---|---|
$A1 | Here, the column reference is fixed. The row reference is relative. So, when we copy a formula containing this type of reference, the column is always fixed but the row reference can change. |
A$1 | Here, the row reference is fixed. The column reference is relative. So, when we copy a formula containing this type of reference, the row is always fixed but the column reference can change. |
$A$1 | In this case, both the row and the column references are fixed. So, when we copy a formula containing this type of reference, both the row and the column are always fixed. |
Types of Excel Functions
Volatile functions – Some Excel functions are “volatile.” Volatile functions are recalculated on every worksheet change. This can have a drastic impact on worksheet performance. In workbooks that contain a small amount of data, the performance impact may not be noticeable. But in workbooks that have a large amount of data and many formulas, a volatile function can slow down the worksheet.
- Non-volatile functions – Unlike volatile functions, non-volatile functions are not recalculated on every worksheet change. Non-volatile functions are recalculated when the data on which they depend changes. Some situations when this happens are as follows:
Entering new data
Modifying existing data
Deleting or inserting a row or a column
Renaming a worksheet
Hiding or unhiding rows (but not columns)
Categories of Excel Functions
Text functions
Date functions
Time functions
Aggregate functions
Logical functions
Reference functions
Math functions
Information functions
Each of these categories will be covered in a chapter of its own. We will also briefly look into what mega-formulas and array formulas are.
Summary
What an Excel function is
How to use an Excel function
Different categories of Excel functions
In the next chapter, we will look into text functions.