Microsoft Excel — ASAP Utilities Add-In — My Top Uses (#2)
#2 — Create a Hyperlinked Workbook Index Sheet
Many Excel users create workbooks with numerous sheets — reports, data input, support schedules, etc. In my experience, few of those workbooks contain an Index Sheet to simplify their use and navigation.
I was taught the importance of this early in my use of Excel and this process has become a natural part of spreadsheet development. This is especially critical if you create Excel workbooks for others to use — your co-workers, supervisors, or just maybe, clients!
Fortunately, ASAP Utilities makes the process super easy!
In this post, I am going to explain the following process:
- Using ASAP to create the hyperlinked index sheet, and
- Basic modifications to make to “clean it up” and make it user friendly.
Create the hyperlinked index sheet
From any workbook sheet, select “ASAP Utilities → Sheets → 7. Create an index sheet with links to all sheets (clickable)…”
The Index Worksheet dialog box appears… select your preferred options.
The Index sheet is created… click OK to rename sheet. Very quickly, you have a hyperlinked Index! This Index Sheet is only 18 sheets, but it works just as well for 250 sheets (or more).
ASAP’s work is done!
Basic modifications you can make to “clean it up” and make it user friendly
Moving on from ASAP Utilities, time to “CLEAN UP” the Index Sheet to be a little more user friendly, so I am going to walk through that process. The steps are:
- Add additional columns for navigation purposes
- Insert additional rows at the top of the spreadsheet to provide space for filters to be added
- Once the structure is set, convert to a Table Data format
Next, populate the table columns — this makes it easy for anyone to use the workbook and quickly navigate to the worksheet they want.
Now, it’s ready to create filters to make the use of the Index a little more friendly. Note — this is a small example…imagine that there are 200 rows and the idea starts to make a lot more sense!
Organize and size the Slicers (this is the Excel name for the filter boxes)…
Finally, HIDE the columns I don’t need (i.e., since the filters are shown, I don’t need to respective columns displayed!)
Click on any (or multiple) filter buttons to reduce your list to just those relevant items.
That’s it. You now have a customized worksheet that serves as a quick, user friendly, navigation Index to your worksheets!
About Don
“On a mission to challenge the status quo to a more productive and effective end…”
Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.
“What Do You Do?”
I frequently get this question. My response (it’s not what you think!)? Check it out here!
