How to generate a list of Microsoft Excel sheet names by exposing Power Query metadata

When a Microsoft Excel workbook contains a lot of sheets, the sheet tab you need is often out of sight, which is annoying and slows you down a bit. There are many workarounds, but a list of sheet names can be helpful, especially if users don’t have basic Excel skills. Once you have a list of sheet names, users you can use them to navigate the workbook. An easy way to generate the list of sheet names is available via Microsoft Power Query.

In this tutorial, I’ll show you how to use Power Query to generate a list of sheet names that you can then load into Excel and use to navigate the workbook. The sheet technique is interesting, but the real focus is exposing the workbook’s metadata using Power Query.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions of Excel through Excel 2010. Excel for the web doesn’t support Power Query, although it does support queries and existing connections.

How to disable OneDrive

Before you do anything, you must disable OneDrive. If you don’t, you’ll get an error that someone else is using the workbook so you can’t access it. The culprit is OneDrive.

To disable OneDrive temporarily, find it in the Taskbar or the overflow area (Figure A). Click the blue cloud icon and then click Settings — the gear in the top-right corner. In the resulting list, choose Quit OneDrive. Confirm the action by choosing Close OneDrive when prompted. If you’re a 365 subscriber, you are using OneDrive whether you realize it or not.

Figure A

This is a temporary situation. When you’re done, you’ll want to re-enable OneDrive, which I’ll show you how to do later.

How to launch Power Query

We’ll work with a simple Excel workbook, shown in Figure B, with six empty sheets. One of the sheets also has a named range, denoted by borders. For demonstration purposes, we don’t need any data because we’re after the workbook’s metadata, which includes sheet names.

Figure B

Once you’ve disabled OneDrive, you’re ready to launch Power Query:

1. From inside the Excel workbook with all the tabs, click the Data tab.

2. In the Get & Transform Data group, click the Get Data dropdown and choose From File.

3. Next, choose From Excel Workbook.

4. When prompted, find, and specify the same workbook, and then click Import. This is where you’ll get into trouble if you don’t first disable OneDrive. If this happens to you, close everything and disable OneDrive, as shown above.

5. In the resulting pane, select any sheet (Figure C). You only need one. Notice that the list also contains a named range, SheetANamedRange. We won’t use it, but I wanted you to see that this loading process offers more than the sheets. It will also display Table names.

Figure C

6. Click Transform Data to launch Power Query.

For now, Power Query looks empty.

How to expose the Excel file’s metadata in Power Query

There’s no data in Power Query, but that’s what we want. This allows us to expose the file’s metadata.

To the right, the Applied Steps list has three steps, but we need to keep only the first, which is Source. Delete Navigation and Changed Type by clicking the X to the left of each step. Doing so exposes metadata about the workbook, as shown in Figure D.

Figure D

The Kind column displays the item type. There are six sheets and one defined name. We only need the six sheets, so our first step is to filter out the defined name row. When applying this technique to your own data, you might not have any defined names, but I recommend that you apply a filter anyway if you plan to reuse this query. When you update the list later, you might have a named range or a Table.

To apply a filter to display only the sheets, do the following:

1. Click the Kind column’s dropdown.

2. In the resulting list, uncheck everything but Sheets (Figure E).

Figure E

3. Click OK to apply the filter.

Figure F shows the resulting records. There’s no row for the defined name. When applying this to your own work, the metadata might have other elements other than sheet names. Be sure to filter everything but sheets.

Figure F

At this point, we must remove all the columns but the Name column. To do so,

1. Right-click the Name header to select that column.

2. Select Remove Other Columns from the resulting submenu.

After removing everything but the column of sheet names, click Load & Close to load the list into Excel. The result is a Table in a new sheet named Apr (2).

How to use the list to navigate sheets in Excel

How you use the Table of sheet names is up to you, but we’ll create a column of hyperlinks for quick navigation. To turn the sheet names into hyperlinks, enter the following function in B2 and copy to the remaining cells, as shown in Figure G:

=HYPERLINK("[PQSheetHyperlinks_Demo.xlsx]" & A2 & "!A1",A2)

Figure G

This function requires the current sheet name, “[PQSheetHyperlinks_Demo.xlsx]”, even when moving within the same workbook. The & A2 & component concatenates the sheet name in A2. The “!A1” component selects A1 of the corresponding sheet. The last argument, A2, specifies the displayed text, which in this case, is the sheet name.

How to refresh the sheet name Table in Excel

When you add, delete, or rename sheets, you’ll need to update the list. First, save the workbook. Then, right-click the list and choose Refresh from the resulting submenu.

If you have Microsoft 365 and OneDrive, you must re-enable OneDrive before this works. The easiest way to do so is to sign-in to your Microsoft 365 account and click Sync on the menu.

It is a bit odd that you must disable OneDrive to generate the list and then enable it to refresh the list. This could change with any update.