Sheets

Sheets

A spreadsheet in your admin. A workbook holds up to 20 sheets of cells; your workbooks are yours, and you can share one with other people on the site to view or to edit.

Getting around

Open Sheets in the sidebar. My workbooks lists yours, Shared with me what others have shared, and Trash what you have thrown away (kept for a while, then removed for good); each tab shows how many it holds. The buttons in the Sheets banner at the top are New workbook (+), which starts an empty one, and Import (the upload arrow), which makes one from a file:

• CSV - rows and columns as they are in the file. Numbers and TRUE/FALSE arrive typed.
• JSON - an array of objects becomes a header row (the keys) with a row per object; an array of arrays becomes rows as they are.

Right-click a workbook (or use its menu button, or press Shift+F10) to open, duplicate, move to Trash, restore or delete it for good. A workbook in Trash shows an In Trash label; clicking it offers Restore and Delete for good.

Import also takes an Excel workbook (.xlsx, up to 15 MB; CSV and JSON up to 5 MB). Imports on the site run one at a time, so a large one may take a few seconds longer while another finishes. The ? buttons beside the lists, the search box, the formula bar and the sheet tabs explain each one.

Inside a workbook, the banner holds the workbook's actions: Formula help (the book), Refresh (fed sheets only), Feed, Share (the owner only), CSV, Excel and Print - hover any of them for its name. Right-click a sheet tab, or focus it and press Shift+F10, to rename or delete the sheet. If you close the browser tab while a change is still being saved, the browser asks first.

The grid

Click a cell and type. = starts a formula. Arrows move, Shift extends the selection, F2 edits the cell, Enter and Tab commit and move, Delete clears, Ctrl+C / X / V copy, cut and paste (Excel and other spreadsheets understand the clipboard), Ctrl+D fills the selection down from its top row, Ctrl+Z / Y undo and redo, and Ctrl+S saves now (the editor also saves on its own as you work). Ctrl+B, Ctrl+I, Ctrl+U and Ctrl+5 turn bold, italic, underline and strikethrough on or off.

Right-click a cell for its menu, in groups:

• Cut, Copy, Paste, Clear contents. Clear contents removes values and formulas but keeps the formatting.
• Format: Bold, Italic, Underline, Strikethrough (a tick shows what the cell has), Align (automatic, left, centre, right), Currency, Number format (text, number, currency, percent, date, checkbox), and Clear formatting, which takes all of those off. Formatting applies to the whole selection and undoes with Ctrl+Z. Number formats change how a value is shown, never what is stored.
• Insert: Function opens the same picker as f(x).
• Rows and columns: insert or delete rows and columns around the selection.

Right-click inside a selected range to keep that range; right-click elsewhere to work on that cell. Viewers and fed sheets can copy, but cannot change cells. Bold, italic and the rest show on the grid and in print; they do not travel to Excel or CSV.

Right-click a column heading for sorting, a filter and the column's format (text, number, currency, percent, date, checkbox - formats change how a value is shown, never what is stored). Drag the edge of a column heading to resize it. The tabs along the bottom switch sheets; right-click one to rename or delete it.

Formulas

New to formulas? Press Help beside the formula bar (or F1, or right-click > Formula help). It starts from the beginning, has ready-made formulas for everyday jobs (multiply by a number, add VAT, total a column, total one category, look something up) with Use to put one in the bar, and lists every function with Insert. Type in its search box - "multiply", "total", "VLOOKUP" - to find what you need.

Click or right-click f(x) beside the formula bar to pick a function from Totals and counts, Numbers, Logic, Lookup, Text or Dates and times. You can also focus the button and press Enter, Down or Shift+F10. The formula bar explains the function's arguments and shows an example as you type.

For totals and counts, the picker suggests the contiguous cells above your cell, or to its left. It leaves that range selected so you can change it. The formula is a draft until you press Enter; Esc cancels it and keeps the cell's previous value. Other functions start with empty brackets for you to fill in using the argument hint.

References are A1, ranges A1:B10, and across sheets Costs!A1 (quote a name with spaces: 'Monthly Costs'!A1). Operators: + - * / ^ and & to join text; % makes a percentage (10% is 0.1); comparisons = <> < > <= >=. To multiply, use *: =A2*1.2 adds 20%, and so does =A2*120%.

Keeping a reference fixed. Fill down (Ctrl+D) moves references with the row: =A2*B1 filled down becomes =A3*B2. Put a $ before the part that must stay put. =A2*$B$1 always uses B1, so a rate typed once in B1 applies to the whole column. B$1 keeps only the row and $B1 only the column.

Functions:

• Totals and counts: SUM, PRODUCT, AVERAGE, MIN, MAX, COUNT, COUNTA, and the conditional ones SUMIF, COUNTIF, AVERAGEIF. A condition is a value ("Paid", 5), a comparison (">100", "<>Draft") or a pattern with * and ? ("Inv*"). =SUMIF(C2:C50, "Paid", D2:D50) adds column D on the rows where C says Paid.
• Numbers: ROUND, ROUNDUP, ROUNDDOWN, INT, MOD, ABS, SQRT, POWER.
• Logic: IF, IFERROR, AND, OR, NOT. IF works out only the answer it gives, so =IF(B2=0, 0, A2/B2) is 0 rather than #DIV/0!. =IFERROR(A2/B2, "-") shows - instead of any error.
• Lookup: VLOOKUP(value, table, column, FALSE) finds value in the table's first column and gives that row's cell from column (1 = the first). Give FALSE for an exact match. Without it, the first column must be sorted A to Z and you get the nearest value not above yours - Excel's rule, kept so imported workbooks agree.
• Text: CONCAT, LEFT, RIGHT, MID, LEN, TRIM, LOWER, UPPER, SUBSTITUTE.
• Dates and times: TODAY, NOW.

When something cannot be worked out the cell says why: #REF! (a reference to nothing), #NAME? (an unknown function), #VALUE! (the wrong kind of value), #DIV/0!, #N/A (a lookup that found nothing), or #CYCLE! (a formula that needs its own answer). Formulas work over cell values only - they never run scripts and never reach outside the workbook.

Sharing and the edit lock

The workbook's owner shares it from Share in the banner: viewers read, editors change cells. One person edits at a time - the same lock as Docs. If someone else is editing you read until they finish, and the owner can take over.

Fed sheets - live rows from your other Tools

Feed in the banner binds the sheet you are looking at to a source: your Contacts, any collection, a form's submissions, a survey's progress, your Campaigns Pro results, your visitor Analytics (by day, page or source) or the Shop's orders and products. The sheet then shows the source's rows - a header row, then the data - and is read-only: Refresh re-fetches, and Feed > Stop feeding keeps the rows as ordinary cells you can edit again.

The rows are kept in the workbook, so they stay put even if the source Tool is switched off or its licence lapses - the chip beside the title says "source unavailable" until it is back. Formulas on your OTHER sheets can read a fed sheet by name (=SUM(Orders!D2:D100)), which is the point: a costs sheet that always adds up this month's real orders.

Feeding follows your permissions: you can only feed a sheet from a source you are allowed to read, and someone you share the workbook with sees the fetched rows, not the source itself. Be deliberate about that: sharing a workbook shares its fetched rows too, even with people who could not open the source themselves - share a workbook holding contact or order rows only with people who should see them.

Files out

CSV downloads the sheet you are looking at, with formulas exported as their computed values.

Excel downloads the whole workbook as a real .xlsx: values, sheet names, column widths, number formats and your formulas (Excel recalculates them from there). You can also import an .xlsx from Home - formulas Sheets understands come across as formulas, anything cleverer keeps its last value. Styling, merged cells and charts do not travel.

Print (the printer button) prints the sheet you are looking at as a clean table - the first row repeats as the heading on every page, and filters are honoured.

Caps

A sheet holds up to 50,000 filled cells and a workbook up to 20 sheets. Administrators can lower these (see Settings).

Settings

Administrators see a Sheets settings cog in the banner of Home and the editor. Changes take effect at once - no restart.

• Who sees workbooks - Each person's own (the default): a workbook is seen by its owner and the people it is shared with. Everyone reads: every Sheets user can read every workbook, and administrators own them all.
• Autosave after (milliseconds) - how long the editor waits after your last change before it saves. 2000 is two seconds.
• Filled cells per sheet and Sheets per workbook - the size limits. A workbook already bigger than a lowered limit keeps everything, but cannot be saved until it is trimmed to fit.
• Keep deleted workbooks (days) - how long Trash keeps a workbook before removing it for good.