Vocabulary for Google Sheets | Formulas, Filters and Sharing Terms Explained

Google Sheets vocabulary is the language of cells, formulas, filters, pivot tables, sharing and data protection. Start with cell, range, relative reference, absolute reference, SUMIFS, FILTER, QUERY, IMPORTRANGE, filter view and PivotTable. Understanding these terms helps you answer a question from the data instead of merely arranging a colourful grid.

The most important distinction is between filter and filter view. A standard filter can change what other collaborators see, while a filter view lets different people inspect data without unnecessarily disrupting one another. Google says even a view-only user can make a temporary filter view that is not saved. Another critical distinction is between protecting a range and securing confidential data. Google explicitly warns that protected sheets and ranges are not security measures: people may still be able to copy, print or export information they can view.

Quick answer: Keep one record per row and meaningful headers. Use =SUM(D2:D20) for totals, =COUNTIF(B2:B20,"Paid") for one condition, =FILTER(A2:D20,D2:D20>100) for eligible rows, and a pivot table for totals grouped by category. Before using IMPORTRANGE, understand that granting a destination sheet access to a source can let its editors retrieve other portions of that source.

• Read a sheet: recognise rows, columns, cells, headers and value types.
• Calculate: separate formulas, functions and relative or absolute references.
• Explore: use filters, filter views, pivot tables and charts.
• Collaborate: give the minimum necessary sharing rights.
• Protect work: check versions, formula errors and independent backups.
• Verify: reconcile totals and inspect primary data before making decisions.

For another spreadsheet comparison, see Vocabulary for Microsoft Excel | Formulas, Tables and Spreadsheet Terms Explained. This article concentrates on Google Sheets behaviours, including its special functions, shared filter views and cross-file permissions.

How a simple spreadsheet turns into a reliable answer

Imagine a small business recording every invoice. Column A holds an order ID, B its Status, C the client, and D the amount. Each row should represent one invoice. To count paid orders, =COUNTIF(B2:B100,"Paid") gives a count. To add their values, =SUMIF(B2:B100,"Paid",D2:D100) gives a paid total. These figures only make sense if the fields are consistently entered and the row range covers all relevant records.

Now the team wants to see unpaid invoices. A regular filter may work, but colleagues editing the same file could be disrupted by the filtering context. Google’s official help describes filter views as a way to create independently saved filters and sorts. For category totals, a pivot table groups records and summarises chosen measures. Google currently says its pivot tables update when their selected source data cells change, though the source range must still include the intended new records.

Finally, some analysts use IMPORTRANGE to bring information from another spreadsheet. Google’s documentation says the first connection needs access approval. More importantly, after permission is granted, any editor of the receiving spreadsheet can use IMPORTRANGE to fetch data from other parts of that source, so the receiving file must not be broadly shared if the source includes confidential information. This is a practical security boundary, not just a formula syntax detail.

Google Sheets basics, cells and references

These 39 terms explain a connected part of working with Google Sheets. Each one comes with an example that shows its use in a real spreadsheet.

Google Sheets

A collaborative spreadsheet application associated with Google accounts and supporting formulas, tables and data analysis. Example: Open Sheets to prepare the monthly budget.

Spreadsheet

The complete grid-based document containing one or more tabs of data and calculations. Example: Use one spreadsheet for the project’s approved figures.

Sheet tab

A selectable worksheet within a spreadsheet, generally shown along the bottom. Example: Name tabs Orders, Customers and Summary.

Cell

One intersection of a column and row containing a value or formula. Example: Type the unit price in cell C2.

Row

A horizontal sequence of cells usually identified by numbers. Example: Each row represents one invoice.

Column

A vertical sequence of cells usually identified by letters. Example: Keep invoice amount in column D.

Range

A group of cells described by its boundaries, such as B2:B20. Example: Select the whole range before calculating.

Cell address

A coordinate such as C5 referring to one position in a worksheet. Example: C5 means column C and row 5.

Active cell

The selected location currently receiving an input or formula. Example: Check the active cell before pasting.

Formula bar

The interface displaying the complete content or formula of the selected cell. Example: Read the formula bar when a displayed value looks wrong.

Relative reference

A cell reference that normally adjusts when a formula is copied to another position. Example: A2 usually becomes A3 when copied down one row.

Absolute reference

A reference fixing both row and column with dollar signs, such as $F$1. Example: Use $F$1 for one tax rate applied to every invoice.

Mixed reference

A reference fixing only one dimension, such as $A2 or A$2. Example: Keep the header row fixed with A$1 when copying across.

Named range

A meaningful name representing a cell or group of cells in the spreadsheet. Example: Use TaxRate instead of an unclear coordinate.

Header row

A row identifying the meaning of the fields below it. Example: Give each column a clear header before a pivot table.

Record

One complete observation or transaction represented by a row in structured data. Example: One order belongs in one record.

Field

An attribute of a record, generally stored in one column. Example: Status is a field in the orders table.

Data type

The kind of value interpreted by Sheets, such as text, number or date. Example: A wrong data type can break sorting.

Plain text

A representation keeping characters as text rather than interpreting them as numeric values. Example: Use plain text for product codes with leading zeros.

Number format

Rules affecting how a numeric value is displayed without necessarily changing its stored quantity. Example: Percent formatting shows 0.25 as 25%.

Currency format

A display style adding a currency symbol and appropriate decimal presentation. Example: Use SGD formatting for Singapore amounts.

Percent format

A number display expressing a value as a percentage. Example: Check whether 0.1 means 10%, not 0.1%.

Date value

A value that Sheets recognises as a date under its locale and formatting rules. Example: A date stored as text may not sort chronologically.

Time value

A quantity interpreted as a time or duration under selected formatting. Example: Convert times carefully before summing shifts.

Locale

A spreadsheet setting influencing date, number and other formatting conventions. Example: Locale can change decimal separators and date appearance.

Time zone

The spreadsheet’s designated zone for date and time functions under its settings. Example: A daily report’s date can differ across zones.

Blank cell

A truly empty cell, which differs from a formula displaying an empty-looking value. Example: Check blank cells before counting missing data.

Frozen row

A row kept visible while scrolling, commonly used for headers. Example: Freeze the first row of an order list.

Frozen column

A column kept visible during horizontal scrolling. Example: Freeze the product identifier column.

Merged cell

Several cells displayed as one combined area. Example: Avoid merged cells in a dataset intended for sorting.

Wrap text

A display setting placing long text on multiple lines within a cell. Example: Wrap instructions rather than cutting off wording.

Clip text

A display setting hiding overflow beyond a cell boundary. Example: Clipped text can conceal important caveats.

Dropdown

A cell control offering configured permitted choices. Example: Create a dropdown for Not started, In progress and Done.

Checkbox

A selectable TRUE/FALSE-style control in a cell, with supported custom values. Example: Use checkboxes for simple verified completion status.

Data validation

Rules governing which values may be entered into selected cells. Example: Validate a status field before other people edit it.

Conditional formatting

Appearance rules that respond to cell values or formulas. Example: Highlight overdue items without deleting records.

Alternating colors

A display style changing row backgrounds to aid reading. Example: Use alternating colors in long transaction tables.

Fill handle

A corner control for copying or extending selected data patterns. Example: Drag the fill handle to copy a formula.

Paste values only

A paste mode transferring displayed values without copying source formulas. Example: Paste values to freeze an approved result.

Essential formulas and Sheets-specific functions

These 48 terms explain a connected part of working with Google Sheets. Each one comes with an example that shows its use in a real spreadsheet.

Formula

An expression starting with = that calculates a value or retrieves information. Example: =B2*C2 multiplies quantity by unit price.

Function

A named operation called inside a formula with its required arguments. Example: SUM is a function rather than a complete worksheet.

Argument

An input passed to a function inside parentheses. Example: B2:B10 is an argument to SUM.

SUM

A function adding eligible numbers. Example: =SUM(D2:D20) totals invoice amounts.

AVERAGE

A function calculating the arithmetic mean of eligible numeric entries. Example: =AVERAGE(C2:C8) finds an average score.

COUNT

A function counting numeric values in its inputs. Example: COUNT does not count ordinary words.

COUNTA

A function counting nonempty cells under its rules. Example: COUNTA can count filled names.

COUNTIF

A function counting entries matching one condition. Example: =COUNTIF(B2:B20,"Paid") counts matching statuses.

COUNTIFS

A function counting entries meeting several matching conditions. Example: Count orders that are Paid and belong to November.

SUMIF

A function summing eligible values for one condition. Example: SUMIF totals spending for a category.

SUMIFS

A function summing eligible values against several criteria. Example: =SUMIFS(D2:D20,B2:B20,"Paid") adds paid invoices.

IF

A function returning different outputs according to a logical test. Example: =IF(C2>=70,"Pass","Review") evaluates a threshold.

IFS

A function considering multiple conditions and returning the matching result. Example: IFS can map scores to several grade bands.

AND

A logical function true only when all its conditions are met. Example: Require a valid date AND an approved amount.

OR

A logical function true when at least one given condition is met. Example: Accept either Card or Cash as a payment category.

IFERROR

A function substituting another result when an expression has an error. Example: IFERROR can conceal useful error details if applied too broadly.

XLOOKUP

A function locating a match in one range and returning a related value from another. Example: Use XLOOKUP to find a product price from its code.

VLOOKUP

A function finding a value in the leftmost column of a supplied range and returning another column’s value. Example: VLOOKUP can fail if you miscount the return column index.

INDEX

A function retrieving a value at a specified position in a range or array. Example: INDEX can return a selected row’s price.

MATCH

A function returning the relative position of a lookup value. Example: MATCH can identify which row contains an item code.

FILTER

A dynamic-array function returning rows or columns satisfying chosen conditions. Example: =FILTER(A2:D20,D2:D20>100) displays higher-value records.

SORT

A function producing a sorted output from a range or array. Example: =SORT(A2:C20,2,TRUE) arranges by the second column.

UNIQUE

A function returning distinct values or rows from a supplied range. Example: =UNIQUE(B2:B100) lists categories once.

QUERY

A Google Sheets function applying Google Visualization API Query Language expressions to tabular data. Example: QUERY can select and group rows without changing the source sheet.

SELECT clause

A query-language instruction determining which columns or expressions appear. Example: Use select A,B in an eligible QUERY expression.

WHERE clause

A query-language condition filtering rows before summarisation. Example: Use where C>100 in a compatible QUERY string.

GROUP BY clause

A query-language instruction grouping rows for aggregated results. Example: Group sales by region with QUERY.

ORDER BY clause

A query-language instruction arranging query results. Example: Order the summary by total descending.

Headers argument

The optional QUERY input describing the number of header rows. Example: Incorrect headers can make a QUERY treat column data as labels.

ARRAYFORMULA

A function supporting array-based calculation over ranges under compatible formula behaviour. Example: ARRAYFORMULA can calculate a column without copying every cell.

ARRAY_CONSTRAIN

A function limiting the size of a result returned by an array. Example: Constrain a large result to ten rows.

IMPORTRANGE

A function retrieving eligible cells from another Google Sheets file after the needed permission is granted. Example: IMPORTRANGE requires approval when first connecting sheets.

Allow Access

The approval step for an eligible IMPORTRANGE connection between spreadsheets. Example: Grant Allow Access only after reviewing the source and destination.

IMPORTHTML

A function attempting to import tables or lists from a supported webpage. Example: IMPORTHTML may fail on a dynamic website.

IMPORTDATA

A function retrieving eligible CSV or TSV information from an accessible URL. Example: IMPORTDATA does not guarantee that an external dataset is accurate.

GOOGLEFINANCE

A function retrieving supported finance information under Google’s service coverage and delays. Example: GOOGLEFINANCE is not reliable for every live trading decision.

SPARKLINE

A function rendering a compact chart inside a spreadsheet cell. Example: =SPARKLINE(B2:B12) gives a quick visual pattern.

TEXTJOIN

A function combining multiple text values using a selected delimiter. Example: TEXTJOIN can build a clear customer label.

SPLIT

A function splitting a text string into separate cells using a delimiter. Example: SPLIT can separate a name from a code.

REGEXMATCH

A function checking whether text matches a regular expression. Example: REGEXMATCH can validate a simple code pattern.

REGEXEXTRACT

A function retrieving text matching a regular-expression pattern. Example: Use REGEXEXTRACT to isolate a reference segment.

TRIM

A function removing selected extra spaces from text. Example: TRIM helps clean copied labels but may not fix every Unicode space.

CLEAN

A function removing certain nonprinting characters from text. Example: CLEAN can help with some pasted control characters.

TEXT

A function representing numeric values as formatted text. Example: =TEXT(A2,"yyyy-mm-dd") creates a date label.

TODAY

A function returning the current date based on the calculation environment. Example: Use TODAY to compare a deadline against the present date.

NOW

A volatile function returning current date and time. Example: NOW can change on recalculation.

ROUND

A function rounding a numeric result to a selected precision. Example: ROUND a displayed value without destroying original source measurements.

SEQUENCE

A function generating a sequence of numbers. Example: SEQUENCE can make a numbered list of months.

Filters, charts, tables and PivotTables

These 42 terms explain a connected part of working with Google Sheets. Each one comes with an example that shows its use in a real spreadsheet.

Create filter

A command providing a regular filter for rows or columns under chosen rules. Example: Filter by one salesperson without deleting other orders.

Filter condition

A rule specifying which records remain visible under a filter. Example: Choose Status equals Paid.

Filter view

A saved or temporary personal-style filter configuration whose effects can be isolated from others’ views. Example: Use a filter view so teammates can keep their own sort.

Temporary filter view

A view-only user’s nonpersistent filtered view of a spreadsheet, supported by Google Sheets. Example: A Viewer can filter temporarily without altering the master.

Save view

A control storing a named filter view for later access under permitted conditions. Example: Save an audit filter to revisit next week.

Filter link

A URL opening a compatible saved filter view under file access rules. Example: A filter link does not grant the recipient editing rights.

Sort ascending

Arrange values from smaller to larger or earlier to later. Example: Sort due dates ascending to see the nearest first.

Sort descending

Arrange values from larger to smaller or later to earlier. Example: Sort revenue descending to see largest orders.

Sort range

Arrange a selected block of cells according to a sort choice. Example: Select the entire row range to avoid mismatching fields.

Sort sheet

Order eligible data across a sheet rather than one small selected range. Example: Check whether other tables share the same sheet.

Protected range

An area with configured edit warnings or restrictions under Google Sheets settings. Example: Protection is not encryption or secure access control.

Protected sheet

A worksheet whose editing rights are restricted or warned under eligible settings. Example: Google says protection does not prevent printing or copying.

Warning only

A protection option alerting editors rather than blocking every change. Example: Warning-only does not prohibit editing.

Editor restriction

A protection rule identifying which users may modify specific cells. Example: Restrict formula columns to appropriate editors.

Hidden sheet

A worksheet tab not displayed in the ordinary interface while still part of the file. Example: A hidden sheet can be unhidden by authorised users.

Sheet table

A structured Google Sheets feature organising data with typed columns and table references. Example: Turn a clean dataset into a table when supported.

Table name

An identifier assigned to a structured table for reference and navigation. Example: Name the table Orders rather than Table1.

Table reference

A formula notation referring to part of a structured table by name or column. Example: Use Orders[Amount] where supported.

Column type

A table or validation setting describing expected data such as date, dropdown or number. Example: Use a date column for real due dates.

Pivot table

A summary tool grouping source records and aggregating fields into a new view. Example: Make a pivot table showing sales by region.

Pivot rows

The source categories displayed as row labels in a pivot table. Example: Put Region in Pivot rows.

Pivot columns

The source categories shown across columns in a pivot table. Example: Put Month in Pivot columns.

Pivot values

The measures aggregated within a pivot table. Example: Put Amount in Pivot values.

Pivot filter

A condition limiting the records summarised in a pivot table. Example: Filter the pivot to completed orders.

Calculated field

A measure computed using a formula within a supported pivot setup. Example: Use a calculated field for an approved derived ratio.

Aggregation

Combining values into a sum, count, average or related statistic. Example: Choose SUM for revenue and COUNT for number of orders.

Grand total

A summary across all included categories and records. Example: Compare the pivot grand total with the underlying source.

Source range

The original cells supplying a pivot or chart. Example: Confirm that the source range includes the newest transactions.

Chart

A graphic summarising data through visual marks such as bars or lines. Example: Use a chart only after validating the underlying numbers.

Column chart

A visual comparing categories using vertical bars. Example: Compare monthly orders with a column chart.

Bar chart

A visual comparing categories using horizontal bars. Example: Use bars when names are long.

Line chart

A chart showing values along an ordered axis, often time. Example: A line chart can show monthly changes.

Scatter chart

A plot of paired numeric values useful for exploring associations. Example: Plot price against quantity sold.

Pie chart

A circle divided into parts representing shares of a whole. Example: Use a pie chart only for a small clear set of proportions.

Legend

A key explaining which colours or marks represent each series. Example: A legend should not conceal missing units.

Axis

A reference line with values or category labels used in chart interpretation. Example: Check the vertical axis before trusting the shape.

Data label

A displayed value associated with a chart mark. Example: Data labels can help when exact totals matter.

Slicer

An interactive filter control for eligible charts, tables or pivot-based views. Example: A slicer helps select a sales region.

Data cleaning

Finding and correcting missing, duplicated or inconsistent information. Example: Clean identifiers before making a PivotTable.

Remove duplicates

A tool that deletes records considered duplicates based on selected columns. Example: Save a copy before Remove duplicates.

Split text to columns

A function turning delimited text into several separate columns. Example: Split a combined code and category field.

Named function

A reusable custom formula definition managed within a spreadsheet under Google Sheets’ supported feature. Example: A named function can standardise a complex repeated calculation.

Collaboration, security, errors and careful analysis

These 45 terms explain a connected part of working with Google Sheets. Each one comes with an example that shows its use in a real spreadsheet.

Viewer

A role generally allowing reading without broad editing. Example: Give a stakeholder Viewer access for a final budget.

Commenter

A role generally allowing comments without direct cell changes. Example: Let reviewers comment on an assumption.

Editor

A role allowing changes to the spreadsheet under its sharing rules. Example: Restrict Editor permissions on confidential financial models.

Restricted

An access setting limiting use to authorised recipients under effective permissions. Example: Use Restricted for client records.

Anyone with the link

A broader sharing option letting eligible possessors of the URL open the sheet. Example: Do not use this for sensitive account details.

Version history

A record of earlier states of a spreadsheet under supported collaboration behaviour. Example: Check version history before rolling back a formula.

Named version

A labelled checkpoint in the spreadsheet’s revision record. Example: Name the approved monthly close.

Restore version

Replace the current file contents with an earlier version under the available permission rules. Example: Review later changes before restoring.

Comment

A discussion note attached to a cell or selected location. Example: Ask for the origin of the reported number.

Assign comment

A supported feature addressing a question or task to a selected collaborator. Example: Assign a correction to the responsible reviewer.

Resolve comment

Mark a discussion as handled under the comment interface. Example: Resolve only once the question has been answered.

Permission inheritance

Access received through a parent folder or group rather than just direct sharing. Example: Inherited rights can expose a sheet unexpectedly.

Share link

A URL allowing access under the selected sharing rules. Example: A share link alone does not establish appropriate security.

Confidentiality

A duty to limit disclosure of private records under the applicable rules. Example: Remove customer identifiers from public examples.

Least privilege

Providing only the rights needed for the task. Example: Viewers should not need full spreadsheet editing.

Circular dependency

A formula depending on itself through one or more paths. Example: A circular dependency can prevent normal calculation.

#REF! error

An error involving an invalid reference or some unapproved cross-file connection contexts. Example: IMPORTRANGE may show #REF! while awaiting Allow Access.

#N/A error

A function result commonly indicating that a requested match is unavailable. Example: An XLOOKUP may return #N/A for an unknown item.

#DIV/0! error

An error when an expression divides by zero or an empty denominator. Example: Check for zero before calculating a rate.

#VALUE! error

An error associated with values or argument types incompatible with a calculation. Example: An imported text value can break arithmetic.

#NAME? error

An error commonly indicating an unrecognised name or function syntax. Example: Check spelling in the function.

#ERROR! message

A general formula parsing or related evaluation error shown with further details. Example: Read the tooltip rather than guessing the cause.

#NUM! error

A problem arising from a numeric domain or unsupported numeric calculation. Example: Inspect an invalid mathematical input.

#SPILL!

An Excel-specific error label rather than the ordinary Google Sheets array-blocking presentation; do not assume every Excel error name transfers directly. Example: Sheets may show a message that an array result was not expanded.

Array expansion error

A Google Sheets error when a formula result cannot occupy the necessary output cells. Example: Clear obstructing data only after checking its purpose.

Volatile function

A function whose result can change during recalculation without source data changes. Example: NOW and RAND are volatile.

Recalculation

The process of updating formula results following changes or timing rules. Example: A volatile result can alter without manual edits.

IMPORTRANGE lag

A delay when one spreadsheet fetches data from another under network or recalculation constraints. Example: An imported dashboard may not update immediately.

Import permission

The explicit approval permitting a destination spreadsheet to retrieve source data via IMPORTRANGE. Example: Grant access only after checking who can edit the destination.

Import exposure

The privacy risk of allowing editors of a destination sheet to use its authorised IMPORTRANGE source connection. Example: A destination editor may gain access to additional source ranges.

Locale mismatch

Different number or date conventions leading to misinterpreted input. Example: A date like 01/02 can be ambiguous across locales.

Mixed-type column

A column mixing text and numeric values in ways that can affect QUERY and analysis. Example: QUERY may treat minority data types as null.

Outlier

A value unusually far from other observations, which may be real or erroneous. Example: Investigate an outlier rather than deleting it immediately.

Missing value

An expected data item that was never recorded or has become blank. Example: Missing price and zero price are different facts.

Zero value

A recorded numerical amount of zero rather than an absent entry. Example: Zero sales is not the same as missing data.

Duplicate record

Two entries representing the same intended transaction or entity. Example: Check duplicates before removing rows.

Assumption

An input used for a calculation without being directly established by the dataset. Example: State growth assumptions alongside forecast outputs.

Audit trail

A record of sources, calculations, edits and approvals important to a decision. Example: An audit trail supports reproducibility.

Reconciliation

Comparing a spreadsheet total against trustworthy independent records. Example: Reconcile monthly revenue to the transaction ledger.

Source data

Original information from which summaries and charts are calculated. Example: Do not edit source data merely to improve a chart.

Source attribution

Identification of where an external dataset or claim originated. Example: Record the agency and data release date.

Independent backup

A separately recoverable copy outside normal file synchronisation. Example: Keep a backup before changing a large source dataset.

Google Apps Script

Google’s scripting environment for automating eligible Google Workspace tasks. Example: Apps Script can perform supported operations beyond ordinary formulas.

On edit trigger

An Apps Script execution condition fired when eligible editing events occur. Example: A trigger may update data after a change.

Data privacy

Managing personal information according to permitted collection, sharing and retention rules. Example: A shared sheet should not reveal student identifiers unnecessarily.

Five real Google Sheets situations

A copied formula starts using the wrong percentage

Check the reference type. A formula containing F1 may change to F2 when copied down. If one tax rate in F1 is intended for every row, use an absolute reference such as $F$1. A mixed reference locks only the row or the column when appropriate. Check what the resulting formula actually points to before sharing the calculated amount.

A colleague says their rows suddenly disappeared

Inspect the active filter or filter view. Filtered-out records still exist. In a shared spreadsheet, a regular filter might change what collaborators see; a filter view is often a better choice for personal investigation. Google’s documentation says saved filter views can be named and linked to while the underlying data remains accessible according to actual file permissions.

IMPORTRANGE shows #REF! or Allow Access

Check the source URL, range string and account permissions. Google says a first-time connection may display #REF! and a request to Allow Access. Do not approve blindly: the receiving sheet’s editors may subsequently import other ranges from the same authorised source. It is often safer to place only the permitted data in a dedicated source sheet or use an approved restricted export.

A pivot table shows unexpected totals

Confirm one source record per row, consistent field types, clear headers, correct source range and aggregation method. Google says columns need headers for a pivot table. A Count aggregation differs from Sum, and a missing source range can exclude recent entries. Reconcile the result against an independently calculated total before making a business decision.

A QUERY function silently loses some values

Google’s QUERY documentation says that a column with mixed data types is interpreted according to its majority type, while minority types may be treated as null for the query. If some numeric codes were imported as text, records may be lost from a calculation even though the cells visibly contain content. Clean types first, then test the query against known cases.

Quick formulas readers can test

• Total: =SUM(D2:D20) adds eligible numeric values.
• Average: =AVERAGE(D2:D20) calculates the arithmetic mean.
• Count numeric entries: =COUNT(D2:D20) ignores ordinary text.
• Count Paid rows: =COUNTIF(B2:B20,"Paid") uses one criterion.
• Sum Paid amounts: =SUMIF(B2:B20,"Paid",D2:D20) applies a status condition.
• Two criteria: =COUNTIFS(B2:B20,"Paid",C2:C20,"East") counts matching pairs.
• Conditional label: =IF(D2>100,"High","Normal") returns one of two results.
• Eligible rows: =FILTER(A2:D20,D2:D20>100) returns selected records.
• Distinct categories: =UNIQUE(C2:C20) produces unique values.
• Compact trend: =SPARKLINE(D2:D12) displays a small chart inside one cell.

These examples assume the data is arranged as described, with suitable column types, and use comma argument separators. Some locales show different separators. Do not use a result for real financial decisions without verifying the source records and the formula’s actual interpretation.

Ten Google Sheets distinctions worth remembering

• Cell value versus displayed format: changing currency or date appearance need not change the stored number.
• Relative versus absolute reference: copied references move unless their row and column are anchored.
• Filter versus filter view: shared filtering behaviour differs from a personal saved or temporary view.
• Sort range versus sort sheet: ordering a partial range can separate data that belongs in one row.
• Protected range versus secure storage: editing restrictions are not confidentiality controls.
• Sheet table versus pivot table: structured source records differ from summarised categories.
• QUERY versus FILTER: a query language can group and transform while FILTER primarily selects qualifying arrays.
• IMPORTRANGE permission versus file sharing: a granted cross-file connection can expose additional source cells to receiving editors.
• Missing value versus zero: an unknown amount differs from a recorded zero.
• Autosaved version versus approved total: saving edits does not mean the numbers were audited.

A dependable six-step spreadsheet audit

1. Define the question. Identify whether you need an average, a total, a count, a category summary or a comparison across dates.

2. Check the source structure. Give every field a clear header, keep one record per row, verify dates and numbers, and preserve meaningful identifiers as text when needed.

3. Build the simplest calculation. Prefer a transparent SUMIFS or FILTER calculation when it answers the question, and use QUERY or pivots when the extra structure is justified.

4. Verify sample rows. Compare at least a few known records, check error states, and reconcile the final total against an independent record.

5. Manage collaborator access. Limit Editors, inspect inherited file rights, and never treat protected cells or hidden tabs as a security boundary.

6. Preserve a useful version. Name important approved versions or keep independent backups where the consequences of loss justify it. A shared live spreadsheet is not a guarantee of historical retention.

Practice: which term solves the problem?

• Every copied row must use the same tax rate: absolute reference.
• One user needs a personal temporary subset: filter view.
• Revenue must be grouped by region and month: pivot table or an appropriate QUERY.
• A user wants distinct names: UNIQUE.
• A second sheet needs approved source information: IMPORTRANGE and deliberate access review.
• Some QUERY values disappear in a mixed-type column: data type and majority-type behaviour.
• An editor cannot alter an important formula cell: protected range, not file encryption.
• The result cannot expand into occupied cells: array expansion error.
• A rate shows zero because there are no transactions: inspect #DIV/0! and missing denominator.
• A completed report must be checked against the ledger: reconciliation.

Frequently asked questions

What is the difference between a filter and a filter view?

A filter changes which records appear in the ordinary shared working context. A filter view can let people use distinct named filters; viewers may create temporary filter views that are not saved.

Can a protected sheet keep its data secret?

No. Google’s own help states protected sheets and ranges are not a security measure. Use actual file-sharing permissions to protect confidential information.

Does IMPORTRANGE need permission?

Yes for a new source-to-destination connection. After approved access, editors of the destination can retrieve other cells from the authorised source.

What is QUERY used for?

It applies a query language to spreadsheet data to select, filter, group, order and summarise eligible columns.

Why does QUERY sometimes ignore text in a numeric column?

Google says mixed-type columns can be interpreted using the majority data type, with minority-type values treated as null.

Does a pivot table update automatically?

Google says pivot tables refresh when values in their selected source range change. Always verify that the range includes all intended data.

What is the difference between Google Sheets and Excel?

Both are spreadsheets with functions and data tools, but their formulas, sharing, filters, permissions and available features can differ.

Does formatting a number as currency change its value?

Usually not. Formatting changes how a number appears, so check the original stored value and any required currency conversion.

Official Google Sheets references

• Google Sheets — sort, filter and create filter views
• Google Sheets — protect or hide sheets and ranges
• Google Sheets — IMPORTRANGE security and limits
• Google Sheets — QUERY function and mixed data types
• Google Sheets — create pivot tables
• Google Sheets — collaborate in spreadsheets
• Google Sheets — Google Docs Editors Help
• eduKateSG — Vocabulary for Microsoft Excel
• eduKateSG — Vocabulary for Google Drive

Google Sheets functions and interface details evolve, and locale, device and account permissions can change the correct procedure. Check the source, formula and current official help before acting on consequential data.