Google Sheets Cheat Sheet: Formulas, Validation, and Workflows

Use Google Sheets reliably by structuring your data first, applying formulas to explicit rules, and using validation to constrain entries. A practical expense tracker, for example, can calculate approved spend with SUMIF, offer a controlled status list, and flag high-value unapproved expenses for review.

This Google Sheets cheat sheet covers the core formulas, input controls, lookup checks, and workflow decisions that matter when a spreadsheet becomes part of an operating process. It also distinguishes documented Google behavior from proposed workflow designs, so you can use the examples without treating them as ready-made templates.

A cell is one box in a sheet, a range is a group of cells, a formula is an expression such as =A1+B1, and a function is a predefined operation such as SUM. Formulas begin with =. Locale settings can affect separators and function names, so adapt examples if your sheet uses different conventions. See Google’s formula instructions and function reference for current syntax.

What this Google Sheets cheat sheet helps you do

Use a formula when the rule is explicit, such as adding amounts, checking a threshold, counting a status, or testing whether a field is blank. Use data validation when people should choose from a defined set. Use a review process when the input is ambiguous or the consequence of an incorrect decision is material.

That distinction prevents a common design mistake: using a flexible tool or AI suggestion where a deterministic check would be easier to inspect. A numeric threshold belongs in a formula. An allowed status belongs in validation. Unstructured text that may change a customer, finance, or legal record belongs in a controlled review path.

Set up a sheet so formulas have dependable inputs

Before adding calculations, use one header row, one record per row, and one field per column. Keep dates as dates, amounts as numbers, identifiers as consistently formatted text, and statuses from a defined set. Avoid merged cells and decorative subtotal rows inside the data table when those rows will be included in formula ranges or later imports.

For an expense log, define the columns before adding calculations:

• expense_id: a stable identifier for the expense
• date, category, and amount: consistently typed values
• status: an agreed value such as Pending, Approved, or Rejected
• source_url and reviewer: provenance and review details where needed

Keep the original value separate from a normalized or reviewed value when a person or process changes it. If records come from another system, retain its record ID, source URL, and received time when auditability matters. Missing or inconsistent keys make it harder to find, reconcile, and safely update the right record.

Google Sheets formulas by job

Assume an expense table has amounts in column E and statuses in column F, with records in rows 2 through 100. These examples use explicit ranges. Expand them to match your table and check whether your locale uses different separators.

Totals and counts

• Add amounts: =SUM(E2:E100)
• Calculate an average: =AVERAGE(E2:E100)
• Count numeric values: =COUNT(E2:E100)
• Count non-empty cells: =COUNTA(A2:A100)
• Find the highest amount: =MAX(E2:E100)
• Find the lowest amount: =MIN(E2:E100)

COUNT counts numeric values, while COUNTA counts non-empty values. A number stored as text may not be counted by COUNT or included as expected in arithmetic. Check the cell format and imported data type when results look wrong.

Results based on a condition

• Count approved expenses: =COUNTIF(F2:F100,"Approved")
• Sum approved expenses: =SUMIF(F2:F100,"Approved",E2:E100)
• Count amounts above 500: =COUNTIF(E2:E100,">500")
• Flag an unapproved expense above 500: =IF(AND(E2>500,F2<>"Approved"),"Review","")

Use COUNTIF for one counting criterion and SUMIF when you need to sum values selected by one criterion. For multiple conditions, consider functions such as COUNTIFS or SUMIFS after defining the conditions clearly. Fixed thresholds and allowed statuses belong in formulas or validation rather than an AI step.

Look up a value

To return the third column from a product table on a sheet named Products, with the lookup key in cell A2, use:

=IFERROR(VLOOKUP(A2,Products!A:C,3,FALSE),"Not found")

VLOOKUP searches the first column of the selected range. FALSE requests an exact match, and IFERROR makes a missing match visible rather than leaving an unexplained error.

Before relying on a lookup, check the key’s uniqueness, position, spelling, whitespace, data type, exact-match setting, and missing-match behavior. A lookup returns a value, but it does not prevent duplicate keys or prove that a returned record is current. If one result is expected, enforce uniqueness outside the lookup formula when the consequence of a duplicate matters.

Text, dates, and rounding

• Lowercase text: =LOWER(A2)
• Join text: =CONCATENATE(A2," ",B2)
• Split text at spaces: =SPLIT(A2," ")
• Round an amount to two decimal places: =ROUND(E2,2)
• Return today’s date: =TODAY()

When debugging a formula, inspect the referenced range, the values’ types, the lookup-key position, and what should happen for a blank or missing value. Google’s function reference covers additional functions and syntax.

Control entries with drop-downs and conditional formatting

To constrain expense statuses, select the relevant cells and use Data > Data validation to create a drop-down with Pending, Approved, and Rejected. Google documents the drop-down and validation setup. Decide how users should handle a value that does not meet the rule, then test the behavior with the kinds of edits your team actually makes.

Conditional formatting highlights values or formulas, but it does not block an invalid entry. To highlight repeated IDs in column A, apply a custom formula rule to the relevant ID range, starting at A2:

=COUNTIF($A$2:$A,A2)>1

Adjust the range and cell reference to match your table. You can also highlight amounts above a review threshold. Rules are evaluated in order, so check their ordering if overlapping conditions produce unexpected formatting. Assign an owner to investigate flagged rows. Color is a visual cue, not an approval control. See Google’s conditional formatting instructions.

Three workable patterns for spreadsheet workflows

A formula-driven tracker is a manual spreadsheet workflow. Human-reviewed intake is a proposed process design for turning unstructured submissions into controlled records. An API append is a documented way for an authorized application to add values to a Sheet, not a complete ingestion or duplicate-prevention system.

Pattern	Trigger and role	Validation and destination	Exception owner
Manual expense tracker	A person enters a row; formulas total approved spend and flag exceptions. No AI is needed for fixed amount and status rules.	Check amount, date, unique ID, and status. The reviewed row remains in the Sheet and approved results go to the designated finance process.	Expense reviewer resolves missing receipts, duplicate IDs, invalid amounts, or unapproved high amounts.
Reviewed intake	A form or document arrives; raw input is preserved and an optional AI step proposes normalized fields.	Check required fields, allowed categories, source ID, and output structure before acceptance. A review queue or Sheet holds the proposed record.	Named intake reviewer handles ambiguity, malformed fields, missing provenance, or consequential changes.
API append	An authorized application validates a row and sends it to a specified spreadsheet and range.	Supply the spreadsheet ID, range, value input option, authorization, and row payload. Record the response and use an external uniqueness control when needed.	Integration owner handles authorization, range errors, transient failures, retries, and reconciliation.

Manual expense workflow

For a hypothetical tracker, the input is a person-entered row such as expense ID EXP-1042, date 2026-11-08, category Travel, amount 640, and status Pending. Configure a status drop-down, put the approved total formula in a summary cell, and use a review formula on each row:

=SUMIF(F2:F100,"Approved",E2:E100)
=IF(AND(E2>500,F2<>"Approved"),"Review","No review")

The reviewer checks a flagged row and its receipt before changing its status. A missing receipt goes back to the submitter or reviewer rather than being treated as approved because the amount is below a threshold. This is an illustrative operating design using documented Sheets features, not a Google-provided expense template.

Human-reviewed intake with bounded AI assistance

For a hypothetical form submission containing free text, an external AI step could propose a category and normalized fields. It should not decide whether to approve a billing change or write that change directly to a customer record. Preserve the submitted text and source reference, then route uncertain or consequential requests to a named reviewer.

A proposed event-level record might contain the following fields. The source event identifies the submission, while the run ID identifies one analysis of it:

{
  "source_system": "intake_form",
  "source_event_id": "evt-8f32",
  "source_url": "https://example.com/submission/8f32",
  "received_at": "2026-11-08T10:15:00Z",
  "raw_text": "Please update the billing contact to the new finance lead.",
  "suggested_category": "Billing contact update",
  "review_status": "Human review required",
  "prompt_run_id": "run-2026-11-08-001"
}

Because this is one row per source event, source_system plus source_event_id is the proposed logical key. If the same event is analyzed several times, retain separate run records keyed by prompt_run_id or link those runs to the event record. Do not overwrite the event identity with the latest analysis.

The reviewer checks required fields and provenance, corrects or rejects the suggestion, and approves any downstream change. An approved CRM update should go through a separately verified write process. CRM systems can serve as a separate system of record when that is the organization’s design.

Programmatic append to a Google Sheet

Google’s append endpoint requires a spreadsheet ID, an A1-style range, a valueInputOption, authorization, and a row payload. It searches the requested range for a table and appends after its last row. Google’s Python quickstart covers introductory project and authentication setup. Its simplified authentication approach is for testing and should not be assumed production-ready. The writing sample demonstrates a narrow API operation, not a complete production workflow.

A proposed row might store source_system, source_event_id, source_url, received_at, normalized category, review status, and a distinct run_id if analysis was performed. Validate required fields, types, allowed values, and the target range before writing. Record the API response and route authorization or range errors to the integration owner.

A repeated append can create another row. A read-then-append check can also race when concurrent workers act at once. When duplicate prevention matters, accept the event through a database-enforced unique key or transactional upsert before writing to Sheets, then store the accepted external ID in the destination row. This is an implementation recommendation, not a feature supplied by the basic append call.

Define the data grain before adding rows. One row per source event, one row per analysis run, one row per citation, and one row per aggregated metric are different datasets with different keys:

• Source event: use a combination such as source_system + source_event_id.
• Analysis run: use a distinct run_id or prompt_run_id linked to the source event.
• Citation: use an observation ID plus citation position when one observation can contain several citations.
• Aggregate: use the entity, engine or model, period, and metric definition. Include other dimensions such as locale or prompt set when they affect the measurement.

A date alone is not a reliable universal deduplication key. Do not combine raw observations, reported summaries, and CRM contact or deal events in one table without explicit record types and keys.

Limits, offline use, and when a spreadsheet is no longer the right system

Google’s current spreadsheet limit documentation states up to 20 million cells or 100 MB under its documented conditions. That is a storage limit, not a promise of good responsiveness at that size. Formula load, formatting, imports, connected data, and recalculation also affect practical use. The reviewed Google sources do not provide a universal slowdown threshold.

Sheets supports offline creation, viewing, and editing when offline access is configured in a supported environment. Some features or integrations may not be available offline. See Google’s offline access setup guidance. Sheets and Excel have different feature coverage, so compare the specific functions, data model, add-ins, automation tools, and workload rather than assuming one is categorically more capable.

Choose a spreadsheet or a task-management view

Google Sheets is a better fit when the work depends on cell-level formulas and flexible analysis. ClickUp Table view is task-oriented: tasks are rows and fields are columns. ClickUp’s documentation distinguishes it from a true spreadsheet with cells and formulas.

Evaluate a task view when ownership, dependencies, status, and work tracking matter more than free-form cell calculations. Do not assume that a general AI product page documents a specific Google Sheets integration, field mapping, validation path, retry behavior, or chart workflow. See ClickUp’s Table view guidance or explore ClickUp consulting if you are assessing a task-oriented workspace.

Frequently asked questions

Can I create a Google Sheet with a personal Google Account?

Yes. Google says anyone with a Google Account can create a Sheet. Check current account documentation separately for storage allowances and other plan-dependent features.

Why does VLOOKUP return an error or an unexpected value?

Check that the lookup key is in the first column of the selected range, exact matching is specified when needed, the key is unique if you expect one result, and missing matches are handled explicitly. Also check whitespace and whether the key and lookup column use compatible data types.

Can I edit Excel files in Google Sheets?

Yes. Google says Excel files can be edited directly or converted to Sheets format. Confirm that the functions and data features your workbook relies on behave as required after editing.

Can I safely retry a Sheets API append?

Not by relying on append alone. The endpoint does not provide application-level idempotency. Use a durable event key and a database-enforced unique constraint or transactional upsert before writing when retries or concurrent workers could create consequential duplicates.

When should I use AI in a spreadsheet workflow?

Use deterministic formulas and validation for explicit rules such as thresholds, dates, required fields, allowed statuses, and exact matches. Consider AI for ambiguous or unstructured work such as proposing a category or summarizing text, then review the result before any consequential external write.