10 Google Sheets Formulas Every Business Owner Should Know

Google Sheets is more than just a spreadsheet — it can be your most powerful business tool. After building 50+ dashboards and automation systems for clients, these are the formulas I use every single day.

1. VLOOKUP

Look up a value in one column and return a matching value from another column. Essential for connecting data across sheets.

=VLOOKUP(A2, Products!A:C, 3, FALSE)

Use case: Auto-fill product prices when a product name is selected in a dropdown.

2. IMPORTRANGE

Pull data from another Google Sheets file. This is how you connect multiple spreadsheets into one system.

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

Use case: Auto-import cost data from a supplier spreadsheet into your P&L report.

3. QUERY

Run SQL-like queries on your spreadsheet data. Filter, sort, group, and aggregate without any coding.

=QUERY(A1:E100, "SELECT A, SUM(E) WHERE C='Active' GROUP BY A")

Use case: Summarize total revenue by client, filtered to only active accounts.

4. ARRAYFORMULA

Apply a formula to an entire column at once instead of dragging it down row by row.

=ARRAYFORMULA(IF(A2:A<>"", B2:B * C2:C, ""))

Use case: Auto-calculate line totals for every row in an invoice sheet.

5. SUMPRODUCT

Multiply corresponding values in arrays and sum the results. Great for weighted calculations.

=SUMPRODUCT((A2:A100="Product X")*(C2:C100))

Use case: Calculate total revenue for a specific product across all transactions.

6. IF / IFS

Add conditional logic to your spreadsheets. IFS handles multiple conditions without nesting.

=IFS(A2>=90,"A+", A2>=80,"A", A2>=70,"B", TRUE,"C")

Use case: Auto-grade performance scores or categorize sales reps by tier.

7. INDEX + MATCH

A more flexible alternative to VLOOKUP that can look up values in any direction.

=INDEX(C2:C100, MATCH(F1, A2:A100, 0))

Use case: Find a client's email address by looking up their name, even if email is to the left.

8. UNIQUE + FILTER

UNIQUE extracts distinct values. FILTER returns rows matching a condition. Combine them for powerful queries.

=FILTER(A2:D100, C2:C100="Completed")

Use case: Show only completed orders in a separate view without affecting the original data.

9. REGEXMATCH / REGEXEXTRACT

Use regular expressions to validate or extract patterns from text data.

=REGEXMATCH(A2, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")

Use case: Validate email formats in a lead list before importing to your CRM.

10. SPARKLINE

Create mini inline charts inside a cell. Visualize trends without building a full chart.

=SPARKLINE(B2:M2, {"charttype","bar"; "color1","#6366f1"})

Use case: Show monthly revenue trends right next to each client's name in a dashboard.

Start Building Smarter Spreadsheets

These formulas are just the beginning. Combined with Google Apps Script automation, data validation, and conditional formatting, Google Sheets becomes a full business management system — no coding required.

Need a Custom Google Sheets Solution?

I build automated dashboards, reporting systems, and business tools in Google Sheets.

Get In Touch