Technology Q&A

Technology Q&A

Synchronize files between desktops and laptops…Hide and protect formulas in Excel…Get a 10-key calculator on your PC...Calculate length of service in years and months in Excel…Protect against snoops reading “erased” Track Changes and Comments…Save your special Excel formulas for easy access…Shortcuts.

BY STANLEY ZAROWIN

Key to Instructions
To help readers follow the instructions in this article, we used two different typefaces:

Boldface type is used to identify the names of icons, agendas and URLs.

Sans serif type shows commands and instructions users should type into the computer and the names of files.

SYNCHRONIZE FILES BETWEEN DESKTOPS AND LAPTOPS
Q. I use a desktop computer in my office and a lightweight laptop when I travel on business. It takes 30 minutes or so to synchronize files between the two machines, so I’m always afraid I’ll end up with a file that is not current. Is there some convenient way to automate the process?

A. You have several options. Windows’ built-in Briefcase feature (see icon at right) is designed to synchronize and update the files on each machine when you connect the two computers with a USB cable or use a removable disk to transport Briefcase from your desktop to your laptop. For more information go to http://support.microsoft.com/default.aspx?scid=kb;en-us;307885.

More robust programs are available from third-party vendors. One of the more popular, Laplink Gold (www.laplink.com), sells for $99.95; Save-N-Synch (www.peersoftware.com) costs $30; and ViceVersa offers a basic version for $29.95 and a pro version for $59.95. You can go to the vendors’ Web sites and download fully functional evaluation copies at no cost.

My favorite synchronization product, though, is Migo (www.4migo.com). It offers automatic synchronization and storage in a convenient package. It’s a thumb-size flash drive that you can easily carry on a keychain. When you connect it to a computer via a USB cable, it monitors the files of which you want to ensure you have the most current versions.

Migo comes in several configurations: A 2-gigabyte model sells for $429.95, 1-Gb for $334.95, 512-megabyte for $229.95 and 256-Mb for $139.95. There’s even the Migo wristwatch, which has a 512-Mg capacity and sells for $229.95.

HIDE AND PROTECT FORMULAS IN EXCEL
Q. When I circulate my statistical Excel worksheet to users outside my company, I need to protect the underlying confidential formulas but also keep the worksheet easy for users to enter their data. Any ideas?

A. Try using Excel’s Protection; it can hide underlying formulas and protect them from any attempted change.

Here’s how it works: Before you enable Protection be sure to format the affected cells (right-clicking opens the menu that includes Format Cells) so they display their results—not the underlying formula—in the format of your choosing. Then, while still in Format Cells, click on the Protection tab, check Locked and Hidden, and click on OK (see screenshot below).

Now go to the Excel toolbar and click on Tools, Protection, Protect Sheet (see screenshot below). Make sure to place a check next to Protect worksheet and contents of locked cells. The defaults in the menu under Allow all users of this worksheet to: are Select locked cells and Select unlocked cells. Check any other options you want and enter a password, which appears as dots.

Be aware that if you fail to enter a password and click on OK, the cell is still hidden but anyone can reverse the protection by going through the above routine and clicking on Unprotect Sheet.

GET A 10-KEY CALCULATOR ON YOUR PC
Q. CPAs once relied on bulky, but wonderful, 10-key calculators until limited desk space relegated them to the closet or, worse, the garbage. But now I find that the calculator built into Microsoft Windows is of little use. Is there a program that functions like a 10-key I can install on my PC?

A. I’ve had a 10-key program called PentaCalc for years. The vendor has since introduced a spiffed-up version called the PentaCalc Pro.

You can engage the PC calculator, which works with any Windows application, by clicking on a hot button no matter what you happen to be running on your computer. It comes with a huge bundle of options, including the ability to combine buttons from other calculators and even link several calculators. It’s programmable and costs just $39.95. For more information go to www.headgatestudios.com/products/pentacalc/features.htm.

The versatile PentaCalc Pro replicates a 10-key calculator.

CALCULATE LENGTH OF SERVICE IN YEARS AND MONTHS IN EXCEL
Q. Is there an easy way to calculate how long in years and months the employees have been working for my company?

A. Excel can do that, as long as you’ve listed all the employees in a database. If so, place them in an Excel worksheet with their start date in one cell and end date (which would be the current date, I assume) in an adjacent cell. Then insert this formula (where the start dates are in the D column and the end dates in the E column):

=DATEDIF(D2,E2,"y") & " years, " & DATEDIF(D2,E2,"ym") & " months ".

Here’s what it looks like in Excel (color added for clarity):

Be sure to format the D and E columns to display dates (see screenshot below).

PROTECT AGAINST SNOOPS READING “ERASED” TRACK CHANGES AND COMMENTS
Q. I recently read about someone who sent a client a sensitive Word document that had been edited many times using Track Changes and Comments. Even though the changes had been erased before the memo was sent, the recipient was able to recover them and read every comment. That’s very scary. How can I prevent it?

A. Yes, it is very scary—especially since the client was even able to see the names of the individuals who made the changes and comments. However, there are steps you can take to be sure that your erasures remain permanent.

If you don’t know how the Track Changes tool works, look at the screenshot below in which I enabled the function by clicking on Tools, Track Changes and typing a change in the text (yes, the story is true). Then I added a comment (I’m relieved to learn I can permanently delete erasures). Next, after reviewing the change and my comment (see screenshot below), I either approved or rejected them and then closed the tool with the expectation that my changes and comments were permanently erased.

The good news is that Microsoft has an add-on program (Remove Hidden Data) that can permanently remove hidden and collaboration data in Word, Excel and PowerPoint. To download the program go to http://office.microsoft.com/officeupdate/category.aspx?CategoryID=CD010225731033&CTT=4&Origin=C.

As a practical matter don’t run Remove Hidden Data until you’re ready to publish or send the sensitive file to its intended recipient because it will permanently erase all collaboration material. To engage it, open the target file and click on File and Remove Hidden Data. That will evoke the screen below, which will tell you to enter a file name for the new, cleaned-up version of your original file.

In the unlikely event the Remove Hidden Data command is missing in the drop-down menu, under File click on Tools, Options and the Security tab. Under Macro Security, click on Macro Security, Trusted Publishers and check the Trust all installed add-ins and templates and then click twice on OK.

SAVE YOUR SPECIAL EXCEL FORMULAS FOR EASY ACCESS
Q. I develop lots of special formulas for different spreadsheet files. I’d like to store them in a workbook and call them up when needed. I know I can do that with macros, but I find the language they are written in (Visual Basic) confusing and hard to use. I want to keep it simple.

A. I’ll show you an easy way, but you really should not be resistant to macros; they are very powerful tools.

To save and store a formula, begin by typing it into a cell (for example, =A1+B1). Now press F2, which puts Excel in the edit mode. Hold down the Shift key, highlight the formula (see screenshot below) and copy (Ctrl+C) it to the Clipboard.

Now get out of the edit mode by pressing Esc, go to the toolbar and click on Insert and Name, evoking the Define Name screen. In the space next to Names in workbook, type in a name for your formula—add_it. Note that the name must be one word, so if you use more, connect them with an underline dash (_).

Place your cursor in the box below Refers to. It will contain a reference to the location of the formula; ignore it and paste (Ctrl+V) your formula (which is still in the Clipboard) into the box (see screenshot below). Click on OK.

Whenever you want to use the formula, place your cursor in the cell where you want the formula to appear and type an equal sign plus the name you gave to the formula (=add_it). It will be copied into the cell and ready to work.

SHORTCUTS

Excel: To easily add noncontiguous numbers, place your cursor in the cell you want the sum to appear, click on AutoSum (), and while you’re holding down the Ctrl key click on each of the cells you want to include. When finished, return to the cell where you want the sum to appear and press Enter. Note that Excel has “written” the formula in the Formula bar just below taskbar.

Word: To select…
…a single word: Double-click on the word.
…an entire paragraph: Triple-click anywhere inside the paragraph or double-click on the left margin.
…a sentence: Hold down Ctrl and click on the sentence.
…a line (from the left to right margin): Single-click on the left margin.
…an entire document: Triple-click on the left margin or press Ctrl+A. .

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

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to contributing editor Stanley Zarowin via e-mail at zarowin@mindspring.com or regular mail at the Journal of Accountancy, 201 Plaza Three, Harborside Financial Center, Jersey City, NJ 07311-3881.

Because of the volume of mail, we regret we cannot individually answer submitted questions. However, if a reader’s question has broad interest, we will answer it in a Technology Q&A column.

On occasion you may find you cannot implement a function I describe in this column. More often than not it’s because not all functions work in every operating system or application. I try to test everything in the 2000 and XP editions of Windows and Office. It’s virtually impossible to test them in all editions and it’s equally difficult to find out which editions are incompatible with a function. I apologize for the inconvenience.