Cells & Ranges

Everything you do in Excel happens inside cells. You click a cell, type into it, format it, reference it in a formula. But once you start working with real data, you rarely work with just one cell at a time — you work with groups of cells called ranges. Understanding how to select, reference, and name ranges is one of the most important practical skills in Excel, and it will make every formula you write cleaner and faster.

A Cell and Its Address

Every cell on a worksheet has a unique address made from its column letter and row number. Column letter comes first, row number second. So the cell in Column B, Row 4 is called B4. Simple as that.

When you click on a cell, two things happen — the cell gets a coloured border to show it is selected, and its address appears in the Name Box at the top left of your screen. You already know this from Lesson 2. What you may not know yet is that you can type any cell address directly into the Name Box and press Enter to jump straight there — no scrolling needed.

What Is a Range?

A range is simply a group of cells treated as one unit. It could be a single row, a single column, a block of cells, or even a scattered selection of individual cells. Ranges are how you tell Excel — "apply this formula to all of these cells" or "format all of these cells at once."

A range is written by putting a colon between the first and last cell in the selection. The colon means "through" — so B2:B6 means "all cells from B2 through B6."

You can also select non-adjacent cells by holding Ctrl while clicking. For example, B2, D5, F8 are three separate cells but you can select all three at once. In a formula this is written with commas: =SUM(B2,D5,F8).

Selecting Ranges Efficiently

Clicking and dragging with the mouse works fine for small ranges. But when your data has hundreds or thousands of rows, you need faster methods. Here are the selection techniques every Excel user should know:

Relative vs Absolute References — The Most Important Concept in Formulas

This is the topic that trips up more beginners than almost anything else in Excel. Once you understand it though, it clicks permanently and you will never be confused again.

When you write a formula like =B2*1.2 and then copy that formula down to the next row, Excel automatically adjusts the reference. In row 3 it becomes =B3*1.2. In row 4 it becomes =B4*1.2. Excel is being helpful — it assumes that as you move down, you want to reference the cell in that same relative position.

This is called a relative reference and it is the default behaviour. Most of the time it is exactly what you want.

Perfect — each row calculates its own price with tax. Relative references working exactly as intended.

But now imagine a different scenario. You have a tax rate in cell E1, and you want every formula to always reference that exact cell — not adjust as you copy down.

If you write =B2*E1 and copy it down, row 3 will try to use E2, row 4 will try E3, and so on. Those cells are empty, so your formula returns zero. This is a very common bug that beginners hit.

The fix is an absolute reference. You add a dollar sign $ before the column letter and the row number to lock that reference in place:

=B2*$E$1      ← $E$1 is locked. Always refers to E1 no matter where you copy the formula.

Now if the tax rate changes from 1.2 to 1.15, you update just one cell — E1 — and every formula recalculates automatically. That is the power of absolute references.

Mixed References

There is a third type — a mixed reference — where you lock either the column or the row, but not both. You use this in more advanced formula setups like multiplication tables or grid calculations.

A quick shortcut — when you are editing a formula and your cursor is on a cell reference, press F4 to cycle through all four reference types: relative → absolute → mixed row → mixed column → back to relative. You do not need to type the dollar signs manually.

Named Ranges

Instead of writing B2:B13 every time you reference your sales data, you can give that range a name — like MonthlySales — and use that name in your formulas instead. This makes your formulas significantly easier to read and maintain.

Without named range:    =SUM(B2:B13)
With named range:       =SUM(MonthlySales)

Without named range:    =AVERAGE(C2:C13)
With named range:       =AVERAGE(MonthlyProfit)

To create a named range, select the cells you want to name, click into the Name Box, type the name you want, and press Enter. That is all there is to it. The name must start with a letter and cannot contain spaces — use underscores instead, like Monthly_Sales.

You can manage all your named ranges — edit, delete, or see where they are — by going to Formulas → Name Manager.

Next up — we get into Basic Formatting, where you will learn how to make your data look professional using fonts, colours, borders, and the Format Painter.