Home » Articles

10 Google Sheets Formulas That Can Replace Hours of Manual Spreadsheet Work

Last updated : October 16, 2026

Google Sheets is easy to use when you're entering a few numbers or maintaining a short list. The trouble begins when that list grows into hundreds of rows and someone has to check it, calculate totals, find missing information, or prepare the same report every Friday.

Consider a small online business processing orders throughout the week. Its spreadsheet records customer names, products, order values, payment statuses, and sales regions. Without formulas, an employee might manually filter the table to count unpaid orders, add up revenue for a particular region, or copy selected records into another worksheet.

All of those tasks can be handled directly in Google Sheets. Once the formulas are in place, most results update as the underlying data changes.

This tutorial covers ten functions that are particularly useful for sales records, inventory lists, administrative reports, and everyday business data. Rather than using a different example for each function, we'll work with the same sample sales dataset throughout.

Prepare the Sample Dataset

Open Google Sheets and create a blank spreadsheet. Rename the first worksheet Orders, then enter the following information in cells A1.

Order ID	Customer	Region	Product	Amount	Status
1001	Alice	North	Laptop	850	Paid
1002	Bob	South	Mouse	25	Pending
1003	Carol	North	Keyboard	75	Paid
1004	David	West	Monitor	230	Paid
1005	Emma	South	Laptop	900	Pending
1006	Frank	North	Mouse	30	Paid
1007	Grace	West	Keyboard	80	Paid
1008	Henry	South	Monitor	250	Pending

The columns are:

• A: Order ID
  
• B: Customer
  
• C: Region
  
• D: Product
  
• E: Amount
  
• F: Status
  

Select row 1 and choose View > Freeze > 1 row to keep the column headings visible.

The examples below assume that the worksheet is named Orders and that the records occupy rows 2 through 9. Enter the formulas in empty cells outside the dataset or create another worksheet called Dashboard.

If you're unfamiliar with a function's arguments, the Google Sheets formula examples available through SheetSteps provide explanations and worked examples for common spreadsheet operations, including conditional sums, lookups, and array formulas.

Practice: Google Sheets MCQs

1. SUMIFS: Calculate Sales That Match Multiple Conditions

Suppose you want to know how much revenue came from paid orders in the North region.

You could filter the table manually, identify the matching transactions, and add their values. But SUMIFS performs the calculation directly.

Enter:

=SUMIFS(Orders!E2:E9,Orders!C2:C9,"North",Orders!F2:F9,"Paid")

Result: 955

The formula adds values from column E only when the corresponding region is North and the payment status is Paid.

In the sample dataset, Alice's $850 order, Carol's $75 order, and Frank's $30 order meet both conditions.

The first argument identifies the values to add. Each subsequent pair specifies a criteria range and the condition that must be satisfied.

This function is useful for calculating regional revenue, expenses by category, sales within a date range, or transactions handled by a particular employee.

You can also replace fixed conditions with cell references. For example, if H2 contains a region and H3 contains a payment status:

=SUMIFS(E2:E9,C2:C9,H2,F2:F9,H3)

Changing either input immediately recalculates the result.

2. COUNTIF: Count Records Without Manual Filtering

A sales manager may need to know how many orders are still awaiting payment.

Instead of scanning column F, use COUNTIF.

=COUNTIF(Orders!F2:F9,"Pending")

Result: 3

Google Sheets examines the specified range and counts cells containing Pending.

The formula doesn't modify the original records, and it continues to work if you change an order's status within the referenced range.

Other practical uses include counting completed assignments, customers from a particular region, products belonging to a category, or support tickets marked unresolved.

For multiple conditions, use COUNTIFS instead. For example:

=COUNTIFS(Orders!C2:C9,"South",Orders!F2:F9,"Pending")

This returns 3, because all three South-region orders in the sample dataset are pending.

3. IF: Automatically Classify Transactions

Imagine that orders worth at least $200 need additional review because they may require special shipping arrangements.

Rather than identifying these transactions manually, add a heading called Order Category in G1.

In G2, enter:

=IF(E2>=200,"Large Order","Standard Order")

Copy the formula down to G9.

Orders worth $200 or more will display Large Order. Smaller transactions will display Standard Order.

For Alice's $850 order, the result is:

Large Order

The structure is straightforward:

=IF(condition,value_if_true,value_if_false)

Google Sheets evaluates the condition and returns one of two specified results.

The IF function is useful for flagging overdue payments, classifying expenses, identifying low inventory, and displaying whether targets have been achieved.

It can also be combined with other functions when more complicated logic is required.

4. FILTER: Display Only the Records You Need

Suppose you want a separate list containing only pending orders.

Normally, you might apply a filter to the original table and then copy the visible records into another worksheet.

The FILTER function creates a separate result without requiring that manual process.

Enter the following in an empty area:

=FILTER(Orders!A2:F9,Orders!F2:F9="Pending")

The formula returns three records:

Order ID	Customer	Amount	Status
1002	Bob	25	Pending
1005	Emma	900	Pending
1008	Henry	250	Pending

The actual output includes all six original columns, not just the four shown in this abbreviated table.

You can apply more than one condition:

=FILTER(Orders!A2:F9,Orders!C2:C9="South",Orders!F2:F9="Pending")

This returns pending orders from the South region.

Make sure the cells into which the results will expand are empty. Otherwise, Google Sheets may return an error because the array cannot expand.

5. XLOOKUP: Find Customer Details Using an Order ID

Looking up information becomes frustrating when a spreadsheet contains hundreds of records.

Suppose a customer contacts your business about order 1004. You need to identify the customer's name without searching through the entire table.

Use:

=XLOOKUP(1004,Orders!A2:A9,Orders!B2:B9,"Not Found")

Result: David

The formula searches column A for order 1004 and returns the corresponding value from column B.

The fourth argument specifies what Google Sheets should display if the order ID cannot be found.

To retrieve the order amount instead, change the return range:

=XLOOKUP(1004,Orders!A2:A9,Orders!E2:E9,"Not Found")

Result: 230

Unlike traditional VLOOKUP, XLOOKUP does not require the return column to appear to the right of the lookup column.

This makes it useful when working with customer databases, employee records, product catalogs, and supplier lists.

6. QUERY: Generate a Sales Summary With One Formula

The QUERY function is one of Google Sheets' more powerful reporting tools.

It allows you to filter, group, and summarize data using a query expression that resembles a simplified database query.

Suppose you want a summary of paid revenue by region.

Enter:

=QUERY(Orders!A1:F9,"SELECT C, SUM(E) WHERE F = 'Paid' GROUP BY C LABEL C 'Region', SUM(E) 'Revenue'",1)

The result is:

Region	Revenue
North	955
West	310

The query selects regions from column C, adds order amounts from column E, and includes only records where column F contains Paid.

It then groups the results by region.

The final argument, 1, tells Google Sheets that the source range includes one header row.

QUERY is useful for creating recurring reports without manually building summary tables. It can group sales by region, summarize expenses by category, or select records that meet particular conditions.

It takes more practice than functions such as SUMIFS, but it can replace several intermediate calculations once you understand its syntax.

7. UNIQUE: Generate Lists Without Duplicates

Suppose you need a list of every region represented in the order data.

You could copy column C into another location and use the Remove duplicates option. But the result would need to be recreated whenever new regions appeared.

The UNIQUE function does this automatically.

=UNIQUE(Orders!C2:C9)

The result contains:

North

South

West

Each region appears only once.

This is useful when building dashboard filters, preparing product-category lists, or identifying distinct departments, suppliers, and customer segments.

To sort the results alphabetically, combine UNIQUE with SORT:

=SORT(UNIQUE(Orders!C2:C9))

For a growing dataset, you can reference C2:C instead of using a fixed ending row, provided you account for blank cells if necessary.

8. IFERROR: Keep Reports Free of Unhelpful Error Messages

Spreadsheet formulas can produce errors for several reasons. A lookup value may be missing, a calculation may involve invalid input, or a formula may attempt to divide by zero.

These errors can make a report difficult to read.

Suppose you want to calculate the average order value but the dataset might temporarily contain no numeric amounts.

Enter:

=IFERROR(AVERAGE(Orders!E2:E9),"No Data")

For the current dataset, the formula returns:

305

If the calculation produces an error, Google Sheets displays No Data instead of an error code.

IFERROR is particularly useful in dashboards where an error message could confuse someone reviewing the results.

However, it should not be used to hide every spreadsheet problem. An unexpected error may indicate incorrect data or a broken formula that needs investigation.

Use it when you understand which errors might occur and have chosen an appropriate fallback result.

9. ARRAYFORMULA: Apply Calculations to Multiple Rows

Copying formulas down a column works, but it becomes inconvenient when new records are added frequently.

ARRAYFORMULA allows one formula to calculate results across multiple rows.

Suppose you want to calculate a 10% commission on every order amount.

Create a heading called Commission in H1, then enter this formula in H2:

=ARRAYFORMULA(IF(E2:E9="","",E2:E9*10%))

The formula calculates commission values for all eight orders.

For Alice's $850 order, the result is $85. For Bob's $25 order, it is $2.50.

The IF condition prevents empty input cells from displaying unnecessary zeros.

To accommodate new records without extending the range manually, use:

=ARRAYFORMULA(IF(E2:E="","",E2:E*10%))

This version references the column from row 2 onward.

Before using an open-ended array, make sure the destination column doesn't contain unrelated information below the formula.

ARRAYFORMULA works particularly well for routine calculations that must be applied consistently as new records are added.

10. SUMPRODUCT: Calculate Totals Using Several Columns

SUMPRODUCT multiplies corresponding values in selected ranges and adds the results.

It's useful for calculating inventory value, commissions, weighted totals, and sales where quantities and unit prices are stored separately.

To demonstrate it, create a second small table in cells J1:L4:

Product	Quantity	Unit Price
Mouse	12	25
Keyboard	8	75
Monitor	5	230

To calculate the combined inventory value, enter:

=SUMPRODUCT(K2:K4,L2:L4)

Result: 2050

The formula calculates:

(12 × 25) + (8 × 75) + (5 × 230)

= 2050

Without SUMPRODUCT, you would typically create another column, multiply the quantity and unit price on each row, and then add the individual results.

The function performs both operations directly.

This is particularly useful for purchasing teams, warehouse managers, and businesses that regularly calculate totals based on quantities and prices.

Combining Formulas Into a Simple Sales Dashboard

Individual formulas become more valuable when they work together.

Create a new worksheet named Dashboard and add four summary fields.

Metric	Formula	Result
Total Order Value	=SUM(Orders!E2:E9)	2440
Paid Revenue	=SUMIF(Orders!F2:F9,"Paid",Orders!E2:E9)	1265
Pending Orders	=COUNTIF(Orders!F2:F9,"Pending")	3
Average Order Value	=AVERAGE(Orders!E2:E9)	305

These formulas create a basic report that recalculates whenever the original values change within the referenced range.

You can add the regional QUERY summary alongside these metrics and insert a chart to visualize sales by region.

For readers who want to expand this into a more complete workflow, step-by-step Google Sheets tutorials cover tasks involving spreadsheet organization, data cleaning, charts, and reporting. These can be useful when moving beyond individual formulas to repeatable processes.

When building a dashboard, avoid including measurements simply because they're easy to calculate. Focus on the figures someone actually needs to make a decision.

A small retailer may care most about paid revenue and unpaid orders. A warehouse manager may need inventory value and reorder quantities. The formulas can be adapted to either situation.

Common Mistakes to Avoid

Even simple spreadsheet formulas can produce incorrect results when their input ranges aren't properly maintained.

A common mistake is using ranges of different sizes. In SUMIFS, for example, the sum range and criteria ranges must have compatible dimensions.

Another problem involves numeric values stored as text. An amount that looks like 250 may actually be stored as a text string, preventing some calculations from behaving as expected.

Extra spaces in category names can also affect results. A status entered as Paid with a trailing space may not match the intended text criteria. Functions such as TRIM can help clean inconsistent text.

Finally, remember that formulas using fixed ranges, such as E2:E9, will not automatically include data added in row 10. Consider open-ended ranges or an appropriate expanding data structure when your spreadsheet will grow over time.

If you need to verify syntax or check a function's supported arguments, consult the official Google Sheets function list. Google's documentation provides the authoritative reference for function behavior and availability.

Conclusion

Google Sheets becomes considerably more useful once you stop treating it as a place to enter information manually and start using formulas to process that information.

SUMIFS and COUNTIF handle conditional calculations, while IF helps classify records. FILTER, XLOOKUP, and QUERY make it easier to retrieve and summarize information. UNIQUE, IFERROR, ARRAYFORMULA, and SUMPRODUCT simplify tasks that might otherwise require several additional steps.

You don't need to memorize every function immediately. Begin with the repetitive task you perform most often and find the formula that addresses it.

A few well-chosen formulas can turn a spreadsheet that requires constant manual attention into a report that largely maintains itself. The time savings become particularly noticeable when the same calculations are needed every day or every week.