To help readers follow the instructions in this article, we use two different typefaces.
Boldface type identifies the names of icons, agendas, URLs and application commands.
Sans serif type indicates instructions and commands that users should type and file names.
Q. I want to format my spreadsheets to show negative numbers in two different ways: in red when I view them on the screen and, because I don't use a color printer, in parentheses when I print them. How do I do this?
A. Excel provides a wide assortment of formatting possibilities for numbers. I'll show you how they work so you can set up any combination you wish.
First, highlight the cells containing the numbers you want to format and then go to Format (right-click and then select Format Cells), and you will get this screen (see below).
The Category box contains a wide assortment of formatting choices. If none of them suits your needs, click on Custom at the bottom of the list. That will give you many possibilities--plus the option of creating your own format.
Here's how to create custom formats: Under the Type box, scroll down the list of choices. Notice that some lines have several formats, each separated by a semicolon.
If you click on such a line, as shown above, the entire line appears at the top of the Type box--semicolon and all. In the example above, that combination of code tells Excel to put the comma in for thousands (1,234); it also sets negative numbers in both red and with parentheses.
You can change the code in many ways--adding colors and conditionals. Whatever you enter automatically is added to the Custom category and can be used anywhere within the workbook.
For some reason--or maybe for no good reason at all--Excel hides a good tutorial on customizing numbers. To find it, go to Help (F1), click on the Index tab and under Type keywords, enter format. Then scroll down the drop-down list until you come to Create or delete a custom format number.
In the meantime, here are some frequently used codes:
Category Form Value Displayed as Number #,##0.00 .01 0.01 0.1 0.10 1234 1,234.00 -5678 -5,678.00 Percentage 0.00% .01 1.00% 0.1 10.00% 1.234 123.40% -5.678 -567.80% Currency $#,##0.00_); .01 $0.10 [Red] ($#,##0.00) 0.1 $0.10 1234 $1,234.00 -5678 ($5,678.00)
Likewise you can format fractions, too.
Be aware that no matter which format you use to display numbers, you aren't affecting the accuracy of the number. Internally, Excel maintains all numbers to 15 places. So if a number is displayed using two decimal places, Excel still maintains it internally to 15 decimal places, and it calculates using the internal representation.
Q. Is there an easy way to move whole paragraphs to different places in a document without going through the whole highlighting process?
A. There is, and it's a real time-saver. In fact, I don't think it's documented in any of the Microsoft help files--or at least I haven't found it.
All you have to do is place your cursor in the paragraph you want to move, hold down both the Shift and Alt keys and press the up arrow to move it up or the down arrow to move it down.
Q. How can I print a worksheet name in my spreadsheets?
A. You may not realize it, but you can generate very customized headers and footers in Excel. Let's tackle the easy one first. Open a worksheet and, on the toolbar, click on File, Page Setup and then the Header/Footer tab.
Excel provides a bunch of default headers and footers. To see them, click on the down arrow at the right edge of the Header or Footer box.
If none of them meets your needs, click on either Custom Header or Custom Footer, which brings up this screen:
By placing your cursor in any of the boxes (Left section, Center section, Right section) and clicking on any of the icons, you can add the appropriate header or footer to that section of the spreadsheet.
For example, let's say I want to add the page number on the left, a graphic in the middle and the date on the right. The setup screen then looks like this:
And when I print the pages, it looks like this:
If none of the defaults meets your needs, here are some command codes you can manually add--by typing an ampersand (&) followed by the command (see below)--in any of the section boxes.
Code Meaning &D Current date &T Current time &F Workbook name &A Worksheet name (from the worksheet tab) &P Current page number &P+x Current page number plus x &P-x Current page number minus x &N Total pages in the workbook && Ampersand character
Q. I like the tip you once gave about removing character formatting from an entire Word document by highlighting the document and pressing Ctrl+ Spacebar. But what if I don't want to remove all the formatting--just the formatting from one paragraph? It's a problem that comes up often when I try to untangle paragraphs with bullets or numbering.
A. Right on! Those bullets and numbers can be a major pain. Here's an undocumented (or well-hidden) two-finger solution. Just highlight the paragraph and press Ctrl+Q.
A BETTER WAY
* The URL I gave you in May 2003 (page 73) for guidance in using the Ruler in Word has since evaporated into the Internet ether, and reader George Salley, a retired CPA, suggests this one: http://www.mvps.org/ word/FAQs/Formatting/Using Rulers.htm.
* Also in the May issue, I gave you a tip (page 72)for shutting down programs running in the background that may interfere with a defragmentation. Reader Helen Donnell, CPA, CMA, of Orlando, Florida, suggests a much better way to close down interfering programs: Use the infamous "three-finger salute" (Ctrl+Alt+ Delete). In the "old" days, those key strokes shut down Windows, but in Windows editions since 2000, Ctrl+Alt+ Delete evokes a screen that displays all the programs running in the background (see screenshot) and gives you the option of shutting each of them down.
To end a program, click on its name and then on End Task. When finished defragging, you'll need to reboot the computer to get rid of the task screen.
* Scroll mouse functions with Microsoft Internet Explorer 6:
* Return to previous page: Shift+scroll wheel down.
* Go forward: Shirk+scroll wheel up.
* Scroll mouse functions with Outlook:
* Decrease font size: Ctrl+scroll wheel up.
* Increase font size: Ctrl+scroll wheel down.
* Keyboard shortcuts that work with Favorites in Internet Explorer:
* To add the current page to your Favorites, hit Ctrl+D.
* To open the Organize Favorites dialog box, press Ctrl+B.
* To move a selected item in the Organize Favorites box up or down, highlight the item you want to move and press either the Alt+up or Alt+down arrow.
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 firstname.lastname@example.org.
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 forthcoming 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.
|Printer friendly Cite/link Email Feedback|
|Publication:||Journal of Accountancy|
|Date:||Aug 1, 2003|
|Previous Article:||Employer benevolent funds: helping needy workers after a disaster.|
|Next Article:||How to tame health care costs: there's no end in sight to annual premium hikes.|