Technology Q&A

Graphics to protect spreadsheet cells…View all the Palm
applications…Unscrambling Word formatting when copying text…Identifying
those plastic doodads…An easy way to count the blanks…Lock the Format Painter…Quick worksheet jumping…Fill a range in Excel…Upgrade advice.

Q. I share several of my spreadsheets with colleagues. Each periodically enters data into various worksheets. Every now and then one of them accidentally enters data in the wrong cell, or, worse, in a misguided effort to improve one of my formulas, corrupts the entire worksheet. Do you have any ideas for protecting shared spreadsheets?

A. You’ve touched on a common and delicate issue because every office has someone who has just enough knowledge about computers to be dangerous to both himself and the office.

I’ve invited Justin D. Stein, a CPA with Arthur Andersen, to address this question because he frequently deals with it and he wrote an article that contains part of the solution (“Yes, You Can Program in VBA,” JofA, Nov.00, page 47).

Stein advises: We provide visual alerts to warn users when a cell in a worksheet or template is special and thus needs exceptional handling and protection. This includes cells that contain formulas, those that allow inputs (be they amortization tables or interest calculations) and those with any sort of problem that will require special treatments. At my firm, we’ve designed graphics for those cells that serve as visual alerts. The goal is to create visuals that are instantly understood by users without additional guidance.

The best way to design the graphics is with Visual Basic for Applications (VBA)—a process that’s described in my JofA article.

One design identifies a cell as an input cell—that is, users are invited to enter new data into it. We format the cell with enough color contrast to catch the users’ eyes. For instance, we’ll use a light background and a dark font (such as a very light yellow with a blue font). A typical input cell looks like this:

In addition, we unlock the input cell so users can change the value of cells formatted with this standard even though the worksheet may be protected. To unlock a cell, right-click on it and then click on Format Cells, which brings up the screen below.

I’ll describe the locking process later.

We use another design, also with a lot of contrast, to identify a cell as containing a formula—so don’t touch it. A typical formula cell looks like this:

This color design, by the way, is used by Conditional Formatting to warn users when two cells aren’t equal.

The design for our calculation cells usually is formatted simply as black text on a white background:

However, we protect these cells so they can’t be modified.

The design choice is up to you. There are no universally accepted designs.

After settling on formats for each type of cell you want to identify (for example, input, calculation, formula), create VBA routines that apply those formats to the cells of your choice. Assign each routine to a separate toolbar button. All these steps are described in my article.

Many times when designing complex models and what-if scenarios, you may find you need a separate worksheet in which you can maintain information you don’t necessarily want users to see.

For instance, if you are creating a file to calculate individual estimated income taxes, you will need a series of tables to calculate the tax based on taxable income and filing status. You need to maintain these tables within your file, but you don’t want users to change them. One way to accomplish this is to maintain the tables on a separate worksheet and, when you’re ready to release the file to users, hide it so they won’t be able to see it—although, of course, the software can “see” the tables and thus use them for calculations. You can hide worksheets by selecting the worksheet and clicking on Format, Sheet, Hide. It’s a good idea to protect all the cells on this type of worksheet since users can easily unhide this worksheet by choosing the hidden sheet from Format, Sheet, Unhide.

Once you’ve formatted your worksheets using these techniques, it’s time to protect them. Click on Tools, Protection, Protect Sheet and be sure that all check boxes are selected.

If you use a password to protect a worksheet, be sure you save it somewhere safe but accessible to your coworkers.

When a worksheet is protected, users can modify only cells that are specifically marked as not locked. You can unlock a cell by clearing the Locked check box in the Protection tab of the Format Cells dialog box. This means that users can’t delete, change, paste or otherwise corrupt any of those cells. A cell is locked only when the worksheet on which it is situated is locked. If a cell is locked but the worksheet isn’t, users may still modify its data.

Q. I love my Palm so much that I’ve loaded it with lots of applications—so many, in fact, that I can’t get all the icons to fit on one screen. What do I do?

A. Tap the menu button on the lower left corner of your screen. Then tap Options, Preferences, and when the View by screen comes up, switch from Icon to List mode. Now you’ll be able to show as many as 20 applications on your screen.

Q. If there’s one thing about Word I hate, it’s the perverse way it “copies” text from one document to another. For example, if the text in the original document is formatted in 12 point Times New Roman and I copy it into another document that’s formatted differently, I’m never really sure how the copied text will look. How can I get it to copy the text the way I want it?

A. I’m happy to report the solution to your problem is not so difficult that you’ll want to give up Word. The trick—and it’s such a strange solution that it does appear to be a trick—is to understand that in Word, text formatting is tied to the paragraph symbol (). In other words, Word will automatically pick up the formatting.… Ah, I can almost hear you saying to yourself, “The paragraph symbol! What paragraph symbol?”

Okay, let’s back up and I’ll explain. Check your toolbar and see if the paragraph symbol () is there. If it’s not, add it. Here’s how: Go to Tools, Customize and click on the Commands tab and highlight View. You’ll see Show all. Using your mouse, drag the symbol into your toolbar. Now, if you click on it, icons will appear everywhere there’s a paragraph break (in addition, little dots, representing spaces, will appear between words).

Here are the rules for copying formatting:

If the selection you highlight for copying doesn’t include a icon, then Word copies only the character style and any additional formats applied to the highlighted section.

If you copy only a single icon, then Word copies only the paragraph style stored in the icon.

If the selection you highlight for copying includes a icon, then Word copies the paragraph style and any additional paragraph formats applied to the paragraph, plus the character style and any additional character formats applied to the selection.

To get rid of those annoying when you’re finished copying, just click again on the icon in the toolbar.

Sometimes, however, a document has such complex formatting, it’s easier to eliminate the formatting entirely when you copy it. Here’s how to do that: Highlight the text you want to copy and right-click and click on Copy. Then, go to where you want to paste the text and instead of right-clicking again and clicking on Paste, go to the toolbar and click on Edit, Paste Special and highlight Unformatted Text and click OK.

The copied text will lose all the former formatting and it will appear only with your computer’s single default format.

Q. One of my colleagues just got a new desktop computer, and I’ve noticed two little plastic doodads on the monitor cable. The installation booklet didn’t identify them, and she was about to pull them off, thinking they are like those Do Not Remove tags on new mattresses or maybe were moisture-absorbing packaging thingamajigs. But I suggested leaving them, on the theory that there must have been a reason for installing them. Was I right? And what do they do?

A. Yes, they’re there for a reason. They’re called ferrite chokes, and they’re designed to filter and reduce any radio-frequency interference that radiates from the system. They reduce the likelihood that interference will slow down the speed of your modem or local area network connection. If you don’t have them on your cable, it’s probably wise to pick up replacements. You can get them at any electronics store for about $5 a pair.

Q. I frequently have to count the number of empty cells in a spreadsheet. Do I need to put some identification mark in those cells or can Excel just count the blanks?

A. There’s no need to specially designate empty cells. Excel has a special formula that counts the blanks. It’s =COUNTBLANK(RANGE).

So, if you want to count the blanks, say, between cells A2 and A6, the formula is =COUNTBLANK(A2:A6). But be careful, if there’s a zero or a formula in a cell, it won’t count that cell as a blank.

Q. Although my Windows 98 operating system and Office 98 are working fine, I was about to follow your recommendation (in “Facing the Future,” JofA, Apr.01, page 26) and upgrade to the 2000 versions. However, I just heard Microsoft has announced another upgrade for both this year. What do you think I should do?

A. I was aware of the planned upgrades—to Windows XP (the new operating system) and Office XP—when I wrote the article. But generally I believe it’s prudent to wait a few months after any new software version is introduced (or at least until the first bug fixes are available) before taking the plunge.

Having just returned from a Microsoft developers conference where I had the opportunity to torture test both programs, I’ve changed my mind for several reasons.

Microsoft has added some components to both Windows XP (for eXPerience) and Office XP that make them easy to use and, even more important, very productive.

My favorite is Smart Tags, which is in Word, Excel and Outlook. It anticipates your next action, pops up a menu of choices (which you can customize) and then performs the action you choose. For example, if you’re writing a letter or e-mail and type in a person’s or company’s name, Smart Tags asks if you want it to seek out the address (or the company’s latest earnings’ report or stock price) by searching your computer, your network or the entire Internet for the information. Once Smart Tags finds the information, it copies it into the document.

Up until this latest version, every Windows operating system has been saddled with DOS operating system components in order to run those old applications. Maintaining that compatibility made Windows more vulnerable to crashes. DOS is now gone entirely and, in the event Windows XP does crash, the software automatically safeguards the file you were working on (up to the keystroke that triggered the crash). Then, when you reboot, it reassembles the events that led up to the crash and gives you the option, with just one keystroke, to e-mail the information to Microsoft so its engineers can find a way to fix the bug.

By the time you read this, Office XP will be out. The new operating system is scheduled to be available before yearend.