If you manage inventory in Google Sheets, you end up repeating tasks like "copy this formula down every row," "pull the product name in from the master," and "reference data from another sheet" almost every day. At first you can muddle through by hand, but once your row count climbs past a few hundred, that work alone starts eating into your day.
In this article we narrow it down to five functions that, in real inventory operations, will instantly make life easier. All of them are standard functions documented in the official help — no Apps Script, no add-ons. You can use them as-is starting tomorrow. We'll focus on the situations where each one really shines, so see if you can apply them to your own sheet.
1. Process an entire column at once with ARRAYFORMULA
First up is ARRAYFORMULA, arguably the signature feature of Google Sheets. Excel doesn't have it (strictly speaking, it has a different mechanism called SPILL), and you could argue it's one of the biggest reasons to use Sheets in the first place. Normally you copy a formula down cell by cell — with ARRAYFORMULA, you can write a single line that says "apply the same logic to this whole column."
Say you want to derive an inventory status in column C from the SKU in column A and the received quantity in column B. Instead of putting an IF formula on row 2 and copying down, you put one formula wrapped in ARRAYFORMULA in cell C2 — and as new rows are added to column A, the calculation spreads automatically. Never having to remember to copy a formula when you add a new product is a real peace-of-mind win.
The practical use cases in inventory operations are wide: "compute inventory value as unit price times quantity," "raise a flag when stock drops below the safety level," "show per-location totals off to the side," and so on. Because the logic lives in a single formula, when you want to change it there's only one place to edit. Calculation logic that tends to get personalized becomes much easier to manage.
2. Pull from the master with VLOOKUP / XLOOKUP
On an inventory sheet, needs like "show the product name based on the SKU" or "look up the unit price from the product code" come up constantly. This is exactly what LOOKUP-family functions are for. VLOOKUP has been around forever, but Google Sheets also has the newer XLOOKUP, which is more intuitive and flexible.
XLOOKUP lets you specify the search key, the search range, and the return range separately, so it doesn't have VLOOKUP's restriction of "left-to-right only." Even if your master sheet has the product name on the left and the SKU on the right, XLOOKUP handles it without issue. On inventory sheets, where the column order in the master often can't be locked down due to how the data is organized, getting comfortable with XLOOKUP unlocks a big jump in flexibility.
Always specify what happens when the key isn't found
An unexpectedly common source of incidents with LOOKUP-family functions is the behavior when the key isn't found. The default error display ruins the look of the whole sheet, and if errors slip in before the data is handed off to a sync tool like Sync Master, you can end up with unexpected columns written out as blanks. XLOOKUP lets you specify a fallback value as the third argument, so set something clear like an empty string, 0, or 'Not registered.'
3. Connect other sheets with IMPORTRANGE / filter with QUERY
As your inventory sheet grows, you'll want to split files apart — "master sheet," "sales sheet," "purchase plan sheet." But once you've split them, a new need emerges: aggregating across sheets. This is where IMPORTRANGE comes in.
IMPORTRANGE is a function that pulls a specified range from another spreadsheet. Once you've authorized access between the two sheets, the latest values are reflected without ever opening the other sheet. You can completely separate the sheet the buyer touches from the sheet the shipping team touches, while consolidating both data sets into your own inventory master.
And rather than pasting the data IMPORTRANGE brings in as-is, extracting only the parts you need with QUERY raises the practical value enormously. QUERY uses a SQL-like syntax, so you can write filters like "only rows where stock is below 10" or "only rows for a specific location." Combine IMPORTRANGE and QUERY and you can pretty much build your own dynamic dashboard spanning multiple sheets.
- The master sheet is managed by the purchasing team
- The sales sheet is managed by the e-commerce team
- Your own inventory sheet references both with IMPORTRANGE
- QUERY extracts only the rows that need reordering and turns it into a dashboard
4. Catch duplicates with UNIQUE and COUNTIF
Last, and unglamorous but extremely effective at preventing operational incidents, is the UNIQUE-and-COUNTIF combo. The scariest situation on an inventory sheet is "the same SKU appears in two rows." Leave that unchecked and when the data is handed to a sync tool like Sync Master, the value will fluctuate depending on whether the last write or first write wins.
UNIQUE returns a list of values from a specified range with duplicates removed. Just put "=UNIQUE(A:A)" off to the side of the sheet and you have an always-current list of SKUs. COUNTIF, meanwhile, counts how many times a particular value appears. Put "=COUNTIF(A:A, A2)" next to the SKU column and you'll know how many times that row's SKU appears in the sheet — anything 2 or higher gets noticed immediately.
Combine it with conditional formatting and you can have duplicate rows highlighted automatically. On "sheets edited by more than one person" and "sheets where data is frequently copy-pasted in from elsewhere," this is exactly the kind of safeguard you want in place.
Wrap-up: functions exist to reduce operational mistakes
The reason to learn Google Sheets functions isn't to write cool-looking formulas. It's to avoid repeating the same task every day and to prevent human error before it happens — in short, an investment in making operations lighter. All five we covered today (ARRAYFORMULA, XLOOKUP, IMPORTRANGE, QUERY, UNIQUE/COUNTIF) share the trait of "write it once and it keeps working on its own."
When you're using a sync tool like Sync Master, keeping the sheet-side data clean is paramount. Once the power of these functions lets your sheet effectively run itself, the quality of data handed to the sync tool naturally rises too. Start with just one — pick one and add it to your own inventory sheet.