The question in your mind is WHY rather than HOW!
Read on and find out. Best of both worlds.
Estimated reading time 4 min
This is a small but useful post.
Spreadsheets become large very quickly. Lot of scrolling is required to navigate it.
Scrolling vertically is easy.
This way you can scroll one screen of data at a time.
How often have you received complex Excel files from others which have many worksheets, some color coded (but you don’t know the meaning of the color), some having similar names …lots of data, calculations, pivots, charts … and then you face a simple problem. Where do I start? Which areas am I supposed to view? The first sheet which opens is the sheet and the area of the sheet which was open at the time of last file save. Which does not necessarily mean that is the area of primary interest. It is quite confusing.
It is not just confusing. It is dangerous. The sender may have intended you to look at a particular sheet / range but in reality, you end up looking at some other area – and take some decision. This is a communication gap – which is difficult to bridge. This can lead to misinterpretation, erroneous decision making and chaos.
The solution: Create a Table of Contents for complex Excel workbooks
The problem: Excel does not provide a built-in feature to create a TOC. But that does not constrain us. As we saw in the last article, we can use a Hyperlink within a document to create a set of links. By default, Excel allows you to create a hyperlink to existing sheets. In practice, sheet based navigation may not be enough – because, the same worksheet may have multiple areas of interest. Therefore, Excel allows us to create other named items which can be used as hyperlink targets. You can create Range Names.
How to define range names?
Select any range in a worksheet. Go to the name box, type a name and press ENTER. No spaces allowed in the name. Now these names appear in Hyperlink Dialog. You can also create and edit names using Formulas tab – Name Manager.
Creating a TOC in Excel
Here are the steps:
- Rename each sheet – never keep the defaults to Sheet1, Sheet2 and so on
- Delete empty sheets
- Identify ranges which are important for creating the TOC and assign names to them.
- Create a new sheet called Contents
- Create the TOC by typing relevant text
- For each TOC item, insert a hyperlink either to a sheet or a defined name
- Just before saving and closing the file keep the content sheet active
- Close the file
Now everyone can use the TOC we just created to browse the file in an informed manner.
Creating a “Back” link
In order to complete the navigation, it is a good idea to have a BACK link at each of the hyperlinks. This is easy. Type Back in a cell just above the named range and insert a Hyperlink to the Contents sheet. Copy this cell to all hyperlink targets.
Remember to update the TOC
Whenever you add or delete more data or sheets, consider if you need to edit the Contents sheet the reflect the changes.
When do you need this?
If you have a presentation which covers multiple topics and you want to have the interactivity to move from a main menu page to any topic, this article is for you.
We will use this presentation as an example…
Use this presentation for practice. Download Presentation
White colored slides are the base presentation. Second slide is sort of an agenda and menu slide. You ask the audience which topic they want and then click on the desired option.
Blue slides are for Word, green for Excel and pink for PowerPoint.
If you clicked on, say, Excel icon in second slide, then PowerPoint should show slides 6,7,8 and then COME BACK to slide 2.
Hyperlink is NOT the answer
Usually you will put hyperlink on the Excel icon to jump to slide 6. But you know the problem – it will just go to all slides after 6 and spill over to the PowerPoint slides. To avoid that we put another link on the 8th slide which gets you back to second slide.
There is a better way – using Custom Shows. Please read the previous article to understand how to create custom shows.
Step 1 – Create custom shows for each topic
This is easy. Just create three custom shows called Word, Excel and PowerPoint.
Step 2 – Insert hyperlink to the CUSTOM SHOW (not to a specific slide)
Now click on each icon – in this case Excel and choose Insert Hyperlink…
Choose the CUSTOM SHOW in the hyperlink. The Show and return option is your best friend … but you never noticed it till now. Ha ha… why? Because most of the times this option used to be inactive – why? because this option requires you to select a custom show. And you did not ever create a custom show… why ? never mind.
Now you can run the presentation in an interactive manner.
What if you want to return to the main menu slide while you were half way through the Excel custom show? Don’t worry… just press Escape. It will go to the base slide. Next escape will stop the slide show (as expected). So remember, if you are in custom show, escape takes you one level back.
Sections v/s Custom Show
Office 2010 onwards, you can add sections to presentation to segregate various topics. These are similar to Custom Shows but following things are different.
- Sections are linear. Custom shows can contain slides in any order
- Every slide must be in some section. Custom show can contain only a subset of slides
- Hyperlink can be used only with custom shows – not with sections
I will cover sections in another article later.