Written by Shahzaib Ali

Google Sheets Hidden Formulas

Google Sheets is easy to use when you’re doing simple calculations.

You enter some numbers, add a SUM, sort a list, and you’re done.

But Sheets becomes much more powerful once you start using formulas that can automate repetitive tasks, clean messy data, connect spreadsheets, and create dynamic reports.

Many people use Google Sheets every day without realizing how much work they could eliminate with a handful of less obvious formulas.

Here are eight useful formulas worth learning if you regularly work with spreadsheets.

1. ARRAYFORMULA — Apply One Formula to an Entire Column

One of the most common spreadsheet habits is entering a formula in one cell and dragging it down hundreds of rows.

It works, but it’s easy to forget when new rows are added.

ARRAYFORMULA allows you to apply a calculation across a range from a single formula.

Imagine column B contains order quantities and column C contains prices. You want the total in column D.

Instead of putting this in D2:

=B2*C2

and dragging it down, you can use:

=ARRAYFORMULA(B2:B*C2:C)

The formula processes the corresponding values throughout the ranges automatically.

When additional data is added to columns B and C, the calculation can extend to the new rows without requiring you to copy the formula manually.

One important limitation

Don’t place individual formulas in cells underneath an ARRAYFORMULA that is already producing results in that column.

The array output needs room to expand. Existing values or formulas in the output range can cause errors.

Best for: Repetitive column calculations and automatically extending formulas.

2. QUERY — Turn Your Spreadsheet Into a Simple Data-Analysis Tool

QUERY is one of the most powerful formulas in Google Sheets, especially when you’re working with larger datasets.

It lets you filter, select, sort, and summarize information using a query language that resembles SQL.

Suppose you have:

• Column A: Salesperson
• Column B: Region
• Column C: Deal Value

You want to display only deals from the North region worth more than $5,000.

You can use:

=QUERY(A:C, "SELECT A, B, C WHERE B = 'North' AND C > 5000")

The result is a new table containing only the rows that match those conditions.

Because the formula references the original range, the result can update when the source data changes.

This makes QUERY particularly useful for dashboards and reporting sheets. You can keep raw data in one area and create cleaner views elsewhere without manually copying and filtering the original information.

Watch your quotation marks

Text values inside the query normally need single quotes:

WHERE B = 'North'

Numbers don’t:

WHERE C > 5000

Mixing these up is a common source of query errors.

Best for: Filtering, analyzing, and creating dynamic summaries from larger datasets.

3. IMPORTRANGE — Connect Separate Google Sheets

If information is stored in different spreadsheets, manually copying data between them can quickly become a maintenance problem.

IMPORTRANGE lets one Google Sheet pull data from another spreadsheet.

The basic syntax is:

=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100")

The first argument is the URL of the source spreadsheet.

The second specifies the sheet and range you want to import.

The first time you connect two spreadsheets, Google Sheets will normally ask you to authorize access.

Once permission is granted, the destination spreadsheet can retrieve the selected data from the source.

For example, you could maintain a master customer database and have separate reporting spreadsheets pull specific information from it.

When the source information changes, the imported data can update as well.

Keep imported ranges reasonable

Large IMPORTRANGE ranges can affect performance.

Instead of importing an entire spreadsheet with thousands of unnecessary rows and columns, bring in only the information your destination sheet actually needs.

Best for: Connecting separate Google Sheets and keeping shared information synchronized.

4. REGEXEXTRACT — Pull Specific Information From Messy Text

REGEXEXTRACT is especially useful when important information is buried inside larger text strings.

It uses regular expressions to find text that matches a particular pattern.

Suppose a product description contains an SKU such as:

Product Name — SKU-AB12345 — Blue

You want to extract only the SKU.

You can use:

=REGEXEXTRACT(A2, "SKU-[A-Z0-9]+")

The formula searches for SKU- followed by one or more letters or numbers.

Other useful examples include:

Extract a number

=REGEXEXTRACT(A2, "[0-9]+")

Extract an email address

=REGEXEXTRACT(A2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]+")

Extract text between parentheses

=REGEXEXTRACT(A2, "\(([^)]+)\)")

If no match is found, REGEXEXTRACT can return an error. You can handle that with IFERROR:

=IFERROR(REGEXEXTRACT(A2, "your_pattern"), "")

This replaces the error with a blank cell when there isn’t a match.

Best for: Cleaning and extracting structured information from messy text.

5. XLOOKUP — A More Flexible Alternative to VLOOKUP

VLOOKUP is useful, but it has an important limitation: the lookup column needs to be positioned to the left of the result column.

XLOOKUP is more flexible because the search range and result range are specified independently.

The basic structure is:

=XLOOKUP(search_key, search_range, result_range, [if_not_found])

For example, suppose employee names are in column D while hire dates are in column A.

You can search column D and return the corresponding value from column A:

=XLOOKUP("Sarah Chen", D:D, A:A, "Not found")

If the name doesn’t exist, the formula returns:

Not found

instead of simply displaying an #N/A error.

You can also use an empty string if you want the result to remain blank:

=XLOOKUP("Sarah Chen", D:D, A:A, "")

The result is easier to read and the formula is often easier to maintain than a complicated lookup workaround.

Best for: Looking up information when your return column isn’t positioned conveniently for VLOOKUP.

6. UNIQUE + SORT — Clean Up Duplicate Lists

Sometimes you receive a list containing hundreds of repeated values but only need to know which unique values exist.

UNIQUE can handle that instantly.

=UNIQUE(A2:A100)

This returns each distinct value from the selected range.

SORT can then organize the results:

=SORT(A2:A100, 1, TRUE)

Here, TRUE sorts in ascending order. Using FALSE would sort in descending order.

The two formulas can also be combined:

=SORT(UNIQUE(A2:A100))

Now you get a sorted list with duplicates removed.

This is useful for creating cleaner reports, preparing lists for data validation, or quickly understanding the distinct categories contained in a dataset.

Best for: Creating clean, unique, automatically sorted lists.

7. SPARKLINE — Put a Mini Chart Inside a Cell

You don’t always need a full chart to understand a trend.

SPARKLINE creates a small chart directly inside a spreadsheet cell.

For example:

=SPARKLINE(B2:M2)

If B2 through M2 contain monthly values, the formula creates a compact visual representation of the trend.

You can also customize the sparkline.

For example:

=SPARKLINE(B2:M2, {"charttype","bar"; "color","#4285F4"})

This can be particularly useful in dashboards where you want to show trends without taking up the space required by a traditional chart.

You can place a sparkline beside each row of a report and quickly see which metrics are rising, falling, or remaining relatively stable.

Best for: Compact dashboards and quick visual trend indicators.

8. CONCATENATE — Combine Text Without Manual Copying

Although newer functions such as CONCAT and TEXTJOIN are also available, CONCATENATE is still useful to understand, especially if you’re working with older spreadsheets or existing formulas.

Suppose first names are in column A and last names are in column B.

Instead of manually combining them, you can use:

=CONCATENATE(A2, " ", B2)

This combines the two values with a space between them.

For example:

A2 = Sarah
B2 = Chen

produces:

Sarah Chen

For larger datasets, this can save a lot of repetitive editing.

Best for: Combining text from multiple cells.

Common Mistakes With Advanced Google Sheets Formulas

Learning the formulas is only half the process. A few small mistakes can create confusing errors.

Forgetting Absolute References

When copying formulas, relative references change automatically.

For example:

A2

can become A3, A4, and so on when copied.

If you need a reference to stay fixed, use $:

$A$2

This remains fixed when the formula is copied.

Using the Wrong Quotation Marks

Formula syntax requires normal quotation marks.

If you’re copying formulas from a formatted document that replaces straight quotes with typographic quotes, Google Sheets may produce a parsing error.

When a formula looks correct but refuses to work, quotation marks are one thing worth checking.

Ignoring Errors in Empty Rows

Some formulas will return errors when they don’t find a value.

If you want to display a blank instead, you can often use:

=IFERROR(your_formula, "")

This is especially useful when building sheets that contain many empty rows.

Making Formulas Too Complicated

It’s tempting to create one enormous formula that performs ten different operations.

Sometimes that’s useful. Often it makes the spreadsheet harder to understand.

Helper columns can make complicated calculations easier to read, test, and troubleshoot.

A few simple formulas are often better than one formula that nobody can maintain six months later.

A Practical Formula Toolkit

You don’t need to memorize every Google Sheets function.

Start with a small toolkit:

ARRAYFORMULA → Apply calculations across ranges.

QUERY → Filter and summarize data dynamically.

IMPORTRANGE → Connect separate spreadsheets.

REGEXEXTRACT → Pull structured information from text.

XLOOKUP → Find and return related information flexibly.

UNIQUE + SORT → Build clean lists without duplicates.

SPARKLINE → Display compact trends inside cells.

CONCATENATE → Combine text from multiple cells.

Once these become familiar, many repetitive spreadsheet tasks become much easier to automate.

Final Verdict

Google Sheets is much more than a digital table.

The biggest productivity gains often come from recognizing that a task you’re doing manually can be expressed as a formula.

ARRAYFORMULA can eliminate repetitive copying. QUERY can turn raw data into useful reports. IMPORTRANGE can connect spreadsheets. REGEXEXTRACT can clean messy text. XLOOKUP can simplify data retrieval, while UNIQUE, SORT, and SPARKLINE can make everyday reporting faster and easier to understand.

You don’t need to learn hundreds of formulas.

Learn a few that solve the problems you actually encounter, and build from there.

The next time you find yourself copying, pasting, sorting, or cleaning hundreds of cells manually, stop for a moment and ask:

“Can Google Sheets do this for me?”

Very often, the answer is yes.

Any Question? Contact Us