| ADD SOME FORMATTING TO YOUR EXCEL PRINTOUTS
Here is an Excel tip from reader Jacky R.:
"I like the fact that Excel doesn't print the grid lines unless you ask it to do so. However, there are times when a worksheet needs some lines to make it easier to read. In such a case, I just convert the data area to a table. Until recently, I wasn't aware that this particular feature existed in Excel--I thought it was a Word feature only.
"All you have to do is select the data you want to format and choose Format, AutoFormat. When the AutoFormat dialog box opens, select the type of format you want to apply, then click OK to close the dialog box and apply your formatting selection."
Yes, AutoFormat does a great job of sprucing up your worksheets. If you use a black-and-white laser printer, you should select one of the simpler black-and-white formats. Otherwise, your printout may look too dark and cluttered. Even if you have a color printer, some of the more colorful formats can prove difficult to read.
Thanks for the tip, Jacky.
ADD A WORDART BUTTON TO EXCEL
Reader Vernon N. sent the following Excel question:
"Is it possible to place a WordArt button into Excel's toolbar? I know
I can choose Insert, Picture, WordArt, but I use WordArt quite a bit
and would like to avoid all that."
You can indeed put a WordArt button into the Excel toolbar. Choose
View, Toolbars, Customize. When the Customize dialog box opens, click
the Commands tab. In the Categories list, click AutoShapes to select
it, then drag the WordArt icon from the dialog box's right pane to
your toolbar. Click Close to close the Customize dialog box.
Now you can open WordArt with a single click.
This method also works in Word for Windows 97.
HIDING DATA IN EXCEL WORKSHEETS
Here is a question from subscriber Pat M.:
"I once saw a tip on how to hide data in a particular cell in an Excel
worksheet. Do you know how to do this?"
Yes--we can help. Let's say you have some data in cell C5 you would
like to hide from the casual viewer. Click cell C5 to select it, then
choose Format, Cells. When the Format Cells dialog box opens, click
the Numbers tab (if necessary) and then select Custom from the
Category list. Now double-click the Type entry box and type three
semicolons:
;;;
Click OK to close the dialog box and accept your new formatting.
At this point, the data in cell C5 disappears. It's still there and
will work in calculations, but it isn't visible. If you need to check
the data, just click the blank cell and the contents appear in the
Formula entry box.
SAVING EXCEL MACROS
This Excel question comes from reader Tom N.:
"When you save a macro in Word, it gets saved in Normal.dot and is
available for all Word documents. On the other hand, when you save an
Excel macro, it only works in that worksheet. Is there a way to make
Excel macros available to all worksheets?"
You can create a worksheet to hold all the macros and have that
worksheet always run when you open Excel. To do this, run Windows
Explorer and go to c:\Program Files\Microsoft Office\Office\Xlstart
(assuming you used the default location when you installed Office).
Right-click in the right pane of Xlstart and choose New, Text
Document. Name your new document personal.xls and press Enter. When
asked if you want to change the extension, click Yes.
Now double-click your new file to open it in Excel. Next, choose
Window, Hide and then choose File, Exit. When asked if you want to
save the Personal macro workbook, click Yes. The next time you run
Excel, personal.xls will run but remain hidden.
To store your macros in personal.xls, choose Window, Unhide to unhide
personal.xls. After you finish storing your macros, choose Window,
Hide to hide personal.xls again.
SETTING THE NUMBER OF WORKSHEETS IN EXCEL
Reader Sandra C. has an Excel worksheet question: "I often end up needing more than three worksheets in a workbook. In fact, I almost always need at least five worksheets. I run Excel and then choose Insert, Worksheet until I have as many as I need. Is there any way to tell Excel to always open with five or more worksheets?"
Yes, there is. Run Excel and choose Tools, Options. When the Options dialog box opens, click the General tab. Now, locate the spin box labeled Sheets In New Workbook and set it to five or whatever number you want. Click OK to close the dialog box and save the new setting selections. The next time you run Excel, it will open with the number of worksheets you specified in Options.
CONCEPT DATA - THE MAKERS OF EXCEL RECOVERY
Got some REALLY IMPORTANT SPREADSHEETS... corrupted? Salvage your
data, use Excel Recovery 1.0! Tell a friend. Forward to helpdesk. Get
your FREE demo:
http://www.pcworld.com/r/tw/1%2C2061%2Ctw-cncptdata0928%2C00.html
AUTOMATIC LABELS IN EXCEL
Here is an Excel tip from reader Rosella M.:
"I have never seen this mentioned in a tip, so I decided to send it
in. Suppose you type some headings in column A. Then you place the
numbers to go with those headings in column B. Excel pairs the numbers
with the labels. For example, suppose you type
One
Two
Three
in A1, A2, and A3. Then, in B1, B2, and B3, you enter
21
22
23
Now, if you move to cell B5 and enter
=Two
Excel will display 22.
"This will work as long as you enter your equation in column B. If you
move to cell C5 and enter
=Two
the result is zero."
Thanks for the tip, Rosella.
DISPLAYING DAYS IN EXCEL
Here is an Excel question from reader Sharon F.:
"I use Excel 97 and have a question about the way Excel displays
dates. What I would like to do is have a column of dates in dd/mm/yy
format. In the column before this one, I would like to have Excel
display the days of the week. That is all I want in this column--just
the days of the week. Is there a way to do what I need?"
You can display the dates in just about any way you want in Excel. To
do what you need, select the entire column and choose Format, Cells.
When the Format Cells dialog box opens, click the Numbers tab. Under
Category, select Custom. Now locate a date format in the Type
list--any date format will do--and select it. Next, click in the Type
entry box and delete the current contents. Now enter
dddd
and click OK to close the dialog box and save your changes.
Since you already have your dates in another column (let's say the
dates are in B, with the first date in cell B1), you can add the dates
to your new column very quickly. Just go to cell A1 and type
=b1
and then grab the cell's handle and drag down. Excel will now display
all of your weekdays next to the appropriate dates.
EXCEL CALCULATION ACCURACY
Here is an Excel question from reader Wally N.:
"I have a question about Excel. I did a worksheet for our payroll that
will figure out wages, withholdings, and our tax deposit. In my
worksheet I have wages fixed. I just enter the hours, and it
calculates wages just fine. Then the trouble begins: I multiply the
wages by 0.062 for FICA and 0.0145 for Medicare.
"I have formatted the cells for currency, which rounds off to two
decimal places and displays as such. The problem is that the computer
is retaining the actual amount out to four or five decimal places, so
that when it adds the two numbers and prints them, the total is
incorrect. For example, Excel might add 35.68 and 24.32, then display
60.01 because it is really adding 35.6848 and 24.3239. How can I make
it add only the two decimal places?"
An easy way to solve the problem is to tell Excel to calculate based
on two places. Run Excel and open your worksheet. Choose Tools,
Options. When the Options dialog box opens, click the Calculation tab.
Under Workbook Options, select the check box labeled Precision As
Displayed. Click OK to close the dialog box and save your settings.
Note that choosing this option reduces workbook accuracy. If you
should need to use the workbook for anything that requires greater
accuracy, you'll need to deselect the Precision As Displayed check
box.
SAVING EXCEL'S TOOLBAR LAYOUT
This Excel question comes from reader Noel K.:
"I know that all the macro and toolbar information in Word is in
normal.dot. But where is the same information in Excel? I would like
to copy my macros and toolbar layout so I can copy it to another
computer, or recover it if necessary."
Excel doesn't operate the same way as Word. When you save a macro in
Word, you most likely save it in normal.dot to make it available to
all documents. Excel macros generally get saved in specific workbooks.
You can open each workbook and copy its macros to Notepad. Then you
could recover them at a later date. Regarding the toolbar, you'll just
have to re-create it at another computer or redo it after a computer
failure.
SPRUCING UP YOUR EXCEL CHART COLORS
Reader Lou A. sent in the following Excel chart tip:
"I recently found that I can color my Excel column charts as I wish. I
have used two-color fills in many of my charts and found this very
effective--especially when presenting the data in a worksheet. I
thought others might like to use this method."
If you would like to see how to make two-color bar charts, run Excel
and open a blank worksheet. In cells A1 through A5, enter
1
2
3
4
5
and in cells B1 through B5, enter
1.2
2.3
3.0
3.9
4.7
Now select all the filled cells and choose Insert, Chart. When the
Chart wizard opens, click Finish. Double-click one of your data sets
to open the Format Data Series dialog box. Click the Patterns tab if
necessary. Now click Fill Effects. When the Fill Effects dialog box
opens, click the Gradient tab.
Under Colors, select the radio button labeled Preset. Click the arrow
at the right side of the Preset Colors list box and select one of the
patterns. If you don't like any of the patterns, you can select the
Two Colors radio button and select your own colors. In either case,
once you've made your selection, click OK to close the dialog box.
Back in Format Data Series, click OK to close the dialog box and save
your selection.
You can repeat this procedure with the other set of columns if you
wish to recolor them both.
Thanks for the tip, Lou.
USING MIXED REFERENCES IN EXCEL WORKSHEETS
This Excel question comes to us from reader Warren C.:
"I have a question regarding how to use absolute and relative
references. I am currently developing a worksheet with a number (in
cell G30) that many calculations use. Is it possible to copy formulas
that reference this specific cell without having them reference all
cells as absolute?"
We assume that you want to copy formulas to other cells containing
that one absolute reference. To see how this works, go to cell G30 and
enter:
0.98
and press Enter. Now, in cells A1, A2, and A3, enter:
1
2
3
In cell B1, enter:
=a1*g30
and press Enter. If you now select B1 and drag it to B3, you'll get:
0.98
0
0
because your formulas as copied are referencing G31 and G32. However,
you can enter:
=a1*g$30
into cell B1. Now, when you drag down to copy the formula, you'll get:
0.98
1.96
2.94
and your formulas will be =a1*g$30, a2*g$30, and a3*g$30. So the
reference to column A is still relative, but the G30 reference is
absolute.
EXCEL NUMBER FORMAT SWITCH
In Microsoft Excel, entering a time with more than 60 minutes into a cell may change the cell to the General number format and give you a decimal. If you type 5:62, for example, the cell will pop up with 0.251389. Try entering THAT into your date book! The following versions are affected: Excel 97 for Windows; Excel for Windows 95, versions 7.0 and 7.0a; and Excel for Windows, versions 5.0, 5.0a, and 5.0c. Microsoft's work-around: Change the cell to the Time number format by highlighting the cell, going to Format, Cells, Number, and clicking Time in the Category list. Then click the time format you want in the Type list and choose OK.
ATTENTION JAQUELINE SUSANNE: ONCE IS PLENTY, THANKS
Say that you have an Excel 97 worksheet in which you've filled columns A and B from top to bottom--that is, from row 1 to row 65536--with numbers. As if creating this worksheet wasn't difficult (and may we add, monotonous) enough, you now want to add a formula into each row of column C that adds the number in the first row to the number in the second row. Now, you COULD type the formula +A1+B1 into cell C1 and then copy that formula to every remaining cell in the column. But there is a slightly faster way to insert the same formula in many cells: 1. Select the range into which you want to enter the same formula (in our example, all of column C). 2. Type the formula. 3. Press Ctrl + Enter (instead of Enter, as you normally would). Excel enters the formula into each selected cell. This trick saves just as much time when used over small areas as it would in our ridiculously exaggerated example.
PROTECT YOUR SPREADSHEETS AGAINST EVERYONE ELSE
Last time we explained how you can protect an Excel 97 workbook against accidental changes YOU might make to it (by opening it as a read-only file--right-click on the file name in the Open dialog box and select Open Read-Only). But YOU are the least of your problems (we assume, although we don't have access to your complete psychological profile). If you share a workbook that you'd rather not have changed with someone else, how can you encourage that person to open it as a read-only file?
No problem: You can set the file to display a "read-only recommendation" whenever someone opens it. Here's how: 1. Choose File + Save. 2. In the Save As dialog box, click on Options. 3. Select Read-Only Recommended. 4. Click on OK and then Save.
The next time someone attempts to open this workbook, Excel displays a box saying that the workbook "should be opened as read-only unless you need to save changes to it." Even better, it enables the person to open the file as read only simply by clicking on Yes. In other words, it's idiot-proof--a phrase which by no means reflects our assessment of your coworkers.
CHARTS BAR NONE
You have the inventive mind of Thomas Edison but the timing of a bent metronome. The night after you tried freezing juice in your ice cube trays, some other guy invented the Popsicle. Mere days after you perfected your tail-operated fly swatter for dogs, some mega-corporation came up with the flea collar. And the day you filed the patent for your pet rocks with fur? That's right--that's the day the Chia Head hit the market.
But this time, you're really onto something: A BETTER BAR CHART. Unlike the bars that bore people stiff every day, you're creating bars they won't be able to resist, bars with texture so lifelike that business people all over the world will want to reach out and grab them.
Sorry, looks like you're a dollar short again because Excel lets you add texture to chart bars and to pie slices and to other chart objects, too: 1. Right-click on a chart object, and choose Format Data Series. 2. Click on the Patterns tab. 3. Click on the Fill Effects button. 4. Click on the Texture tab. 5. Select a texture and click on OK; then click on OK again.
Excel fills the chart object with the texture you selected. And you're back to the drawing board.
SKIP A STEP
There's an old saying that goes something like this: Never run when you can walk, never walk when you can sit, never sit when you can lie down, and never just lie down when you can slip into a benign, temporary coma. If this kind of thinking sounds good to you, then pay attention because today we're going to show you how to open an Excel 97 workbook without first starting Excel! 1. Click on the Start button. 2. Choose Open Office Document. 3. Double-click on the Excel document you want to open.
The system automatically loads Excel for you and then opens the file in question, saving you valuable nano-calories along the way.
We're going to show you how to create a NEW Excel document without first starting Excel. 1. Click on the Start button.
2. Choose New Office Document. 3. In the dialog box that appears, click through the tabs until you find a template for the type of Excel document you want to create. 4. Double-click on the template icon. Excel opens automatically and then creates a new file, using the template you chose.
CHOOSY CHARTERS CHOOSE CHART DATA
Excel's automated Charting Wizard has a mind of its own--and that's usually a good thing. Place your cursor anywhere within a range of data that you want to chart, click on the Chart button, and the Chart Wizard automatically selects the entire range and guides you through creating the chart.
But what if you don't want to chart ALL the data in a range? No problem: Just let the chart Wizard know: 1. Select ONLY the data you want to chart. Be sure to include the appropriate column and row titles. Hold down the Ctrl key to select unconnected ranges of data. 2. Click on the Chart button.
The Wizard still guides you but includes only the data you selected in your chart.
ACCESS, EXCEL LINKING LIMITS
Links go only so far between Access and Excel. If you create a link table in Microsoft Access 97 that links to a worksheet in an Excel 97 for Windows workbook, be careful. If you change a value or values in the link table using Access, open the workbook in
Excel, AND if one or more formulas in the workbook refer to the changed values, you may get recalculation errors. Microsoft's
work-around: Open the workbook in Excel and press Ctrl-Alt-F9. This will recalculate all the workbook's values. However, if you
again make changes through Access, you'll need to go through the same ritual.
CLOSE EVERYTHING!
Once you start opening Excel workbooks . . . well, you can't help yourself. It starts innocently enough, say, with your daily inventory status workbook. Before long, you've got your individual supplier workbooks open, your order entry workbooks, your invoicing workbooks--and that's just the beginning, before you break out your lottery number tracking, college basketball pool, and bookmaker phone number workbooks.
In other words, before midday, your desktop is littered with workbooks. And the thought of closing them all, one by one, makes your mouse finger twitch with anticipated pain. Isn't there an easier way? Of course there is! (Do you think we would have launched into this spiel if there wasn't?) To close all your open Excel workbooks at one time, do the following: 1. Hold down the Shift key. 2. Choose File + Close All (the Close command changes to Close All when you hold down the Shift key).
Excel closes all your files at once (stopping only to prompt you to save changes, if you haven't already), which, we suppose, only encourages you to make an even bigger mess next time.
SPECIAL DATES Version 4.x, 95
You can do more with Excel's date formatting than you might think. Suppose you'd like a particular cell to show only the month and the day. Click the cell (to select it) then choose Format, Cells and select Custom. Double-click the entry that's in Type and press Delete to get rid of it. Now, under Type enter mmmm dd and then click OK. Let's say the date is 1/25/98. The cell will display January 25.
Suppose now that you'd like to show the day of the week, the month, and the day (numerical). Choose Format, Cells and select Custom again. This time, type dddd, mmmm dd to produce a display of Sunday, January 25. Click OK to close the dialog box and record the changes. If you want to add the year, go back to the Format Cells dialog box and type dddd, mmmm dd, yyy Now click OK. This time, the cell will display Sunday, January 25, 1998.
DELIGHTFUL, DELOVELY, DELIMITER Version 4.x, 95
There are times when you might need to use an Excel file in some program that doesn't use Excel files. If this happens to you, check to see what kind of files the program will handle. Many programs will read delimited ASCII files. This means that each Excel column must be separated by some character. It can be a Space, a Tab, or a comma.
Let's give this a try. Open a new worksheet and enter text and numbers into three or four columns. Perhaps something like this:
Column 1 Column 2 Column 3
1 4 6
2 5 7
3 8 9
This will do fine as an example. Choose File, Save As. Type in a name for your file and then click the arrow at the right side of the Save as Type list box. Scroll down to CSV (Comma Delimited)(*.csv) and click Save. You'll get a warning telling you that you're not saving the file as a standard Excel file. Tell it Yes, you want to do it. Now you can quit Excel if you like. When you do, you'll get another warning about saving a nonstandard file. Tell it that you do want to save it.
You can open NotePad now and then open the new file, YourName.csv. The file will appear in the form shown here:
Column 1,Column 2,column 3
1,4,6
2,5,7
3,8,9
At this point, you can try reading the new file into that choosy program. If your program requires tab- or space-delimited files, just select the appropriate option when you choose File, Save As.
JUST TAKING UP SPACE?
While all that food from the holidays still may have you struggling to minimize how much space YOU fill in the physical world, in today's tip we show you a way to maximize how much space you fill in the virtual world. More specifically, the topic today is how you can get the ultimate possible horizontal space for viewing your message list and preview pane in Outlook Express. The answer is actually quite obvious, although you may not think to do it: Hide the Outlook Bar, Folder List, and Folder Bar from view. (Don't worry, we also show you how you can get along without them at the end of the tip.) To hide these bars, do the following: 1. Choose View + Layout. 2. In the Basic section, click in each of the Outlook Bar, Folder List, and Folder Bar check boxes until each is empty. 3. Click on OK.
So now that you have all this great space, how do you get to those folders? Press Ctrl + Y and, in the Go To Folder dialog box, double-click on whatever folder you want to open.
There you go. You're taking up all the horizontal space you possibly can. Speaking of which, you may want to think about getting in a few extra post-holiday sit-ups this week.
WHEN IS CONTIGUOUS CONTIGUOUS?
Excel will let you select rows and columns of cells whether they be contiguous or noncontiguous. Let's look at how this works.
To check out Excel's selection options, first open a new sheet. Now start at cell A1 and type 1, 2, 3, 4, 5 moving downward (that is, enter numbers in cells A1, A2, A3, A4, and A5. Repeat those entries in cells D1 through D5 and cells E15 through E19.
Now use the mouse to select cells A1 through A5. Hold down Ctrl and use the mouse to select cells D1 through D5. Choose Edit, Copy. Go to cell F1 and press Ctrl-V. Excel will paste the values in the selected cells into cells F1 through F5 and G1 through G5. So there's one use for the technique--you can eliminate unnecessary spaces between entries.
Now use the mouse to select cells A1 through A5 again. Hold down Ctrl and select cells E15 through E19. If you now choose Edit, Copy, you'll get an error message telling you that you can't use the command on multiple selections. Obviously, this message isn't entirely accurate--we just used it on multiple selections. The message really means that you can't use Copy on multiple non-contiguous selections.
If you select columns that have spaces (cells) between them, Excel considers them contiguous if they are adjacent. For example, cells A1 through A5 and F1 through F5 are adjacent. However, cells A1 through A5 and F2 through F6 are not adjacent. Also, cells A1 through A5 and A15 through A19 are adjacent. You can select them and then choose Edit, Copy. Then you can move to a new location (perhaps H1) and press Ctrl-V (or choose Edit, Paste). This will paste all the numbers into cells H1 through H10.
WHAT THE ########### DOES ########### MEAN?
Sure, entering formulas can be a lot of work: making sure your cell references are correct, entering your functions accurately, checking and double-checking everything. But it's all worth it when you see the result--the number you've been waiting for--appear like magic the split second you press Enter.
Except when it doesn't. Because sometimes, no matter how perfect you're sure your formula is, it yields nothing but a string of "pound" signs, or ############.
Take heart: There's probably nothing wrong with your formula. Those pound signs mean that the number your formula has calculated is too large to fit in the current column. And you can resolve the problem with one double-click of your mouse. Just double-click the right edge of the heading of the column containing your formula. Excel instantly expands the column to accommodate your number--and display the hard-earned fruits of your formulaic labors.
HOW DO YOU COUNT THE DAYS? Version 4.x, 95
If you'd like to know how many days left until St. Patrick's day, run Excel and type ="03/17/98" - "01/15/98" ' or today's date into a cell. Excel will return the number of days (61) between the two dates. How many days until Christmas? Enter ="12/25/98" - "01/15/98" and you'll get 344 days. Time to think about shopping. The trick here is to remember to use the quotes. If you don't use quotes, you'll get some very strange results.
IT'S SUPPOSED TO BE AUTOSAVE, NOT AUTO SABOTAGE
If you're concerned about losing Excel data during a system crash, a sudden power outage, or a sudden attack from those invisible little people you just know are living in your office, you're probably using AutoSave--the Excel add-in that automatically saves the file you're working on every so many minutes. (If you're not using AutoSave, this tip won't make much sense--sorry.)
But like all conveniences, AutoSave does have a drawback: If you open a workbook to run a few what-if scenarios that you don't necessarily want to save, AutoSave--with all the best of intentions--may save them anyway.
So does this mean you bag AutoSave? Of course not--that would be panicking. Instead, set it to warn you before every save, by doing the following: 1. Choose Tools + AutoSave. (If you don't have an AutoSave on your Tools menu, it's because you haven't installed AutoSave.) 2. Select Prompt Before Saving. 3. Click OK.
From now on, before AutoSaving, Excel displays a dialog box asking your permission. Isn't that polite?
YOU CAN NEVER HAVE TOO MANY OPTIONS
Ask any millionaire what money does for him or her, and the answer is usually, "Options. Money gives me options." This is ridiculous, of course; it's the kind of thing millionaires' handlers have taught them to say, so that they don't seem like greedy jerks. We all know that 1) money gives them more stuff than you or I have, and 2) WE have options (not stock options, but options nonetheless), even if we don't have a million bucks.
Example? Well, we have options when it comes to selecting an entire Excel worksheet. As we've noted in earlier tips, you can select the entire sheet by either clicking the top left corner of the worksheet frame (where the column and row headings intersect) or pressing Ctrl + A. But--would you believe--there's still another way to do the job? Press Ctrl + Shift + spacebar. See? Who needs money?
A QUICK CONVERSION
The world has tried for years to settle on one system of measurement. But if you live in the United States, you'll find that you have to deal with more than one system. If your work requires you to publish figures in more than one system, you need Excel's Convert feature.
WHY BUY THE WHOLE PAPER JUST TO READ THE FUNNIES?
It's your masterpiece, the workbook that will make you famous: 256 pages of data and formulas, all summarized brilliantly on a single page. There's only one problem: Every time somebody wants a copy of the summary page, you waste half a ream of paper printing in the process. Out of a deep concern for the environment (and the certainty that you'll lose your mind if you have to blow another 15 minutes printing this workbook), you ask yourself: "Is there a better way?" Sure. You can print ANY single page of ANY Excel workbook, as follows: 1. Choose File + Print. 2. When the Print dialog box appears, look in the Print Range section; in the From box, type the number of the page you want to print. 3. In the To box, type the number of the page you want to print. 4. Click Print. Excel prints that page only. And you've just multiplied some lucky tree's life expectancy by 256.
TAKE OVER AN ENTIRE REGION WITH ONE KEYSTROKE
Here's the deal: You have a huge, huge, huge region of data in an Excel workbook--many columns wide by many, many rows tall--and you need to select it. Now you could do it with the mouse, but we all know what a royal pain in the keister that is--especially when the screen starts scrolling like crazy when you try to select beyond the current window contents. Try it this way instead: 1. Select ANY cell in the data region. 2. Press Ctrl + Shift + *. Excel selects the entire data region and, as an added convenience, doesn't move you anywhere! Now that's a lot easier.
PLEASE GET ON WITH YOUR SUMMATION
Last time, we explained how to quickly select an entire data region: Simply select one cell in the region and then press Ctrl + Shift + *. Well, in celebration of that age-old adage, "no tip is an island" (OK, we just made it up), today we show you how to combine instant data-region selection with another shortcut for--ta-dah--instant region summing: 1. Select any cell in the data region.
2. Press Ctrl + Shift + * to select the entire region. 3. Click the AutoSum button.
In general, this technique instantly sums the columns of your data region. However, if you've labeled a row or a column (or both) with the word "Total" or "Totals," it sums that row, or that column, or both.
A LITTLE REMINDER YOU CAN ADD TO EVERY MULTIPAGE EXCEL PRINTOUT
You've got a workbook with several long columns of data. You print it out. The first page makes perfect sense--there are the column titles, there is the data below them. Who could ask for more?
Turn to the second page, though, and it's like you've peeled back the wraps on an entirely new universe. You THINK those are your columns--they look familiar--but you have no way of knowing for certain, because the column titles are GONE! And if you think you're disoriented, just imagine how other people reading the printout might feel.
Next time, make reading your printout easier for everyone by including column titles on every page: 1. Choose File + Page Setup. 2. Click the Sheet tab. 3. Click the button next to the "Rows to Repeat at Top" box and select the row on your worksheet containing your column titles (if your sheet has a title OVER your column titles, you may want to select that row, too.) 4. Press Enter.
5. Click Print. The rows you selected appear at the top of every printed page.
WHAT'S GOING ON UNDER THERE?
Some of us, we're satisfied with superficial knowledge. We don't quite understand WHY the earth revolves around the sun, we just know that it does, and we're darned happy about it. But you--you can't leave well enough alone. You want the story behind the story. It's not enough to see the sausage--you want to see how it was made.
Same goes for your Excel workbooks. While the rest of the world is screaming "Show me the numbers," you want to see the formulas. Now, you could select every cell you think contains a formula, one at a time, and view the formula in the formula bar at the top of the Excel display (and this is just the tedious fate many of us superficial folk think you deserve). But the makers of Excel have more respect for your curiosity than we do and have given you a quick way to see all the formulas in your sheet: Press Ctrl + ` (the "lowercase" character on the ~ key). Excel displays all the formulas on your sheet, for your perusal. To switch back to superficial view, just press Ctrl + ` again.
MAKE A QUICK MOVE
In Excel, you can copy a group of cells by selecting them and then pressing Ctrl-C. This copies the cells to the Windows Clipboard. Now you can move to another cell range and press Ctrl-V to paste the cells to the new location.
If you prefer to move the group of cells, simply select them and press Ctrl-V at a new location.
In either case, you'll find that you can eliminate the Ctrl-V part of the paste operation by clicking into the first cell of your target range and pressing Enter.
This shortcut doesn't actually save any keystrokes, since you press Ctrl and V at the same time to make the paste. But simply pressing Enter has a certain elegance about iT.
TOWARD MORE COLORFUL BAR CHARTS
When you create a chart in Excel (or any other Office program) you may find the default chart colors a bit drab. Let's say you have three bars in a bar chart. By default, the bars will appear as light blue, plum (kinda purple), and pale yellow. Yuck!
Double-click the chart to select it. Now double-click one of the bars to open the Format Data Series dialog box. Click the Patterns tab. Now select a new color from the color menu. Click OK, and there's your new color selection. Repeat for the remaining colors. Note that single-series bar charts display all the bars in the same color, so you can't set them to separate new colors. You can change their color using the described method, though.
ADDING NEW COLOR TO PIE CHARTS
In the last tip, we showed you how to modify the colors of the bars in a bar chart. You can do the same for your pie charts, but the method is slightly different. Insert your pie chart. You'll get those same weak colors that you get by default in the bar chart.
First, double-click the chart. Next, click one of the slices to select it. Now right-click the selected slice and choose Format Data Point. When the dialog box opens, click the Patterns tab and choose your new color. Click OK to close the dialog box and apply the color selection. Repeat as necessary.
ONE BAD APPLE NEEDN'T SPOIL THE BUSHEL
Because you're such a cagey Excel user, you've just finished formatting several sheets at once by grouping them first (as we
explained how to do in a tip last month). Trouble is, there's one sheet that you want to remove from the group--seems it's a "black sheet" (sorry). How can you do this while keeping the rest of the group together? Buy removing the sheet from the clique with a single click (sorry again): 1. Hold down the Ctrl key. 2. Click on the sheet tab of the sheet you want to remove. The sheet is no longer in the group. Of course, now the others may need a fourth for bridge.
FREEZE THOSE NUMBERS FOREVER
You don't like to brag (yeah, right), but this latest workbook you worked up generated some good numbers--some really good numbers. So wonderful are these numbers, in fact, that you want to preserve them; specifically, you want to protect them, forever, from any goon who decides to change your worksheet so that the formulas return different results. How can you prevent such a travesty? By converting your formulas to values. Here's how: 1. Select the cells containing your formulas. 2. Choose Edit + Copy.
3. Choose Edit + Paste Special. 4. When the Paste Special dialog box appears, under Paste, select Values. 5. Click OK. Now, your good numbers are actually numbers, not formulas--so they won't change if someone changes other numbers on the sheet. Another disaster averted.
MAKE A GROUP OF DATES
As subscriber J. K. points out, there is an easy way to fill a series of Excel cells with a sequence of dates. All you have to do is type a date into a cell. Now grab the cell by its little handle (you'll see the handle if you look closely at the lower right corner of the cell when the cell is selected) and drag it through the cells you want to fill. For example, go to cell A1 and type 02/02/98 Use the mouse to grab the cell handle. Now drag the mouse through cell A6. Cells A1 through A6 will now display the data shown here:
02/02/98 02/03/98 02/04/98 02/05/98 02/06/98 02/07/98 You can use the same technique to fill a series of horizontal cells
with the sequential dates. Just enter the first date and then grab the cell and drag horizontally.
ACT NATURAL
Say you've got an Excel data region with plain-English column and row titles--such as "Q1" or "Name" or "North"--and you want to create a formula that makes a calculation based on one or more of those rows or columns. Now, if you've got a thing for pointing to--or even better, typing--cell addresses in your formulas, well, far be it from us to spoil your fun. But if you think you might like to try something a little more intuitive, try using the row and column names in your formulas. Just include the name of the column as the argument in your formula. For example, to calculate the average of the values in your "North" column, try this: 1. Select the cell in which you want to enter your formula. 2. Type =AVERAGE(North). 3. Press Enter. Excel is so smart, it knows which cells you mean when you type "North" (truth is, it would have to be pretty brain-dead NOT to know), and it makes the calculation based on those numbers. You may never type a cell reference again.
A MICROSOFT PATCH
Microsoft recently posted a patch for users of Excel 97 with Service Release 1 (SR-1). The patch corrects an error that occurs during Excel's Auto-calculation. If you've already installed SR-1, you can download and install the Excel 97 Auto Recalculation patch. If you haven't installed SR-1, you need to do so before attempting to install the Excel 97 Auto Recalculation patch. To download the patch, go to http://www.microsoft.com/Excel/Recalc.htm and follow the instructions. If you'd like to see the error, here are some instructions from Microsoft describing how to produce the condition. (The following material is copyrighted by Microsoft.) "In a row, there must be a run of 18 or more consecutive formulas where each formula refers to the cell to the left of itself. This run of formulas must occur in a row number that is divisible by 16 with a remainder of 1. (For example, row 33.) There must be a formula that references at least one cell in a row above the run of formulas. To create an example of this issue, follow this procedure: Go to row 33 Select D33:U33, and type: =C33+1 Press Ctrl-Enter In cell A36, type: =D33 In cell A37, type: =D17
Change the value in cell C33 Note that under these circumstances, the value in cell A36 does not recalculate. You can press Ctrl-Alt-F9 and the cell recalculates as expected."
SEEKING YOUR GOAL
Let's say you have a worksheet designed to handle your widget sales. Here you are at the start of your fiscal year and you know you must realize at least $2,000,000 in widget sales during the coming year to meet your goals. How many widgets do you need to sell to take in $2,000,000? This isn't so hard to calculate. You can divide $2,000,000 by the Widget price of $255.54 to get 7826.56335603 (round to 7827). You can make the calculation very quickly in Excel if you use the Goal Seek function. And this way, you don't have to add anything to your worksheet or run a calculator program. Let's look at an example. Widget Price $255.54 (cell A1) Number of Widgets sold (cell A2) Total Sales (cell A3). The formula is =(A1*A2). At this point, cell A2 is empty. Because it's the first of the fiscal year, you haven't sold any widgets yet. Now click cell A3 (Total) to select it and then choose Tools, Goal Seek. When the dialog box opens, you'll see three entry boxes: Set Cell, To Value, and By Changing Cell. Set Cell will be set to A3, since you selected that cell before opening the dialog box. Into the entry box labeled To Value, type 2000000 (your goal). In the entry box labeled By Changing Cell, type A2 This is the value you want to determine. Click OK and cell A2 will display 7826.563. Round this number to 7827. You'll need to sell 7827 widgets to take in $2,000,000. After you read and record the number displayed in cell A2, click Cancel and your worksheet reverts to its original entries (blank in this case).
SHOW IT ALL
Subscriber J.R. asks if there's a way to print the formulas that appear in an Excel worksheet. The answer is, yes, there is.
To show the formulas, choose Tools, Options. When the Options dialog box opens, click the View tab. Now, under Window Options, select the check box labeled Formulas. Click OK to close the dialog box and record the change. If you look at your worksheet now, you'll see that all the formulas appear. If you choose File, Print and click OK, the formulas will appear on the printout. The selection you just made remains in effect in the current worksheet until you change it. When you open a new worksheet, it will automatically default to displaying the value in a cell rather than the formula.
MAKE YOUR PRINTOUT THE CENTER OF ATTENTION
Here's the situation: You've just finished preparing your quarterly sales-projection worksheet. It's the result of weeks of work culling information from branch offices, running numbers through regression analysis tools (you really don't even know what they ARE) and other forecasting methods, and passing the data around for approval. So what's the problem? The darn spreadsheet--the only thing you've worked on this month--is just five columns wide by two rows tall! How can you possibly present this as justification for your last few paychecks? One way is to print it centered on the page so that instead of having a little worksheet in the top left corner of your page and lots of white space everywhere else, you have a little
worksheet in the center of the page and what looks like LESS white space everywhere else. Here's how to do it: 1. With the worksheet you want to print open on screen, choose File + Page Setup. 2. Click the Margins tab. 3. Under Center on Page, select Vertically AND Horizontally. 4. Click Print.
There's no better way to prevent a lot of work from looking like an on-the-job vacation.
MORE ON COMMENTS
Last time, we told you how to print any comments in a worksheet on a separate page. But suppose you'd rather see your comments just as they appear on screen--in little sticky-note frames, with arrows pointing to the appropriate cells? Well, you can do that, too; here's how: 1. With the worksheet you want to print open on screen, choose View + Comments. All the comments appear on the sheet. 2. If necessary, reposition any comments that obscure cell contents you want to appear in your printout. (To move a comment, click on the comment border and with the four-headed arrow point, drag the comment
to a new location.) 3. Choose File + Page Setup. 4. Click the Sheet tab. 5. Under Print, set Comments to As Displayed on Sheet. 6. Click Print.
Your comments appear on the page just as they do on screen--in brilliant yellow if you have a color printer, or in light gray if you're using a (yawn) black-and-white printer.
WHEN YOU ACTUALLY WANT TO HEAR FROM THE PEANUT GALLERY
Let's face it: All this stuff about "working together" and "team productivity" is just a big, steaming load of cow chips. You're not interested in other peoples' comments, and they're not interested in yours. Everyone just wants to finish their work and go home. But sometimes comments ARE important. Like when somebody hands you an Excel sheet with formulas that might as well have come from Mars. If
the perpetrator was kind enough to attach comments to the cells in question, you can print them out, as follows: 1. With the worksheet you want to print open on screen, choose File + Page Setup. 2. Click the Sheet tab. 3. Under Print, set Comments to At End of Sheet. 4. Click Print.
Excel prints the sheet and then prints the comments--complete with cell references--on a separate sheet of paper.
REPLACE IT IN EXCEL
You can use Excel's Find and Replace utilities in much the same way you would Word's. Let's say that you'd like to find the cell that contains the number $21,987.23. You know it's in there, but you just
don't know where. Choose Edit, Find and, when the dialog box opens, type in the value and click Find Next.
Now let's suppose you'd like to find all occurrences of the value 25 and replace them with 35. Choose Edit, Replace. When the dialog box opens, enter the two values and click Find Next. When Excel finds the first occurrence, click Replace (if that's what you want). If you're very sure this is what you want, you can click Replace All and Excel will replace every instance of 25 with 35. Here's where you can have
a problem. Let's say that you have a number of occurrences of 25 and you're sure you want to replace them all with 35. But, do you have some occurrences of 25,000? If you do and you're not careful, 25,000 will get replaced with 35,000.
To prevent this, select Find Entire Cells Only before you do any kind of search and replace. This tells Excel to find and replace only those occurrences of 25 (not 25,000).
GETTING IN SHAPE
AutoShapes: If you haven't experimented with this handy feature in Excel, now is the time to see how it works. Let's imagine that you've just finished a worksheet that contains a chart. You can add labels
to the chart to make it easier for people to read. But you could also use some AutoShapes to enhance the appearance of the worksheet.
Look at the Drawing toolbar at the bottom of the Excel window. If you don't have a Drawing toolbar, choose View, Toolbars, Drawing. In the Drawing toolbar, click AutoShapes. When the list expands, select Callouts and then select the one of your choice. Now use the mouse to draw the shape.
Once the callout is in place, use the mouse to drag its pointer near one of your chart elements. Type in the information about the element the callout points to, and then use the mouse to size the callout
box. This is a very effective way to label chart elements for a presentation.
NOTE PASSING
The ability to add comments to an Excel worksheet is especially useful when several people are reviewing it. For example, suppose you commonly create worksheets that get sent to Sue. This time, the total really doesn't look quite right to you. So you'd like Sue to take a good look at the numbers. Click your Total cell and choose Insert, Comment. When the Comment dialog box opens, type in your message to Sue and save the worksheet. You'll notice that a small red triangle will appear in the upper-right corner of the cell that contains the comment.
When Sue opens the worksheet, she will see the comment indicator. She can read the comment by simply moving the mouse cursor over the marked cell. To enter a response to your comment, she can right-click the cell and select Edit Comment.
When Sue passes the file along to Harvey, he'll see the marker and move the cursor over the cell. Now he can read both comments and add one of his own if he chooses.
DO IT ALL AT ONCE
When you need to work with multiple sheets in Excel, you may want to make the headers the same on all the sheets. Here's a little trick that works very well. Hold down the Ctrl key and click the name tab of each sheet that you intend to use. Now type in your headers on one of the sheets. Hold down the Ctrl key again and deselect all the sheets except Sheet 1. Now click Sheet 2. The header will appear on Sheet 2 just as it did on Sheet 1, and just as it will on all the sheets you selected initially.
You're not limited to entering headers--you may also want to enter formulas this way. This trick is handy for entering headers. Just don't forget to turn it off before you start entering data. Otherwise, all the sheets will show the same data.
ADD A NEW ONE HERE
Let's say you're just working away on a multiple-sheet workbook. You suddenly realize that you really should have a sheet between Sheet 1 and Sheet 2. All is not lost. You don't have to start all over. All
you have to do is click the Sheet 2 name tab to select it and then choose Insert, Worksheet. This will insert a new sheet before Sheet 2. Since Excel's default workbook contains 16 sheets, the new sheet will be named Sheet 17.
NO MORE SHEETS
In the last tip, we showed you how to insert a new worksheet between two existing sheets. In that tip, we mentioned that the Excel default is 16 sheets to a workbook. If a given workbook needs only one or two sheets, why keep all those other sheets around?
Let's say that you have a workbook that uses only one sheet. Go to the bottom of the worksheet and click Sheet 2. Now hold down the Shift key and use the Move to End button (it looks like a right-arrow
running into a vertical line) to move to the end of the workbook. Click Sheet 16. Now choose Edit, Delete Sheets. You'll get a dialog box warning you that you are about to permanently delete the worksheets. Click OK.
If you should need another sheet, you can choose Insert, Worksheet. The new sheet will be placed before Sheet 1 and will be named Sheet 17.
BUMPER CROP
Last time, we showed you how to wake up your yawn-fest of a worksheet by adding a picture. Today's perspicuous picture pointer has to do with the fact that sometimes PART of a picture is better than the whole thing. And how can you display only PART of a picture once you insert it into a Excel 97 sheet? By cropping it, of course. Here's how: 1. Click on the picture you'd like to crop (the Picture toolbar appears). 2. In the Picture toolbar, click the Crop button. 3. With the mouse, drag any of the "handles" (little white squares) toward the center of the picture. 4. Continue doing this until the part of the picture you want to see is all that shows.
Instead of resizing the picture, Excel "crops" away as you drag, leaving you with less--which, as advertising weenies have been drumming into your head most of your life, is sometimes more.
DO YOUR WORST, THEN BACK OUT WITH ONE CLICK
Well, let's see: We've told you how to insert a picture into your Excel worksheet, and we've told you how to crop it. We're willing to bet body parts that you've experimented with other picture-manipulation techniques yourself. And that might be a problem. Don't get us wrong: Experimentation is good, and we encourage it. But like those "drop-the-open-faced-peanut-butter-sandwich-off-the-table-to-see-whic h-side-it-lands-on" experiments of your youth, experimentation can also be very messy.
So if you've "experimented" with your picture in such a way that you can no longer recognize it, what can you do? Well, you can change it back to the way it looked at the very beginning, when you first
inserted it. 1. Select the picture. 2. On the Picture toolbar, click the Reset button (OR right-click the picture, choose Format Picture, click Reset, and click OK.) Excel undoes all your little mistakes, returning your picture to its original size, proportions, colors, whatever. (Too bad there's no Reset button for those times you get pulled over for speeding and then tell the cop, "I pay your salary.")
GET THE PICTURE, WILL YA
Okay--numbers are great. And for a spreadsheet wonk like you, there's probably no greater thrill than sheet after sheet full of perfectly calculated numbers. However, the rest of us--including those of us
stuck READING your blasted numbers--would appreciate a little break. Would it kill you, we ask, to perhaps illustrate your work with a picture--before we fall into a DEEP CATALEPTIC COMA?
The answer is no, it would not kill you, because Excel 97 gives you two easy (and entirely nontoxic) ways to add pictures to any sheet. To add a picture from any picture file: 1. Choose Insert + Picture + From File. 2. Excel opens to its default Clipart folder. To explore the files in this folder, double-click any of the subfolders (Background, Popular, etc.), then select the files and preview them in the box to the left. (NOTE: You can also navigate to ANY folder of disk drive containing the picture file you want to add. 3. When you've found the picture file you want to add, click Insert.
To add a picture from Excel's Clip Art Gallery: 1. Choose Insert + Picture + Clip Art. 2. In the list on the left side of the dialog box, select a picture category. 3. Select the picture you want to insert and click Insert (OR simply double-click the picture).
Either way, Excel inserts the picture in your sheet. And unless it's a picture of tooth decay, it's sure to be a BIG improvement over your uninterrupted rows of numbers.
HAD ENOUGH OF OUR COMMENTS? SO HAVE WE
Yes, it's time to erase comments from our minds for a while--and from our worksheets.
To delete any single comment from a worksheet: 1. If the comment is not displayed, choose View + Comments. 2. Find the comment you want to delete, and CLICK ON ITS BORDER to select it (clicking inside the comment does no good in this case). 3. Choose Edit + Clear + Comments, OR click the Delete Comment button on the Reviewing toolbar.
To delete several or all comments from a worksheet: 1. If the Reviewing toolbar is not displayed, right-click ANY toolbar and choose Reviewing from the shortcut menu. 2. Select cell A1. 3. On the Reviewing toolbar, click the Next Comment button. 4. On the Reviewing toolbar, click the Delete Comment button. 5. Repeat steps 3 and 4 until you've deleted all the comments you want to delete.
IF YOU'RE GOING TO PUT YOUR TWO CENTS IN, AT LEAST SPELL THEM CORRECTLY
Worksheet comments--sure, sometimes they're necessary, but it only takes a few to clutter up your worksheet. And what's the only thing worse than a worksheet cluttered with comments? A worksheet cluttered with MISSPELLED comments. (Those of you who guessed "oral surgery" were way off.) Of course, misspelled comments are 100 percent inexcusable, because Excel will spell-check your comments for you, without forcing you to spell-check the rest of your dang worksheet. To spell-check a comment: 1. If the comment is displayed, select it; otherwise, simply select the cell containing the comment. 2. Choose Tools + Spelling. 3. Spell-check as usual (yes, this tip assumes that you already know how to spell-check. . . . Oh, all right: Choose Tools + Spelling, or press F7). 4. When Excel tells you it's finished, click OK.
MR. QUAYLE, MEET MR. OTTO KORECT
You'd think that since the famed "potatoe" incident--and the resulting ridicule--more people would have become more careful about their spelling. But NOOOOOOO--we had our laugh, then went right back to filling our worksheet cells with gems like "finanse" and "buget." If we could only remember to spell-check the worksheet, Excel would catch these boo-boos--but who has time to spell-check every time?
Well, if you're smart about the way you spell-check, you may not have to do it often--and eventually, you may not have to do it at all. Because as you spell-check, you can tell Excel to remember your most common mistakes, and automatically correct them as you type. Here's how: 1. Choose Tools + Spelling to start the spelling checker. 2. Take a look at the first misspelled word Excel finds. IF IT'S A MISTAKE YOU MAKE OFTEN, and if Excel offers the spelling you intended in the Suggestion box, select the correct spelling and click AutoCorrect. (If it's a mistake you don't think you'll make that
often, simply click Change). 3. Repeat step 2 for as many words as Excel finds. 4. When Excel tells you it's finished, click OK.
From now on, Excel will automatically correct the "AutoCorrected" misspellings as you type--whether you're typing in a cell, or in a comment, or even in a text box created with Windows Draw 97. Which makes it easy to look like Noah Webster--even if you're no Jack Kennedy.
PLAIN SPEAKING
A while ago, we showed you how to create plain-English formulas. For example, if you have a worksheet that looks like this:
Small Big Huge
Dogs 123 124 136
Cats 156 147 138
Rats 157 128 139
you can type =SUM(Rats) to calculate the total of the Rats row.
Well, if you found that amazing you'd better sit down, because today we're here to tell you that in your formulas, you can refer to SPECIFIC CELLS by plain-English names. If you wanted to calculate the
total of small cats and big rats, for example, you would do the following: 1. Select an empty cell. 2. Type
=Small Cats+Big Rats 3. Press Enter.
Excel understands that by Small Cats, you mean the intersection of the Small column and the Cats row, and that by Big Rats, you mean the intersection of the Big column and the Rats row (even if you don't
understand this yourself). So Excel finds the values at those intersections and adds 'em up. You can use this "intersection method" to refer to any cell in a labeled row AND a labeled column. You have to admit, this capability is pretty slick.
SIMPLE TASKS DEMAND SIMPLE TOOLS
If asked to name the world's leading cause of wasted electricity, you might nominate "leaving the lights on all night," or "letting the remaining 119 minutes of the Paulie Shore video continue playing even
after you've left the room in disgust." But our choice would be "loading Microsoft Excel just to do a simple math problem."
It's true: All across the world, folks stumped by little mysteries like "What's 237 plus 457?" or "What's the square root of 22?" (that one keeps coming up, doesn't it?) are starting Excel, entering the appropriate formula, noting the answer, and shutting down Excel again. How can you prevent this sinful waste of one of our most precious resources? Well, next time you have a simple math problem to solve--and your computer is already turned on--try this: 1. Click the Windows 95 Start button. 2. Choose Programs + Accessories + Calculator. 3. Do your math. 4. Close the Calculator.
Sure, it's a non-Excel tip, but you and the rest of the world will be better off for it. Tomorrow: dusting off that abacus. Just kidding.
SIMPLE TOOLS DEMAND SIMPLE ACCESS
Last time we made a big, self-important deal about telling you how you could save electricity by opening your Windows Calculator--and NOT Excel--to do simple math problems. Well, we bet that more than a few of you wags out there are asking, "Well, suppose Excel is already open? It just wouldn't make good ecological sense to open the Calculator, too, would it?" And to this we answer, it's time to LET GO of our little ecological joke and revert to (or should we say discover?) common sense. Even if Excel is already open, using the Calculator is a lot faster and easier when you need to do simple calculations that won't remain part of a worksheet. Especially if you know this trick: 1. Right-click ANY Excel toolbar and choose Customize from the shortcut menu. 2. Click the Commands tab. 3. Under Categories, select Tools.
4. Scroll down the Commands list until you find the first Custom command--the one with the little calculator icon next to it. 5. Drag this calculator icon to the Standard toolbar (the toolbar with the New, Open, Save, Print, and other basic icons). 6. Click Close.
From now on, whenever you have to do some math that won't stay in your spreadsheet, instead of creating a formula that you'll only have to erase, just click this Custom icon to open the Windows Calculator.
TIME FOR YOUR EVALUATION
Say you've created a pretty long and complex formula--the kind of formula you really have no business creating, like =SQRT(22)*AVERAGE(B30:D30)--and suddenly, because you're oh-so-curious (a trait considered an advantage in childhood, but little more than an annoyance to all who know you now), you just have to know what only PART of the formula calculates to. You could reenter part of the formula in another cell to get the answer. But why would you, when you have the following handy-dandy technique at your disposal? 1. Select the cell containing the formula. 2. In the Formula Bar (the bar at the top of the Excel window that displays the contents of the current cell), select the portion of the formula that you want to evaluate. 3. Press F9. Excel replaces the selected portion of the formula with its value (for example, if you selected SQRT(22), Excel replaces it with 4.69041575982343). 4. IMPORTANT: Press Esc. Otherwise, you risk reentering the formula with the value instead of the formula portion, which could seriously mess up your worksheet.
97
ERASING EXCEL CELL CONTENTS WITH THE MOUSE
Here is a tip from Jan A.:
"This tip is handy when you have your hand on the mouse and don't want to reach over to press the Delete key. Here's how it works.
"Select the cells that contain the data you want to erase. Now use the handle to drag backward over the cells. The cells turn gray as you do this. When you release the mouse button, the cell contents disappear."
This operation is exactly the opposite of dragging the handle to AutoFill cells. Thanks for the tip, Jan.
LEAVE THE SQUINTING TO JEWELERS
In a small town in southern Italy, young men aged 16 to 29 sit bent over their workbenches, carving cameos out of abalone shells. Why do we know their age? Because after a decade and a half of this kind of close work, these poor guys' eyes, necks, and backs hurt too much for them to continue; they have to find new careers ringing bells in old French churches.
Does the plight of the cameo maker remind you of how you feel after a day squinting over your Excel worksheet? Why not try zooming in on it? Doing so, in either of the following two ways, makes it a little easier to read: - To make your sheet twice as large as normal: On the Standard toolbar, click the arrow next to the Zoom box and choose 200% from the drop-down list. - To zoom in so that your view enlarges to fit a specific selection, do the following: 1. Select the range of your sheet that you want to work on. (NOTE: The range should be SMALLER than what you can see in a single screen; otherwise this trick won't work.) 2. On the Standard toolbar, click the arrow next to the Zoom box and choose Selection from the drop-down list. Excel Zooms in on your selection as much as possible, while still fitting the entire
selection in the Excel window.
WELL, OKAY, IF YOU WANT TO SQUINT ...
Last time we showed you how to use the Selection Zoom option to make a smaller-than-entire-window selection as large as possible, while still fitting the entire selection in the window. But suppose that
you want to do the opposite--that is, you want to REDUCE a larger-than-single-window selection to a size that just fits within a single window? Well, you can use the Selection Zoom option to do that, too: 1. Select the cells you want to fit into a single window. 2. On the Standard toolbar, click the arrow next to the Zoom box and choose Selection from the drop-down list. Excel reduces the selection to fit in the window. Although the information in the resulting worksheet may be too small to read, having it all in one window can be useful for other purposes, such as formatting, charting, and so on.
FIND THE MISSING LINKS
No, we're not talking about discovering the whereabouts of the Groks, those old school chums who had so much body hair by third grade, they could get into nightclubs. We're talking about links you've added to your Excel spreadsheet. Add enough of them and you may lose track of what's a link and what's not. Sound like a mess? Don't worry--just let Excel do the work and find the links for you: 1. Select cell A1. 2. Choose Edit + Find. 3. Type an exclamation point. 4. Make sure Look in is set to Formulas (it should be; it's the default). 5. Make sure Find entire cells only is not selected. 6. Click OK.
Excel finds your first link; click Find Next to find the next one and again to find the next one, and so on and so on.
THREE SHEETS TO THE WIND?
When you enter headers into an Excel worksheet (choose View, Header and Footer) you
need to be aware that Excel doesn't automatically place the headers and footers onto all the worksheets. If you want a header to appear on more than one worksheet, you need to select the tabs of all the worksheets on which you want the header to appear.
FIGHT THE POWER
Excel offers two ways to calculate the power of a number. Let's look at both. Click in cell A1 to select it. Enter =22^3 and press Enter. Excel will perform the calculation and report that 22 to the third power is 10648. Now go to cell A2 and type =POWER(22,3) then press Enter. Once again, Excel will report that 22 raised to the third power is 10648. Take your pick--either one works.
MIGHTY MOUSE!
If you use the IntelliMouse, you can use the center wheel to move up and down in an Excel worksheet. If you don't use IntelliMouse, you'll just have to be content with using the scroll bars. Here are some notes on scrolling in Excel: When you drag the scroll box, a little yellow tool tip will appear to help guide you. If you're scrolling around and decide you want to see the active (selected) cell very quickly, press Ctrl-Backspace. Excel will immediately transport you to the page that shows the active cell.
AN ALT-TERNATIVE TO TEXT SELECTION
Numerous subscribers have asked about a way to select an entire column of specific text. Try this: Click the area where you want to begin your selection. Now hold down the Alt key and use the mouse to
select the text you want to work with. This method allows you to select any specific text regardless of where it appears in the document.
IN ROMAN TIMES
Now that a better way of writing numbers has been around for a thousand years or so, wouldn't you think people would stop using Roman numerals? They haven't, though. You still run across them in copyright dates and other places where you don't really expect to find them. If you're like most of us, you don't have a great memory for converting numbers to Roman numerals. Everyone can do I to X or so, but suppose you'd like to be cool and use Roman numerals just like the movie moguls do? All you have to do is run Excel and then enter into a cell =ROMAN(MyDate,0) and press Enter. Excel will present you with the date in classical Roman numeral form. If MyDate is 1998, Excel will present MCMXCVIII. The 0 after the date tells Excel that you want to use the classical form. If you enter =ROMAN(MyDate,1) Excel will display MLMVLIII. This is a more concise form of Roman numeral. So it's up to you--do you want the original style, or the new, improved version?
EASY ENTRY
Ah yes, data entry--a purgatory of repetition and drudgery that, unfortunately, many of us are required to visit often before death. For example, suppose you have to enter names in a list, like this one:
FIRST MIDDLE LAST
John Q. Public
Jane J. Doe
(Let's hope Mr. Public doesn't sue.) If you're like most folks, you type the first name, press Right Arrow, type the middle initial, press Right Arrow, type the last name--and then either click the first cell of the next row, OR press a few arrow keys to get there. What a drag. And all so avoidable--because if you know the following trick when entering data into a list, you can work FASTER, without touching the mouse OR the arrow keys: 1. Press Tab to move from one column to the next in the same row. 2. Press Enter after typing the last entry in the current row--and Excel moves you to the first column in the next row.
So, in the example above, you would type the first name, then press Tab, then the middle initial, then Tab, then the last name, then Enter. And so on until you finish. It's still purgatory, but it's quicker.
EVEN LESS DATA ENTRY MISERY
A picture may be indeed worth a thousands words (actually, it's probably worth a thousand dollars not to have to LISTEN to the thousand words), but sometimes it's not enough to convey the significance of five or ten numbers. What we're trying to get at, in plain English, is that sometimes the bars or lines or pie slices in an Excel chart just aren't enough: Your audience, dullards that they are, need to see numbers too.
The folks who designed Excel understand this--and provide a neat way to display your numbers in table form, TOGETHER WITH YOUR CHART: 1. Select the chart. 2. In the Chart toolbar (which should display automatically when you select your chart), click the Data Table button. 3. If necessary, adjust the size of the chart so that the entire table fits, and looks good.
Now the numbers in your chart should be absolutely clear to everyone. If not, your business is in real trouble.
NEAT PICTURE TRICK
After the past three of days of picture tips, some folks would say "enough already" and move on to a new topic (probably to audible sighs of relief from their audience). But not us here at Dummies Daily, no sir. If there's a dead horse that needs beating, a not-quite-dry sponge that needs wringing, a stone from which can be squeezed the last drop of blood--well, we're your guys.
All this to say that today we have one more picture tip. Suppose that you'd like to add a "frame" to your picture. There are probably a few ways to do this, but we think we've found one of the best: 1. Click on the picture to select it. 2. In the Picture toolbar, click the Crop button. 3. Hold down the Ctrl key. 4. With the mouse, drag any of the corner "handles" (little squares) AWAY from the center of the picture. (Instead of cropping part of the picture away, this "outward" cropping ADDS a uniform border around the picture.) 5. Right-click the picture and choose Format Picture from the shortcut menu. 6. Click the Colors and Lines tab. 7. Under Fill, select a color form the Color grid. 8. Click OK.
Pretty neat, huh? Sometimes it pays to dwell on the same topic for days.
OKAY, IT'S ANOTHER PICTURE TIP--BUT IT'S A CHART TIP, TOO!
We admit we're getting totally out of hand with this "pictures in Excel" thing. But our single-mindedness is your gain! Think for a moment about your Excel charts: bars, and lines, against a boring blank or grid background. Why, they're almost as boring as your sheet itself. And what would be just the thing to perk up your charts? You guessed it: a picture background!
You can make ANY picture into a background for your chart, as follows: 1. Select your chart; the Chart toolbar displays automatically. 2. In the drop-down box on the Chart toolbar, select Plot Area--UNLESS your chart is a pie, donut, cylinder, cone, or pyramid chart, in which case you should choose Chart Area. 3. On the Chart toolbar, click the Format Plot Area or Format Chart Area button (it's the same button, but the name will change depending on which area you selected in Step 2). 4. In the dialog box that appears, click Full Effects. 5. Click the Picture Tab. 6. Click Select Picture. In the dialog box that appears, choose the picture file you want to use for your background and click OK. (You can see a preview of the picture you selected in the Preview box.) 7. Click OK, then OK again. The picture becomes the background for your plot or chart area. Beats your plain-white background with a stick, doesn't it?
SPELL IT OUT ON YOUR CHART
A picture may be indeed worth a thousands words (actually, it's probably worth a thousand dollars not to have to LISTEN to the thousand words), but sometimes it's not enough to convey the significance of five or ten numbers. What we're trying to get at, in plain English, is that sometimes the bars or lines or pie slices in an Excel chart just aren't enough: Your audience, dullards that they are, need to see numbers too.
The folks who designed Excel understand this--and provide a neat way to display your numbers in table form, TOGETHER WITH YOUR CHART: 1. Select the chart. 2. In the Chart toolbar (which should display automatically when you select your chart), click the Data Table button. 3. If necessary, adjust the size of the chart so that the entire table fits, and looks good.
Now the numbers in your chart should be absolutely clear to everyone. If not, your business is in real trouble.
DIVIDE AND SORT
Not long ago, a subscriber wrote us with this poser: "When I sort this . . .
A-1 A-2 A-3 A-4 A-11 ... it always becomes this: A-1 A-11 A-2 A-3 A-4 How can I sort these alphanumeric entries?"
Unfortunately, this is the only way to sort them because Excel always sorts by first character, then second, then third, and so on. Therefore, A-11 always comes before A-2 (in ascending order). In the future, one thing you CAN do is enter a list like this inseparate columns--the letters in column A and the numbers in column B. Then you can sort them together as follows: 1. Select both columns of data.
2. Choose Data + Sort. 3. In the Sort dialog box, set Sort By to Column A and Then By to Column B.
4. Click OK. Excel sorts the two columns the way you want them sorted.
EXCUSE US WHILE WE SCRAMBLE THE EGG ON OUR FACE
On Monday, February 9--a day that will live in infamy here at Dummies Daily--we recommended the following shortcut for copying a formula down a long column: 1. Select the range of cells into which you want to enter the same formula. 2. Type the formula. 3. Press Ctrl + Enter.
This works well enough, but as subscriber Dale Bohman pointed out, there's an even faster way: 1. Type the formula into the top cell in the column. 2. Double-click the bottom-right corner of that cell.
Excel copies the formula to the other cells in the column until it encounters an empty cell in one of the columns to which the formula refers. For example, if you have numbers in cells A1 through A22 and
B1 through B19, and then use this method to copy the formula +A1+B1 into column C, Excel enters the formula in cells C1 through C19. Thanks for the comeuppance, Dale.
FLIP OVER YOUR CHARTS
Another day, another chart tip. Suppose you want to create a bar chart to illustrate your trusty Dogs/Cats/Rats worksheet:
..........Small.....Big.....Huge
Dogs......123.......124.....136
Cats......156.......147.....138
Rats......157.......128.....139
By default, Excel charts your data by row--in this case, it generates a group of bars (small, medium, and large) for each animal. But suppose you're more concerned about size (clean minds, please) than type of animal, and you want do group the bars by column instead. Do you have to re-create the sheet? Of course not. 1. Select your chart. 2. In the Chart toolbar, click the By Column tool.
Excel "flips" your chart, and generates a group of bars for each column. If you don't like what you've done, just click the By Row button to flip it back. And no, we can't tell you why it's important
to know whether more dogs are huge, or more huge animals are dogs--it's just an example, for the love of Mike.
GET RID OF THAT EMPTY FEELING
If you only have one Excel workbook open, and you close it, how many does that leave? We're not testing your mathematical skills (we know a lot better than to open THAT can of worms), we're just trying--in our trademark playfully convoluted way--to introduce the topic of an EMPTY EXCEL WINDOW.
Can you picture such a window? Good. Now suppose you want to fill this empty window with a new or existing file? Well, you COULD drag your mouse all the way to the menu and choose File + New or File + Open--or, all the way to the Standard toolbar, to click the New or Open buttons. But why not just give your wrist a rest as follows: 1. Right-click anywhere within the vast emptiness of your Excel window.
2. Choose New or Open from the shortcut menu.
We admit it's not that much faster than the conventional method. But if we can eliminate even one more split second of emptiness from your life, we'll sleep more soundly at night.
WE'RE HERE TO HELP
We want to thank each and every one of you who writes to Dummies Daily--not only because it's good to know you're out there reading our tips, but because WHENEVER OUR FEEBLE BRAINS RUN OUT OF IDEAS, YOU PROVIDE NEW ONES.
Take Thomas Nickolaus, for example. He recently wrote us to ask, "How can I switch between different worksheets in Excel without using the mouse?" Well, Tom--may we call you Tom?--here's how:
- To move to the previous worksheet, press Ctrl + Page Up. - To move to the next worksheet, press Ctrl + Page Down. - To move to the next WORKBOOK (that is, the next Excel file, if you have more than one open), press Ctrl + Tab. - To move to the previous workbook, press Ctrl + Shift + Tab.
GOT AN INTELLIMOUSE? THEN INTELLI-SCROLL
Do you head for the company garage every day holding your wrist, which throbs with pain from a full day of scrolling through gigantic Excel 97 worksheets? Well, if you have an IntelliMouse--that
newfangled mouse with the wheel between the buttons--then you've been suffering for NO REASON. Because IntelliMouse lets you scroll with your finger instead of your wrist.
- To scroll up with IntelliMouse, roll the wheel away from you. - To scroll down with the IntelliMouse, roll the wheel toward you.
At least, that's the BASIC way to scroll with IntelliMouse. For more complicated--well, not complicated, just trickier--methods, join us tomorrow. Same mouse time, same mouse place.
MORE INTELLI-SCROLLING
Last time, we showed you how to use your IntelliMouse--if you have one--to scroll up and down, using your index finger instead of your overworked wrist. Today, we show you how to do even more
not-so-stupid scrolling tricks with IntelliMouse.
To quickly scroll in any directly using Intellimouse: 1. Press down on the IntelliMouse wheel. The pointer changes to a four-pointed box. 2. Drag in the direction you want to scroll. The farther you move from the box, the faster you scroll. When you start scrolling at a comfortable speed, stop dragging and simply hold the button down. This is a fantastic, easy-to-the-wrist way to scroll very long distances. Think we're through with IntelliMouse? You underestimate our ability to drag out a topic. Tomorrow we show you how to zoom.
IF I WANTED EXTRA SHEETS, I'D HAVE CALLED HOUSEKEEPING
In case you haven't noticed, each new Excel workbook you create starts with THREE--count 'em--sheets. Also, in case you haven't noticed, 99.999 percent of the time you probably never use more than
ONE sheet. Is there any way you can put an end to this senseless waste of mathematical linen?
Sure there is. You can set Excel to open each new workbook with just one sheet (or as many sheets as you want), like so: 1. Choose Tools + Options. 2. In the Options dialog box, click the General tab. 3. Set Sheets in New Workbook to, er, the number of sheets you want in each new workbook--even just one. 4. Click OK. From now on, each new sheet includes exactly as many sheets as you say.
MAYBE WE DESERVE THE PUNCH
In a previous tip titled "BEFORE YOU PUNCH MR. ZIP IN THE TEETH," we told you that one way to make Excel keep the leading 0 in your Zip code--as in 02176--was to enter the Zip code as text. Well, did THAT set the e-lines crackling. Seems like we overlooked a much easier way to solve the problem: FORMAT THE COLUMN FOR ZIP CODES. Here's how to do it: 1. Select the cell or column of cells into which you plan to enter Zip codes--or into which you've already entered Zip codes. 2. Right-click the cells/column and choose Format Cells from the shortcut menu. 3. Click the Number tab. 4. Under Category, select Special; under Type, select either Zip Code (for 5-number codes) or Zip Code + 4 (for Zip + 4 codes). 5. Click OK.
Either option maintains your leading zeros. And the Zip + 4 option automatically adds the zeros, so you don't have to.
MORE ON LEADING ZEROS
Yesterday, we offered a better way to keep leading zeros in your Zip codes. But suppose you need to keep leading zeros in OTHER kinds of numbers, such as, say, seven-digit part numbers? Is there a way to do this? Would we pose the rhetorical question if there weren't? You can maintain leading zeros in numbers of ANY fixed length by creating a CUSTOM format, as follows: 1. Select the cells that contain, or are to contain, the numbers. 2. Right-click the selection and choose Format Cells from the shortcut menu. 3. Click the Number tab. 4. Under Category, select Custom. 5. Under Type, scroll down until you find the 00000 format and select it. Then add or delete as many zeros as necessary, until you have as many zeros as you want fixed places in your number. (For example, to create a format for seven-digit part numbers, you'd add two zeros.) 6. Click OK. From now on, the numbers in these cells retain leading zeros (and we're not talking about the premier idiots at your office).
IT'S ZOOM TIME
In this, our third in a three-part mini-series on IntelliMouse, we reveal to you another invaluable skill: zooming out to see more of your worksheet. There are two ways to do it. - To zoom out, hold down the Ctrl key and roll the Intellimouse wheel toward you (preferably one click at a time). - To zoom back in, hold down the Ctrl key and roll the wheel away from you again. (Note that you can't zoom in closer than 100 percent.)
If you don't like having to use the Ctrl key (after all, why tax two of your already overworked digits), you can set Excel to zoom when you roll the IntelliMouse wheel. Here's how: 1. Choose Tools + Options. 2. In the Options dialog box, click the General tab. 3. Select Zoom on Roll with IntelliMouse. 4. Click OK.
Now whenever you roll the IntelliMouse wheel, you zoom--which means you can no longer scroll up and down by rolling the wheel; instead you must use the press-and-drag method described in the previous day's tip.
MAKE YOUR DATA TALK
Days of worksheet design, hours of entering and tweaking formulas, all for what? Many times, it's all for ONE VERY IMPORTANT NUMBER that sits somewhere on the Total line of your worksheet. How can you make sure that people see that number? Oh, you could bold it or shade the cell, but let's face it--people have seen so much bolding and shading that it probably won't even raise their eyebrows. You need something a little more out of the ordinary. How about making your data TALK the same way that Dilbert or Charlie Brown does--with a little speech bubble? It's easy to do: 1. If your Draw palette is not visible, right-click ANY toolbar and choose Drawing from the shortcut menu. Otherwise, skip to Step 2. 2. In the Draw toolbar, click AutoShapes and choose Callouts. 3. In the Callouts palette, select one of the speech bubble shapes. 4. On your worksheet, start at one of the corners of the cell containing the number you want to highlight, and drag away from it to draw a bubble.
5. Type your message (something like, "Look at me--this is the only number that counts!"). Now that ought to get their attention.
MAN PROPOSES AND EXCEL TRANSPOSES
You've created a great Excel table, summarizing product sales by quarter. You show it to your boss, glowing with that special pride only an Excel user knows. And what does your graceless, ungrateful
superior say? "Can you flip this to show quarterly sales by product?"
Refraining from telling this chucklehead what's really on your mind ("No, but I can flip THIS . . ."), you return to your desk and perform the following bit of technical wizardry: 1. Select the range you need to flip. 2. Right-click the range and choose Copy from the shortcut menu. 3. Right-click a cell OUTSIDE of the copy range and choose Paste Special from the shortcut menu. 4. In the Paste Special dialog box, select Transpose and then click OK.
Excel pastes a "flipped" version of your table at the cursor location: What were once row headings are now column headings and vice versa--which leaves you free to imagine what important change your boss will ask for next.
PRINT WHAT YOU WANT
Everything in your Excel 97 worksheet may be important to you, but chances are that only some of it--maybe even only a few rows and columns of it--are important to your coworkers or your boss or whoever it is besides you who needs to look at it. So when it comes time to print the worksheet, why not just print the part that's important to them?
1. On your worksheet, select the area you want to print. 2. Choose File + Print. 3. In the Print dialog box, under Print What, select Selection. 4. Click Print to print.
Excel prints only what you selected. Just think of the paper you'll save--and the boredom you'll prevent.
THE ANSWER IS YES, BUT ...
Tip subscriber Gloria Bandy writes with this Excel 97 question: "In the previous version of Excel, you were able to center multiple rows of data across several columns. Now with Excel 97, when you do this function, it merges the data into one cell; consequently, some of the data is lost by doing this. Is there any way to do this without losing the data?"
Well, sure there is--but it's probably not what you want to hear: 1. Select the cell containing the data you want to center over several columns and then drag to extend the selection to cover those several columns. (For example, if you want the title in cell A1 to be centered over cells A1 through E1, select cells A1 through E1.) 2. Click the Merge and Center button on the Formatting toolbar. 3. If any cell in the selection BESIDES the leftmost cell contains data, Excel displays a warning that tells you that data will be lost. 4. To prevent losing the data, click Cancel. 5. Move the data to another location. 6. Repeat Steps 1 and 2.
Sure, it's not pretty, but some programs wouldn't even bother to warn you.
LIKE SHAPES?
Do you draw LOTS of AutoShapes on your Excel worksheets? What we mean to say is, are you as likely to draw an AutoShape in a worksheet as you are to enter a number? Don't worry--you're not alone. And just because you're more "visually oriented," you shouldn't be made to suffer by having to click through several layers of the Draw tools every time you want to draw a particular AutoShape. Why not drag the AutoShape toolbar you use most often to a more permanent position under the menu--along with the OTHER toolbars you use most often? Here's how to do it: 1. On your Drawing toolbar, click AutoShapes. 2. From the Menu, select a type of AutoShape--Lines, Basic Shapes, and so on. 3. When the shape palette appears, click the gray bar at the top of the palette and drag the palette to the top of the screen, under the menu with your other toolbars.
Now your AutoShape palette is always accessible, even if you hide your Drawing toolbar. You can use this same technique on any palette.
MAKE A QUICK CANCELLATION
You've been there before: You're typing an entry into an Excel cell, and about halfway through--or maybe all the way through--the process, you realize that what you're typing is so patently ridiculous, so
thoroughly inappropriate, so entirely preposterous, and so definitely wrong that you just want to give it up and start again from the beginning (or shut down for the day). What can you do?
Two things, it turns out. To cancel a cell entry BEFORE you enter it--or to cancel a cell edit and return the cell to its original contents, you can do either of the following: - Press Esc - Click the X button to the left of the Formula Bar.
Why they don't change the name of the Esc key to the Cancel key, we'll never know.
MAKE YOUR BACKGROUND LESS OF A BACKGROUND
Sure, the black numbers in your Excel 97 worksheet show up best against the default white background. But sometimes your eyes--and your soul--crave more. Like textures. Or swirls. Or something besides the endless oceans of white. After all, you're creating a spreadsheet, not decorating a psychiatric hospital.
Well, Excel lets you pep up your spreadsheet backgrounds the same way Web designers pep up their Web pages--with fancy graphic backgrounds. Here's how: 1. Choose Format + Sheet + Background.
2. At the top of the Sheet Background dialog box, click the Preview button (second from right). 3. Navigate to a folder containing background graphics (such as C:\Msoffice\Backgrnd or C:\Windows).
4. Select a file name and view the preview. 5. When you find a background you like--and one that's light enough to be a good background for text--click Open.
Excel "tiles"--or repeats--the graphic to create a background for your spreadsheet. If you don't like the change, choose Edit + Undo Background.
SOMETHING A LITTLE LESS FANCY
In the last tip, we showed you how to add a graphic background to your Excel worksheet, which is fine if you're a flashy type. But what can you do if you want a background other than white but don't go for
those noisy, Web-page style background effects? You do this, that's what: 1. Click the button to the left of the A column header button; this selects the entire sheet. 2. Choose Format + Cells. 3. Click the Patterns tab. 4. In the Color palette, pick the color you want your sheet background to be (and that's light enough to be a good background for your text). 5. Click OK.
Your entire sheet background is now the color you selected. If you don't like it, simply select Edit + Undo Format Cells to undo it.
PREVIEW OF COMING ATTRACTIONS
What with IRS audits, El Nino tornadoes, and unannounced visits from your in-laws, we're pretty sure you don't need another surprise in your life--something like opening an Excel worksheet that you expect to contain one thing, only to find it contains something entirely different.
Lucky for you, the folks who made Excel also file taxes, get caught in the rain, and have in-laws of their own--and so have given you a way to "preview" Excel files before you open them: 1. Choose File + Open. 2. At the top of the Open dialog box, click the Preview button (it's the eighth button from the left, or second from the right). 3. The dialog box splits into two panes. In the left pane, select the file you think you want to open; in the right pane, Excel displays a portion of the selected file's contents. 4. When you find the file you want to open, click Open.
Note that sometimes Excel displays the message "Preview Not Available." What this means, we're afraid, is that you're on your own.
RECALCULATION FAILURE
G. L. W. sends along this information: There is a potential automatic recalculation problem in Excel 97. The problem occurs under these conditions: 1. In a row, there must be a run of 18 or more consecutive formulas where each formula refers to the cell to the left of itself. 2. This run of formulas must occur in a row number that is divisible by 16 with a remainder of 1. (For example, row 33). 3. There must be a formula that references at least one cell in a row above the run of formulas.
If you'd like to check this out, try this: Go to row 33; Select D33:U33, and type: =C33+1; Press Ctrl-Enter; In cell A36, type: =D33; In cell A37, type: =D17; Change the value in cell C33
Under these circumstances, the value in cell A36 fails to recalculate automatically. However, if you press Ctrl-Alt-F9, the cell will recalculate as expected.
SWATTING THE BUG
In the last tip, we showed you (thanks to G.L.W.) how to investigate a recalculation bug in Excel 97. This time, let's see what you can do to get rid of the bug. First of all, you must have installed Microsoft Office 97 Service Release 1. If you haven't installed it yet, go to http://www.microsoft.com/office/office97/servicerelease/ and download it. Now go to http://www.microsoft.com/excel/xlenharch.asp?prev=313111 and download the Auto-Recalculation Patch to your hard disk. Now run the SR-1 installation patch. When that's completely finished, make sure Excel isn't running and run the recalculation patch program to install it (the file is Xl8p1.exe).
The first time you run Excel with the patch installed, the automatic calculation will fail to work. Press Ctrl-Alt-F9 to recalculate. From this point on, the automatic recalculation will work.
Note: Excel's patch Web site notifies us that ANOTHER patch will be issued soon because the first patch (which you've just downloaded) does not resolve all of the calculation problems. But they do offer a work-around that you can use in the meantime. Watch this space for further instructions on repairing the Excel shared formula bug.
A NEAT TRICK YOU MAY NEVER USE
Ever really, really wish you could draw a rectangle that was precisely the same size as a cell or block of cells? Well, as a direct result of their 24-hour-per-day, seven-day-per-week clicking around in Excel worksheets just to find out every scrap of information they can possibly pass on to you, the chief scientists here at Dummies Daily have your answer! 1. Select the cell or range of cells the size of which you want your rectangle to be. 2. In the Drawing toolbar, click the Shadow button. 3. Click ANY shadow option from the palette. For reasons we do not yet fully understand, Excel draws a shadowed shape precisely the size of your selection. 4. Optional: To remove the shadow, click the border of the shape, click the Shadow button again, and click No Shadow.
This technique also works with the 3-D button on the Drawing toolbar. What else can you say but "Wow?"
MAKE YOUR COMMENTS MORE COLORFUL--PART 1 OF 2
However many years ago, those clever folks at a major mining and manufacturing company in the Midwest invented sticky notes and colored them yellow. A few years later, not to be outdone by mere miners, software manufacturers began adding "comment" features--modeled on sticky notes--to their software programs and (showing the full range of the inventiveness and originality you can expect to find in the software industry) also colored them yellow.
Hence Excel 97's yellow comment notes. Fortunately, it's not a color you're stuck with. To change the color of any comment note, follow these steps: 1. Click the BORDER of the comment note. 2. On the Format toolbar, click the arrow next to the Fill Color button. 3. Select a color from the palette.
If you like, you can have as many differently colored comments in the same sheet as you want. Of course, you still won't be very original (those miners already came up with notes in other colors), but you will still have the satisfaction of having multicolored sticky notes.
MAKE YOUR COMMENTS MORE COLORFUL--PART 2 OF 2
Last time, we showed you how to change the color of individual comments in Excel. But what if you use a LOT of comments--and want them all to be the same color, but not yellow? Are you sentenced to a lifetime of changing comment colors one at a time?
Fortunately, no. You can change the default color of ALL your comment notes as follows: 1. Right-click any blank area of your Windows 95 desktop. 2. From the shortcut menu, choose Properties. 3. Click the Appearance tab.
4. In the Item drop-down list, select ToolTip. 5. Click the arrow next to the Color box and select a color for your comments--or click Other, select or specify another color, and click OK. (Tip: Choose a light color!) 6. Click OK.
From now on, every new comment you create in Excel--or in Word or any other Office program that uses comments--will be the color you selected. Not only that, but your ToolTips--those little help messages that appear when you wave your mouse over a toolbar button--also appear in this color. HOWEVER, any note you created
previously retains its original color (or the last color you colored it). Can you remember all that?
OUT, DAMNED TEXT BOX
How I Went Clicking Mad, Chapter 44: You draw a text box on your Excel sheet, using the Draw tools. At some point thereafter, you decide you no longer want the text box. You decide to delete it by using the age-old method practiced by deleters for centuries: You click the text box and press Delete. AND NOTHING #@*($&@!! HAPPENS!
Well, pardon us for dragging you kicking and screaming into the next millennium, but the centuries-old deletion methods just don't cut it anymore (Get it? "Delete?" "Cut?" We crack ourselves up sometimes). They've been replaced by new and more highly sophisticated deletion methods. To wit: 1. Click the text box's BORDER (anywhere else just won't do). 2. Press Delete.
Note that you can still delete non-text-filled shapes using the centuries-old technique. Progress isn't really all it's cracked up to be.
WHAT'S THE BIG DEAL ABOUT MERGE AND CENTER, ANYWAY?
Excel 97's Merge and Center feature makes centering a title over several columns of data easy. Just click the Merge and Center button on the toolbar. (This button looks like a little mime "a" trying to get out of a little mime box.) But we've had more than a couple of subscribers ask us, "Suppose you want to undo combine and center--in a different work session, when the Undo command no longer works. What do you do then?"
Well, if you just can't stand the way the centered title looks, you can get rid of it as follows: 1. Right-click the cell containing the title. 2. From the shortcut menu, choose Format Cells. 3. In the Format Cells dialog box, click the Alignment tab. 4. Deselect Merge Cells, set Alignment to General, and click OK.
Excel splits the cells and left-aligns your title so that your title looks just the way it would have in the old days, before the Merge and Center feature. Kind of makes you nostalgic, doesn't it?
COULD I HAVE A TOOLBAR ON THE SIDE?
In past Excel 97 tips, we've described how to hide toolbars, how to display toolbars, how to drag toolbars onto the main workspace, and even how to add buttons to them. Not being ones to give up on a topic
until it's completely exhausted, today we're going to show you how to do yet another thing with toolbars: drag them to the SIDE of your screen. Please follow along at home: 1. Click the double bar at the left edge of the toolbar you want to move and hold down the mouse button. 2. Drag the toolbar to the very edge of the left or right side of the screen. 3. When the bar's orientation changes to vertical, release the mouse
button.
Note: If the toolbar you're dragging contains drop-down selectors, such as the Style or Font selectors on the Formatting toolbar, these selectors do NOT display when you position the toolbar on the side of the screen. Don't say we didn't warn you.
GET YOUR WINGS WITH EXCEL
If we told you it was possible to have rip-roaring, barnstorming fun right in ANY Excel workbook, you'd probably give a collective "yeah, right." But it's true: The mischievous, fun-loving gang at Microsoft
has hidden a flight simulator in Excel! And thanks to subscriber Doyle Ramey, we can tell you how to find it: 1. Click the New button to create a new workbook. 2. Press F5; the Go To dialog box appears. In the Reference box, type X97.L97 and then click OK. 3. Press your Tab key once; you should end up in cell M97.
4. All at one time, press Ctrl + Shift AND click the Chart Wizard button.
In a few moments you should be flying. Steer with the mouse and look for the credits screen and the lake area. Use the left-mouse button to move forward and the right-mouse button to move backward. When
you've had enough--assuming you ever do--exit by pressing the Ctrl + Shift + Esc buttons simultaneously. And don't ever call us sticks-in-the-mud again.
IT'S NOBODY'S DEFAULT BUT EXCEL'S
Alejandro Rodriguez, an inquisitive Dummies Daily subscriber, wrote us with this very good question: "How can I set Excel to turn grid lines off and page breaks on with each new worksheet I create? Right now I have to constantly go to my settings and change these for each new sheet."
Well, Alejandro, we have the answer, but it's not as simple a you might think. To make every new file open with the View settings you want, you have to create a new Default Workbook Template with those settings selected. Here's how to create a new default template: 1. Click the New button to create a new, blank worksheet. 2. Choose Tools + Options. 3. Click the View tab, make any changes you want, and choose OK. For
example, to turn gridlines off and page breaks on, you would deselect Gridlines, select Page Breaks, and click OK. 4. When Excel returns to your worksheet, choose File + Save As. 5. In the Save as Type list box, choose Template (*.xlt). 6. Under Save In, navigate to the Xlstart folder (you can most likely find this in C:\Msoffice\Office\Xlstart\). 7. In the File Name box, type book 8. Click OK. From now on, every new workbook you create features page breaks and no gridlines.
WHO SAID PERCENTS HAVE NO VALUE
Dummies Daily subscriber Kathleen Alme recently asked, "How do I get a chart in Excel to show value labels instead of percent labels?" Well Kathleen--and the rest of you out there in e-mail land--you can have Excel show you either OR both! Here's how: 1. Right-click your chart and choose Chart Options from the pop-up menu. 2. In the Chart dialog box, click the Data Labels tab. 3. Under Data Labels, choose Show Value (or whatever other option that corresponds to the type of labels you want). 4. Click OK.
Tip in a tip: The Show Values and Percents option is ESPECIALLY useful for pie charts, which show percents by default, often leaving you and your audience wondering what the real numbers are.
ONE KEY, FOUR CORNERS
Many, many tips ago, we told you how you could more easily enter data into an area of an Excel worksheet by selecting the area first. Well, a certain Shri Govande, a member of our perspicacious readership, discovered something else about navigating a selected worksheet range: namely, how to move quickly from one corner of the selected range to the next.
- To move clockwise to the next corner of a selected range, press Ctrl + . (that is, Ctrl plus the period key).
- To continue moving clockwise from one corner to next corner of the range, press Ctrl + . (period) again.
This is an especially convenient way to zip around very large selected ranges.
TODAY IS TODAY, BUT TOMORROW IT SHOULD BE YESTERDAY
Today's tip topic comes all the way from Norway and Dummies Daily subscriber Anders Normann, who writes: "I've tried using Excel's TODAY() function to enter dates in a spreadsheet that's updated regularly, but not every day. The problem is that ALL the dates are updated every time I open the spreadsheet. Is there a way to lock the date when the function is first used?"
Well, yes and no: There IS a way to enter a date that won't change the next time you open the sheet, but you can't do it with the TODAY() function. Try this instead: 1. Select the cell into which you want to enter the date. 2. Press Ctrl + ; (that is, Ctrl plus the semicolon key).
Doing so enters the current date in the cell, and the date doesn't change when you reopen or recalculate the sheet. Anders, we trust you'll send a big "hello" to all our friends in the Land of the Midnight Sun.
FORMATTING: WHO NEEDS IT? PART 1 OF 2
It's happened to all of us at one point or another: We get a little crazy with formatting and styles, loading bold, italic, borders, cell shades, and all manner of things onto numbers until we can't remember what was in the cell in the first place. So you may be comforted to know that no matter how much formatting you add to a cell or range, you can remove it ALL with one quick command: 1. Select the cell or range you want to clean up. 2. Choose Edit + Clear + Formats.
Excel clears everything--number formatting, attributes, cell borders, and shades--leaving you with the number and only the number. Note that when you use this command on cells containing dates, you'll be left with the "date number"--the number Excel uses to calculate dates--instead of the date itself. For example, to Excel, December 13, 1922 is 8383. You may want to reapply the date format to make the number look like a date again.
FORMATTING: WHO NEEDS IT? PART 2 OF 2
Last time, we hooked you up with a great way to remove ALL the formatting from a cell or range. This time we're going to show you how to make this process even faster--by adding the Clear Formatting button to your Formatting toolbar! To add this button to the Formatting toolbar, follow these steps: 1. Choose Tools + Customize. 2. In the Customize dialog box, click the Commands tab. 3. Under Categories, select Edit. 4. In the Commands box, scroll down until you find the Clear Formatting button; drag the button into place on your formatting toolbar. 5. Click OK.
From now on, when you want to bring any over-formatted cell or range back to basics, just select the cell and click the Clear Formatting button, and then get on with the rest of your day.
WE STAND CORRECTED
A couple of weeks ago, in a tip entitled "Preview of Coming Attractions," we noted that you can preview some Excel files before opening them but that other files display a "Preview not available" message, meaning that there's no way you can preview them. Just another reason why ignoramuses like us can be grateful that there are truly knowledgeable folks, like subscriber Frank Krzystofiak, Ph.D., to set us straight. Frank suggested we mention that you CAN make sure an Excel file displays a preview; you just have to set its file properties to do so, as follows: 1. With the file open, choose File + Properties. 2. In the Properties dialog box, click the Summary tab. 3. At the bottom of the Summary tab, select Save Preview Picture; then click OK. 4. Choose File + Save to save the file.
From now on, this file will display a preview picture in your File Open dialog box (if you ask for a preview). Note that this option also increases the size of the file somewhat. You were expecting maybe a free lunch?
95
FORMAT SHORTCUTS Version 4.x, 95
When you're working with an Excel worksheet, you frequently need to set a cell's format. You can click the cell, choose Format, Cells and make your selection from the dialog box.
There's a quicker way to make those cell format changes, though. All you have to do is press a key combination, and you can apply the standard form of a number of formats. Let's say you want to use the
Currency format. Just click the cell to select it and then press Ctrl-Shift-$ to apply the default Currency format. Here is a list of some other formats and their appropriate keystrokes.
Format Keys
General Ctrl-Shift-~
Two decimal places (0.00) Ctrl-Shift-!
Percent (%) Ctrl-Shift-%
Scientific Ctrl-Shift-^
ABSOLUTELY RELATIVE Version 95
When you enter a formula in an Excel worksheet, you usually use the relative reference form. The formula =sum (a1:a10) is a relative reference formula. If you copy this formula and move it
to a new location, the formula will change to reflect its location. However, there are times when you might need a formula that uses absolute referencing. One way to handle this is to convert an
existing formula to absolute. You don't even have to remember how to enter an absolute formula. All you have to do is double-click the cell that contains the formula and then use the mouse to highlight
the formula. Now press F4 and then press Enter. Now you have a formula with absolute referencing.
While a formula is selected, you can use F4 to switch among all the referencing forms. If you'd like to give this a try, type in a formula, select it as we described, and then press F4 and watch the changes.
BORDER PATROL
Nothing makes a cell or range stand out from its surroundings like a nice outline border. And nothing makes adding a border more of a joy than knowing you have not just one but two relatively short and remarkably painless ways to get the job done.
Way number one: 1 Select the cell or range. 2. On the Formatting toolbar, click the arrow next to the Borders button. 3. From the Borders palette, choose one of the "border-all-around" buttons (the second-to-last or last button in the bottom row).
Way number two: 1. Select the cell or range. 2. Press Ctrl + Shift + &.
And as if that weren't easy enough, you also have a super easy way to get rid of the dang border: 1. Select the cell or range (the entire thing). 2. Press Ctrl + Shift +_ (that's an underline).
BORDER PATROL, THE SEQUEL
Last time, we provided fast ways to add and delete outline borders. Today, we offer an easier way to get to the Borders palette if you need (or want) to add LOTS of borders to LOTS of different ranges: "Tear" the Borders palette off of the Formatting toolbar and keep it close to the cells to which you're adding borders! Just follow these steps to put the palette wherever you want it: 1. On the Formatting toolbar, click the arrow next to the Borders button. 2. Grab the bar on top the Borders palette and drag the palette to the desired location.
Not only can you now move the palette close to the cells you're working on--saving valuable wear and tear on your wrist--but you can also skip the annoying "click the arrow next to the Borders buttons step" every time you want to add a border. Pretty cool, no? To remove the palette, just click the X button in the palette's top-right corner.
FAST FORMAT FRENZY
Quick: What are the five most commonly used formats in Excel worksheets? Naturally, we haven't done the laborious research required to give a provable answer to this question, but that doesn't stop us from having an opinion: We say they're Number (2 decimals), Currency (2 decimals), Percentage, Date (00-Mon-00), and Time (HH:MM XM). And, just coincidentally, Excel gives you a shortcut for applying each of these to a selected cell or range, without even picking up your mouse!
- Press Ctrl + Shift + ! to apply the Number format with two decimal places to a selected cell or range.
- Press Ctrl + Shift + $ to apply the Currency format with two decimal places.
- Press Ctrl + Shift + % to apply the Percentage format (with no decimal places).
- Press Ctrl + Shift + # to apply the 00-Mon-00 Date format.
- Press Ctrl + Shift + @ to apply the HH:MM XM Time format.
Finally, we would be remiss if we didn't tell you that to return any cell to Excel's default General number format, you need only press Ctrl + Shift + ~.
6.0
|