MS Excel — Simplify Your Excel Workbooks With A Navigation Index

Quit Navigating Tabs the Hard Way!

After seeing the reaction to this concept — and appreciation from my clients — I decided to document HOW to create an interactive Index in an Excel workbook


Why Do I Want An Index?

When we are creating Excel workbooks, we tend to create tabs and just rely on “remembering” where everything is and what it does. This quickly becomes cumbersome and difficult to manage.

Worse yet, if we give the workbook to someone else, they are likely lost and spend an inordinate amount of time figuring everything out.

Let’s just help ourselves (and others) by spending a few minutes creating an easy to use Index worksheet tab.


Sample Index from a Financial Reporting Project

I recently completed a management reporting project for an organization and will use this index for presentation purposes — no names… :-)

When the Excel workbook is opened, you see this:

Excel Open View — to navigation Index

Basic Functionality of Index

The goal is to make interacting with the workbook as easy and simple as possible. I’m in, I’m out!

  • A date selection box to provide simple report updating — select the month and the reports all update
Date Selection tool using Data Validation
  • Filters (using Excel Slicers) to quickly modify the list we are working with
Filters to quickly modify list displayed
  • Buttons to unhide / hide displayed columns (I don’t need to display all the time since the filters display the choices).
Unhide columns — to show all columns in list
Hide columns — to condense displayed list

Using The Index

Filtered version of Index — Reports only!
  • Filter the Index by clicking tab in a Slicer. Note that selected item “stands out” once clicked on.
  • Click on the desired report — in this case, I select the “Balance Sheet Trend” report. The selected workbook tab then opens.
Trended Balance sheet worksheet — based on “Report Specific” date
  • Notice the date fields at the top. By default, the report drives off of that date (see below), but in this case, it has been overridden by selecting a “Report specific” date — I wanted a different period trended for a report I needed just on the balance sheet.
  • Below, I have DELETED the contents in the “Report specific” field — and the report is now based on the Master Reporting Date.
Trended Balance sheet worksheet — based on “Master Report” date

How Do I Create The Index?

It’s not as complicated as it looks! Below, I step through the process to make this happen — just change the content to match your workbook!

Create Your Reference Table

Create hyperlinks to other Excel workbook tabs
  • Create reference columns with easy to navigate content (e.g., sequenced, brief, etc.).
Create columns to Filter off of — these are Reference “Filters”
  • Provide a Comments column that might be helpful to you (or another user) as needed.
Add a Comments column — add anything that might be helpful to the user.

Develop Automation and Filters to Streamline Use (and Efficiency)

  • Create a date selection box (using Excel’s Data Validation capabilities). By default, all reports drive off of this date (this is the “Master Date”), which can be changed at the Report Level, if needed.
Date selection box created with Excel Data Validation
Buttons with assigned macros to Hide & Unhide Columns displayed
Excel Slicers to automate Index filtering (Excel Version 2013 is needed to do this!)

The (near) final Index appears as follows:

FINAL INDEX — NOW GET TO WORK!

Work through this process one time and I guarantee you will be using it repeatedly. If you share files with others, they will THANK YOU (oh, and you will be noticed!)


About Don

“It’s time for different”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

I frequently get this question. My response (it’s not what you think!)? Check it out here!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

Show your support

Clapping shows how much you appreciated Don Tomoff’s story.