Wednesday, August 12, 2009

Excel: many sheets

When you want to present a lot of data in Excel it may lead to a large number of sheets. In contrast to imposed limits on the number of rows and columns, Excel allows you to add an almost infinite number of sheets (only “limited by available memory”). However a large number of sheets can make it difficult to navigate for a user. The sheets are represented as tabs at the bottom of the window:

sheets

If there are more than a dozen or so it becomes cumbersome to find and select the correct tab.

A useful alternative is to use hyperlinks for easy navigation. It may make sense to introduce a “table of contents” page, e.g.:

toc

Then in every sheet make sure it is easy to go back, e.g. by adding a link to the TOC page:

toc2

This now offers a completely alternative way of navigation in addition to using the sheet tabs. The above example spreadsheet is here.