In continuation of my previous blog on basic Excel functions, today I want to dive deeper into some more powerful features of Excel that are incredibly useful for everyday data work. These features include:

  • Referencing (Relative, Absolute, Mixed)
  • Named Ranges
  • Date & Time functions
  • Fill Series, Flash Fill, and Text to Columns
  • Logical conditions using IF, AND, OR
  • SUMPRODUCT
  • INDIRECT and dynamic referencing
  • Information functions

Let's explore each with examples, syntax, and use-cases.

1. Referencing in Excel

Relative Reference:

By default, Excel uses relative references, like =A1, which adjusts when copied to another cell.

Absolute Reference:

Use $ to lock a row, column, or both.

  • Syntax: =$A$1
  • Use Case: Keep a value fixed during copy-fill operations.

Mixed Reference:

Lock either row or column:

$A1 → Locks column

A$1 → Locks row

1. Relative Reference

Definition:

A reference that changes when you copy a formula from one cell to another.

Syntax: =A1

Example:

| A  | B  | C        |
| -- | -- | -------- |
| 10 | 20 | `=A1+B1` |
| 30 | 40 | `=A2+B2` |

If you write =A1+B1 in cell C1 and drag it down to cell C2, Excel automatically adjusts the formula to =A2+B2.

Use Case:

Use relative references when you want your formulas to adapt to the new row/column as you copy/paste.

2. Absolute Reference

Definition:

A reference that does not change when you copy the formula elsewhere. You "lock" the row and column using $.

Syntax: =$A$1

Example:

| A  | B | C          |
| -- | - | ---------- |
| 10 | 2 | `=A1*$B$1` |
| 30 | 2 | `=A2*$B$1` |

Here, the value in cell B1 (which is 2) is fixed using $B$1. So when you copy the formula from C1 to C2, the formula still uses B1 — it becomes =A2*$B$1.

Use Case:

Use an absolute reference when referring to a fixed value like a tax rate, discount, or constant.

3. Mixed Reference

Definition:

A reference that locks only the row or the column, not both.

$A1: Locks column A, row changes when copied down

A$1: Locks row 1, column changes when copied right

Example:

| A  | B | C | D |
| -- | - | - | - |
|    | 2 | 3 | 4 |
| 10 | ? | ? | ? |
| 20 | ? | ? | ? |

If you want to multiply the row values (10, 20) by the column headers (2, 3, 4):

In B2, use: =$A2*B$1

Then drag across and down. Here's what happens:

  • $A2: Locks column A (so it always takes values 10, 20)
  • B$1: Locks row 1 (so it always uses 2, 3, 4)

So, the final grid looks like:

| A  | B  | C  | D  |
| -- | -- | -- | -- |
|    | 2  | 3  | 4  |
| 10 | 20 | 30 | 40 |
| 20 | 40 | 60 | 80 |

Use Case:

Mixed references are useful in multiplication tables, dynamic matrices, or financial models where you lock either row or column.

2. Named Ranges

Named Ranges help you refer to a range with a name rather than coordinates.

How to Create:

  1. Select the range (e.g., G2:G17)
  2. Go to Formulas → Define Name
  3. Name it, e.g., Salary

Use in Formula:

=SUM(Salary)

Benefit:

Improves readability and reusability of formulas.

3. Date & Time Functions

Common Functions:

| Function    | Syntax                       | Description                                 |
| ----------- | ---------------------------- | ------------------------------------------- |
| `TODAY()`   | `=TODAY()`                   | Returns current date                        |
| `NOW()`     | `=NOW()`                     | Returns current date and time               |
| `DATEDIF()` | `=DATEDIF(start, end, unit)` | Returns difference (in years, months, etc.) |
| `EDATE()`   | `=EDATE(start_date, months)` | Date after/before a number of months        |

Example:

=DATEDIF(A1,B1,"Y") → Age in years

4. Fill Series & Flash Fill

Fill Series:

Use Home > Fill > Series to:

  • Fill numbers, dates, or patterns
  • Options: Linear, Growth, Date, AutoFill

Flash Fill:

Automatically fills based on a pattern.

  • Example: Extract first name from "John Smith":
  • Type John in next cell, press Ctrl + E

5. Text to Columns

Use this to split data:

  • Go to Data > Text to Columns
  • Choose Delimiter (like comma or space)
  • Result: Splits full name, address, etc., into multiple columns

6. Logical Operators & Conditions

IF Statement:

=IF(condition, value_if_true, value_if_false)

Example:

=IF(G2<5000, 0, 50)

IF with AND/OR

=IF(AND(G2>10000, G2<20000), 100, 0)

IFS Function:

Simplifies multiple IF statements.

=IFS(G2<5000, 0, G2<10000, 50, G2<20000, 100)

Use IFS for clean multi-condition logic.

7. INDIRECT Function

Allows dynamic cell referencing.

Example 1:

=INDIRECT("G2")

→ Gives value in G2

Example 2 (Named range):

=SUM(INDIRECT(I9))

→ If I9 contains the name "Salary", this becomes =SUM(Salary)

Useful for dynamic range calculations or dropdowns.

8. SUMPRODUCT Function

Multiplies corresponding values in two or more arrays and returns the sum.

Syntax:

=SUMPRODUCT(array1, array2)

Example:

=SUMPRODUCT(A1:A5, B1:B5)

→ Multiplies A1×B1 + A2×B2 + … + A5×B5

Perfect for weighted averages, profit calculations, etc.

9. Information Functions

Some Useful Examples:

| Function                | Syntax                            | Returns |
| ----------------------- | --------------------------------- | ------- |
| `ISNUMBER(A1)`          | `TRUE/FALSE` if value is a number |         |
| `ISTEXT(A1)`            | Checks if it's text               |         |
| `ISBLANK(A1)`           | Checks if cell is empty           |         |
| `ISERROR(A1)`           | TRUE if cell has error            |         |
| `IFERROR(A1,"default")` | Returns value or default if error |         |

Wrap-Up

From dynamic referencing and named ranges to text functions, logical operations, and SUMPRODUCT, these Excel tools are incredibly useful for automating and analyzing data.

Next Steps:

Try combining these in your day-to-day tasks and you'll be amazed at how powerful your Excel workflow becomes!

What's Next?

Let me know in the comments:

  • Which Excel function do you use the most?
  • Want to explore Pivot Tables, Data Validation, or Power Query next?