Printer Friendly

Navigate speedily in excel data: click on a button to get to target information.

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.

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, at right).


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 at right).

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. Lett-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, at right).

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.

Return to Contents Page

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.

Key to Instructions

To help readers follow the instructions in this article, we use two different typefaces: Boldface type is used to identify the names of icons, agendas and URLs. Sans serif type shows the names of files and the names of commands and instructions that users should type into the computer.

James T. Severson, CPA, is utility accounting manager at Alliant Energy, Cedar Rapids, Iowa. His e-mail address is
COPYRIGHT 2007 American Institute of CPA's
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2007, Gale Group. All rights reserved. Gale Group is a Thomson Corporation Company.

Article Details
Printer friendly Cite/link Email Feedback
Author:Severson, James T.
Publication:Journal of Accountancy
Date:Jan 1, 2007
Previous Article:Sell financial products wisely: manage your risk in this new niche.
Next Article:Guidance for audit standards for nonissuers that took effect on or after December 15, 2006.

Related Articles
The power of spreadsheets.
How to link to Web data.
Getting the Oops! out of spreadsheets.
Spreadsheet, meet database - database, meet spreadsheet.
Vigilant spreadsheets: make data analysis fast and easy.
Show-and-tell in real time: link a spreadsheet to a PowerPoint slide for up-to-the-minute visuals.
Push-button spreadsheets: perform repetitive tasks with one mouse click. (Technology Workshop).
Boost profits with Excel: Solver calculates the most cost-effective and profitable product mix.
Collaborate on spreadsheets: how to share Excel files with others on your network.
Add even more muscle to "what-if" analyses: team Scenario Manager with Scenario PivotTable for a more powerful tool.

Terms of use | Privacy policy | Copyright © 2021 Farlex, Inc. | Feedback | For webmasters |