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

In this article, we will learn how to create a product catalogue in Excel. A Product Catalogue carries information on new or main products of a store. It is used for advertisement purposes. You can easily create a Product Catalogue in Excel. So, without any delay, let’s start the discussion.

How to Create Product Catalogue in Excel: with Easy Steps

In this section, we will make a Product Catalogue of a mobile shop. In this mobile shop, there are various types of mobile sets. So, for advertisement purposes, the seller wants to make a catalogue in Excel of his store. Let’s follow the steps below to create a Product Catalogue in Excel.

STEP 1: Make Dataset Ready

Firstly, we need to collect the information about the mobiles stored in the shop.
Then, we need to collect the images of the mobiles that are available.
After that, save the pictures in a specific folder.
After that, open the Excel Worksheet and make the following format for our catalogue.

After making the format, we need to make some changes to the format.
Then, select the 4 to 10 rows where the information will be kept.

After selection, right-click on the mouse.
Instantly, a menu bar will open up.
From this menu bar, select Row Height as we want to change the height of the selected rows.

Then, a dialog box named Row Height will come up.
In the Row height field, set the height as 50.

As a result, the height of the rows has increased.
Here, we have increased the height because the figure will need enough space for clearly watching.
So, our catalogue format is ready.

STEP 2: Open Visual Basic Editor

In the following step, we have to open the module where you can write the VBA code.
Therefore, click on the Developer tab and select Visual Basic from there.

Instantly, the Microsoft Visual Basic for Application window will open up.
From this window, select Insert >> Module to open the module.
In the module, we will write our VBA code.

STEP 3: Insert VBA Code

In the next step, write the following code in the Module:

Sub Create_Product_Catalogue()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Range("B4") = "Description"
Range("C4") = "Thumbnail"
Range("D4") = "Hyperlink"
Range("B4:D4").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.ColorIndex = xlAutomatic
End With
Set File_Selection_Catalogue = Application.FileDialog(msoFileDialogFolderPicker)
File_Selection_Catalogue.AllowMultiSelect = False
File_Selection_Catalogue.Title = "Select the Folder with the Images"
If File_Selection_Catalogue.Show <> -1 Then
    Exit Sub
End If
File_Path = File_Selection_Catalogue.SelectedItems(1) & "\"
File_Name = Dir(File_Path & "*.jpg*")
Count = 1
Do While File_Name <> ""
    Full_Path = File_Path + File_Name
    Range("B5").Cells(Count, 1) = Left(File_Name, Len(File_Name) - 4)
    Set photo = ActiveSheet.Pictures.Insert(Full_Path)
    With photo
        .Left = ActiveSheet.Range("B5").Cells(Count, 2).Left
        .Top = ActiveSheet.Range("B5").Cells(Count, 2).Top
        .Width = ActiveSheet.Range("B5").Cells(Count, 2).Width
        .Height = ActiveSheet.Range("B5").Cells(Count, 2).Height
        .Placement = 1
    End With
    Range("B5").Cells(Count, 3) = "=HYPERLINK(""" + Full_Path + """)"
    Count = Count + 1
    File_Name = Dir()
Loop
End Sub

VBA Code Explanation

Range("B4") = "Description"
Range("C4") = "Thumbnail"
Range("D4") = "Hyperlink"
Range("B4:D4").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.ColorIndex = xlAutomatic

Here, this section declares the header of the catalogue. We have made 3 headers for our task. Here, 1st column will show a short description of the product. 2nd column will show the Thumbnail or figure of the product and 3rd column will show us a hyperlink. We have also declared the size and color of the header.

Set File_Selection_Catalogue = Application.FileDialog(msoFileDialogFolderPicker)
File_Selection_Catalogue.AllowMultiSelect = False
File_Selection_Catalogue.Title = "Select the Folder with the Images"
If File_Selection_Catalogue.Show <> -1 Then
    Exit Sub

In this section, we have kept the selection option. You can select the folder where you have stored the catalogue information like figures and descriptions.

File_Path = File_Selection_Catalogue.SelectedItems(1) & "\"
File_Name = Dir(File_Path & "*.jpg*")

Here, we have saved the path directory of the folder and its containing items.

Do While File_Name <> ""
    Full_Path = File_Path + File_Name
    Range("B5").Cells(Count, 1) = Left(File_Name, Len(File_Name) - 4)
    Set photo = ActiveSheet.Pictures.Insert(Full_Path)
    With photo
        .Left = ActiveSheet.Range("B5").Cells(Count, 2).Left
        .Top = ActiveSheet.Range("B5").Cells(Count, 2).Top
        .Width = ActiveSheet.Range("B5").Cells(Count, 2).Width
        .Height = ActiveSheet.Range("B5").Cells(Count, 2).Height
        .Placement = 1
    End With
    Range("B5").Cells(Count, 3) = "=HYPERLINK(""" + Full_Path + """)"
    Count = Count + 1
    File_Name = Dir()
Loop

In this section, we have put the figures and information in the Excel file. The description column is taken from the figure name. The Thumbnail contains the figure of the products and the Hyperlink contains the file directory of the figure.

After writing the code, we need to run the code.
To do so, click on the following icon to run the code.

As a result, you can see a window that asks you to Select the Folder with the Images.
So, you have to select your folder accordingly.

We have kept our images in the Product Catalog subfolder of the Pictures folder.
So, we have selected accordingly and pressed OK.

As a result, the catalogue outcome is before us.
But, the catalogue is not looking impressive.
That’s why we need to modify the catalogue.

STEP 4: Modify the Design of Catalogue

In the following step, we will modify the catalogue.
Here, we want the information in the middle alignment.
To do so, select the B4:D10 range and go to the Alignment group of the Home tab.
After that, select the following icons of Middle Align and Center to modify the data in the cells.

In the catalogue, the column width needs to increase to fully fit the data.
To increase the column width of the B column, double-click in the middle of the B and C column.

As a result, the data is fitted in the Description column.

Similarly, we will also increase the width of the Hyperlink column.
Therefore, double-click in the middle of the D and E columns.

As a result, the data is fitted in the Hyperlink column.

In the next step, we will modify the header.
For this reason, make the row height 20.
After that, select the headers and click on the Fill Color option in the Font group.

From the options, select the color you want to show in the cell of the header.
Here, we have selected green color.

As a result, the header cells are showing green.
After that, we need to adjust our Thumbnail images.
To do so, click on the image you want to adjust.
After clicking, the following radio icons can be seen on the borders of the image.
Here, you can adjust the figure by dragging these radio icons.

Moreover, you can also move the picture in the cells.
To do so, click on the picture.
After clicking, radio buttons can be seen.
Now, hold the left-click button and move your cursor.
Moving the cursor will also move your figure.
After placing the figure in your desired place, hold off the left-click button.

Final Output

As a result, we have successfully created our product catalogue.
In the Hyperlink column, you can press any link.
Instantly, the figure of the mobile phone model will be shown.

Download Practice Workbook

To practice by yourself, download the following workbook.

Product Catalogue.xlsm

Conclusion

In this article, we have demonstrated step-by-step procedures for creating a product catalogue in Excel. There is a practice workbook at the beginning of the article. Go ahead and give it a try. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.

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

Get FREE Advanced Excel Exercises with Solutions!