Table of Contents

In MS Excel, a function is a predefined formula that performs a specific calculation or operation using arguments (inputs) and returns a single output value. Excel offers a vast library of functions categorized by type, such as mathematical, statistical, logical, and text functions, to help automate complex calculations and data manipulation. 

Types of functions in MS Excel

MS Excel provides a variety of functions. Some relevant categories of functions include:

1. Text Functions –

  1. UPPER() – Converts text to uppercase (e.g., case names).
  2. LOWER() – Converts text to lowercase.
  3. PROPER() – Capitalizes the first letter of each word.
  4. CONCAT() or TEXTJOIN() – Combine text from multiple cells.
  5. LEFT(), RIGHT(), MID() – Extract parts of text (e.g., dates or codes).
  6. LEN() – Counts characters in a text.
  7. TRIM() – Removes extra spaces.

2. Date and Time Functions –

  1. TODAY() – Returns current date (auto-updated).
  2. NOW() – Returns current date and time.
  3. DATEDIF() – Calculates difference between two dates (e.g., days until a deadline).
  4. DAY(), MONTH(), YEAR() – Extracts parts of a date.

3. Lookup and Reference Functions –

  1. VLOOKUP() – Searches for a value in a table vertically.
  2. HLOOKUP() – Searches horizontally.
  3. XLOOKUP() – A modern and flexible lookup tool.
  4. INDEX() – Returns a value at a given position.
  5. MATCH() – Finds the position of an item in a range.

4. Mathematical Functions –

  1. SUM() – Adds numbers.
  2. AVERAGE() – Finds the mean.
  3. COUNT() – Counts numeric entries.
  4. COUNTA() – Counts all non-empty entries.
  5. ROUND() – Rounds numbers.

5. Logical Functions –

  1. IF() – Returns values based on a condition.
  2. AND() / OR() – Combines multiple conditions.
  3. NOT() – Reverses a logical value.
  4. IFERROR() – Handles errors in formulas.

6. Information Functions –

  1. ISBLANK() – Checks if a cell is empty.
  2. ISNUMBER() – Checks if the value is a number.
  3. ISTEXT() – Checks if the value is text.

Syntax

The basic syntax order for a function in Microsoft Excel follows a specific structure:

  1. Equal Sign (=): All formulas and functions in Excel begin with an equal sign. This tells Excel that the following characters constitute a calculation or a function.
  2. Function Name: Immediately after the equal sign, the name of the function is entered. For example, SUM, AVERAGE, IF, VLOOKUP, etc.
  3. Opening Parenthesis ((): After the function name, an opening parenthesis is required to enclose the arguments of the function.
  4. Arguments: Arguments provide the input or information that the function needs to perform its calculation. These can be:
    • Cell references: e.g., A1, B5:B10.
    • Ranges: e.g., A1:C5.
    • Values/Constants: e.g., 10, "Text".
  5. Closing Parenthesis ()): After all arguments have been entered, a closing parenthesis is required to complete the function's syntax.

Example:

To sum the values in cells A1 through A5:

=SUM(A1:A5)

Function Wizard

Steps to use function wizard include:

  1. Select a cell where you want the result.
  2. Click the fx button (next to the formula bar). The Insert Function dialog box will appear.
  3. In the Search for a function box, type what you want (e.g., "average", "if", "date") and click Go.
  4. Select a function from the list (e.g., SUM, IF, VLOOKUP).
  5. Click OK.

Alternatively,

  1. Go to the Formulas tab on the Ribbon.
  2. Choose a category (e.g., Text, Date & Time, Math & Trig).
  3. Click on the function you want (e.g., CONCAT, TODAY, ROUND). A dialog box opens. This is the Function Wizard.
  4. Fill in the input fields (Excel gives descriptions for each one).
  5. Click OK. Excel inserts the function into the selected cell.