How to Create a Catalogue in Excel (with Easy Steps)

Step 1: Giving a Suitable Title to the Catalogue

Open an Excel file and give a suitable title to the workbook.
On the 2nd row of the workbook, click on some cells (I took B2: G2), merge them, and give them a suitable title. i.e., “Product Catalogue of Evaly”. (see the figure below)

Modify the formatting of the title so that it looks better. Here, I have changed the cell color, made the font larger, and set the middle alignment.

Step 2: Entering a Column Header of the Catalogue

We will insert the column headers appropriate to our example company’s requirements. I have chosen an e-commerce company that sells a wide range of products, from cosmetics and beauty products to lifestyle, electronics, and health gadgets. The fields that I need to insert to specify the products properly are taken, as shown in the picture below.

Here, you must ensure that you have included all the necessary fields to identify and arrange all your business’s products.

Step 3: Entering the Product Information

We must enter the required info on all the products related to the column headers.

If you also want to add a product picture, you need to make the row height larger (I have taken 50) and insert a picture by going to the Insert tab and then Illustrations >> Pictures >> This Device.

Select the image File and click on Insert.

After inserting the image, you have to adjust the size of the picture to fit into the cell under the picture column.

Step 4: Creating a Table

Select the whole dataset and press Ctrl+L.
A dialogue box named “Create Table ” will appear.
Click on My table has headers. Select OK.

An Excel table will be created with the Sort & Filter feature.

You can use the filter feature to show only one product category. Here, I am only showing Cosmetic items.
Click on the drop-down icon under the Category and select Cosmetic.
Click OK.

You will only see the Cosmetic category products.

Things to Remember

If necessary, add extra columns (Brand name, tag, etc.) to make it more appropriate for your company.

Download the Practice Workbook

Download this workbook to practice.

Creating Catalogue.xlsx

<< Go Back To Catalogue in Excel | Hyperlink in Excel | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Me.Range("G5:G11")) Is Nothing Then Call InsertAndFitImage End If End Sub Sub InsertAndFitImage() Dim cellAddresses As Variant Dim cellAddress As Variant Dim imgFile As Variant Dim pic As Picture Dim i As Long Dim cell As Range Dim mergedWidth As Double Dim mergedHeight As Double Dim ThisPath As String cellAddresses = Array("G5:G11") With Application.FileDialog(msoFileDialogFilePicker) .Title = "Choose Images" If .Show = -1 Then For i = 1 To .SelectedItems.Count cellAddress = cellAddresses(i - 1) Set cell = ThisWorkbook.ActiveSheet.Range(cellAddress) ThisPath = .SelectedItems(1) cell.InsertPictureInCell (ThisPath) Next i End If End With End Sub

2 Comments

Alexander Wilcox Mar 18, 2024 at 7:09 AM

How can I prevent pictures from bunching up at the top of the column when I sort by category. All the pictures from the unselected category got to the top cell in the row.
- Reply
  Lutfor Rahman Shimanto Mar 18, 2024 at 12:32 PM
  Hello ALEXANDER WILCOX
  
  Thanks for visiting our blog and sharing your problem. When sorting categories, you get all the pictures from the unselected category in the top cell of the row.
  
  To overcome your situation, you can insert images using the Place in Cell feature, like the following GIF.
  
  I am delighted to inform you that I have developed an automated solution for you using the Excel VBA Event procedure and Sub-procedure. Please, follow these steps:
  Right-click on the sheet name tab.
  
  Click on View Code.
  
  Insert the following code in the sheet module and save it:
  Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Me.Range("G5:G11")) Is Nothing Then Call InsertAndFitImage End If End Sub Sub InsertAndFitImage() Dim cellAddresses As Variant Dim cellAddress As Variant Dim imgFile As Variant Dim pic As Picture Dim i As Long Dim cell As Range Dim mergedWidth As Double Dim mergedHeight As Double Dim ThisPath As String cellAddresses = Array("G5:G11") With Application.FileDialog(msoFileDialogFilePicker) .Title = "Choose Images" If .Show = -1 Then For i = 1 To .SelectedItems.Count cellAddress = cellAddresses(i - 1) Set cell = ThisWorkbook.ActiveSheet.Range(cellAddress) ThisPath = .SelectedItems(1) cell.InsertPictureInCell (ThisPath) Next i End If End With End Sub
  
  Return to the sheet and insert the intended images in the selected cell, like the following GIF.
  As a result, you will no longer see pictures from the unselected categories in the top row cell when filtering or sorting categories, like the following GIF.
  
  I am attaching the solution workbook for better understanding; good luck.
  
  DOWNLOAD SOLUTION WORKBOOK
  
  Regards
  Lutfor Rahman Shimanto
  Excel & VBA Developer
  ExcelDemy