n the four years the Technology
Q&A column has appeared, the JofA
has received more than 2,000 questions from
readers. For this article we’ve selected the best
questions—the ones that leave many readers
sputtering in frustration as they search for
solutions. In some cases we’ve updated the original
answers, describing new things we’ve discovered
since they first were published. In others we’ve
augmented solutions with helpful new suggestions and
alternatives from our readers.
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 . |