Look at the photo below. A library once kept a card for every book in drawers like these. How could anyone find one card among thousands? Everything was kept in alphabetical order. Spreadsheets can put data in order for you in one click.
Image source: File:Card catalog at the Indiana State Library (Wikimedia Commons)
In this post:
- Sorting data in ascending and descending order
- Two sorting mistakes to avoid
- Relative cell references, which change when you copy a formula
- Absolute cell references with $ signs, which stay fixed
- A sorting lab, a fill-down challenge and a quiz
Sorting data
Sorting means putting rows in order, using one column to decide the order. (That column is the criterion.)
| Order | Words | Numbers | Dates |
|---|---|---|---|
| Ascending | A → Z | Smallest → largest | Oldest → newest |
| Descending | Z → A | Largest → smallest | Newest → oldest |
How to sort in Excel
- Click any cell inside your table (or select the whole table, headings included).
- For a quick sort: Home tab → Sort & Filter → Sort A to Z or Sort Z to A. This sorts by the column of the cell you clicked.
- For more control: Data tab → Sort. Pick the column in Sort by, then the Order.
- Make sure My data has headers is ticked, so your heading row stays at the top.
- Click OK. In Excel 2003, use the Data menu → Sort….
1. If you select just one column and sort it, Excel shows a Sort Warning. Always choose Expand the selection. Otherwise the names get mixed up with the wrong marks!
2. If My data has headers is not ticked, your heading row gets sorted in with the data.
Watch: sorting data
This video shows how to sort data in Excel.
Video: Excel: Sorting Data – LearnFree – https://www.youtube.com/watch?v=Ep5q1cUhQas
Activity: sorting lab
Try it: sort the class list
Choose the column and the order, then click Sort. Click Check to see if you are right. Try the “Sort this column only” option once, just to see what goes wrong!
Relative cell references
When you copy a formula down a column, the cell addresses in it change to match the new row. =B2*C2 in row 2 becomes =B3*C3 in row 3, then =B4*C4, and so on. These are called relative references, because they are relative to where the formula is. Usually this is exactly what you want.
Absolute cell references
Sometimes one cell must not change, for example a cell holding a tax rate or an exchange rate that every row uses. Put a $ sign in front of the column letter and the row number: $F$1. This is an absolute reference. It stays exactly the same wherever you copy the formula.
| Formula in C2 | Copied to C3 becomes | Why |
|---|---|---|
| =B2*F1 | =B3*F2 | Both addresses moved down a row. F2 is empty, so the answer is 0. Wrong! |
| =B2*$F$1 | =B3*$F$1 | B2 moved to B3, but $F$1 stayed fixed. Right! |
Shortcut: while typing a formula, click the address and press F4 to add the $ signs. LearnFree’s video Relative and Absolute Cell References explains this well.
Activity: fill-down challenge
Try it: work out the VAT
VAT in Trinidad and Tobago is 12.5%, and that rate is in cell F1. Type a formula in C2 that multiplies the price by the VAT rate, then click Enter and fill down to copy it to C3:C6. First try it without $ signs and see what happens.
C2 fx
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ||||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 |
Self-check quiz
Click the best answer. You get feedback straight away; only your first click counts towards your score.
1. Sorting numbers in ascending order puts them…
2. Why should you tick “My data has headers” when sorting?
3. Excel shows a Sort Warning when you select one column. What should you choose?
4. =B2*C2 is copied from row 2 to row 5. What does it become?
5. Which reference stays exactly the same when copied?
6. Which key adds $ signs to the address you are typing in Excel?
Related posts on islandclass.org
- Sorting information and formatting values
- Simple Sorting, Subsorting Tutorial.
- Absolute Addressing and Chart Creation, Revision – Spreadsheets Tutorial
- Sorting, Sub sorting – Spreadsheets Live class (2023-10-30)
For later, in Forms 4–5 (CSEC IT): these go deeper and are for when you reach CSEC Information Technology.
- Spreadsheets: Copying Formulas with Relative, Absolute and Mixed Cell References and Named Ranges
- Spreadsheets: Number Formats, Sorting, Filtering and Pivot Tables
Next: Making Charts from Spreadsheet Data
Syllabus link: Forms 1–3 ICT curriculum, Module 3, Objective 3.12 – perform a sort operation using one criteria.
Attributions to media used in this post:
- Card catalog at the Indiana State Library – headings Lover through Luma.jpg – TBurmeister (WMF) – CC BY-SA 4.0 (via Wikimedia Commons)
- Video: Excel: Sorting Data – LearnFree (YouTube)
Updates
2026/10/21
- Fixed the activity code so its buttons keep working.
© 2026 Vedesh Kungebeharry. All rights reserved.