Image: Andreas Prott/Adobe Stock When a Microsoft Excel workbook contains a great deal of sheets, the sheet tab you need is typically out of sight, which is irritating and slows you down a bit. There are numerous workarounds, however a list of sheet names can be helpful, specifically if users do not have fundamental Excel abilities. As soon as you have a list of sheet names, users you can utilize them to navigate the workbook. A simple method to produce the list of sheet names is readily available through Microsoft Power Inquiry. In this tutorial, I’ll show you how to use Power Question to create a list of sheet names that
you can then pack into Excel and use to navigate the workbook. The sheet technique is intriguing, however the genuine focus is exposing the workbook’s metadata utilizing Power Inquiry. SEE: Google Work area vs. Microsoft 365: A side-by-side analysis w/checklist(TechRepublic Premium
To disableOneDrive briefly, find it in the Taskbar or the overflow area(Figure A). Click the blue cloud icon and after that click Settings– the gear in the top-right corner. In the resulting list, select Quit OneDrive. Validate the action by choosing Close OneDrive when prompted. If you’re a 365 subscriber, you are
utilizing OneDrive whether you realize it or not. Figure A Disable OneDrive temporarily. This is a short-lived situation. When you’re done, you’ll want to re-enable OneDrive, which
I’ll reveal you how to do later on.
How to launch Power Query We’ll work with an easy Excel workbook, shown in Figure B, with six empty sheets. One of the sheets
also has actually a called range, denoted by
borders. For presentation purposes, we do not need any data because we’re after the workbook’s metadata, which includes sheet names. Figure B Load the Excel data into Power Inquiry. Once you’ve disabled OneDrive, you’re ready to release Power Question: 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, pick from Excel Workbook. 4. When triggered, discover, and
specify the very same workbook, and after that click Import. This is where you’ll enter difficulty
if you do not first disable OneDrive.
If this takes place to you, close whatever and disable OneDrive, as shown above. 5. In the resulting pane, choose any sheet(Figure C). You just need one. Notification that the list likewise contains a named range, SheetANamedRange. We won’t use it, however I desired you to see that this packing procedure uses more than the sheets. It will likewise display Table names. Figure C< img src ="https://d1rytvr7gmk1sx.cloudfront.net/wp-content/uploads/2022/10/tr-PQSheetHyperlinks_C.jpg?x85972 "alt= "Load the empty sheet into
Power Question.”width=”537″ height=”624″/ > Load the empty sheet into Power Query.
6. Click Transform Data to launch Power Question.
In the meantime, Power Query looks empty. How to expose the Excel file’s metadata in Power Inquiry There’s no information in Power Question, but that’s what we want. This permits us to expose the file’s metadata.
To the right, the Applied Steps list has three steps, however we need to keep only the first, which is Source. Delete Navigation and Changed Type by clicking the X to the left of each action. Doing so exposes metadata about the workbook, as displayed in Figure D.
Figure D
Expose the file’s metadata. The Kind column shows the product type. There are six sheets and one specified name. We just require the 6 sheets, so our first step is to filter out the specified name row. When using this method to your own data, you might not have actually any defined names, but I recommend that you use a filter anyhow if you plan to recycle this query. When you upgrade the list later, you may have a called variety or a Table.
To apply a filter to display just the sheets, do the following:
1. Click the Kind column’s dropdown.
2. In the resulting list, uncheck whatever however Sheets (Figure E).
Figure E
Get rid of everything but the sheets. 3. Click OK to use the filter. Figure F reveals the resulting records. There’s no row for the specified name. When using this to your own work, the metadata may have other components aside from sheet names. Make certain to filter everything however sheets.
Figure F
The specified name row is gone. At this moment, we should remove all the columns however the Name column. To do so, 1. Right-click the Name header to choose that column.
2. Select Eliminate Other Columns from the resulting submenu.
After eliminating everything however the column of sheet names, click Load & Close to fill 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 utilize the Table of sheet names is up to you, however we’ll develop a column of hyperlinks for fast navigation. To turn the sheet names into links, enter the following function in B2 and copy to the staying cells, as shown in Figure G:
=LINK(” [PQSheetHyperlinks_Demo. xlsx] & A2 & “! A1”, A2)
Figure G
Add links to the sheet names for fast access. This function requires the existing sheet name,”[ PQSheetHyperlinks_Demo. xlsx], even when moving within the same workbook. The & A2 & part concatenates the sheet name in A2. The “! A1” component chooses A1 of the matching sheet. The last argument, A2, specifies the shown text, which in this case, is the sheet name.
How to refresh the sheet name Table in Excel
When you include, delete, or rename sheets, you’ll need to upgrade the list. Initially, save the workbook. Then, right-click the list and select Refresh from the resulting submenu.
If you have Microsoft 365 and OneDrive, you must re-enable OneDrive before this works. The simplest method 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 need to disable OneDrive to produce the list and then enable it to refresh the list. This could alter with any update.
