Excel Function
A function in Excel is a predefined formula that performs a specific calculation using the values provided to it.
Functions help simplify complex calculations and save time.
Building Block of an Excel Function
Each Excel function has three main parts:
- Equal Sign (=) – Indicates that a formula or function is being entered.
- Function Name – The name of the specific operation (e.g.,
SUM, AVERAGE, MAX).
- Arguments – The values or cell references enclosed in parentheses that the function uses to perform calculations.
📘 Syntax:
=FunctionName(Arguments)
📊 Example:
=SUM(B4:B8) → Adds all the values from cells B4 to B8.
Function Argument Window
The Function Argument Window is a dialog box in Excel that helps users enter and understand the arguments required for a function.
It appears when you click the “fx” button next to the formula bar or start typing a function.
This window displays:
- The name of the function and a short description of what it does.
- Input fields for each argument, showing whether they are required or optional.
- A preview or result section at the bottom that shows the outcome of the calculation as you enter the data.
💡 It is especially helpful for beginners to correctly structure functions without remembering the exact syntax.
🧭 Formula Tab – Function Library
The Formula Tab in Excel is used to create, edit, and manage formulas and functions easily.
Within this tab, the Function Library group provides a quick way to insert various built-in Excel functions categorized by type.
It helps users find and apply formulas without remembering their exact names or syntax.
🗂️ Main Sections in the Function Library
1. AutoSum
- Automatically adds a range of numbers.
- Click the AutoSum (Σ) button to quickly insert functions like:
- SUM – Adds numbers.
- AVERAGE – Finds the average.
- COUNT – Counts numeric cells.
- MAX – Finds the highest value.
- MIN – Finds the lowest value.
📘 Shortcut: Select a range and press Alt + = to insert SUM automatically.
2. Recently Used
- Shows a list of functions you have used recently.
- Makes it easy to reuse functions without searching again.
3. Financial
- Contains functions used for financial calculations.
- Examples:
PMT, FV, PV, RATE, NPV.
4. Logical
- Includes functions used for decision-making and condition checking.
- Examples:
IF, AND, OR, NOT.
5. Text
- Functions for handling text and strings.
- Examples:
LEFT, RIGHT, MID, LEN, UPPER, LOWER, CONCAT.
6. Date & Time
- Used to insert and calculate date and time values.
- Examples:
TODAY, NOW, DAY, MONTH, YEAR, DATEDIF.
7. Lookup & Reference
- Functions used to search for and retrieve data from tables or ranges.
- Examples:
VLOOKUP, HLOOKUP, INDEX, MATCH, CHOOSE.
8. Math & Trig
- Contains mathematical and trigonometric functions.
- Examples:
SUM, ROUND, PRODUCT, POWER, SQRT, ABS.
9. More Functions
- Includes specialized function categories:
- Statistical:
AVERAGEIF, COUNTIF, MEDIAN, MODE
- Engineering:
CONVERT, COMPLEX
- Information:
ISERROR, ISBLANK
- Compatibility: Older Excel functions for backward compatibility.
💡 Extra Option: Insert Function (fx)
- Located at the left of the formula bar and also in the Function Library.
- Opens the Function Arguments window, helping users insert functions step-by-step.
- Provides descriptions, required arguments, and a preview of results.
🧮 How to Use a Function from the Formula Tab (Example: SUM Function)
Step 1: Open your Excel sheet
Make sure the worksheet has some numeric data (for example, numbers in cells B2 to B6).
Step 2: Select the cell for the result
Click on the cell where you want the total (for example, B7).
Step 3: Go to the Formula Tab
On the Excel ribbon, click the Formulas tab.
You’ll see several groups such as Function Library, Defined Names, Formula Auditing, etc.
Step 4: Choose Math & Trigonometry
In the Function Library group, click on Math & Trig.
A dropdown list of math-related functions will appear.
Step 5: Select the SUM function
Scroll and click on SUM from the list.
The Function Arguments window will open.
Step 6: Enter the cell range
In the Function Arguments window:
- In the Number1 box, enter the range of cells you want to add (for example,
B2:B6).
- You can also select the range directly on your worksheet using the mouse.
Step 7: Preview the result
Excel will instantly show the calculated result at the bottom of the Function Arguments window, so you can confirm it’s correct.
Step 8: Click OK
After confirming the range and result, click OK.
The total sum will appear in the selected cell (B7).
📘 Final Formula Example
=SUM(B2:B6)
This adds all numbers from cells B2 to B6 and displays the total in B7.
🔎 Using fx (Insert Function) Option
- Click the fx button beside the formula bar.
- In the Insert Function box, type MIN and press Go.
- Select MIN from the list → click OK.
- Enter the cell range (e.g.,
B2:B6).
- Click OK → the smallest value appears in the selected cell.
📘 Example: =MIN(B2:B6)
We can also use MAX(), AVERAGE() AND COUNT() etc. functions.
Adjacent cells error in excel calculation➕ Using AutoSum Command
- Select the cell for the result.
- Click AutoSum (Σ) on the Home or Formulas tab.
- Excel auto-selects nearby cells — press Enter to confirm.
📘 Example: =SUM(B2:B6)
Note: we can also use shortcut key: Alt + = to perform autosum.
🔁 Using AutoFill to Copy Formulas
- Select the cell with the formula.
- Drag the fill handle (small square at the corner) across cells.
- Excel copies and adjusts the formula automatically.
