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)

how to make catalogue in excel

  • 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.

how to make catalogue in excel

  • 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.

how to make catalogue in excel

  • 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.

how to make catalogue in excel

  • Select the image File and click on Insert.

how to make catalogue in excel

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

Read More: How to Make a Picture Catalog in Excel


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.

how to make catalogue in excel

  • You will only see the Cosmetic category products.

how to make catalogue in excel


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.


Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

2 Comments
  1. 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
      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:

      1. Right-click on the sheet name tab.
      2. Click on View Code.
      3. 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

      4. 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo