Catalogue in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn how to make various types of catalogues in Excel. Here we will show you how to make efficient catalogues of books that can be used in libraries or in bookshops. We will also learn to make product catalogues for business purposes.

First, we will learn to make the Product Catalogue. Product catalogues here also contain images. So we will learn to insert images of products in a catalogue in Excel. We will also learn a VBA code to hyperlink all the worksheets in a workbook.

We will also learn to make a book catalogue for bookshops or libraries. This will contain all the information regarding the book. We can also filter and find out one particular book easily by using the filter option in Excel. So without any further delay let’s dive into the main article.

Overview Image

Download the Practice Workbook

How to Create a Basic Catalogue in Excel

The article below would cover almost everything about catalogues in Excel.

1. Inputting and Linking Manually

  • First, we will create the headings in an Excel sheet.

creating heading for the catalogue

  • Then we will create multiple worksheets based on the category of products and will make a list of them in those worksheets. Here we have five worksheets.

Create worksheets based on product type

  • Now put the necessary information about the products into them. In the Electronic Devices worksheet, we have put information on electronic devices.

Electronic devices worksheet

  • In the same way, we will provide the information on confectionary items in the Confectionary Items worksheet.

Confectionary Items Worksheet

  • Then we will provide the information on Fruits items in the Fruits worksheet.

Fruits Item Worksheets

  • We will provide the information on Grocery items in the Grocery Items worksheet.

Grocery Items Worksheet

  • Finally, we will provide the information on clothes in the Clothing and Apparel worksheet.

Clothing and Apparel Worksheet

  • Now go to the first worksheet and change the name of the worksheet to Menu. Then write the names of the product worksheets from B5:B9.

Writing the worksheet name in Menu Worksheet

  • Select cell B5 and right-click the mouse, you will get a menu and from there select Link.

Selecting the B5 cell

  • Then we will get the Insert Hyperlink window, from there select Place in This Document and select Electronic Devices. Click on OK.

Interlinking the worksheet name

  • Then a hyperlink will be created. Now if we click on the Electronic Devices in the B5 cell of the Menu worksheet, it will take us to the Electronic Devices worksheet.

  • Then we will repeat the process for other worksheet names.

Hyperlinking all the worksheets name

2. Using VBA Code

  • From the Menu worksheet, go to the Developer option, and click on Visual Basic.

Opening the VBA window

  • Then go to Insert >> Module.

Inserting Module in VBA window

  • Write the following code and click on the Run button.
Sub CreateMenu()
    Dim menuSheet As Worksheet
    Dim ws As Worksheet
    Dim cell As Range
    Dim targetSheet As Worksheet
    Dim i As Integer
    i = 1
    For i = 2 To ActiveWorkbook.Worksheets.Count
        Worksheets("Menu").Range("B" & i + 3) = Sheets(i).Name
    Next i
    Set menuSheet = ThisWorkbook.Sheets("Menu")
    For Each cell In menuSheet.Range("B5:B9")
        Set targetSheet = ThisWorkbook.Sheets(cell.Value)
        If Not targetSheet Is Nothing Then
            menuSheet.Hyperlinks.Add Anchor:=cell, _
                Address:="", _
                SubAddress:="'" & targetSheet.Name & "'!A1", _
        End If
    Next cell
End Sub

Writing the VBA code

  • This will create hyperlinks to the product worksheets in the Menu Worksheet from the B5:B9 cell.

Created hyperlinks for all the worksheets

How to Create Book Catalogue in Excel

  • Write the column names in the worksheet.

Writing the column names in the worksheet

  • Then separate the rows and columns using borders.

making a table using borders

  • To change the format of the Published Date column from Number to Date, select Column E and go to Home >> Cells >> Format >> Format Cells.

Opening format cells

  • From the Number tab select the Date format and click OK.

Choosing date format

  • Then to freeze the Heading row, select Row 5 then go to View >> Freeze Panes >> Freeze Panes.

freezing the headings

  • To put the filter option in the table, select B5:G5 and go to Data >> Filter.

adding filters

  • Then enter the book data into the table.

Entering the book data

Read More: Catalog Books in Excel

Frequently Asked Questions (FAQs)

1. Can I add images to my catalogue in Excel?

Yes, you can add images in a catalogue in Excel. To do this go to the Insert tab and select Picture to add an image in the Excel sheet.

2. How can I sort and filter the catalogue based on specific criteria?

To do this select the dataset and go to Data and from Sort & Filter option select Filter. This will sort and filter the catalogue based on criteria.

3. How can I protect my catalogue in Excel from accidental changes?

To do this you can use worksheet/workbook protection. To do this go to Review tab and then select Worksheet Protection or Workbook Protection based on your need.


Finally, making a catalogue in Excel gives businesses a flexible and effective way to exhibit their products or services. You can develop a visually appealing and informative catalogue that attracts the attention of potential consumers and generates sales using Excel’s hierarchical framework, adjustable formatting options, and ease of updating. Excel is a good choice for producing catalogues that can adapt to the changing needs of your organization because to its ease of updating and maintaining information, as well as its flexibility in sharing in multiple forms.

Catalogue in Excel: Knowledge Hub

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md Sakibul Hasan Nahid
Md Sakibul Hasan Nahid

Md. Sakibul Hasan Nahid, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. As an Excel & VBA Content Developer for ExcelDemy, he not only provides solutions to complex issues but also demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in C++, Python, Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF