A product catalog template to organize our SKUs

By Ranjit Bhinge, founder of Blur Studio · Updated · 7 min read

Short answer: A product catalog template is a spreadsheet with one row per item and one column for each fact you need to sell it. For a distributor that means a unique item code (SKU, short for stock keeping unit), name, category, specs, unit of measure, pack size, price tiers, supplier, lead time and status, not just the basic fields a retail shop uses. Copy the column list below into Excel or Google Sheets, add dropdown lists so people cannot type free-form values, and move to an online catalog when customers need to search it or several people edit it at once.

Ranjit Bhinge

You need a product catalog sheet that actually fits a distributor, with specs, units and price tiers, not a retail inventory list.

I'm Ranjit, and I run Blur Studio: 15+ years building software, 120 apps taken live, and clients who have gone on to raise $10M+. I build AI systems and processes that help businesses do a lot more with a lot less time. I also work differently from most of what else is out there, more on that further down this page. Say hello.

What columns go in a product catalog template

Retail templates stop at name, price and stock. A distributor sells parts that must be found by size, grade or rating, and priced by quantity or customer. Here is the column list I would start with. Copy it into row 1 of a new sheet. Related reading: a spec sheet template.

ColumnWhat goes in itRule
Item IDYour own unique codeNever reuse. Never change.
Supplier part numberThe maker's codeKeep exactly as printed
NameShort plain nameSame pattern every time
CategoryFrom a fixed listDropdown only
Size, material, ratingOne column per specOne unit per column
Unit of measure (UOM)Each, box, foot, caseDropdown only
Pack sizeHow many units in a box or caseNumber only
Minimum order quantity (MOQ)Smallest order you acceptNumber only
CostWhat you payHide from customer copies
Price tier 1, 2, 3Price at quantity breaksAdd a column for each break
Lead time (days)Days to get it if out of stockNumber only
SupplierFrom a listDropdown only
Spec sheet linkLink to the PDFOne link per item
StatusActive, discontinued, replacedDropdown only
Replaced byItem ID of the new partOnly if replaced
Last checkedDate and initialsUpdate on every edit

Do not add a column for everything you can think of. Eight to twelve fields that you really use are better than thirty that go stale.

How to build it in Excel or Google Sheets, step by step

  1. Set up the header row. Paste the column names from the table. Freeze the top row so it stays in view. Turn on filters.
  2. Add dropdown lists. Make a second tab called Lists with your categories, units and statuses. In Excel, use data validation to restrict what can be typed into a cell, including a dropdown list, as Microsoft's help page describes. In Google Sheets, use Data, Data validation, or the in-cell dropdown option. This one step removes most spelling mess.
  3. Lock the rules for numbers. Set pack size, MOQ and lead time to accept whole numbers only. Reject, do not just warn.
  4. Catch duplicates. Add a check column that counts how many times each Item ID appears. Anything above 1 turns red with conditional formatting.
  5. Add a status column and a change log tab. Record date, person, item, what changed. It is dull and it saves you the first time a price is wrong.
  6. Name an owner for each column. Sales owns descriptions, purchasing owns cost and lead time. One owner per column.
  7. Keep one master file. Not five copies by email. If you must share, share a view-only copy.

Formulas and settings that keep the sheet clean

Three small settings do most of the work. The first is the duplicate check from step 4. In Excel, the COUNTIF function counts how many cells match a value, and the formula =COUNTIF(A2:A2000,A2) returns 2 or more when an Item ID repeats. In Google Sheets, choose Format, Conditional formatting, then "Custom formula is", and enter =COUNTIF($A$1:$A$2000,A1)>1. Google's help page uses this exact pattern to highlight duplicates, and the dollar signs lock the range while the row reference moves down. Two cautions from Microsoft's COUNTIF page: matching ignores upper and lower case, so "hx-0412" and "HX-0412" count as the same, and stray spaces at the start or end of a cell can throw the count off. Excel's TRIM and CLEAN functions remove them, so clean the ID column once before you trust the check.

The second is the filter. In Excel, Data, Filter puts an arrow on each header. Microsoft warns against mixing text and numbers in one column, because Excel then shows only the filter type that matches most of the cells. A pack size typed as "50 boxes" in one row and 50 in the next is the usual cause, which is another reason to keep pack size as a number only. In Google Sheets, a filter view lets several people sort and filter the same sheet at once without changing what the others see, and Google notes that the Create a filter option can be unavailable when the range has merged cells, so avoid merged cells in a catalog.

The third is the Excel table. Select your range and turn it into a table, and each header gets filter and sort controls, a formula typed in one cell fills the whole column, and Microsoft lists built-in data validation, such as allowing only numbers in a column, among its features. For a catalog edited by one or two people, a table is the quickest way to get the rules above without code.

A filled-in example (made-up figures)

Item IDNameSizeMaterialUOMPackMOQTier 1Tier 2LeadStatus
HX-0412Hex bolt1/2 in x 3 inZinc steelBox501$18.40$16.90 (10+)5Active
HX-0413Hex bolt1/2 in x 4 inZinc steelBox501$20.10$18.60 (10+)5Active
CB-2201Copper tube3/4 inCopperFoot120$4.15$3.80 (100+)12Active

Notice that the two bolts differ only in length. They are a "looks the same but is not" pair. Give each its own row and ID, and group them by a shared name, instead of one row that says "hex bolt, various". Customer-specific prices do not belong in this sheet. Keep those in your quote workflow or your accounting system, because a column per customer becomes unmanageable fast.

A second example: replaced and discontinued items

The first table shows live items. A catalog also has to handle parts that change. These rows use made-up figures.

Item IDNameSizeUOMPackTier 1StatusReplaced by
CB-2200Copper tube3/4 inFoot1$3.95ReplacedCB-2201
HX-0399Hex bolt1/2 in x 2 inBox50$15.20Discontinued
HX-0412Hex bolt1/2 in x 3 inBox50$18.40Active

Never delete a row for a part you have sold. Keep it, set the status, and fill in "Replaced by" so a sales rep who finds the old code is pointed to the new one. Filter the Status column to Active for customer copies. A quick price check shows how the tiers work: an order of 60 boxes of HX-0412 qualifies for a 10+ box price of $16.90, so the line is 60 x $16.90 = $1,014.00, where the first-tier price would have given $1,104.00. A tier column is only useful if everyone reads the quantity break the same way, so state whether "10+" includes 10 in the column heading.

What is a product information management template?

Product information management (PIM) is the habit, and the software, of keeping product data in one controlled place. A PIM template is simply a catalog sheet with extra controls: a status workflow (draft, approved, published), a completeness check that shows which rows are missing fields, a column for each sales channel, and an owner per row. You can add a completeness check in a sheet with a formula that counts blank cells in the required columns. It is not as good as PIM software, but it teaches you what you would need from one. If you later compare tools, my guides to Akeneo alternatives and Salsify alternatives cover prices and fit.

The limits of a catalog spreadsheet

  • Customers cannot search it. A sheet is a back-office file, not a catalog a buyer can filter by size and see their price.
  • Two people editing break it. Version clashes appear fast, especially with copies on email.
  • No link to stock or prices. It goes stale the day after you save it, unless someone updates it from the ERP (the system that holds your orders and stock).
  • Weak rules. Dropdowns help, but a sheet will not stop someone pasting over a whole column.
  • Size. Very large sheets with many columns get slow and hard to check.

When to move the catalog online

Move when customers need to search or order from it, when three or more people edit it, or when you must keep several sales channels in sync. The step in between is not always a full PIM. Often it is a simple web app on the same columns, which I describe in turning a spreadsheet into a web app. If the messy part is getting supplier data into the sheet, see AI for product catalogs. And once the catalog is online, a sales desk can answer questions faster, as in answering product catalog questions by phone or chat.

How I'd approach this

Build the sheet with only the columns you sell from. Fill 50 items by hand, the awkward ones first, and see where the template breaks. Add dropdowns before you add rows, not after. Name one owner and a weekly check. When customers start asking for something the sheet cannot do, such as search by size or a live price, that is your signal to move it online, and the columns you already cleaned become the structure of that catalog.

Questions people ask

What should a product catalog template include?

A unique item ID, supplier part number, name, category, specs, unit of measure, pack size, minimum order, price tiers, lead time, supplier, spec sheet link and status. Distributors need the unit, pack and tier columns that retail templates skip.

Is there a free product catalog template for Excel?

Yes, you can build one in an hour from the column list above. Check that any template you download has unit, pack and price tier columns before you adopt it.

How do I manage a product catalog in Excel?

Use one row per item, dropdown lists for categories and units, filters, a duplicate check and a change log. Give one person ownership of each column.

What is a product information management template?

It is a catalog sheet with extra controls, such as a status workflow, a completeness check, a channel column and an owner for each row. It keeps product data consistent before it is published.

How do I find duplicate item IDs in a catalog sheet?

In Google Sheets, apply conditional formatting with the custom formula =COUNTIF($A$1:$A$2000,A1)>1 so repeats are highlighted. In Excel, put =COUNTIF(A2:A2000,A2) in a check column and flag anything above 1. Matching ignores case, and extra spaces can hide a duplicate, so trim the column first.

When should I move my catalog from a spreadsheet to an online system?

Move when customers need to search or order from it, when several people edit it, or when you must keep several sales channels in sync. See turning a spreadsheet into a web app for the steps.

More in this topic: B2B: product catalogs and PIM (8 guides) →

Sources

Ready for the next step?

A 30 minute call. If you do not need me, I will tell you.

Ranjit Bhinge You talk to me, Ranjit. I reply within a few hours.

Not sure what you need? Tell me what is going on. I will point you the right way, even if it is not me.

Blur Studio · 30 minute call

Book a 30 minute call

You talk to me, Ranjit. If you do not need me, I will tell you.

  • We talk about your business and what is going on.
  • You leave with a clear next step, even if it is not me.
  • I reply to every message within a few hours.
Loading the calendar…