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.

Download the Practice Workbook

Catalogue in Excel.xlsx

Catalogue in Excel.xlsm

Book Catalogue in Excel.xlsx

Table of Contents Expand

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.

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.

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

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

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

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

Finally, we will provide the information on clothes in the 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.

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

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

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.

2. Using VBA Code

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

Then go to Insert >> Module.

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", _
                TextToDisplay:=cell.Value
        End If
    Next cell
End Sub

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

How to Create Book Catalogue in Excel

Write the column names in the worksheet.

Then separate the rows and columns 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.

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

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

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

Then enter the book data into the table.

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.

Conclusion

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.