Aleksandra Badaczewska

Aleksandra Badaczewska

Alex is a Research Scientist IV at the Genome Informatics Facility at Iowa State University. Her academic background is in Chemistry and Biotechnology, with a Ph.D. in Computational Biology and broad experience in programming and designing web applications. She develops a comprehensive collection of highly customizable visualization solutions for Bioinformatics and supports software optimization for the USDA Geospatial analyses.

DataScience Workbook / 07: Data Acquisition and Wrangling / 2. Data manipulation / 2.1. Manipulating Excel data sheets / 2.1.3. Create index for all worksheets

Introduction

This tutorial has been upgraded to incorporate Python-based automation approach, suplementing the foundational guide on Excel macros originally created by Arun Seetharam.

TASK or CHALLENGE:

If you have a large number of worksheets in your Excel file, this tutorial will guide you through automating the creation of an index sheet with links to each worksheet, making navigation quicker and reducing the risk of errors.

Generate index sheet linking all spreadsheets in Excel

Managing multiple worksheets in an Excel workbook can be overwhelming, especially when trying to navigate between them. Creating an index sheet with links to all the worksheets can streamline this process and enhance productivity.

A. Create macro in Excel (Windows and macOS)

Microsoft Excel: Before you begin, ensure you have macros enabled in your Excel tool.

Windows:
  1. Open Excel: Start Excel and open a new, empty workbook.
  2. Access Excel Options:
    • Click on the File tab in the top-left corner.
    • From the menu, select Options. This will open the Excel Options dialog box.
  3. Customize Ribbon:
    • In the Excel Options dialog box, click on Customize Ribbon from the list on the left.
    • On the right side, you’ll see a list of the main tabs. Look for the Developer checkbox.
    • Check the box next to Developer.
  4. Apply and Close: Click OK to apply the changes and close the Excel Options dialog box.
    The Developer tab should now be visible in the Excel ribbon.
macOS:
  1. Open Excel: Start Excel and open a new, empty workbook.
  2. Access Preferences:
    • Click on Excel in the menu bar at the top of the screen.
    • Select Preferences from the dropdown menu.
  3. Ribbon & Toolbar:
    • In the Excel Preferences dialog box, click on Ribbon & Toolbar. (see an image in the next step)
  4. Customize Ribbon:
    • In the Customize Ribbon section, you’ll see a list of tabs. Look for the Developer checkbox.
    • Check the box next to Developer.
  5. Apply and Close: Close the Ribbon & Toolbar preferences dialog box.
  6. Enable Full Disk Access for Excel To ensure that Excel macros work correctly and can manage inputs and outputs on macOS, you need to grant Excel full disk access.
    • Open System Preferences.
    • Go to Security & Privacy.
    • Click the Privacy tab.
    • In the left sidebar, select Full Disk Access.
    • Click the lock icon in the bottom left corner and enter your password to make changes.
    • Click the + button and add the Microsoft Excel application to the list.

1. Prepare your Excel spreadsheets

Excel Spreadsheets: An Excel workbook containing multiple worksheets (tabs).

Each worksheet should contain structured data in a consistent format. The VBA code for the macro can be adjusted to handle different data formats appropriately.

You can use any Excel file that contains at least 2 spreadsheets to complete this exercise. If you followed the tutorial, you can use the final output.xlsx file or you can simply . You should get an Excel file with 5 worksheets. the preview of the output Excel file

2. Add VBA code of the macro

  1. Open Excel app (graphical user interface). Open your Excel workbook that contains multiple worksheets.
  2. Click on Developer tab in the Excel wirndow and then Macros button.
  3. In the pop-up window, type in some name for a new macro (e.g., export_text) and click create or + (on macOS). The new pop-up window with Microsoft Visual Basic will appear.
  4. MS Visual Basic window: Paste the VBA code of the selected macro in the Code panel on the right:

Automatic Index Sheet

Sub CreateLinksToAllSheets()
    Dim sh As Worksheet
    Dim indexSheet As Worksheet

    ' Create or clear the index sheet
    On Error Resume Next
    Set indexSheet = ActiveWorkbook.Worksheets("Index")
    On Error GoTo 0

    If indexSheet Is Nothing Then
       Set indexSheet = ActiveWorkbook.Worksheets.Add(Before:=ActiveWorkbook.Worksheets(1))
        indexSheet.Name = "Index"
    Else
        indexSheet.Cells.Clear
    End If

    ' Create links
    indexSheet.Select
    indexSheet.Cells(1, 1).Value = "Sheet Links:"
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> indexSheet.Name Then
            indexSheet.Cells(indexSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Hyperlinks.Add _
                Anchor:=indexSheet.Cells(indexSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1), _
                Address:="", SubAddress:="'" & sh.Name & "'!A1", TextToDisplay:=sh.Name
        End If
    Next sh

    ' Adjust column widths
    indexSheet.Columns("A:A").AutoFit
End Sub
What the script does?

Initialize the Index Sheet: The script first checks if an “Index” sheet exists. If not, it creates one. If it does exist, it clears its contents. Then, it adds a header “Sheet Links:” to the index sheet.
Create Links: The script iterates through each worksheet in the workbook. For each worksheet, it adds a hyperlink to cell A1 of that sheet, skipping the index sheet itself.
The script adjusts the column widths to fit the content.

Run the macro in Excel spreadsheet

Go back to your Excel spreadsheet window.

  1. Open your Excel workbook that contains multiple worksheets.
  2. Click on Macros again. This time, the pop-up Macro window has a list of your added macros. Select the one you’ve just created. Then, click the Run button. This macro will automatically create an index sheet named “Index” at the beginning of your workbook, making it easy to navigate between worksheets with a single click. If you run the macro again, it will update the index sheet to reflect any changes in your workbook.
    Macro can be run from any sheet without affecting its content.

In-Place Index

Sub CreateLinksToAllSheets()
Dim sh As Worksheet
Dim cell As Range
For Each sh In ActiveWorkbook.Worksheets
    If ActiveSheet.Name <> sh.Name Then
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
        ActiveCell.Offset(1, 0).Select
    End If
Next sh
End Sub
What the script does?

Starting Cell: The macro will start creating the index from the currently selected cell in the active sheet.
Creating Links: The macro iterates through each worksheet in the workbook. For each worksheet, it adds a hyperlink to cell A1 of that sheet, skipping the active sheet itself. After adding each link, it moves down one cell to place the next link.

Run the macro in Excel spreadsheet

Go back to your Excel spreadsheet window.

  1. Open your Excel workbook that contains multiple worksheets.
  2. Navigate to the desired sheet. Select the cell from which you want the index to start.
    Be careful as this will overwrite the existing content starting from this cell.
  3. Click on Macros again. This time, the pop-up Macro window has a list of your added macros. Select the one you’ve just created. Then, click the Run button. This simpler macro places the index starting from the currently selected cell. It can be useful if you want to create an index on demand within any worksheet. However, be cautious of which cell is selected when running the macro, as it will overwrite the content starting from that cell.
    Macro can be run from any sheet with affecting its content.

Ensure you select an appropriate starting cell before running the macro to avoid overwriting important data. This method is useful for creating an index within the context of any sheet on demand.