If 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.
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.
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
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,
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
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
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