Q. I regularly prepare executive summaries of no more than 450 words. I gather the information, in bits and pieces, from a large master document. When I use the Word Count function in Word, I can't get a total count in one effort if the groups of words are not contiguous. It's a minor problem, but a frequent irritation. Is there a solution?
A. There is, and it's simple. After you select the first block of words, hold down the Ctrl key as you use the mouse to select each subsequent block.
Since you have to use Word Count often, I suggest you put it in your toolbar. You'll find it by going to Tools, Customize and the Commands tab; it's under the Tools category.
Tip: When the Ctrl key is added to a shortcut, it often serves the purpose of allowing a user to select a series of noncontiguous items, as it does in this screenshot where a group of noncontiguous folders are selected in Explorer.
A FASTER WAY TO ROUND NUMBERS
Q. When I round financial data to the nearest half-dollar, I use the ROUND function, and while it works fine, a colleague told me the MROUND function is even better. But I can't find it in Excel. Can you help?
A. My bet is that you haven't loaded Analysis ToolPak, a collection of powerful functions that comes bundled with Excel but for some reason isn't automatically loaded when you set up Excel. To load it click on Tools, Templates and Add-Ins. Then place a check next to Analysis ToolPak and you're in business (see screenshot below).
If you don't find it under Add-Ins, you have to reload Excel from your original Microsoft Office disk.
When using the ROUND function, you can round to the nearest half-dollar with either of these formulas:
=ROUND(A1/0.5,0)*0.5 or =ROUND(A1*2,0)/2.
The MROUND function uses a shorter formula:
LOCATE FUNCTIONS IN EXCEL
Q. I know Excel contains numerous functions. Short of getting a PhD in Excel, is there some practical way to discover them?
A. One of the best things Microsoft did in designing Excel was create Insert Function, which does just what you're seeking. It not only helps you locate an Excel function you're aware of; it also helps you discover a function you don't even know exists. Go to Insert in the toolbar and click on Function to generate this screen.
If you aren't aware of an Excel function that performs a calculation you need, type a brief description of what you want to do under Search for a function. That will trigger a list of functions that may fit the bill. Once you select one and you need to learn how to use it, click on Help on this function.
If you'd rather skip Help and get on with the calculation, click on OK and Insert Function will set up the arguments in the Function Arguments dialog. Just fill in the required data or ranges and Excel will automatically set up the function for you. It's almost like having a genie do your bidding.
SHADE ALTERNATING ROWS IN WORD TABLES
Q. I know I can use conditional formatting to shade every other row (or every nth row) of an Excel data table. But how do you do that in a Word table?
A. One way is to use the Table AutoFormat capabilities of Word. Start by creating your table as you normally would. Select the table design from the menu as shown in the screenshot below.
If you prefer the alternating colors in the columns instead, just keep clicking on the All table styles.
What makes the Table AutoFormat particularly handy is that it creates a "smart" table that automatically adjusts row shading or color even when you add new rows.
USE HIDDEN TEXT
Q. I want to be able to place some sensitive commentary in a memo to clients in a way that only my partners will be able to read, and I guess Hidden text is the obvious way to do it. However, I'm afraid to apply it because there may be subtleties in its use and I would hate for our commentaries to become public. Can you provide some guidance?
A. I can help, but a word of caution is in order: I think using Hidden text for such an application would be imprudent. Accidents happen, and you may just forget to change a default switch. That could be embarrassing. Even more likely, what if the client, for one reason or another, checks the Hidden text radio button on his or her own computer so that all the hidden text will show? As a general rule, I always leave that box checked just in case there is hidden text in a document.
As you've probably surmised by now, Hidden text has two controls. The first comes on when it's created, which is under Format, Font. Highlight the text you want hidden and place a check in the Hidden box.
If you want all hidden text to show in any document displayed on your computer, click on Tools, Options, the View tab and under the Formatting marks section place a check next to Hidden text.
The next thing you should know is how to find the hidden text if your computer is not defaulted to show it. The easiest way is with Find and Replace, which you evoke by pressing Ctrl+H. Once the Find and Replace screen opens, go to the bottom and click on the down arrow next to Format and then on the down arrow next to Font, which opens the Find Font screen. Place a check next to Hidden (see screenshot at left).
Click on Find Next and it will track down all the hidden text.
CLEAN UP DATA IMPORTED INTO EXCEL
Q. When I import data to a worksheet, I sometimes see a bunch of small rectangles in the cells. Although they don't seem to affect the calculations, I can't clean them out. Any ideas?
A. What you're seeing are nonprinting characters. You're right; they don't affect the data but they do look messy. Use Excel's CLEAN function to get rid of them.
If the nonprinting character is in cell A1, go to its adjacent cell, B1, and type CLEAN (A:L) and press Enter. Cell B1 will then display the text or data without the nonprinting characters.
CHOOSE AN OPTICAL MOUSE
Q. One of my colleagues--the kind of guy who buys all the latest electronic toys--told me I should junk my ordinary mouse and get a wireless optical one. I wisely listened to him when he advised me to go for Windows XP, but I get the feeling he's pushing the wireless optical mouse because he likes the rosy glow it produces (on the underside of the mouse, that is).
A. In my view, he's half-right. Wireless may be an option--I can take it or leave it--but the optical mouse is surely the only way to go, rosy glow or no rosy glow (see screenshot below). First of all, since optical mice have no moving parts (except for the top wheel), there's nothing to take apart and clean. As a result they track easier and they are more accurate than the conventional rolling ball mouse. As if that's not enough, remember the time you tried to use a mouse on an airplane and there was not enough room to move it around? Well, an optical mouse can track effectively on an airline seat's armrest.
A BETTER IDEA
In the February column (page 82) I suggested accessing the Zoom function in the toolbar to enlarge or shrink text in either Word or Excel. Reader Jim Cole, a CPA and the lead senior auditor in Florida's Auditor General's office, reminded me of an easier way to access and run Zoom. Hold down the Ctrl button and roll the mouse's scrolling wheel. Scrolling up zooms in and down zooms out.
Bonus: Not only does it adjust the size of the page, but if you shrink the text sufficiently, you can view more than one document page on a screen at a time (see screenshot).
Excel: Adding Shift to a key changes its function:
* Shift+Open becomes Save As.
* Shift+Save becomes Open.
* Shift+Print becomes Print Preview.
* Shift+Print Preview becomes Print.
* Shift+Sort Ascending becomes Sort Descending.
* Shift+Sort Descending becomes Sort Ascending.
* Shift+Underline becomes Double Underline.
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 shows commands and instructions users should type into the computer and the names of files.
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 email@example.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.
STANLEY ZAROWIN, a former JofA senior editor, is now a contributing editor to the magazine. His e-mail address is zarowin@mind spring.com.
|Printer friendly Cite/link Email Feedback|
|Publication:||Journal of Accountancy|
|Date:||Jul 1, 2005|
|Previous Article:||E-mail express: an easy way to send many customized messages.|
|Next Article:||Financial aid 101: planning opportunities for CPAs.|