Agenda slide is usually the second or third slide. During the course of the PowerPoint presentation, we may have to refer to that slide frequently, especially when the next agenda item is taken up. Most of us copy paste that slide multiple times. This may work, but if the agenda slide changes, we may forget to update other slides. The same method can be used to refer to any key slide or slides during the presentation. The key thing expectation is that you may show this slide at any point during the presentation. After showing the slide, how do you come back to the slide where you were? Here is how you do it.
View the video in full screen mode. Download this Sample File which illustrates the method shown in the video.
Scrolling is inevitable. But there is a catch. If you press Page Down, the next screen of rows are shown. The problem is, we also need to scroll horizontally. What do you do then?
What is the most efficient way of scrolling horizontally?
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.