1---2title: "References"3sidebar_position: 24---56# References78- Cells: `A1`, ranges: `A1:B3` (1-2-3's `A1..B3` also works), whole9 columns `A:C` and whole rows `2:5` (`$A:$A`, `$2:$2` absolute). Whole10 columns and rows stay whole when copied, filled or when lines are11 inserted and deleted; `A1:A1048576` reads back as `A:A`.12- Absolute parts: `$A$1`, `A$1`, `$A1`. F4 while typing cycles the reference13 at the caret through them. Copying, filling, sorting and inserting or14 deleting rows and columns adjust the relative parts, as in Sheets;15 references to deleted cells become `#REF!`.16- Named ranges: `=SUM(Sales)`. Define them from Data > Named ranges or Data >17 Define named range. Names are case-insensitive, follow the selection when18 rows move, and renaming one rewrites the formulas that use it.19- Tables by column name: `=SUM(Sales[Amount])`, `=Sales[@Amount]*2`;20 see [Tables by column name](#tables-by-column-name).21- Other sheets: `=Sheet2!A1`, `=SUM('Q3 plan'!B2:B9)`; names with spaces or22 punctuation, or that look like a cell, go in single quotes. While typing a23 formula, Ctrl+PgDn or clicking a tab points into another sheet and inserts24 the reference. Renaming a sheet rewrites the formulas that use it;25 deleting one leaves them as written, showing `#REF!` ("Unresolved sheet26 name") until a sheet of that name exists again, as in Sheets. Inserting27 and deleting rows moves references into that sheet from every sheet, and28 leaves references to other sheets alone. Named ranges belong to the whole29 file and may point into any sheet.30- Each sheet is 16,384 columns (A to XFD) by 1,048,576 rows, Excel's size;31 a reference past them (`XFE1`, `A1048577`) reads as a name. Formulas cost32 what their ranges hold, not their size: `SUM(A:A)` over ten numbers reads33 ten cells, and `ROWS(A:A)` is still 1,048,576. See [Bounds of support](../contributing/limits.md)34 for what that means in practice.3536## Tables by column name3738A [table](../sheets/tables.md), or a notebook output sent to a sheet, is39read by its columns' names with a structured reference: the table's40name, then in brackets what to read of it.4142| Reference | Reads |43|---|---|44| `Sales[Amount]` | The Amount column's data rows, under the header |45| `Sales` or `Sales[]` | Every data row, header left out |46| `Sales[#All]` | The whole table, header row included |47| `Sales[#Headers]`, `Sales[#Data]` | The header row; the data rows |48| `Sales[@Amount]` | The Amount cell in the formula's own row (`Sales[[#This Row],[Amount]]`, as Excel's files write it); outside the table's rows, `#REF!` |49| `Sales[[#Headers],[Amount]]` | The Amount column's header cell |50| `Sales[[Units]:[Amount]]` | The columns from Units to Amount |51| `Sales[[#Headers],[#Data],[Units]]` | The Units column, header and data |5253Names match in any case. A column whose name has characters other than54letters, digits, `_` and spaces goes in brackets of its own,55`Sales[[Total, EUR]]`, and `[`, `]`, `#` and `'` in a name take a `'`56before them: `Sales['#Items]`. In a locale that separates arguments with57`;`, so are the items: `Sales[[#Headers];[Amount]]`.5859A structured reference is kept as written and read when the formula60computes, so it follows the table: rows added at its end, columns61inserted or moved, the table moved or resized. Renaming the table or a62column's header rewrites the formulas that read it; a deleted column, or63a deleted table, reads as `#REF!`. Copying, pasting and filling keep64structured references as they are, since they name columns rather than65positions: filled down a table, `Sales[@Units]*Sales[@Amount]` reads66each row's own cells.6768While typing, the table's name is offered with the named ranges, and69after its `[` its columns, or after `#` the items; the context line lists70the columns with the one being typed marked71([Building formulas](building.md)).7273## A range where one value is wanted7475A range used where a formula wants one value (with an operator, or as76an argument that takes a number or text) reads as one of its cells, as77in Sheets and Excel (implicit intersection):7879- a single column gives the cell in the formula's own row, so with the80 named range Rent = B2:B4, `=Rent*2` in F4 is B4*2 and `=B:B+1` in G5 is81 B5+1;82- a single row gives the cell in the formula's own column: `=B6:D6+1` in83 C8 is C6+1;84- a single cell is itself;85- anything else is `#VALUE!`: a formula outside the range's rows (or86 columns), and a range of several rows and columns.8788Ranges on other sheets work the same, by the formula's row or column:89`='Q3 plan'!C2:C9*10` in A3 reads `'Q3 plan'!C3`. A range that is a90cell's whole formula, `=B2:B4` or `=Rent`, is an array and spills, as in91Sheets (see [Arrays and spills](arrays.md)). Functions that92take ranges (`SUM`, `COUNTIF`, `MATCH`, `VLOOKUP`, `SUMPRODUCT` and the93like) read the whole range, and an expression given to them is computed94over arrays: `SUM(B2:B4*2)` doubles each cell and adds them.