Feb 28, 2017
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:
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
- Filters (using Excel Slicers) to quickly modify the list we are working with
- Buttons to unhide / hide displayed columns (I don’t need to display all the time since the filters display the choices).
Using The Index
- 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.
- 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.
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 list of workbook tabs (worksheets) and create a hyperlink to each worksheet.
- Create reference columns with easy to navigate content (e.g., sequenced, brief, etc.).
- Provide a Comments column that might be helpful to you (or another user) as needed.
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.
- Create macros to 1) Hide the columns you don’t need to display, and 2) Unhide for use when maintenance / updating is necessary. These are simple macros you create using the Macro Recorder functionality. Then create Buttons and assign the macros.
- Create Filters (this is done using Excel’s Slicer feature). You need EXCEL VERSION 2013 for this step!
The (near) final Index appears as follows:
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!
