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 spreadsheet!
I enlarged it to acomodate up to 120 products, but I have a problem in sheet C, the # only goes up to 81, if I try to type the following numbers, it makes strange things.
Any idea what to do?
Thanks!
Thank you.
There should not be anything in the C sheet that would stop with 81. Strange things? Can you please provide more detail on what changes you have so far done and what exactly is missing?
Thanks. Best wishes.
Hi.
I need to change the currency of the products. I am able to do this on the input sheet, but the product catalog sheet still displays dollars. How do I change this?
If you click on the cell which has the $ formatting, and then change the Number format in the menu (or press ctrl+1 for the dialog box), does it not work?
Thanks & Best wishes
This is an outstanding template. Made completing a catalogue of several of our software products extremely easy, all-in-all taking less than 2 days, likely reduced the amount of time and work by a week or more.
The modifications we made:
Currently your template only allows for categories, of which each “product” is only allowed to have one of. This didn’t work for us, as each of our products is complex in features and attributes. Additionally, the “Price” information wasn’t applicable. Therefore, we converted the category system to a “tag” system.
We duplicated the category list in the Input_Data table to create a series of collections of sorted tags. The Product Category column for the products was then changed to contain a comma-delimited list of tags.
A VBA function was written to take that tag list, split it into individual tags, and then check those against the table of selected tags at the top of the C sheet. The function returned a number representing how many tags matched, and THAT was used to determine the rank of the product (the more tags that matched, the higher the rank).
Changes were extremely easy to implement considering what a large change in functionality was being undertaken, and was completed in a single day.
The only critique we had was the use of Formula Names (in the Name Manage), which convoluted some of the processes that were going on behind the scenes.
Otherwise, again, outstanding template!
Could you put a copy up of your edited vesion with tags or could you explain in step by step and detail how to do tag process.
Thank you for sharing
I will post the step by step instructions soon.
Thanks & Best wishes.
Hello,
Please, i did not get how to add the hypertext link to the “view” that leads to the product details
Customizing Product Detail Sheets
Thanks for using the template.
Catalog shows 8 products at a time. The hyperlinks work automatically as long as the product detail sheet name matches the Product names. There is a calculation in the hidden sheet ‘C’ (see column SHEET LINK) that does it.
I have sent this information to your email as well.
Best wishes.
And One more question , I have edit the data input of my product then change names of the sheets and they are not linking .. what could be the reason ??
I am not following your question. Please email me the file to indzara at gmail and I will be happy to help. Thanks. Best wishes.
Hi Indzara ,
I was wondering if there is a way of creating products within products ?? I will give you an example I have a product name LAW withing this product I have 2 more product 1. Personal Injury 2.Work Injury , and withing 2 and each of this products has another 2 product within in them . Please see visual :
LAW —- Work Injury — Accident while at work
|
Personal Injury — Accident outside home
|
Accident at Home
So basically what I need is to create product within products which will be responsive to Product categories, Is this something we can do ?
If I am understanding you correctly, this will be challenging to implement with just formulas. I am sorry. Thanks.
Thank you so much for this template! Now… how do I unprotect the sheet? I need to customize a few things and it wont let me. Thank you!!
You are welcome. The password is indzara. It is also provided in the HOME sheet. Thanks & Best wishes.
I had just found it, thank you very much (and I’m sorry I didn’t look better hehe). Just one last question… why aren’t the prices showing correctly on the catalog itself as it is on the Input Data sheet? i.e. 9.99 shows just as 10… :/
I’m trying to change the formula but I cant seem to get it right…
You are welcome.
Please click on the cells (with currency format) and press Ctrl+1. Now, change the format to include 2 decimals. It is set to 0 and that’s why 9.99 rounds to 10.
Best wishes.
I have added more than 30 products on the Input Data sheet, but they do not all appear on the Product Catalog sheet. How did I fix this?
I’ve wasted most of my day trying to figure out how to make the product catalog work for more than 30 products. The template is pretty much useless unless you have only 30 products.
Brief overview of steps to expand:
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.
This catalog is great! I’m trying to add more than 30 products (but no additional categories), and I followed the steps above. I extended the product table in Sheet C, and the formulas seem to be carrying over, but the additional products in the Input_Data sheet is not carrying over to the Sheet C… Can you help or direct me to the detailed steps to incorporate more products? Thank you!
I need to add more than 30 products and have more than 6 categories. How do I unprotect the document so that I can make these changes?
Nevermind. I found what I needed on the support page. Thanks!
Not a problem. I am glad that you found the article. Best wishes.
well done.
Thank you very much. Best wishes.