Tag Archives: Hyperlink

Link recent files instantly

Here is a smart new feature which allows you to Link recent files instantly. It is difficult to notice but a very nice addition. Just notice the dropdown below the Insert – Link button. Also available with Right Click – Link button. Shows recent files to choose from. Works with Word, Excel, PowerPoint and Outlook. It is similar to the Insert Attachment dropdown in Outlook. One difference.

This list does not show the cloud icon for files which are on OneDrive or SharePoint. Look at the path before adding the link. Whether the file is on local drive or OneDrive, it is not going to automatically give access to others, in case you send the documents to them. The Outlook – Add Attachment list shows the cloud icon and automatically gives EDIT permission to the recipients.

SNAGHTML21e14e3

How to create invisible hyperlinks to files

While presenting using PowerPoint, we often embed related documents. We want the ability to open these documents on demand. But at the same time, you don’t want the audience to SEE the hyperlink. You don’t want them to know you are prepared!  Another practical usage is when you want related presentations to be available in a single click without affecting the look and feel of your base presentation. Here are many ways in which you can achieve this.

Invisible Hyperlinks to files

Continue reading How to create invisible hyperlinks to files

Did you know: Hyperlink within the same document

This is a brilliant feature. Often unknown. It is really very simple. Hyperlink usually refers to an external file or web page. But it need not. It can also refer to a part of the current document – provided we can identify the parts. This humble icon packs so much power – we have no idea what we have been missing. I myself learnt so much WHILE writing this article!

Hyperlink story

Continue reading Did you know: Hyperlink within the same document

How to create Table of Contents in Excel

The Problem

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.

The Risk

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. how to create a table of content in Excel by Dr. Nitin Paranjape You can also create and edit names using Formulas tab – Name Manager.

Creating a TOC in Excel

Here are the steps:

  1. Rename each sheet – never keep the defaults to Sheet1, Sheet2 and so on
  2. Delete empty sheets
  3. Identify ranges which are important for creating the TOC and assign names to them.
  4. Create a new sheet called Contents
  5. Create the TOC by typing relevant text
  6. For each TOC item, insert a hyperlink either to a sheet or a defined name
  7. Just before saving and closing the file keep the content sheet active
  8. 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.

Create Interactive Presentation with Custom Shows and Hyperlink

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

Interactive Presentation with custom shows and hyperlinks

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.

Pathetic.

There is a better way – using Custom Shows. Please read the previous article to understand how to create custom shows.

The solution

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…

Insert Hyperlink in PowerPoint

Choose the CUSTOM SHOW in the hyperlink. The Show and return option is your best friend Smile … 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 ? Winking smile never mind.

Insert Hyperlink in PowerPoint

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.

  1. Sections are linear. Custom shows can contain slides in any order
  2. Every slide must be in some section. Custom show can contain only a subset of slides
  3. Hyperlink can be used only with custom shows – not with sections

I will cover sections in another article later.