I get asked why I still do so much of my catalog work in a spreadsheet when there are tools that promise to do it for me. The honest answer is that almost every product data problem I’ve ever fixed on a live store started in a spreadsheet. If I clean product data in Excel before it moves, I fix it once. If I let it through, I fix it on the product page, in the cart, in the order, and sometimes on the phone with a customer.
I’m a heavy Excel user, and I’ll admit that’s partly habit. But it’s habit built on years of managing catalogs at Universal Wholesale and Wild Bill’s Tobacco, including migrations where thousands of rows had to arrive in a new platform correctly the first time.
The spreadsheet is where the catalog actually lives
On paper the catalog lives in WooCommerce or Shopify. In practice, every change to it passes through a file first. Supplier price lists, new product sheets, stock updates, platform exports for a migration. Every one of those is a spreadsheet someone typed, copied or generated.
That’s where the extra space before a SKU sneaks in. Where a size column gets shifted one row down. Where a long product code turns into scientific notation because Excel thought it was a number. By the time those reach the store, they look like random bugs. In the spreadsheet, they’re visible if you look.
How do you clean product data in Excel before importing it?
This is what people search for, and the answer is less about clever formulas and more about a fixed routine. Mine looks like this.
- Import as text, not by double-clicking. I open CSVs through Data, From Text so I can set SKU, barcode and ID columns to text. That keeps leading zeros and stops long codes from turning into scientific notation.
- Trim everything. TRIM and CLEAN on every text column, pasted back as values. Invisible spaces are the most common reason a lookup says a SKU doesn’t exist when it plainly does.
- Check for duplicates. COUNTIF on the SKU column and filter for anything above one.
- Check required fields. Filter each required column for blanks: SKU, name, price, category, and image for anything going live.
- Standardize values. A pivot table on each attribute column shows every spelling in use. I map the variants to one value on a separate tab and apply it with XLOOKUP.
- Validate numbers. ISNUMBER on prices, weights and stock. Anything that comes back false gets fixed by hand.
- Compare against the store. XLOOKUP each row against a fresh export from the store to find what’s new, what’s changing and what’s missing.
None of that is advanced. The value is doing all of it, every time, in the same order.
Why I don’t hand it off
I could hand a file to a tool or a person and get it back cleaned. Sometimes that’s the right call. But there are three reasons I keep doing it myself on catalogs I’m responsible for.
The first is that I learn the catalog. Scrolling through a few thousand rows, I notice that one supplier always ships colors in capitals, or that a product line has a pack size hiding in the name instead of an attribute. That knowledge pays off on every later update.
The second is that judgment calls don’t automate well. Is “Navy” the same as “Dark Blue” for this store? Should a discontinued item be deleted or set to draft so its URL keeps working? A tool will make a choice. I want to make it on purpose.
The third is accountability. If a price goes live wrong, it’s my name on it. I want to have seen the row.
During platform migrations this mattered most. Moving a catalog meant old URLs needed redirects, collections had to be rebuilt, and every product had to land with the right price and image. The spreadsheet was the map for all of it. I couldn’t plan the redirects without knowing which rows were being merged, renamed or retired, and I only knew that because I’d been through the file myself.
A few habits that save me every time
- Keep the original file untouched and work on a copy. Name copies with the date.
- Never edit and import in the same sitting for a big change. Clean today, check tomorrow with fresh eyes.
- Add helper columns for checks instead of overwriting data. Delete them only when building the final import file.
- Build the import file last, with only the columns the import needs.
- Save the final file as CSV UTF-8 so accented characters and symbols survive.
Where tools help
I’m not against tools. I’ve built some myself for checking and mapping CSV files, because some checks are tedious to repeat by hand across many files. A tool is great at flagging every blank, every duplicate and every value that doesn’t match an allowed list.
But a tool tells you what’s different. It doesn’t tell you what’s right for this store. I still want a person, usually me, deciding what each flag means before the file goes anywhere.
Start with one file
If your store has data problems you keep fixing on the front end, go back one step. Take the next supplier file or product sheet before it’s imported and run the seven checks above on it. Write down what you find. Most people are surprised how much was hiding in a file they assumed was fine, and after a few rounds the routine takes less time than the fixes it prevents.