Navigate Speedily in Excel Data

Click on a button to get to target information.

by James T. Severson

f you’re like most financial professionals, some of your large spreadsheets contain many worksheets with a wide assortment of data. Locating information or identifying just the right worksheet or cell to input new data is like searching for the proverbial needle in a haystack. If this describes a problem you often face, then read on to find out how to create a spreadsheet that, with a single mouse click, can take you instantly right to the target cell.

The solution is based on two Excel tools: Forms Toolbar Buttons and Macro. We will show you how to install and format those functions on a contents page that contains buttons designed to speed you to your data destinations.

Begin by creating a blank worksheet and label it Contents Page. Then, in various cells, list all the data targets that will be stored in subsequent worksheets. If you wish, add additional information that can be printed on or next to the buttons to guide you on when to use the buttons.

Exhibit 1, below, is an example of a completed contents page that includes both the data-locating buttons and the user instructions.

Next add the actual buttons and then the macro commands that will wing you instantly to your targets. Begin by right-clicking in any free space in the toolbar area of the worksheet to engage a drop-down menu. Then click on Forms (exhibit 2, below).

That will open a Forms toolbar; it contains, among other things, a Button icon. To locate the icon, drag your cursor over the toolbar until the Button label appears (see screenshot below).

Now left-click on the Button icon and move your cursor to the cell on the contents page where you want to install the first button (see screenshot, below).

You can change the size of a button anytime by grabbing and dragging any of the tiny circles along the edges. Left-clicking on the button allows you to edit the text. For this exercise, we’ll label the first button input revenue.

Now create a macro that will take you directly to the worksheet target—in this case input revenue. Engage the Assign Macro menu by right-clicking on the button (see screenshot, below).

If you know Visual Basic, the macro software, you can write the script yourself, but it’s a lot easier to let Excel do it for you. All you have to do is go through the physical cursor and keyboard steps needed to perform the command and Excel will record and translate them into the macro language.

Begin by clicking on Tools, Macro and then, from the drop-down menu, select Macro, Record New Macro (see screenshot below).

When you’re prompted to assign a name and description to the Macro, use the button label (Input-Sales-Data, for example). Note that macro names must be one word, so be sure to add dashes between the words. Then click on OK, which opens a Stop Recording toolbar option window, but do not click on the Stop Recording button until you reach your data target (see the screenshot below).

Now go through all the keyboard and mouse clicks needed to maneuver to the data target. Once there, click on Stop Recording. Now, to assign that new Macro to your first button, return to the contents page, right-click on the button, select the Assign Macro option, click on the macro name you created and on OK (see exhibit 3, below).

Follow the same steps for each command you want on the contents page.

Finally, set up a button in one of the worksheets and create a macro that returns you to the contents page (see screenshot below). This return button and macro command needs to be set up only once since you can then copy and paste it to all the other worksheets.

Now, no matter how complex a spreadsheet is, by adding a set of strategically located buttons and complementary macros you can instantly be taken to any location in the file. No more wasted time clicking from one worksheet to another searching for a piece of data or a specific place to enter new data.

James T. Severson, CPA, is utility accounting manager at Alliant Energy, Cedar Rapids, Iowa. His e-mail address is jimseverson@alliantenergy.com.