Ranges, tables and named ranges

Formulas are only as reliable as the ranges they refer to. Sheets offers several ways to make ranges clearer and more robust.

Open ended ranges

In Sheets you can refer to a whole column from a starting row with an open ended range:

=SUM(Bookings!E2:E)

E2:E means "from E2 to the end of column E". As new form responses arrive, they are included automatically. This is especially useful with Google Forms, which add rows continuously.

Named ranges

Select a range, then use Data, Named ranges to give it a name such as BookingAmounts. Formulas become easier to read:

=SUM(BookingAmounts)

Named ranges also make it easy to find important data in large workbooks, because the Named ranges panel lists them all.

Tables in Sheets

Recent versions of Google Sheets let you convert a range into a table with Format, Convert to table. Tables give each column a type, such as number, date or dropdown, apply consistent formatting, and let you refer to columns by name in formulas:

=SUM(Bookings[Amount])

Column types help keep data clean, because Sheets warns when a value does not match. Tables also come with built in filtered views and grouping options.

Form response sheets

When a Google Form is linked to a sheet, responses arrive in a sheet named something like Form Responses 1. A few rules keep this working smoothly:

• Do not insert columns in the middle of the response sheet or reorder them; the form writes to fixed columns.
• Do not type formulas in rows below the data; new responses can overwrite or shift them.
• Do calculations on a separate sheet that references the response sheet.

This separation of raw data and calculations is the same principle as in any well built spreadsheet.

Referring to other sheets

To refer to another sheet, use its name followed by an exclamation mark. If the name contains spaces, wrap it in single quotes:

='Form Responses 1'!B2:B

Renaming sheets updates references automatically, but references written as text, for example inside QUERY strings or INDIRECT, do not update. Be careful when renaming.

Practice

In your copy of the workbook, create named ranges for the client list and pricing table, convert the clients range into a table and write a formula that totals all booking amounts using an open ended range.