Tag Archives: TOC

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.

Be stylish – Use Word Styles

Thank you for responding the vote in the previous article on 3 Efficiency Best Practices. In this article, we explore Word Styles in greater detail. Read this even if you are using Word Styles.

word styles

Picture created using Fresh Paint on Surface Pro with finger painting.

Problem: We spend too much time on formatting and too little on the core content.

Solution: Use Word Styles

Styles are just ready to use formatting which is created by graphics experts. There are 9 styles – Heading 1 to Heading 9

While creating a document, you know which of the paragraphs are Headings or Topics or Titles. As soon as you type the paragraph, choose the style.

Word styles

Main topics get H1, sub topics get H2 and so on.

How to select styles quickly

  1. Click the style in the Home tab – Styles drop down (it is called a Gallery).
  2. Or use keyboard shortcuts CTRL ALT 1 for H1 CTRL ALT 2 for H2 and
    CTRL ALT 3 for H3
  3. Or use the shortcut Shift Alt Up arrow to apply Higher styles, Shift Alt Down arrow to apply lower styles

Default styles in Word 2013

This how the default styles look. If you want to change the appearance to match your corporate branding guidelines, Right Click on each style, Choose Modify and change the look and feel.

Default styles in Word 2013

Customized styles affect only the current document by default. If you want all further documents to have the customized styles, choose the following option in the Modify dialog.

Customized styles in Word

Change the look and feel instantly using Themes

Choose Page Layout tab (2007,2010) or Design tab (2013) and open the Themes dropdown. Just move the mouse cursor over each theme and see how the document reformats instantly with different color and font combinations.

If you find some combination which resembles your brand colors, it is the easiest way of customizing document look and feel for corporate branding. Remember to use the same theme in Excel and PowerPoint as well.

What are the benefits of using Word Styles?

The main benefit is that Word knows what are the main and sub-topics in your document. Word uses this information intelligently to provide you with many useful features.

Navigation Pane

Choose View tab – Navigation Pane. Now a Separate window opens on the left side and shows you the document hierarchy. This never gets printed. This is your on-screen navigator.

Navigation Pane in Word

  1. Just click on the item to navigate to that area. No more scrolling and searching.
  2. Reorganize the document (and your thoughts) by just using drag-drop.
    (Amazing! is it not?)
  3. Right click on any heading and you get many useful options. Explore and find out.
    The Show Heading levels option is very useful for reading complex documents. Just show level 1 first and then drill down into the desired area quickly.

image

Sample file for practice

This file contains styles and TOC Download Style Document

Automatic Table of Contents

Now that word knows your topics and it anyway knows the page numbers, it can put these together and create a TOC automatically.

Go to the place in the beginning of the document where you want to create the TOC and Choose References tab and choose Automatic Table of Contents

Automatic table of contents

By default three levels of headings are added to the TOC along with page numbers. You can change the settings from the Custom Table of Contents option.

Remember to update the table before printing because the page numbers are not automatically updated when you edit the document. Right click on the TOC and choose Update Field or click inside the TOC and choose Update Table option.

Choose the second option.

Update table of contents

For very large and complex documents you can choose the first option (which is faster) when you are sure that you have not added any new headings. In most cases using the second option is safer.

Retrofitting styles into non-style documents

You will love Styles once you start using them. But then you will feel very sad that your older documents are not getting all these benefits of styles. But don’t lose heart. Microsoft has thought of that as well.

Open a document which is using manual formatting. You need to find all areas where manual formatting indicates various levels of headings and then apply the correct Heading Style. But this is going to be very time consuming if you want to do it one by one. Repetition means inefficiency.

Solution: Select text with similar formatting

  1. Click in one of the manually formatting heading. Choose Home tab – Select dropdown – Select text with similar formatting

Select text with similar formatting
Don’t worry if it shows (No Data). It works anyway.

  • Now Word finds and selects similar headings. You just have to apply Heading 1 once
  • If you have three levels of headings, you will have to do this thrice. Absolutely worth it because now you get all the benefits of Styles

Next article

Styles have many more benefits. One very useful feature is the ability to convert a Word document to a presentation. I will cover that in the next article.