Written by Shahzaib Ali
Google Sheets Hidden Formulas
I spent forty-five minutes manually combining first and last name columns once. Cell by cell. Copy, paste, type a space, copy, paste again. I was doing this across 200 rows of a client contact list and was about halfway through when a colleague walked past, looked at my screen, and said “why aren’t you just using CONCATENATE?”
I didn’t know what CONCATENATE was.
That afternoon taught me two things. First, that I had been using Google Sheets like a very expensive notepad. Second, that most people — including people who use Sheets every single day for real work — are only using about 15% of what the tool can actually do.
Since that embarrassing afternoon, I’ve gone deep on Sheets formulas. Not the obvious ones everyone knows (SUM, AVERAGE, COUNT — those don’t count). I mean the formulas that make colleagues stop and ask “wait, how did you do that?” Here are the ones I actually use regularly, explained the way I wish someone had explained them to me.
ARRAYFORMULA — The One That Unlocks Everything Else
Most people write a formula in one cell and then drag it down the column to apply it to every row. That works, but it’s fragile — if you add rows later, you have to remember to extend the formula, and if someone pastes data over your formula cells, the whole thing breaks.
ARRAYFORMULA fixes this by applying a formula to an entire column at once, from a single cell.
Here’s how it works. Say you have order quantities in column B and price per unit in column C, and you want a total in column D. Normally you’d write =B2*C2 in D2 and drag it down. With ARRAYFORMULA, you write this once in D2:
=ARRAYFORMULA(B2:B*C2:C)
That single formula automatically calculates the result for every row in the column — including rows you haven’t filled in yet. When new data is added to B and C, D updates automatically with no action from you.
The first time I used this on a client reporting sheet that had new data added weekly, I stopped getting the “the totals column is broken again” message. Completely. The formula just handled it.
One gotcha: Don’t put ARRAYFORMULA in a column where another formula already exists in the rows below. They’ll conflict and you’ll get errors. Start with a clean column or clear the individual cell formulas first.
QUERY — A Database Inside Your Spreadsheet
This one sounds intimidating and the syntax looks weird the first time you see it. Push through that, because QUERY is probably the most powerful formula in all of Google Sheets.
QUERY lets you pull, filter, sort, and summarize data from a range using something similar to SQL database language — but you don’t need to know SQL to use it. The basic version is genuinely approachable.
Say you have a sales data sheet with columns for salesperson name (column A), region (column B), and deal value (column C). You want to pull only the rows where the region is “North” and the deal is over $5,000. Here’s the formula:
=QUERY(A:C, "SELECT A, B, C WHERE B = 'North' AND C > 5000")
That’s it. That single formula creates a filtered table wherever you put it, pulling only the rows that match your criteria. It updates automatically as the source data changes.
I use QUERY to build summary dashboards that pull from raw data sheets. The raw data sheet is messy and constantly changing — new rows being added, people editing things. The dashboard just reads what it needs via QUERY and stays clean and current automatically.
The mistake I made early on: forgetting that the WHERE clause uses single quotes for text values (WHERE B = 'North') but no quotes for numbers (WHERE C > 5000). Mixing these up causes errors that are confusing if you don’t know why.
IMPORTRANGE — Making Two Sheets Talk to Each Other
Before I found IMPORTRANGE, my solution for pulling data from one Google Sheet into another was downloading one as a CSV and uploading it into the other. I am not proud of this.
IMPORTRANGE pulls a range of data from a completely separate Google Sheet into your current sheet, and it stays live — when the source updates, the imported data updates too.
The syntax is:
=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100")
Replace the URL with the actual URL of the source spreadsheet (paste the whole thing in quotation marks), and specify the sheet name and range after the comma.
The first time you use it between two sheets, Google will ask you to grant permission for the connection — there’s a small “Allow access” prompt that appears. You only do this once per connection.
I use IMPORTRANGE to maintain a master client database that multiple project sheets pull from. When we update a client’s contact details in the master sheet, every other sheet that imports from it gets the update automatically. Before this, we were maintaining the same contact information in seven different places and they were always slightly out of sync.
Practical tip: IMPORTRANGE can be slow to load when the imported range is very large. If you’re importing thousands of rows, it can make your sheet sluggish. Import only the columns and rows you actually need rather than entire sheets.
REGEXEXTRACT — Pulling Specific Text From Messy Data
This is the one that makes non-technical people think you’re doing something magical.
REGEXEXTRACT pulls a specific piece of text out of a cell based on a pattern. It uses something called regular expressions, which sounds technical, but for common use cases the patterns are simple and learnable in about twenty minutes.
Here’s a real scenario: a client sent me a sheet of product descriptions that included the SKU number embedded somewhere in the middle of each description, formatted like “SKU-XXXXX”. I needed to extract just the SKU codes into a separate column — all 300 of them.
With REGEXEXTRACT:
=REGEXEXTRACT(A2, "SKU-[A-Z0-9]+")
That formula finds anything matching the pattern “SKU-” followed by letters and numbers, and pulls it out of the cell. Applied to all 300 rows, the whole task took about four minutes instead of the afternoon it would have taken manually.
Other patterns I use regularly:
- Extract a number from a string:
=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 the pattern doesn’t find a match, REGEXEXTRACT returns an error. Wrap it in IFERROR to handle those cleanly: =IFERROR(REGEXEXTRACT(A2, "your_pattern"), "")
XLOOKUP — The VLOOKUP Replacement That Actually Works Properly
If you’ve been using VLOOKUP for years, this section might mildly annoy you because you’ll realize VLOOKUP was making things harder than necessary.
VLOOKUP has a well-known limitation: it can only look to the right. If your lookup value is in column C and the value you want to return is in column A (to the left), VLOOKUP can’t do it. You’d have to restructure your data or use an awkward INDEX/MATCH workaround.
XLOOKUP looks in any direction, handles missing values gracefully, and has a cleaner syntax.
=XLOOKUP(search_key, search_range, result_range, [if_not_found])
Example: you have employee names in column D and their hire dates in column A. With VLOOKUP this was impossible without rearranging your sheet. With XLOOKUP:
=XLOOKUP("Sarah Chen", D:D, A:A, "Not found")
That fourth argument — “Not found” — is what XLOOKUP returns if there’s no match, instead of the dreaded #N/A error. You can put any text there, including an empty string "" if you want nothing to appear.
The thing I appreciate most about XLOOKUP: it handles duplicate values more intuitively than VLOOKUP, and it’s easier to read when you come back to your sheet three months later and try to figure out what a formula was doing.
UNIQUE and SORT — The Underappreciated Pair
These two are short formulas but they’ve saved me ridiculous amounts of time in specific situations.
=UNIQUE(A2:A100) returns a list of all unique values from a range, removing duplicates automatically. I use this constantly when I receive data with repeated entries and need to know what distinct values exist. Drop it in a blank column, instantly get a clean non-duplicate list.
=SORT(A2:A100, 1, TRUE) sorts a range and spits the sorted version wherever you put the formula — without touching or rearranging the original data. The second argument is which column to sort by (1 means the first column of your range), and TRUE means ascending. FALSE gives you descending order.
Combining them: =SORT(UNIQUE(A2:A100)) gives you a sorted, deduplicated list from messy source data. One formula. I’ve used this to build dropdown lists for data validation that stay current as the source data grows.
SPARKLINE — Tiny Charts Inside Cells
Most people don’t know you can put a chart inside a single cell in Google Sheets. SPARKLINE does exactly this.
=SPARKLINE(B2:M2) creates a tiny line chart inside the cell, based on the values in that row. For a sheet tracking monthly metrics across 12 months, adding a SPARKLINE column gives you instant visual trend information without creating a separate chart.
You can customize the type and color:
=SPARKLINE(B2:M2, {"charttype","bar"; "color","#4285F4"})
I use SPARKLINE in dashboard sheets where I want trend visibility at a glance without the bulk of full charts. It looks significantly more polished than a plain data table and takes about 30 seconds to add.
Common Mistakes People Make With These Formulas
Copying formulas between sheets without checking absolute vs relative references. A formula that works perfectly in Sheet 1 can break completely in Sheet 2 because the cell references are shifting. Use $ signs to lock references that shouldn’t move: $A$2 stays fixed, A2 adjusts relative to where you paste it.
Using the wrong quotation marks. Google Sheets needs straight quotation marks (") not curly/smart quotes ("). If you’re typing formulas by copying from a document that auto-corrects quotes, the formula will fail with a parsing error. Always type formula text directly in the formula bar.
Not using IFERROR around formulas that might fail on empty rows. A formula that works on populated rows will usually return an error on blank rows, which makes a sheet look broken. =IFERROR(your_formula, "") wraps any formula and returns empty instead of an error when something goes wrong.
Building enormous nested formulas when multiple steps would be clearer. I spent a while trying to cram everything into single cells as a badge of honour. Then I started using helper columns — intermediate calculation steps in columns I’d hide later — and everything became easier to maintain and debug.
The honest truth about Google Sheets is that most people reach for a manual process first because they don’t know a formula exists for it. The formulas above cover probably 80% of the “I’ve been doing this by hand” situations I encounter regularly.
Every time I’ve taken twenty minutes to learn a new formula, it’s paid back in hours within a month. The next time you find yourself doing something repetitive in a spreadsheet, it’s worth pausing and asking whether Sheets can do it for you. Chances are better than you’d think that it can.
Any Question? Contact Us