Established onExcel 365, Windows and Mac: current region, sort warningExcel 2016 and later: sameGoogle Sheets, web: differs on the sort warning
Almost every feature that operates on data infers where the data is, and it infers from contiguity. A sheet holding one table, starting at the top left, with one header row and no gaps, is a sheet where every one of those features is right.
What the software actually does
Given a cell, it expands outwards until it meets a blank row and a blank column, and treats what it found as the table. Sorting, filtering, totalling, charting, pivoting and the import tools all begin from that inference.
Which means a blank row in the middle of the data ends the table there, as far as the software is concerned, and a note typed in a cell to the right becomes part of it.
The failure that destroys data
Selecting one column and sorting it. The column is rearranged and the columns beside it are not, so every row now pairs a name with somebody else's figure.
Excel notices the ambiguity and asks whether to expand the selection, which is a dialogue with a default and is dismissed by people who are concentrating on something else. Google Sheets behaves differently again. Either way, the operation completes, the sheet looks normal, and there is no way to reconstruct the original pairing except by undoing immediately.
This is the most destructive single action available in a spreadsheet and it is two clicks.
Why one table to a sheet fixes it
Because the inference becomes unambiguous. There is no second block for a sort to stop at, no note to be swept in, and no blank row to truncate a range.
The rule sounds fussy and it is the precondition for almost every other recommendation on this site working reliably.
The stronger version
Both products can convert a range into a named table object with its extent recorded rather than inferred. Once that exists, formulas refer to its columns by name, appending a row extends every dependent formula, and sorting acts on the whole table because the table knows what it is.
That single conversion removes the shifted range from the first part of this site, the ambiguity above, and a good share of the lookup fragility from the second part. It takes one keystroke and the great majority of sheets in existence have not had it applied.
What to do with the things that are not the table
Notes, parameters, working calculations and the odd chart all want to live beside the data and should not. Put them on their own sheet, which the next entry is about, or above the table separated by a full blank row and clearly outside it.
Where a note genuinely belongs to a row, it belongs in a column of the table with a header, not floating to the right of it.
Headers
One row, at the top, with a distinct name per column, no blanks, no merged cells and no units on a second line. Two-row headers are the commonest reason a range cannot be converted into a table object and the commonest reason an import tool produces columns named Column1.
Units belong in the header text in brackets, or in a documented note, and not in a second row.
How to check a sheet you inherited
Put the cursor in the data and select the current region with the keyboard. What gets highlighted is what the software believes your table is, and the difference between that and what you believe is the list of problems on that sheet.
It takes one keystroke and it answers the question directly rather than by inspection.
What we cannot verify
The contiguity inference, the sort warning and the table object behaviour are documented by both vendors and demonstrable directly. The claim that the sort warning is frequently dismissed is our observation from practice, not a measurement. Keyboard shortcuts differ between platforms and were checked on current builds of both products.
What a table object costs
Some things behave differently inside one. Formulas referring to its columns use a different notation, which is more readable and unfamiliar. Certain older functions and some array constructions do not accept table references. And a table cannot contain a merged cell, which is a constraint and, given the entry later in this part, an improvement.
None of these outweighs the extent being recorded rather than guessed, and it is worth knowing before converting a sheet somebody else maintains.
The sheet that is a form rather than a table
Some sheets are genuinely not tables: an input form, a summary page, a printed layout. The rule does not apply to them and the distinction should be deliberate rather than accidental.
Name those sheets so it is clear which kind they are, and keep the tables they draw from on their own sheets, which is the next entry.
In short
- Features infer the extent of the data from contiguity, starting at the cursor.
- A blank row ends the table and a note beside it becomes part of it.
- Sorting one column without its neighbours is the most destructive two clicks available.
- One table to a sheet makes the inference unambiguous, which every other rule depends on.
- Converting the range to a named table records the extent instead of inferring it.
- Selecting the current region shows what the software believes your table is.