Google Sheets for Data Analysis: Underrated Power Tools

Google Sheets is often treated as a lighter Excel, but for many analysis tasks it has distinctive strengths: real-time collaboration, easy sharing, and a few functions that have no exact Excel equivalent, above all QUERY. For small and medium datasets, a well-built Sheets workbook can be a complete analysis tool. The examples below assume a tab named Orders with columns A to E: order date, region, product, units and revenue, with headers in row 1. Formulas are shown with the behaviour Google documents; no calculated results are shown because they depend on your data.

QUERY: SQL-like summaries in one formula

QUERY runs a query written in the Google Visualization API Query Language against a range (Google: QUERY). The language looks like SQL, and columns are referred to by letter. Revenue and orders by region, largest first:

=QUERY(Orders!A1:E, "select B, sum(E), count(A) where E > 0 group by B order by sum(E) desc", 1)

The last argument, 1, says the range has one header row. One formula replaces a pivot table or a block of SUMIFS formulas, and it updates as rows are added because the range A1:E is open-ended. Filtering by date uses the date keyword:

=QUERY(Orders!A1:E, "select B, sum(E) where A >= date '2026-01-01' and A < date '2026-02-01' group by B", 1)

If you already know SQL, QUERY is the quickest way to use it inside a spreadsheet; if you do not, it is a gentle introduction. From Excel to SQL covers the underlying ideas.

ARRAYFORMULA: one formula for a whole column

Instead of copying a formula down thousands of rows, ARRAYFORMULA applies it to entire ranges at once (Google: ARRAYFORMULA). Revenue per unit for every row, leaving blank rows empty:

=ARRAYFORMULA(IF(Orders!D2:D = "", "", Orders!E2:E / Orders!D2:D))

One formula in the first cell fills the column and extends automatically to new rows. That removes a common source of errors: new rows that the copied formula never reached.

FILTER, SORT and UNIQUE: live subsets

FILTER returns only the rows that meet conditions (Google: FILTER). All North region orders above 1,000, sorted by revenue:

=SORT(FILTER(Orders!A2:E, Orders!B2:B = "North", Orders!E2:E > 1000), 5, FALSE)

UNIQUE(Orders!B2:B) returns the distinct regions. Combined, they create small live reports that update whenever the source data changes.

REGEXEXTRACT: pulling structure out of messy text

Exported text often hides useful pieces: order IDs inside descriptions, domains inside email addresses, codes inside product names. REGEXEXTRACT extracts the first part of the text that matches a regular expression (Google: REGEXEXTRACT). The domain of an email address in column F:

=ARRAYFORMULA(IF(F2:F = "", "", REGEXEXTRACT(F2:F, "@(.+)$")))

The part in brackets is what is returned. Regular expressions take some practice, but a handful of patterns, digits \d+, everything after a character, text between two markers, cover most cleaning needs.

IMPORTRANGE: combining spreadsheets

Teams often keep data in separate files, one per region or month. IMPORTRANGE pulls a range from another spreadsheet (Google: IMPORTRANGE):

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID", "Orders!A1:E")

The first time, you must explicitly allow the connection between the two files. Combine several imports with curly braces, ={IMPORTRANGE(...); IMPORTRANGE(...)}, and wrap the result in QUERY to summarise across files. Each import depends on the source file's structure, so agree a fixed column layout with whoever maintains it.

Pivot tables and charts

For exploration, pivot tables are often faster than formulas: select the data, then Insert, Pivot table, and choose rows, columns, values and filters (Google: pivot tables). Use them to explore and to cross-check totals from QUERY formulas; the two should always agree.

A reusable workbook layout

A structure that keeps a Sheets analysis understandable months later:

1. Raw: data exactly as imported or pasted, never edited by hand.
2. Clean: ARRAYFORMULA columns that trim text, fix types and add derived fields, reading from Raw.
3. Summary: QUERY and FILTER formulas producing the tables people look at.
4. Checks: row counts and totals comparing Raw, Clean and Summary, so a broken import is noticed immediately.
5. Notes: definitions, sources, exclusions and the date of the last refresh.

Because every calculated tab reads from the one before it, replacing the Raw tab with new data updates everything, and the Checks tab shows at a glance whether the numbers still reconcile.

Collaboration features that help analysis

• Version history lets you see who changed what and restore earlier versions, which makes shared workbooks safer.
• Comments and assignments keep questions about specific numbers attached to the cells they concern.
• Protected ranges stop colleagues from accidentally overwriting formulas or source data.
• Sharing with view-only access lets you publish a summary without exposing the working tabs.

Where Sheets stops being the right tool

Sheets slows down as workbooks grow large and formula-heavy, and it is not a database. Move the heavy lifting elsewhere when:

• data runs to hundreds of thousands of rows or more;
• the same cleaning must be repeated often and exactly;
• several reports depend on shared definitions that must stay consistent;
• access to sensitive data needs fine-grained control.

At that point, SQL on a database or warehouse, with Sheets or a BI tool on top for presentation, is the usual pattern. Many habits transfer directly from Excel; Excel formulas for data analysis covers the shared foundations, such as lookups and conditional totals.

Excel basics, formulas and pivot tables, whose skills transfer directly to Google Sheets, are part of the Data Analytics course.

Sources

• QUERY function (Google Docs Editors Help) — Google Docs Editors Help
• ARRAYFORMULA (Google Docs Editors Help) — Google Docs Editors Help
• FILTER function (Google Docs Editors Help) — Google Docs Editors Help
• REGEXEXTRACT (Google Docs Editors Help) — Google Docs Editors Help
• IMPORTRANGE (Google Docs Editors Help) — Google Docs Editors Help
• Create and use pivot tables (Google Docs Editors Help) — Google Docs Editors Help