Make Excel tabs list in a worksheet
Make your own clickable list of workbook tabs in an Excel worksheet to workaround the small number of tabs that can fit in a single line.
See Fit more tabs across an Excel workbook for some suggestions to squeezing more across the single line available. Those suggestions work to make a bit more space available but aren’t effective for many more worksheets.
Another consideration is shared workbooks that can be opened by others on computers and devices; wide and narrow. At one extreme is the narrow view of Excel for iPhone or Android phones. A tab display option needs to work across the different Excel incarnations.
The solution? An index page with clickable links to the tabs. A very simple one looks like this.
Index page works across all devices and window widths
Making a tab or worksheet index page
Excel can make hyperlinks to cells or ranges both within the current workbook and to other spreadsheets. We’ll just talk about internal links for a single workbook but the same principles apply to external links.
- In a cell, type the name of the tab or some other label then press enter to confirm the cell contents.
- Press Ctrl + K — the usual ‘Insert Hyperlink’ shortcut that works across all Office programs and beyond.
- Link to: Place in this document.
- Then choose a cell reference or range in the workbook.
The workbooks are listed under ‘Cell Reference’.
Excel can’t link to a tab generally, it needs a specific cell reference so it inserts cell A1 of the workbook as a default. If you’d prefer to view another part of the workbook, change the cell reference accordingly.
Links to hidden workbooks
Links to hidden tabs/workbooks don’t work.
You can only make links to visible workbooks (ie that appear in the Insert Hyperlink dialog). Clicking on the link to a now-hidden workbook does nothing until the tabs is ‘unhidden’.
Fancy workbook index pages
Once you have the basics of index pages, go crazy with different ways to present the list or the same list in different orders.
Data | Sort will sort the list.
Of course, the list doesn’t have to be in a single column.
Rather than a simple list of workbooks, create a summary of the entire worksheet including links to each tab. Like any table, it can be sorted via the headings.
