The Best of Technology Q&A

ADD HEADERS OR FOOTERS TO SPREADSHEETS
Almost every week a reader asks how to add a header or a footer that contains the file’s name and path to an Excel spreadsheet. Early Excel editions had no built-in tool for doing this, and users kept urging Microsoft to add one. The software company finally responded and added such a tool in editions beginning with Excel 2000.

Still, all is not lost for those CPAs who have not upgraded to a newer application; there’s a way to insert a “faux” header or footer that even displays a file’s name and path in every workbook page. But unlike a real header/footer, which appears in the page’s margin, you can add the faux one only in an unused cell.

For those with Excel 2000 and later editions, here are the steps to create a header/footer: Go to File , Page Setup and then click on the Header/Footer tab. The rest is intuitive—so just follow the screen instructions…

and the header will look like this:

For those working in earlier editions, here’s how to add a faux header/footer: Begin by selecting a cell position where you want to place the header/footer—probably at the top or bottom of a page—and type =cell(“filename”) in that cell. Excel will translate the code “filename” and place the full path and file name in the cell when it’s printed, as illustrated in the screenshot below.

If you want the printed spreadsheet to display that header or footer on every page of the file, click on File , Page Setup and select the Sheet tab. Excel will copy the code and insert it in the box adjacent to Rows to repeat on top , as shown below.

And this is what a faux header looks like:

While researching this subject we came across a free add-on utility from J-Walk that lets you create real headers/footers in pre-2000 versions of Excel. To download it go to www.j-walk.com/ss/excel/files/addpath.htm .

VIEW MULTIPLE COPIES OF A SPREADSHEET AND DOCUMENT
Readers frequently ask whether there is a way to place multiple copies of a spreadsheet or document on the screen and view them at the same time. The solution is right in your toolbar.

Open your target spreadsheet or Word document and click on Windows , New Window , which opens a duplicate of the target file; or, if you wish, open a different file. If you keep clicking on Windows , you have the option of opening as many copies as you wish—limited only by your ability to view them on your screen.

Now that you have multiple files open, you can arrange the screens for convenient viewing. Click again on Windows and this time on Arrange , launching the Arrange Windows screen, which gives you choices on how you want the separate views displayed— Tiled , Horizontal , Vertical or Cascade .

In each of the Arrange views, you have the option of scrolling independently. Thus, you can do this:

If all you want to do is compare two worksheets side by side, click on Windows again and then on Compare Side by Side with .

The bottom of that screen shows three available views: Stanley Two Budget and two copies of Stanley budget . Click on your choice.

Unlike the copies shown by clicking on Arrange , each of which can be scrolled independently, the views produced by Compare Side by Side move in tandem once you’ve established a view for each worksheet.

After you have the screens lined up to your satisfaction, you can save the arrangement for later viewing by clicking on View , Custom Views . When you want to switch back to the Arrange view, click on the Close Side by Side screen that floats on the screen.

PROTECT AGAINST INEVITABLE POWER LOSS
Many readers ask what they can do to protect their computers against power outages, brownouts or spikes (sudden, short increases in line voltage). It’s a major problem, and one that should not be ignored.

Even if you’re in a big city with a dependable electric utility, power failures happen: Recall the summer 2003 blackout that dimmed much of the Northeast. Connecting your computer to an uninterruptible power supply (UPS)—which is a battery system that automatically and instantly kicks in the moment power is interrupted or spikes—will save all your open files and then close down the computer in a normal way.

Lacking a UPS, a power failure or spike can crash your computer, causing the electronic sensor arm that hovers above the spinning hard disk to come crashing down, destroying the data or even the disk itself.

A UPS device to protect one computer costs less than $100—pretty cheap insurance for your valuable data.

SAFEGUARD A DOCUMENT FROM CHANGES
I’m often asked about privacy—mostly about ways to protect a file from unauthorized changes.

The simplest way to protect a file is to format it as a read-only file. As its name implies, a read-only document can only be read; it can’t be altered. To change a file’s format, open Explorer , whose icon looks like a magnifying glass examining a file folder.

Highlight the file and right-click on it, bringing up a menu whose lower section resembles the screenshot below.

Click on Properties and the menu below will appear.

Place a check in the Read-only box, click on Apply and then on OK . Now people can look at the file and even save a copy with a new name ( File, Save as ) but they can’t change the original.

Caveat: While this method protects the file from intrusion by a computer novice, anyone with some computer knowledge can simply go into Properties and remove the Read-only check.

Protecting against the savvier user requires a more powerful method, which is similar for both Word and Excel (although the screens and options are different).

In Excel, begin with the target file open and then click on Tools , Protection , evoking this screen:

Notice all the options on the right side of the screen. Click on the option that meets your needs; you’ll find the following screens that open, which include one for adding a password to protect the file, are intuitive, so just follow the directions.

Caveat: Once you create a password, don’t forget what it is or you won’t be able to open the file.

In Word, also begin by opening the file and then click on Tools , Protect Document. In pre-XP version, this screen is evoked:

In Word XP, the screen appears to the right of your document (see screenshot below).

Clicking in either of the two boxes triggers a series of options on how much protection you need. Eventually you will be invited to enter a password that will guard the document in the way you customized.

Another way to safeguard the text is to convert it to PDF format, which essentially is an image that users cannot edit. You can download free PDF software from www.pdf995.com .

TRANSPOSE A RANGE OF CELLS
You’ve probably experienced this nagging problem: You set up a regional sales spreadsheet, lay out the time period on one axis and the geographic regions on the other one. Then you spend an hour keying in the data. Oops! You just discovered the worksheet would look better if the X and Y axes were swapped. You can spend another hour transposing the data or you can use this shortcut that’s built into the Copy command.

Let’s say your spreadsheet looks like this:

Highlight all the cells you want transposed—from A1 to D3—and copy them (Ctrl+C). Now place your cursor where you want the transposed cells to go and right-click, evoking this screen:

Then click on Paste Special , which brings up this screen:

Now, place a check in the Transpose box at the bottom of the screen. When you click on OK , the table will look like this.

CUSTOMIZE YOUR TOOLBAR
Most CPAs use only a handful of favorite tools for each application. Yet each time they want to access them, they have to remember under which toolbar category they’re situated. Here’s a way to create your own personal toolbars—one for each Microsoft application. I’ll demonstrate how to do it in Word; the method is exactly the same in Excel and the other applications, except, of course, your menu selections will be different.

Start by clicking on Tools and Customize to bring up this screen:

On the Customize screen, click on the Toolbars tab and on New , which brings up the New Toolbar screen.

In the blank space under Toolbar name , type the name you want to identify the toolbar. That will generate a tiny toolbar on the screen that contains the first few letters of the toolbar name.

Using your mouse, drag the tiny toolbar to a convenient place, such as just above the regular toolbar on the top of the screen.

To load it with custom commands, again click on Tools and Customize , only this time click on the Commands tab. Drag and drop (holding down the mouse button) each of your favorite command icons up to the new toolbar one at a time. Release the mouse button when each icon is in place.

If you don’t want to display the custom toolbar on your screen, you can hide it. To do that go to View and click on Toolbars ; that will evoke a menu of available toolbars. Toggling on any adjacent toolbar box will either launch a toolbar or hide it.

GET EXCEL TO SPEAK TO YOU
Wouldn’t it be handy if you could get Excel to speak the numbers entered in a spreadsheet? That way you could verify the accuracy of the data. Well, it’s easy to get Excel to do it.

Excel has a built-in function that can speak both the numbers and the words in a spreadsheet. Of course, you must have speakers for your computer.

To evoke the speech function, click on Tools , Speech and Show Text to Speech Toolbar , bringing up this toolbar:

Notice there are five icons in the toolbar; each controls a different read-back function. To see what each does, pass your cursor over the icons. Starting at the left, the first (see screenshot below) orders Excel to read the numbers in the cell—hesitating a second or so between cells. If the cell contains a formula, it will not read the formula, just the resultant number, unless you press Ctrl+` (grave accent).

The second icon halts the process. The third and fourth icons control whether the automatic reading moves down a column or along a row. To program a cell to speak only after you press Enter, click on the fifth and final icon (see screenshot at below).

ACCESS THE 10-KEY CALCULATOR
Now that accountants have powerful computers on their desks, many have retired that old, reliable 10-key calculator. But wouldn’t it be nice every now and then to have a simple calculator handy—and without taking up desk space? There’s one in every PC, and it can be made always accessible by adding it to your desktop toolbar.

Open Explorer and find the calc.exe file—the icon looks like a small calculator.

It’s usually under C:WindowsSystem32 . Highlight it and right-click; then, in the screen that’s created, click on Create Shortcut . Then go to your Desktop and right-click again and click on Paste Shortcut . When the icon appears on your Desktop, drag it to the left side of your taskbar, which usually is on the bottom of your screen.

STANLEY ZAROWIN, a former JofA senior editor, is now a contributing editor to the magazine. His e-mail address is zarowin@mindspring.com .