Microsoft Excel - working with basic Excel Functions

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:

1. Equal Sign (=) – Indicates that a formula or function is being entered.
2. Function Name – The name of the specific operation (e.g., SUM, AVERAGE, MAX).
3. 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

1. Click the fx button beside the formula bar.
2. In the Insert Function box, type MIN and press Go.
3. Select MIN from the list → click OK.
4. Enter the cell range (e.g., B2:B6).
5. 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

1. Select the cell for the result.
2. Click AutoSum (Σ) on the Home or Formulas tab.
3. 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

1. Select the cell with the formula.
2. Drag the fill handle (small square at the corner) across cells.
3. Excel copies and adjusts the formula automatically.