Journal of Accountancy

Technology

Get the Red Out of Negative Spreadsheet Numbers

By Stanley Zarowin

July 2002

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 indicates commands and instructions that users should type into the computer and the names of files.

Q. All of my spreadsheets display negative numbers in red rather than preceded by a negative sign. I don’t mind the red on the screen, but when I print a worksheet, those negative numbers come out a light gray because I use a black-and-white laser printer. I know I can format a cell to appear with the negative sign rather than in red or I can go to Page Setup (under the File menu) and change to black-and-white printing, but is there a way to change the default so I won’t have to make these changes every time?

A. There certainly is. Before showing you how, let’s take a moment to demonstrate how to format an individual cell or a group of selected cells to display a negative number in red or with a minus sign.

With the target cell(s) highlighted, click on Format, Cells (or right-click, Format Cells ). That produces the Format Cells screen (see right).

Notice your options: You can select the number with a minus sign, in red, in parentheses, or in parentheses in red.

If you want to extend the format change to default to the style of your choice, click on Format, Style . That brings up the Style screen (see below).

To change the appearance of a number, click on Modify , which brings up the same Format Cells screen as before—only now, any change you make will become a default style. Notice the Style name menu at the top of the screen. Clicking on the down-pointing arrow displays all your existing style categories. The first name in the list is Normal . If you want the change to be evoked in the Normal style (that’s Excel’s opening default style) make the changes there. Or you can create a unique style and give it a unique name—say, My Favorite.

Also, notice that you can combine several elements to a style by clicking on Add . Or, if you already have a named style, you can merge this style choice into it by clicking on Merge .

Now that you know how to format negative numbers, let’s hope you don’t have many of them in your future accounting reports.

Technology

Get an Ampersand into an Excel Header

By Stanley Zarowin

July 2002

Q. The name of my firm is Jones & Jones. But when I try to put it in an Excel header, it comes out Jones Jones—no ampersand. How can I add the ampersand?

A. I know it sounds like a silly omission on the part of Microsoft. Actually, it has something to do with the fact that Excel uses the ampersand in its command code. But there is a way to get around the problem. If you put two ampersands, one after the other with no space between them, you can fool Excel to print just one.

For those who don’t know how to set up a header in Excel, follow these steps. After creating the spreadsheet, go to File, Page Setup, Header/Footer tab and then click on Custom Header and in the right, center or left box type Jones && Jones.

You can format the name as you wish with the format A button right above the three sections. When done, click on OK .

To check that you did it correctly, go to File, Print Preview . Jones & Jones should be at the top of the spreadsheet.

Technology

Increase Clipboard’s Memory

By Stanley Zarowin

July 2002

Q. I read that the latest version of Microsoft Word contains a very sophisticated Clipboard that allows users to copy many different parts of a document and then selectively paste them into another document. I wish I could do that with my older version of Word, in which I can make only one selection for the Clipboard . Do I have to upgrade to get that one feature or do you know some way to do it in my old version?

A. You’re lucky. There is a way, and I must confess I learned of it only after I upgraded because I, too, had wished I could make multiple selections in Clipboard .

Before showing you the trick, let me demonstrate how it works in Word 2000 and later, and maybe you will want to upgrade.

For illustration, I copied the first few words of your question by highlighting them, pressing Ctrl+C and then doing the same thing several more times with a few more words.

After the second Ctrl+C, the Clipboard popped up on the right side of my screen with the copied words; and each subsequent group of copied words kept being added to that list (see left).

When you’re ready to copy a selection to a different part of your document or a new document, all you have to do is click on a selection and it gets copied to the last place you put your cursor. You have to agree that’s pretty neat.

Now, if you still aren’t convinced the super Clipboard is worth the upgrade, here’s the trick for doing almost, but not quite, the same thing in earlier versions of Word. The function is called the Spike—named after the old-fashioned paper holder. But like the Spike and unlike the upgraded Clipboard , the words you gather are not copied ; instead, they are cut and then pasted into your target document.

To launch the Spike, begin by highlighting each snippet of text you want to cut and paste and press Ctrl+F3 after each selection. You can do that up to 12 times, and Word will hold all your selections in memory; but it will not display the contents as you go along as does the upgraded Clipboard .

When you are ready to paste the information into another document, press Ctrl+Shift+F3, and all the accumulated information—not just the first or last selected snippet in the Spike—will be pasted in the document at the point where your cursor is situated.

However, that action also will erase everything in the Spike. If you want to retain the contents in the Spike for multiple pastings, type the word spike and then press F3.

Now, you have to admit, that’s not bad either.

Technology

Expand Spreadsheet’s Find and Replace to Include Entire Workbook

By Stanley Zarowin

July 2002

Q. The Find and Replace tool in Excel is very convenient. But it would be far better if it could search beyond the one worksheet I happen to be in. After all, I would guess that most workbooks CPAs use contain many sheets, so a search that encompasses an entire workbook would be very helpful. Is there some trick to accomplishing that?

A. It’s not so much a trick as simply adapting Excel’s Group feature—a tool of many uses. The idea is to gather the target worksheets into a custom “group.” Once you’ve gathered the worksheets, every command of Find and Replace affects each sheet in the group.

To create a group, open your workbook and select the sheets by holding down the Shift key and then clicking on each tab you want included. To include all the sheets, click the first sheet tab, hold down the Shift key and then click on the last tab.

Notice the tabs for the group of worksheets now have white backgrounds; the unselected remain colored. Now I’ll evoke Find and Replace from the Edit menu (shortcut: Ctrl-H) and search for the word hotel. In the screenshot below the screen shows all the selected tabs and invites you to meander through the choices under the same search command for the word hotel.

And, presto, the three sheets that contain the word are instantly identified.

Technology

Get an Ampersand into an Excel Header

By Stanley Zarowin

July 2002

Q. The name of my firm is Jones & Jones. But when I try to put it in an Excel header, it comes out Jones Jones—no ampersand. How can I add the ampersand?

You can format the name as you wish with the format A button right above the three sections. When done, click on OK .

To check that you did it correctly, go to File, Print Preview . Jones & Jones should be at the top of the spreadsheet.

Technology

Back Up Your Internet Explorer Favorites

By Stanley Zarowin

July 2002

Q. I always back up all my files. However, I can’t figure out a way to back up my Internet Explorer Favorites list. And that worries me because it’s taken me years to collect all those url addresses and I’d hate to lose them. In fact, when I upgrade the operating system, I want to be sure they are safe. Any ideas?

A. Yes, there is a way to back them up and it’s quite easy. Follow these steps: Open Internet Explorer and click on File and then on Import and Export . That brings up the Import/Export Wizard (see right). The export command, in this case, means you will be copying your favorite Internet addresses and exporting them to a new location, which can be on your hard disk or a removable disk.

Then click on Next , which gives you a choice of importing or exporting either Favorites or Cookies (see below). Select Export Favorites and click on Next .

That, in turn, evokes a third wizard screen, which asks you which Favorites you want to export (see below).

Highlight the folder that contains your favorites (or the individual favorite). In this example, you can see that I gather my favorites in folders sorted by category. You can export only one folder or individual favorite at a time; to export all requires that you repeat the process for each folder. Then click on Next , and that brings up a fourth screen, which asks you where you want to save the exported folder.

If you don’t like the default location, either change it in the address box or click on Browse and pick a new address.

Do you have a technology question for this column? Or, after reading an answer, do you have a better solution? Send them to Senior Editor Stanley Zarowin via e-mail at zarowin@mindspring.com . We regret that we cannot answer letters individually. If a reader’s question is deemed to have sufficiently broad interest, we will answer it in a forthcoming Technology Q&A column.

—The editors

View Comments |