Tidy Data, Sorting and Filtering

Introduction

Most spreadsheet problems are not about formulas. They come from messy data: blank rows, merged cells, mixed formats. A tidy layout makes sorting, filtering, formulas and charts work properly.

The rules of a tidy table

1. One header row at the top, with a clear, unique name for each column.
2. One record per row, such as one sale.
3. One kind of information per column. Do not put "10 pens" in one cell. Use separate Units and Product columns.
4. No blank rows or columns inside the table.
5. No merged cells inside the data, because they break sorting and filtering.
6. No totals or notes in the middle of the table. Put them above, beside or on a separate sheet.
7. Consistent values. Always "North", not "North", "N." and "north".

Sorting

Sorting puts rows in order, for example highest revenue first. Always sort the whole table so that each row stays together. In most programs: click a cell inside the table, then choose Data and Sort, pick the column, and choose A to Z or Z to A.

Sorting only one column by selecting just that column scrambles your data, because other columns stay behind. If the program warns you, extend the selection.

Filtering

A filter hides rows that do not match, without deleting them. Turn on filter buttons for the header row (Data, Create a filter). Then use the arrow on a column to show only certain values, such as Region N, or Revenue above 30.

Filters change what you see, not the data. Remember to clear them, and note that a total formula such as SUM still adds hidden rows unless you use a function made for visible cells.

Freeze panes

For long tables, freeze the header row (View, Freeze, 1 row) so the headings stay visible as you scroll.

Number, currency and date formats

The format controls how a value looks, not what it is.

• Number: choose decimal places. 12.5 can show as 12.50.
• Currency: adds a symbol such as £, $ or €.
• Percent: 0.2 displays as 20%.
• Date: how a date is displayed.

Dates are stored as numbers behind the scenes, which is why you can subtract them. Be careful with day and month order in different countries, such as 03/04/2026. A clear format such as 3 Apr 2026 avoids confusion.

Data validation

Data validation limits what can be typed in a column. For example, allow only the regions N or S from a drop-down list, numbers between 0 and 1000 in the Units column, or a date range. It prevents typos that would spoil later formulas. In most programs: select the cells, then Data, Data validation.

Cleaning common problems

• Extra spaces around text make "Pen" and "Pen " different. Use the TRIM function or Find and Replace.
• Numbers stored as text appear left-aligned. Convert them to numbers.
• Duplicates: most tools have a "Remove duplicates" option. Check carefully before using it, and keep a backup.
• Inconsistent spelling: use Find and Replace.

Always work on a copy when you clean data, and keep the original.

Try it yourself

Take the sales table, add a drop-down list for Region, freeze the header row, filter to show only Region N, then sort by Revenue from high to low. Clear the filter afterwards.

Summary

• A tidy table has one header row, one record per row, one kind of data per column and no merged cells or blank rows.
• Sort the whole table, use filters to view subsets, freeze headers and format numbers and dates clearly.
• Data validation prevents typos, and you should clean data on a copy.