Google Sheets Guide: Formulas, Filters & Sharing

Google Sheets is an online spreadsheet tool for recording information, calculating figures, filtering records, building charts and collaborating on data. Open sheets.google.com, create a blank spreadsheet, put a clear header above each column and type one record per row. A formula starts with =: for example, =SUM(E2:E20) adds the numeric values in that range.

The biggest Google Sheets mistake is believing that a calculated answer is automatically meaningful. Sheets will happily total a column even when the values mix currencies, contain duplicate records or exclude rows the user forgot to select. Another serious mistake is sharing an editable spreadsheet publicly when it includes private information. Reliable work depends on good input data, correct formulas, clear definitions and permissions.

Quick answers: Use SUM for totals, AVERAGE for means, COUNTIF for matching records and FILTER to return selected rows. Turn on filters only after confirming the dataset includes full rows. Use filter views where available when collaborators need different views. Protect ranges to reduce accidental edits, but do not confuse sheet protection with security; choose Restricted sharing for private information.

Go directly to: Start · Formulas · Filters · Dropdowns · Sharing · Errors · FAQ.

What is Google Sheets, and when should you use it?

Google Sheets is part of Google’s online document editors. A spreadsheet consists of a grid of cells organised into columns and rows. Cells can contain text, numbers, dates, formulas and links. Sheets also includes tools for sorting, filtering, validation, charts and collaborative editing. It is useful when data must be organised into repeatable fields rather than buried in unstructured paragraphs.

A student can record study practice by date and topic. A family can track expenses. A community group can maintain an event checklist. A small business can summarise orders or stock. Sheets works best when each entry is consistent and the intended analysis is explicit. For very large or highly controlled transactional systems, a database or specialised product may be more suitable.

Google Sheets is not identical to Microsoft Excel. They share basic spreadsheet ideas, but function availability, formula syntax, collaboration behaviour, file compatibility and advanced features can differ. Use the actual file’s editor when following instructions, and verify converted workbooks after moving between them.

Make a spreadsheet that works before adding colours

Open Google Sheets and create a Blank spreadsheet.
Rename it for its purpose, such as ‘Household Expenses — November 2026’.
In row 1, enter Date, Description, Category, Quantity, Unit Price and Total into columns A through F.
Put one complete transaction on each row beneath the header.
In F2, enter =D2*E2 to calculate quantity times unit price.
Copy the formula down and manually check two results.
Share or export only after verifying the data and using the correct access permissions.

For example, if D2 is 4 and E2 is 12.50, then F2 should return 50.00. If D3 is 2 and E3 is 8, then F3 should return 16. These values can be added using =SUM(F2:F3) to produce 66. A simple example is better for learning than a complicated template that obscures where the numbers came from.

A spreadsheet should answer a question. ‘How much did we spend on groceries this month?’ is a clear objective. ‘Make the workbook more colourful’ is not a data question. Establish the purpose first, then decide which columns, formulas and chart types help.

Rows and columns: choose one meaning for each field

Every column should represent one field such as Date, Department, Amount, Unit or Status. Every row should represent the same type of thing, such as a transaction or task. Do not mix running totals, unrelated headings and ordinary transactions in the middle of one dataset if you intend to sort or filter it later.

Identifiers deserve special treatment. A Singapore postal code or product reference that begins with zero is an identifier, not a quantity you want to add. Store it as text before entry or import so that leading zeros survive. A mobile number is also not a numerical measurement, even though it contains digits.

Use a consistent date format and verify whether Sheets recognises a value as a real date or merely as a text string that looks like a date. Dates imported from another country can be interpreted differently under different locale settings. If later sorting depends on dates, this matters.

Google Sheets formulas: the basic idea

A formula starts with = and may use numbers, cell references, arithmetic operations and functions. =B2+C2 adds two cells. =B2*C2 multiplies them. =SUM(B2:B10) calls a function to add numeric values in a range.

The advantage of using references is that the answer can update when the input changes. Suppose an event organiser changes attendance from 30 to 35 and the cost per attendee is stored in another cell. A formula based on those cells recalculates. Hard-coding the original quantity into ten different formulas makes the workbook harder to maintain.

The formula can still be conceptually wrong. A calculation of ‘average expense’ across all transactions may not answer the more relevant question of monthly expense. Before using any function, define the real-world quantity, units and time period.

Six useful functions beginners should know

=SUM(F2:F20) totals the numeric values in the range.
=AVERAGE(F2:F20) calculates the arithmetic mean of numeric entries.
=COUNT(F2:F20) counts cells containing numbers; COUNTA counts non-empty cells.
=COUNTIF(C2:C20,"Transport") counts records in that category.
=SUMIF(C2:C20,"Transport",F2:F20) adds the corresponding amounts for that category.
=IF(F2>100,"Review","Within budget") produces a label based on the stated condition.

These functions do different jobs. COUNTIF counts qualifying rows, while SUMIF totals qualifying amounts. AVERAGE calculates a numerical mean without understanding the business meaning of the records. A zero value can affect a mean; an empty cell can behave differently. Do not substitute zero for missing information without a reason.

Formula separators and number formats may depend on spreadsheet locale. If an example using commas does not parse in your file, check File → Settings and the relevant locale before changing the mathematical meaning. For a mixed-country collaboration, document which currency and date conventions everyone is using.

Relative and absolute references: copy formulas safely

When you copy =D2*E2 downward, Sheets normally adjusts its row references to =D3*E3, then =D4*E4 and so on. These are relative references. They are useful when each row follows the same calculation.

If one percentage or rate remains fixed in H1, use an absolute reference such as $H$1. A formula like =F2*$H$1 can be copied down while retaining the same rate cell. The dollar signs lock the reference; they do not themselves mean that the value is in dollars.

Check copied formulas near the first and last rows. A spreadsheet might contain one formula pointing outside the intended range after rows were inserted or deleted. A wrong reference can create a plausible-looking total that passes a casual visual check.

FILTER: create a list of matching rows

The FILTER function returns values that meet the supplied conditions. For example, =FILTER(A2:D20,D2:D20="Paid") can display records whose fourth column is Paid, assuming the status really is in column D and the ranges contain matching numbers of rows. This can be useful for creating a separate view of active or completed tasks.

A FILTER formula is different from clicking the filter icon on a dataset. The formula returns another array of results in cells, while an ordinary filter changes which rows are shown in the existing dataset. Understanding this difference prevents confusion when a team member says ‘I filtered the sheet’ without explaining what was changed.

If no rows match, Sheets may return an error rather than an empty list. An appropriate IFERROR or other condition can provide a clearer user-facing message, but first check whether the lack of matches is legitimate. Silently hiding every error can conceal incorrect data.

Sort and filter without mixing one person’s records with another

Sorting rearranges records by a chosen column; filtering hides records that do not meet conditions. For example, sorting by Amount descending shows the largest transactions at the top. Filtering Category to ‘Groceries’ shows only records belonging to that group. The underlying list can remain intact when filtering is done properly.

Google’s official help warns that a normal filter applied to a shared spreadsheet can affect what other editors see. When several people need different filtered views of the same data, consider an appropriate filter view, where available, so one person’s analysis does not disrupt another’s current work.

Select the complete dataset including its header row.
Choose Data → Create a filter, or the current filter action.
Use a header filter to select conditions or values.
Confirm the visible rows are the intended subset.
When comparing totals, check whether the formula includes filtered-out rows.
Clear or remove the filter when the full dataset is needed.
For separate personal analyses, use a filter view where supported and label the view clearly.

The dangerous mistake is sorting only one column without its neighbouring columns. Then names can become paired with the wrong amounts, students with the wrong scores, or dates with the wrong events. Always ensure that whole records move together. A clean, contiguous dataset makes this easier.

Filters versus filter views in collaborative work

A basic filter is useful when one person is examining a sheet. In collaborative work, a standard filter can surprise another editor because its chosen criteria are applied to the shared view. Filter views can let collaborators apply their own temporary or saved perspectives, depending on the interface.

A named filter view is valuable for a repeat question: ‘Unpaid invoices’, ‘Tasks due this week’ or ‘Purchases above $100’. The label should explain the conditions used. If conditions change, review the view rather than trusting its familiar name.

Do not use filtered results as evidence of the complete population unless you document the excluded rows. A total that includes hidden records can be mathematically correct but misleading next to a label that seems to refer only to visible rows.

Dropdown lists and data validation

Data validation helps restrict or guide what users enter. For a task tracker, a Status column may have values such as Not started, In progress and Complete. A dropdown reduces inconsistent variations such as ‘Done’, ‘done!’ and ‘Completed’, which would make COUNTIF formulas unreliable.

Select the cells intended for consistent input.
Choose Insert → Dropdown or Data → Data validation, depending on your current interface.
Define the valid options or range of allowed values.
Choose whether invalid inputs should be warned about or rejected, where supported.
Apply the rule and test several example inputs.
Explain the meaning of each option to collaborators.
Review the validation when the business process changes.

Validation supports quality but does not prove that the entered facts are true. A person can select ‘Complete’ for work that was never finished. The data model should include an owner and evidence or confirmation when that distinction is important.

Avoid excessively complicated dropdowns where ordinary text would work better. The purpose is to improve accuracy, not to make it difficult for someone to enter a valid exception.

Conditional formatting: make exceptions visible

Conditional formatting changes a cell’s appearance when a stated condition is satisfied. Google’s help describes applying rules through Format → Conditional formatting. For example, an overdue date can be highlighted, or a number below a threshold can receive a different background. This can make a long sheet easier to scan.

Colour is not proof. A green cell may mean that one formula returned ‘Complete’, but the underlying information could still be wrong. Provide text labels or an accessible visual distinction so a reader does not have to interpret colour alone. A useful status column remains valuable even without formatting.

Be careful with thresholds. A score of 80% may be low for one task and high for another. A chart or conditional format should communicate a justified rule rather than impose an arbitrary colour that exaggerates a difference.

Protect sheets and ranges: not the same as confidentiality

Google Sheets provides a Protect sheets and ranges feature that can limit who edits selected cells in supported files. This is useful when you have formulas or reference fields that should not be changed accidentally. Google’s own documentation explicitly warns that protection is not a security measure: people may be able to copy, print, export or otherwise reuse visible data.

If a sheet contains sensitive information that someone should not read, do not rely on range protection. Share a separate limited document or change actual file access permissions. Protection is primarily a control over editing, not a system for hiding secrets from authorised viewers.

Identify which cells contain formulas or approved reference values.
Open Data → Protect sheets and ranges in a supported Google Sheets file.
Select the intended sheet or range.
Choose editing permissions or warning controls where available.
Test the rule with an appropriate collaborator.
Review whether protected formulas still update when valid input changes.
Keep the file’s overall sharing restrictions suitable for the information inside.

If the feature is absent, check whether the workbook is an Office file being edited without conversion, or whether permissions or device limitations apply. An option being unavailable in a menu does not mean an unofficial browser extension should be installed.

Sharing uses Google Drive’s general access model. Viewer allows reading; Commenter permits relevant feedback; Editor allows changes. A spreadsheet can contain financial and personal data far beyond what appears on the first visible tab, so review every sheet before inviting outside people.

A link set to Anyone with the link can be forwarded. Even if you choose Viewer, a person may still capture or copy visible information. For confidential data, use Restricted access, named recipients and separate redacted reports where appropriate.

Before giving Editor access, ask whether the person needs to change formulas, enter rows or merely verify data. A small input form or a separate designated area may be safer than unlimited editing of an important shared register.

Collaboration, comments and version history

Google Sheets can show edits and version history in supported interfaces. This is useful when someone accidentally overwrites a formula or changes a crucial value. The ability to inspect earlier versions is not a guarantee that every error can be reversed without affecting later legitimate edits.

For important workbooks, establish who owns the definition of each metric and who may change formulas. A team member can legitimately enter a new order while another person should approve changes to tax calculations or sales categories. Separate data-entry permission from formula governance.

If a sheet becomes inconsistent, pause and examine the change history before overwriting more cells. Recovering one affected formula may be safer than restoring an entire older version that excludes recent entries.

Charts: visualise a result without misleading the reader

A chart is a visual summary of numbers. A column chart can compare expenses by category, while a line chart can show a time series. Choose the chart after checking the source data and the question to be answered. A chart cannot make a mislabelled dataset accurate.

Use clear titles and units. For example, ‘Monthly transport expenses, SGD, January–October 2026’ is more informative than ‘Expenses’. Be cautious with scales that exaggerate small changes or with category labels that hide missing periods.

Before publishing a chart, inspect whether its data range includes the newest rows and whether a filter is excluding records. A chart based on a partial subset is fine when labelled honestly. It is misleading when described as the entire dataset.

Importing Excel and CSV files

Google Sheets can import supported CSV and spreadsheet files. An import may change formulas, fonts, date recognition, separators or data types. Inspect the results before using them for a decision. A file that opens without a warning is not necessarily identical to the original.

CSV files contain values separated by delimiters, not a full record of complex workbook formatting and formulas. If the data includes commas inside text or numbers, proper quoting and locale matter. Verify the number of rows, important figures, first and last records, and any leading-zero identifiers after import.

If the source is a financial statement, retain the original document for verification. Do not rely on a converted table until totals and representative entries match. A correct formula applied to corrupted input remains an incorrect analysis.

Exporting a Sheet to Excel, PDF or CSV

Sheets can export to formats such as Microsoft Excel, PDF and CSV, depending on the file and interface. Choose Excel if another person will continue editing formulas in Excel, PDF for a stable visual report, or CSV when another system needs a simple data table. Each format preserves different properties.

A CSV does not preserve the workbook’s multiple sheets, formula design or formatting in the way an Excel file does. A PDF is usually not suitable for later data manipulation. An Excel export can introduce compatibility concerns when the original relies on Sheets-specific functions.

Always reopen the exported file and test it in the intended application. Check date fields, formula results, row counts and page layout. Do not claim a report is final merely because it has been downloaded.

Google Sheets offline: test before travelling

Google provides offline editing for supported Sheets through appropriate setup. The official Drive help explains that desktop offline access requires a compatible browser, the Google Docs Offline extension and preparation while connected. It is not enough to bookmark the online link.

Open the appropriate Google Account on a trusted computer.
Enable the documented offline capability for the browser and Drive.
Mark the spreadsheet Available offline where supported.
Disconnect from the internet and test the actual file.
Reopen it online after reconnecting and verify synchronisation.
Inspect version history if multiple editors changed the file during the offline period.

Offline edits may later conflict with changes made by collaborators. If the sheet controls an event schedule, stock count or budget, confirm the current authoritative state after reconnecting. Do not assume that the most recent-looking number is necessarily the one everyone agreed on.

Three worked examples

A household budget

Use Date, Category, Description and Amount. Enter one expense per row. Use SUM for all expenses and SUMIF for each category. Check whether transfers between accounts should be counted as spending or excluded. A clean definition prevents double counting.

A small event registration list

Use Registration ID, Date, Name, Status and Payment Confirmation without storing unnecessary sensitive identifiers. Use dropdowns for status and a filter view for confirmed registrations. Restrict edit access to authorised organisers and publish only aggregate counts to others.

A school project progress tracker

Use Task, Responsible Person, Deadline, Status and Evidence Link. Apply conditional formatting to overdue items and keep the real status in text. Use comments to ask for changes and version history to investigate accidental overwrites. Do not place private student data into a public link.

Common Google Sheets errors

#DIV/0! appears

A formula is dividing by zero or an empty denominator. Check whether the denominator should have a value. Do not simply show 0% if the measure is actually undefined.

#VALUE! appears

A formula may be receiving text where a number is required. Check imported currency strings, spaces and data types rather than changing the result blindly.

#REF! appears

A referenced cell, range or array output may be invalid. Inspect whether rows or columns were deleted or whether a formula needs a different output location.

The filter shows the wrong records

Review the actual selected data range and filter conditions. A hidden row or stale saved filter can make the result appear incomplete.

A collaborator cannot edit certain cells

Check both file access roles and protected sheet or range settings. Being an Editor of the file does not mean every range has identical editing permissions.

My chart does not include new entries

Check the chart’s data range and whether your new rows are included. A fixed range such as A1:C20 will not automatically include row 21 in every configuration.

My exported file has changed formulas

Check whether the original formula is supported in the destination program. Verify outputs with a sample calculation rather than assuming a successful download preserved identical behaviour.

A ten-minute accuracy checklist

Confirm each column has a clear definition and one type of data.
Check identifiers, dates, currencies and missing values.
Test basic formulas against hand calculations.
Verify sorting did not separate values from their original rows.
Inspect whether filters or filter views exclude information.
Check formula ranges and new rows.
Review sharing permissions and protected ranges separately.
Reopen the exported file in its intended program.
Keep an independent backup of important source data.

A dependable spreadsheet is not just a set of equations. It is a documented system whose inputs, definitions, calculations and access rules correspond to the real-world question.

Frequently asked questions

Is Google Sheets free?

Core Sheets functionality is available with standard Google Accounts. Additional storage, organisational or specialised capabilities may depend on account type and subscription.

What formula adds a column?

Use a SUM formula such as =SUM(B2:B20), adjusting the range to your actual data.

What is the difference between FILTER and filtering rows?

The FILTER function produces a result array; the interface filter changes which rows are shown in the existing sheet.

Can I protect a sheet so nobody can read it?

Protect sheets and ranges primarily controls editing. Google’s help says it is not a security measure. Use appropriate file sharing for confidentiality.

Why are people seeing the same filtered view?

A standard filter can affect collaborators. Use an appropriate filter view when people need separate filtered perspectives.

Can Google Sheets replace Excel?

Many everyday tasks are similar, but formula, file compatibility and advanced features differ. Verify the specific workbook and workflow.

Why do dates or postal codes change after import?

Sheets may interpret text as dates or numbers under locale rules. Preserve identifiers as text and verify imported values before analysis.

Official resources and next steps

Google’s current documentation explains sorting and filtering, conditional formatting, protecting sheets and ranges, sharing files and working offline. Use these to confirm the current menus and limitations of features.

Your next step is to create a ten-row table, calculate a total with SUM, filter for one category and share it with a trusted reviewer as Viewer. If the data, result and permissions all make sense, you have the foundation for reliable Sheets work.

Continue with Microsoft Excel formulas and tables, Google Drive sharing, Google Docs collaboration and Google Sheets vocabulary.

Explore the other guides in this batch: Google Docs · Microsoft Teams · Apple App Store. Use the guide that matches the particular work you need to complete, and follow the appropriate official product documentation for changing features.

Everything we need to know about Google Sheets | Formulas, Filters and Sharing