Once you can write a formula, functions and smart cell references make Excel really powerful. This chapter covers the tools that turn a table into useful information.
1Cell referencing: relative, absolute & mixed
When you copy a formula, its cell references behave in different ways:
| Type | Looks like | When copied |
|---|---|---|
| Relative | A1 | Changes to fit the new position (the normal way) |
| Absolute | $A$1 | Stays FIXED — never changes |
| Mixed | $A1 or A$1 | One part fixed, one part changes |
$ sign "locks" what comes after it. Use an absolute reference like $B$1 when a formula must always point to the same cell — e.g. a fixed tax rate — as you copy it down a column.- Relative reference (A1) changes when the formula is copied; absolute ($A$1) stays fixed.
- Mixed reference ($A1 or A$1) fixes one part (column or row) and lets the other change.
- The $ locks what follows it — use absolute for a value every formula must reuse (like a rate).
2Built-in functions
Functions are ready-made formulas. A range like A1:A10 means cells A1 to A10:
| Function | Returns |
|---|---|
=SUM(A1:A10) | Total of the range |
=AVERAGE(A1:A10) | Average (mean) |
=MAX(A1:A10) / =MIN(...) | Largest / smallest |
=COUNT(A1:A10) | How many cells contain numbers |
Text functions
| Function | Does |
|---|---|
=UPPER("vid") | → VID |
=LOWER("VID") | → vid |
=CONCATENATE("Vid","aara") | → Vidaara (joins text) |
- SUM totals, AVERAGE means, MAX/MIN give largest/smallest, COUNT counts number cells.
- A range like A1:A10 means all cells from A1 to A10.
- Text functions: UPPER/LOWER change case; CONCATENATE joins text.
3Sorting & filtering
Sorting arranges rows in order — ascending (A→Z, small→large) or descending (Z→A, large→small) on a chosen column.
AutoFilter
AutoFilter adds dropdown arrows to your column headings so you can quickly show only the rows that match a value (e.g. only students from Class 7A), hiding the rest.
- Sort ascending (A→Z, small→large) or descending (Z→A, large→small) on a column.
- AutoFilter adds dropdowns to headings to show only rows matching a value.
- Filtering hides rows (doesn't delete them) — clear it to see all again.
★ Practical: a marks sheet
In Excel, build a marks sheet that:
- Has Name and three subject columns for five students.
- Uses =SUM(...) for the total and =AVERAGE(...) for the average of each student.
- Uses =MAX(...) to find the highest total; copy a formula that uses an absolute reference.
- Sorts students by total (descending) and uses AutoFilter to show one class.
Ready for the chapter test?
Answer 5 questions. Score 60% or more to mark this chapter complete.
Start the test →💡 Log in to save your progress and earn the certificate.