2024 Small Business Product Catalog in Excel – Free Template
Have you wondered if it would be handy to have a product catalog for your small business?
Did you know that we can create the entire online product catalog or online store experience within Excel? Without payment processing, of course 🙂 You could use this to share your product info with your prospects and customers in an interactive tool.
This is how the product catalog/store appears in Excel.
For more features try our premium template Small Business Digital Catalog
If this sounds interesting, please read further on the functionality and for how to use this (hint: it’s so easy. Just enter your data and you are done). You can download this Excel template for free using the link below.
Free Download
Note: If you have older versions of Excel other than Excel 365, please download the V1.3 of the template.
Benefits of Product Catalog in Excel
- Filter products by Categories
- Search products by keywords (on Product Name)
- Sort products by Price (Ascending or Descending)
- Scroll to see products in sets of 8
- Add thumbnail images to appear on the Catalog
- Add links to the website as needed on product detail sheets
- Catalog links to individual product detail sheets
- Format the catalog as needed with usual Excel formatting options
- Customize product detail pages as needed
How to create Product catalog for your business?
Overview of Steps
- Unhide hidden Input_Data sheet. Enter product categories and product data for your business.
- In Product_Catalog sheet, change logo and tagline text to suit your business.
- In each product’s individual product detail sheet, rename the sheet to match the product name
- Update product image and highlights for each product
- Change formatting – colors, fonts, etc. as needed
Detailed Steps
Please note that the V 2 of the Product catalog works only with the Excel 365 subscription.
Entering product data
Enter Product Categories you need in the Categories sheet. Enter as many categories as you need.
Unhide Input_Data sheet.
Select Input_Data Sheet.
Now, the sheet should be open and visible.
Enter Product Data in the Products table.
a. DO NOT enter anything in the THUMBNAIL IMAGE column as it is a calculated column based on the Image Link column.
b. To get the thumbnail (or the product image), enter the Image link which is the image embed link generated from OneDrive.
To get this, click on the image in your OneDrive folder and click on the three dots that appear on the top as shown:
c. This will open a small pop-up to the right where you can copy the embed link.
d. Once this link is pasted in the Image Link column, the IMAGE function in Excel fetches the corresponding image from the URL.
Please ensure that the image needs to be shared as anyone with the link can view.
e. Enter the product name, price, category, highlights and the product link of each of your product.
This ends our product data entry process for one product. Repeat for all products. After you enter all your products, you can hide this sheet. Now, we are ready to customize the catalog/store appearance.
Customize Catalog Appearance
First, in the Product_Catalog sheet, change logo by changing picture as explained above. Change the tagline text to suit your business.
If you want to change the colors, fonts or any formatting, please select cells and change formatting as usual in Excel.
Customizing Product Details Excel Sheet
You will notice that in the catalog, there are links to product detail sheets. Each product has its own sheet where we have its image and description.
- Rename the sheet to match your product name. If your product name is Cool Product, please rename the sheet to Cool Product.
- Replace image and description with your products’ info.
- I have added two links, one to the product page on the website and the other to go back to Catalog. Replace the web link with your product web page link. You can remove the web link if you don’t need or have.
- (Optional) You can change formatting (colors, fonts) as needed. There are no formulas in the product detail sheet.
Repeat this step for each of your products.
Tip: to insert a bullet as I did in the product highlights, use Alt+0149.
Once you have completed all your product detail sheets, you are done. You can go back to the Product_Catalog sheet and your catalog is ready. Start. Sort, Search, Filter, Scroll. 🙂
Print/Export
You can export to PDF or print as all sheets are set up as print friendly in this excel product database template.
Limits
- Designed by default for 30 products. Can be extended if needed.
- Names of Products should match the Product Detail Sheet Names. This allows hyperlinks to function properly.
Note:
If you are using images from Google Drive, follow the steps here to get the correct hyperlink to the products.
First, ensure the image in your Google Drive has viewer access as shown:
i. The initial part of your hyperlink will be: https://drive.google.com/uc?export=download&id=
ii. Copy the above part and to this we need to add the “id” of the image. This is the part after “/d” and before “/view?”. For example if the image has a URL of https://drive.google.com/file/d/1cHwmELRTFQBlOQntrBf8eCcxhdmgPjjn/view?usp=sharing
The part highlighted in bold is the ID for the image, copy that part alone.
iii. With this, the URL you need will be: https://drive.google.com/uc?export=download&id=1cHwmELRTFQBlOQntrBf8eCcxhdmgPjjn
As always, I look forward to your feedback to understand how this can be extended to suit more small business needs. If you have any feedback or suggestions, please leave them in the comments. Thanks.
165 Comments
Great product. Thank you. Question: Is there any way to hide the product tabs at the bottom of the spreadsheet so that only the PRODUCT CATALOG tab is showing? I tried this but the links to view the products will not work.
Hello
Thanks for using our template.
As you might have observed, if you hide the tabs, then the selected products will not be displayed.
However, you can hide the input data.
Best wishes
Ok thanks for the reply. One more quick question: Is there a video on how to add more product categories (need more than the default). 10 total categories.
Hello
Please un hide the “Input_Data” Sheet. Extend the formulas for the category and rename them.
After that on the “Product_Catalogue” sheet, copy and paste the formula for categories and change the row number.
Let us know if you are stuck somewhere.
Best wishes
I do not need the price field and would like to change it to a text field instead. How do i make this change so that the text shows up properly? Changing the cell format to text doesn’t seem to work.
Please do not delete the price column in Input_Data. It is needed for sorting the products in catalog. Please use the price to create your sorting order.
I have explained in my response to your other comment on how to add a new column.
Best wishes.
I really like the template you have created. It is going to be so useful to me. However….I have a problem. I have started putting my information in just as you have suggested and when I go back to the product page…there is nothing there. Can you help me solve this problem. I am not sure what I have done to create this problem. Thank you so much.
Even when I click on the categories.,.,.none of the pictures pop up on the product page.
Thank you. Please email me the Excel file (contact@indzara.com) and specify what changes you have made. This will help me find out what is the root cause for the issue.
Best wishes.
Thank you. Please email me the Excel file (contact@indzara.com) and specify what changes you have made. This will help me find out what is the root cause for the issue.
Best wishes.
Is it possible to change the PRICE cell to a cell containing just text? Instead of displaying the price, I’d like to show some text. How and what would I need to modify? If I change the format of the cell from currency to text, the filtering will not work.
As it is set up now, price is necessary for the catalog to work. We need to edit the formulas in the hidden sheet (for the ranking column) and remove price as a factor in calculations.
Best wishes.
Thanks for your response. Sorry for asking the same question twice. I didn’t see your answer until just now. I don’t mind leaving the price field. Is it possible to add another text field to the product catalog? I want to add one more field/cell on the product catalog page with a short text description. Is this possible and what steps are needed?
Step 1: Entering additional column data:
We have to extend the input data table in Input_Data sheet and add column. Click in cell E14 and enter the new column name. I will assume ‘New Column’ is the name of our new column. It can be anything you would prefer. Excel will automatically add a new column. Now, enter values in that column E for each table.
Step 2: Add this new column to Calculations:
We have to now add this new column to calculations. Enter ‘New Column’ in cell J39 in C sheet. Use the same name. Assuming the new column name is ‘New Column’, please type the formula in cell J40 in C sheet. =IFERROR(INDEX(T_PROD[New Column],[@[‘#]]),””)
Step 3: Unprotecting the catalog sheet: We have to edit the catalog sheet in order to bring the new column to display. How to unprotect https://indzara.com/faq-items/unprotectingprotectedsheets/ . Use indzara as password.
Step 4: You can type the following formula anywhere to see the new column value for first product on display. =IFERROR(INDEX(T_PROD_FILTERED[New Column],INDEX(C_PLIST,1)),””)
Replace the 1 in the formula with 2 for the second product. Make changes for all 8 products on display.
If you would like to replace the price column with this new column, then replace cell C13 with this formula. But do not delete the price column in Input_Data. It is needed for sorting the products in catalog. use the price to create your sorting order.
Step 5: Protect the sheet to prevent unintentional editing of formulas.
Please let me know if this worked for you.
Thanks. Best wishes.
Hello,
Is there a way to insert new buttons for ‘Select All’ and ‘Select None’ of the Categories? It’s easier to just hit ‘Select None’ and then pick just one category than uncheck 5 of them. Thanks!
It should be possible with a macro. In the current file, we don’t use any macros.
I understand your point though. I will try to address this in the next version.
Best wishes.
How do I have products have multiple categories such as product A is in category Letter, Food and Animals
Sorry, this template is not designed for that scenario. We would have to design differently some of the calculations to account for that.
Best wishes.
Thanks for doing this. Do you have any template for retail Point-of-sale (POS)?
You are welcome. Sorry, I don’t have any designed for high volume POS.
Best wishes.
I would like to add 3 more product categories. Where do I add these? Also, how do I make sure the new product categories show up on the product catalog page so they appear as items to be filtered with the check boxes?
Enter the additional products in hidden Input data sheet
Increase the row height to 120 to make sure that the image can be visible.
Name the thumbnail image cell as P_31 for 31st product, P_32 for 32nd product,…
Enter additional categories in hidden Input data sheet
In the hidden sheet C, insert row in row 10 to make room for more product categories.
Add checkboxes in Product Catalog sheet for the additional categories.
Link the checkboxes to the column B in hidden C sheet.
Extend the products calculation table in hidden C sheet
I believe the above should help expand the products and categories.
Best wishes.
How do I link the checkboxes to the column B in C sheet?
Please insert a new checkbox from the Developer tab. Or copy an existing checkbox and paste.
Then rightclick on the new checkbox and in the options, select the cell in hidden sheet to link.
Best wishes.
Specifically, I am having difficulties adding the new product categories to the Product Catalog tab. I have the original 6 product categories filterable with the checkbox. I added three more product categories on the hidden Input_Data sheet. How do I get these new product categories to show up on the Product Catalog sheet with the others and make sure they are filterable with the check box as well?
Please add the new category names in the catalog sheet manually. Then, add the checkbox and link as explained in my previous response.
Best wishes.
Thank you!
Actually I am on an Apple platform using numbers – so when it converts to Numbers it does not import the equations making the file static instead of dynamic. What to do?
Sorry, the templates are only compatible with Microsoft Excel.
Best wishes.
The document does not import the equations so its not active file
Sorry, the templates are only compatible with Microsoft Excel.
Best wishes.