Is there a certain AutoShape that you really, really like to use in
your worksheets? Well, instead of having to poke through your
AutoShape palettes every time you want to draw the shape, we recommend
you just add the shape to your Standard toolbar so it's always handy:
1. Choose Tools + Customize.
2. Click the Commands tab.
3. Under Categories, choose AutoShapes.
4. Under Commands, find the AutoShape you like to use most and drag it
onto the Standard toolbar.
5. Click Close.
There's your shape, ready whenever you need it. How many other
things can you say THAT about?
START 'EM UP
Hey, even someone who can't read can tell you which page of a book
is page 1. That's why many documents don't start numbering pages until
page 2--or sometimes even page 3.
If you're printing a multipage workbook, maybe you don't want to
insult your audience by numbering page 1. And for precisely this
reason (okay, maybe not for PRECISELY this reason), Excel lets you
choose the page of your printout on which you can start your page
numbers:
1. Choose File + Page Setup.
2. Click the Page tab.
3. In the First Page Number text box, type the number of the page on
which you want to start your page numbering.
4. Click OK.
Another potential source of offense eliminated. Now if you can just
find a better looking business suit. . .
CHECK IT ALL
You know the old joke:
Bob: I just checked this entire workbook.
Joe: Oh, that's too bad--I was thinking it would look better striped.
What Bob meant, we think, is that he SPELL-checked the entire
workbook--which requires a little trick, because, by default, Excel
spell-checks the current worksheet only:
1. Right-click any worksheet tab.
2. Choose Select All from the shortcut menu.
3. Press F7 to begin spell checking.
Feel free to borrow our little joke, by the way. As they say, "Use
it three times, and it's yours."
WHEN ONE FORMAT IS NOT ENOUGH
Coworkers: You can't live with 'em, you can't live without 'em--and
if they haven't upgraded to Excel 97, sometimes you can't send 'em
your spreadsheets, either. Fortunately, Excel 97 has predicted your
colleagues' sluggishness and included a feature that lets you save
your workbooks in a single file that includes BOTH the Excel 97 format
and the Excel 5.0/95 format! To make the save:
1. Choose File + Save As.
2. In the Save as Type list box, choose Microsoft Excel 97 & 5.0/95
Workbook.
3. Click Save.
Now you can share your rows and columns with all the rank and file.
MAKE DUAL SAVES THE NORM
Last time, we told you how to save an Excel 97 file in dual (Excel
97 and Excel 5.0/95) format. If you find you need to save the majority
of your files in this format, why not make it the default?
1. Choose Tools + Options.
2. Click the Transition tab.
3. In the Save Excel Files As list, select Microsoft Excel 97 & 5.0/95
Workbook.
4. Click OK.
>From now on, Excel 97 saves ALL your files in this dual format so
that you don't even have to think about it yourself. Which is good,
because what with your daughter dating that biker, you have enough to
think about as it is.
WE'VE TOLD YOU--ONE HUNDRED TIMES
Actually, we've never told you once, so it's about time we passed
along this simple but oft-ignored technique: When you're entering
percentage values--values you plan to format as percentages or values
into cells already preformatted as percentages--enter them in DECIMAL
format. For example, to enter fifteen percent, type
.15
Why? Because when you format a cell as a percentage, Excel
multiplies the value by 100. Therefore, .15, formatted as a
percentage, yields 15 percent; 15 formatted as a percentage yields
1500 percent. The latter is a little too optimistic to believe, don't
you think?
GET THE POINT (MODE)
Sometimes we're so busy finding obscure Excel tips that we forget
the basics--which is a shame, because the basics are usually the tips
that save you the most work.
Consider Point mode, for instance. Point mode is Excel's way of
letting you enter a formula without typing a single cell address--you
just "point" to the cells you want to include in your formula. 1.
Select the cell in which you want to enter the formula.
2. Type the beginning part of your formula. For example, if you want
to take the average of the numbers in cells A1 through A10, you'd type
=average(
3. With your mouse, drag to select the range (cells A1:A10 in our
example). Don't worry--the formula knows what you're doing; in fact,
the range address appears in your formula!
4. Type your closing parenthesis ")" and press Enter to enter the
formula.
You can create any formula--or any part of any formula--in this
way; you can even point to cells in other worksheets. In fact, we show
you how to do that tomorrow.
POINT MODE MADNESS
Last time, we showed you how to create a formula just by pointing.
Today, we show you how to create a formula by pointing to ANOTHER
SHEET!
1. Select the cell in which you want to enter the formula.
2. Type the beginning of your formula--such as
=average(
3. With your mouse, click the worksheet tab of the sheet containing
the cells you want to reference.
4. Drag to select the cells. Notice that, although you can't see the
formula in its cell, you can see it--complete with the selected range
address--in the formula bar.
5. Type your closing parenthesis ")" and press Enter to enter the
formula.
Pointing isn't only easier; it also prevents mistakes resulting
from typos. When you point, you don't type.
SURE, BUT HOW HOT IS IT IN LONDON?
Hey, it's a global market now--which means you have to know global
things, such as the temperature in Celsius. Good thing Excel is a bit
more cosmopolitan than you are; it comes with a function, called
CONVERT, that changes any Fahrenheit temperature to Celsius, and vice
versa.
First, make sure you have the function:
1. Choose Tools + Add-ins.
2. Under Add-ins Available, make sure Analysis ToolPak is checked.
3. Click OK.
To convert a Fahrenheit temperature--such as 72 degrees--to
Celsius:
1. In any cell, type
=CONVERT(72,"F","C")
2. Press Enter. Excel returns the Celsius temperature (in this
case, 22.222).
To convert a Celsius temperature, such as 72, to Fahrenheit:
1. In any cell, type
=CONVERT(72,"C","F")
2. Press Enter. Excel returns the Fahrenheit temperature (in this
case, a sweltering 161.6).
PROTECTION QUESTION
Subscriber Mark Folks writes, "I created a spreadsheet that other
employees need to use, but they keep typing over and destroying the
formulas. Isn't there a way to protect cells containing formulas so
that they cannot be changed, while leaving the other cells
unprotected?"
You bet there is, Mark. In fact, there are several ways, but we'll
just give you the quickest:
1. Press Ctrl + A to select the entire sheet.
2. Choose Format + Cells; in the dialog box that appears, click the
Protection tab.
3. Deselect Locked and click OK.
4. Choose Edit + Go To.
5. Click Special.
6. Under Select, choose Formulas; then click OK. Excel selects all the
cells that contain formulas.
7. Choose Format + Cells; in the dialog box that appears, click the
Protection tab.
8. Select Locked and click OK.
9. Choose Tools + Protection + Protect Worksheet.
10. In the dialog box that appears, click OK (you can do the password
thing if you like, but it sounds like people are changing your
formulas accidentally, so you probably don't need it).
11. Save the workbook.
If you like, test this protection out by double-clicking any
formula cell; Excel displays a warning and won't let you in!
REPETITION NEEDN'T BE SO BORING
Nothing is more satisfying than formatting a range of cells to look
just the way you want; nothing is more tedious than having to repeat
the same steps on another range of cells you want to look exactly the
same way. So next time you complete a format you KNOW you want to
repeat, skip the tedium and try this instead:
1. Immediately after you format the cells, select the NEXT range of
cells you want to format in the same way.
2. Press F4 (alternatively, press Ctrl + Y).
3. Repeat Steps 1 and 2 to apply the formatting to as many other
ranges as you like.
HE'S RIGHT, YOU KNOW
Longtime subscriber and frequent contributor Dan Riordan writes,
"In a recent Excel 97 tip (QUICK-CLICK MOVEMENT, 04/26/99), you
mentioned that you can double-click the appropriate edge of a cell to
get to the next filled cell in that direction. You can do the same
movement by using Ctrl + the arrow keys."
That's absolutely correct, Dan; thanks for giving the keyboard
equal time.
MORE OF THE SAME
Okay, let's run down all we've covered over the past couple of
tips: You know how to jump to the next FILLED cell in any direction
and how to jump to the last EMPTY cell BEFORE the next filled cell in
any direction. How'd you like to know how to quickly select all the
empty cells between the current cell and the next filled cell?
We knew you would:
1. Hold down the Shift key.
2. Double-click the cell border corresponding to the direction in
which you want to select.
This is a great way to select a row or column of cells for data
entry, by the way.
DAZED AND CONFUSED, DUMMIES STYLE
Subscriber Charissa Pinnick writes, "In your comments at the end of
a recent tip entitled YOU SAY SELECTION, I SAY PRINT AREA (05/04/99),
you stated that using the Print icon reverts back to the Entire Sheet
default. That is incorrect. Once the print area has been selected and
the file is saved, the print area is the default until you change it."
Alas, this is true. The Print icon DOES default to your print area,
even if you HAVEN'T saved the file (saving ensures that the print area
is the default NEXT time you open the file). What the Print icon does
NOT do is remember any selection you specified (in the Print dialog
box) the last time you printed. Sorry to have mixed this up.
SPEAKING OF THE PRINT AREA. . .
. . . as we were just a tip ago, our subscriber Ms. Pinnick also
alerted us to the presence of a Set Print Area icon--probably a real
handy addition to the toolbar for those of you who regularly specify
or change print areas. To add the icon:
1. Choose Tools + Customize.
2. In the Customize dialog box, click the Commands tab.
3. Under Commands, find and drag the Set Print Area icon to your
Standard toolbar (next to the Print icon would be a good spot).
4. While you're at it, find and drag the Clear Print Area icon to the
Standard toolbar.
5. Click Close.
To use the Set Print Area icon:
1. Select the area you want to print.
2. Click the Set Print Area icon.
To clear any existing print area, just click the Clear Print Area
icon.
Thanks again, Charissa.
MANUAL INSERTION
Wanna insert some cells? Before you go scrambling to the menu or
the right mouse button, try one of these alternatives:
To insert cells BELOW the selected cell:
1. Hold down the Shift key.
2. Click the cell's Fill Handle (the bottom-right cell handle). The
mouse pointer changes to a double-arrow pointer.
3.Drag down to cover the area in which you want to insert cells.
To insert cells to the RIGHT of the selected cell:
1. Hold down the Shift key.
2. Click the cell's Fill Handle (the bottom-right cell handle). The
mouse pointer changes to a double-arrow pointer.
3. Drag to the right to cover the area in which you want to insert
cells.
YOU CAN'T DO THAT
Dipping today into our bursting mailbag, we found this question
from Excel user Mike Broadwell: "I have to print many spreadsheets in
Excel 97 that have three columns and may be several pages long. Is
there a way to format the page to automatically print in
newspaper-style columns? I'd appreciate an answer as soon as possible,
as this is driving me crazy!"
Well, Mike, stop torturing yourself. What you want to do can't be
done. Excel can't "snake" your columns. It can only print them as
they're arranged on your sheet. Newspaper-style columns are a job for
your word processor, not your spreadsheet program.
NONSCIENTIFIC METHOD
Subscriber Steve Harvey writes, "Is there a way to prevent Excel 97
from autoformatting an alphanumeric entry with an 'E' in the
middle--such as '3E8'--into scientific notation?"
There sure is. Identify the entry as TEXT, and not a number: Type
an apostrophe before the alphanumeric entry. For example, to type 3E8
into a cell:
1. Select the cell.
2. Type '3E8.
Excel will NOT AutoFormat the entry.
SHUT DOWN, ALL OF YOU
Having several Excel workbooks open during a single session is not
uncommon. What IS uncommon is knowing how to close--and save--all of
the workbooks with a single command. Today we pass along to you that
uncommon knowledge:
1. Hold down the Shift key.
2. Choose File + Close All (which is what the File + Close command
changes to when you hold down the Shift key).
3. Click Yes to All.
Excel saves and closes every one of your open workbooks. G'bye.
CHART HELP FOR THE GRAPHICALLY CHALLENGED
Ah, charts--you can't beat 'em for translating your boring numbers
into exciting shapes and colors. Exciting, that is, for all but a few
people who just can't make the transition and would rather see the
cold hard figures than a stack of colorful bars.
No problem, numbers-guys: Excel makes it easy to see the numbers
behind ANY chart element, any time you like:
1. Point to any element in your chart.
2. Wait.
In a few seconds, Excel displays a little yellow box--called a
Chart Tip--that tells you the data series and the value represented by
that chart element. This information is pretty handy even if you
AREN'T graphically challenged. `
WHAT--NO CHART TIPS?
Last time, we told you how to display Chart Tips, which tell you
the data series and value represented by a chart element. But some of
you, no doubt, couldn't get the tips to display. Why? Our best guess
is that somehow you--or some deviant with whom you work--has set Excel
to NOT display Chart Tips. Correct the problem immediately, as
follows:
1. Choose Tools + Options.
2. In the Options dialog box, click the Chart tab.
3. Under Chart Tips, select both Show Names and Show Values.
4. Click OK.
You'll never again be denied a Chart Tip.
MYSTERY DATE
Dummies Daily subscriber and bill-collector extraordinaire Leo
Mitchell writes, "I make all my invoices on Excel, and every time I
review them, the original date that I entered in them changes to the
date I am reviewing them on. How do I make the original date remain
unchanged unless I want to change it???"
Easy, Leo. Enter the date AS A DATE and don't use the =TODAY()
function. For example, if you create an invoice on May 7, 1999:
1. Type "5/7/99" (without the quotation marks).
2. Press Enter.
This date never changes because you entered it specifically.
(Tip-in-a-tip: If you don't know the date, just check the right end of
your Windows 95 taskbar.)
WE DUNNO--DO YOU?
Subscriber Patricia Weiler recently blindsided us with this poser:
"When I insert a comment, my name automatically appears in the box. Is
there any way to set Excel to automatically include the date the
comment was made?"
Well, after extensive research, Patricia, we can't find one. So as
we so often do when we're completely stumped, we put the question to
our subscribers. If you know of a way to set Excel to insert dates in
comments, please let us know, and we'll make you famous for a day.
ARIAL--WHAT THE WORLD NEEDS NOW IS MORE ARIAL
Like about 1,000 other Windows programs, Excel 97 uses Arial as its
standard font. While we can't imagine a world without Arial, we don't
understand why we can't have a serif now and then. If you agree, you
can make all your future worksheets Arial-free--by changing Excel's
default font to something else, as follows:
1. Choose Tools + Options.
2. In the Options dialog box, click the General tab.
3. In the Standard Font box, choose another font. (Note: You can also
change the font size if you want; some fonts, especially serif fonts,
are easier to read at 11 or 12 points than at the default 10 points.)
4. Click OK.
5. Choose File + Exit; save the files if Excel prompts you to do so.
6. Restart Excel.
Type in any cell on the new, blank worksheet; there you can see
your new font in action.
DECIMAL MESS-IMAL
Dummies Daily subscriber Don Little reports this problem: "Whenever
I enter a number (for example, 1200) it appears in the spreadsheet as
one hundredth of the number (to continue the example, 12.00). Why is
this occurring? I have not changed anything since the installation of
the program to make it do this!"
Well, Don, you may not have done anything, but SOMEONE (maybe an
interoffice enemy!) must have changed Excel's Fixed Decimal settings
to automatically format entries to two decimal places (accounting
types often do this, because it lets them enter numbers as they would
in a financial calculator or cash register).
Not to worry. You can resolve the problem quickly:
1. Choose Tools + Options.
2. In the Options dialog box, click the Edit tab.
3. Deselect Fixed Decimal.
4. Click OK.
No more unwanted decimals.
MORE ON DECIMALS
Last time, we helped a reader find out why Excel was automatically
formatting his entries to two decimal places. Today, we offer a
variation on the decimal theme--by reminding you that to format ANY
entry to a specific number of decimal places, you needn't look any
further than your trusty Formatting toolbar:
1. Select the cell(s) containing the data you want to format.
2. Click the Increase Decimal icon once for every additional decimal
place you want to display; OR click the Decrease Decimal icon once for
every decimal place you want to hide.
You have to admit, this method is a lot easier than going through
all that Format menu nonsense.
WE WANNA HELP SOMEONE THIS FRIENDLY--BUT WE CAN'T
"Hi there," writes subscriber Idham Ismail. "Is there any way to
increase the Excel's Name box so that I can see all the names in the
sheet without scrolling, as I could in 1-2-3?"
Try as we did, Idham, we could not find a way to change the size of
the Name Box drop-down list; it seems to display seven names (maybe
fewer, depending on your screen resolution) and no more. Maybe another
of our industrious subscribers will come to the rescue?
SOME HELP WITH NAMES, AT LEAST
Last time, we were forced to admit that we knew of no way to
lengthen the Name Box drop-down list so that you could see all your
names without scrolling. Today, with the guilt of failing to satisfy
you still fresh in our souls, we thought we'd offer a shortcut for
using names in formulas.
Next time you'd like to add a name to a formula you're writing:
1. With the cursor positioned where you want to insert the name,
press F3. The Paste Name dialog box appears.
2. Using your up- and down-arrow keys, select the name that you want
to insert (you can use the mouse, too, but why take your hands off the
keyboard?).
3. Press Enter.
The name appears in the formula. This tip wasn't exactly what you
were looking for, but it does assuage our guilt, and isn't that what's
most important?
AWESOME AUTOSUM
If you've spent just a few minutes with Excel 97, you probably know
that to quickly sum any row or column of data, you can
1. Select the cell just beneath the column or just to the right of
the row.
2. Click the AutoSum icon (on the Standard toolbar).
What you may not know is that you can get the same job done without
so much as a glance at the mouse:
1. Select the cell just beneath the column or just to the right of
the row.
2. Press Alt + = and press Enter.
Of course, you may still GLANCE at the mouse. After all, it's so
lovely . . . and you're only human.
IN WHAT DIRECTION IS YOUR ENTRY?
You know, we've sent you more than a few tips on changing the
"selection direction" of the Enter and Tab keys. What we've failed to
mention--and what some of you may have already discovered--is that you
can have complete control over the "after entry" direction just by
using the arrow keys to complete your entries.
For example, to move one cell to the left after an entry:
1. Type your entry into a cell.
2. Press the left-arrow key.
This technique works with the up-, down-, and right-arrow keys,
too.
I CAN PRINT CLEARLY NOW
You've formatted your cells in brilliant color. But your printer is
black and white. So on paper, your brilliant colors are translated
into bland shades of gray--gray that sometimes is dark enough to make
your cell contents hard to read.
Guess the only thing to do is reformat all those colored cells to
light gray or no color at all, right? WRONG.
Anticipating a legion of black-and-white printer users who couldn't
resist formatting in color, the makers of Excel have included a neat
feature that converts your colors to sensible, legible grays:
1. Choose File + Page Setup.
2. Click the Sheet tab.
3. Under Print, select Black and White.
4. Click OK.
5. Print as usual.
One warning: Like many of Excel's other color printing features,
this one does NOT seem to work on an HP LaserJet 4 printer. Wish we
knew a reason why.
DON'T FORGET THE FORMAT BUTTONS
Attention, veteran spreadsheet users: Thanks to the "feature
poverty" of previous spreadsheets with which you may have worked,
you've probably developed formatting habits that, while once
necessary, are now costing you valuable time. For example, when you
want to format a cell for currency, you probably select the cells,
choose Format + Cells, click the Number tab, and so on.
Well, STOP AND SMELL YOUR TOOLBAR. Or at least look at it--the
Formatting toolbar, we mean. Because to apply the Currency format, all
you have to do is this:
1. Select the cells.
2. Click the Currency Style button on the Formatting toolbar.
Likewise, to apply the Percent format:
1. Select the cells.
2. Click the Percent Style button on the Formatting toolbar.
And to apply the Comma format:
1. Select the cells.
2. Click the Comma Style button on the Formatting toolbar.
Yes, letting old habits die is hard. Try to think of it as natural
selection.
ADD ANOTHER WORKSHEET, WICKED FAST
Quick, you need another worksheet--what do you do?
Press Ctrl + Shift + F1.
Excel puts a new worksheet BEFORE the current worksheet. To move
the new worksheet to another position, just drag the worksheet tab to
the left or the right.
That's it for today--after all, we DID say "wicked fast."
BETWEEN THE SHEETS
Subscriber Barb Reed writes, "You guys give some really good tips.
(Why, thank you, Barb.) Here's one I accidentally discovered. In Excel
workbooks that have a lot of worksheets, instead of scrolling through
all of the worksheets to find the one you want, you can
1. Right-click the navigation arrows in the lower left-hand corner
of the page display (next to the horizontal scroll bar).
2. Choose the worksheet you're looking for from the shortcut menu."
Wow--a compliment AND a tip. That's as good as it ever gets around
here. Thanks again, Barb.
WE CAN'T HELP BUT MENTION . . . THAT WE DID IT THE HARD WAY
In our April 9 tip (WE CAN'T HELP BUT MENTION . . .), we showed you
how to use Excel's WEEKDAY function to convert a date into its
corresponding day of the week. Well, if we earned a quarter for all
the e-mail responses we received from THAT tip, we could certainly
retire from the tip business altogether. Seems like everybody but us
realized that the easiest way to turn a date into a weekday is to do
the following:
1. Select the cell containing the date.
2. Right-click the cell and choose Format Cells from the shortcut
menu.
3. Click the Number tab.
4. Under Category, select Custom and, in the Type box, type "dddd"
(without the quotation marks).
5. Click OK.
Sorry--from now on, we'll do our best to actually SAVE you time.
INSTA-NAMES
You've got a huge table, and you want to turn all the row and
column labels into names. Big job, right? Wrong again,
financial-statement breath. If your cursor is ANYWHERE WITHIN THE
TABLE, you can get the whole job done with three--as in 3--keystrokes:
1. Press Ctrl + Shift + *. This keystroke selects the entire
current table--that is, the current region enclosed by empty rows and
columns. (If your table ISN'T enclosed by blank rows and columns OR if
your table contains blank rows and columns, you have to select the
table manually.)
2. Press Ctrl + Shift + F3. This keystroke displays the Create Names
dialog box, with Top Row and Left Column already selected!
3. Press Enter. This keystroke is the same as clicking OK.
Your names have been created. Naming your kids should be this easy.
ANOTHER WAY TO NAME
Suppose you want to name an unlabeled range of cells--that is to
say, you haven't entered the name of the range anywhere in your
worksheet. Unfortunately, the little Ctrl + Shift + F3 technique we
showed you last time doesn't do you any good.
Try this instead:
1. Select the range.
2. In the Name box--that's the box directly to the left of the Formula
bar, with the measurements of the range in it--type the name of the
range.
3. Press Enter.
That's it--you named the cells.
GOT NAMES?
If you've got names in your worksheet and you want to write
formulas that refer to these names, Excel makes the job about as easy
as you can imagine (without actually sending someone from Redmond,
Washington, to enter the formula for you):
1. Start typing your formula.
2. When you get to where you want to insert a name, press F3.
3. In the Paste Name dialog box, select the name you want to insert
into your formula.
4. Click OK.
5. Continue entering your formula, repeating Steps 2 through 4 if you
need to insert another name.
Not only have you spared yourself the digital (in both senses of
the word) pain of typing the names, but you've also eliminated the
possibility of misspelling the name.
ANOTHER FORMULA HELPER
Last time, we showed you how to paste names into your Excel
formulas instead of typing them yourself. "But what about those of us
who DON'T use names?" those of you who don't use names ask. "Could WE
get a little help here?"
Sure. If you want help entering a formula--one that includes a
function, that is--try this:
1. Type the name of the function, such as "=SUM" (without the
quotation marks).
2. Press Ctrl + Shift + A.
Excel displays the function's arguments RIGHT THERE IN THE CELL
(and in the Formula bar); all you have to do is replace the function
arguments with cell addresses.
Easy enough? If not, stay tuned.
REAL HANDHOLDING
So let's see, where were we . . . ah, yes. You want even MORE help
completing your Excel functions. Say, for example, that you don't even
know WHICH function to use, let alone how to enter it. If this is the
regrettable position in which you find yourself, follow these steps:
1. Click the Paste Formula button (on the Standard toolbar) OR
press Shift + F3.
2. In the Paste Function dialog box, select the Category; then select
the function that best suits your goals (when you highlight a
function, an explanation of the function at the bottom of the dialog
box appears).
3. Click OK. Excel inserts the function name in your formula and then
displays the Function palette.
4. In the Function palette, enter each argument in each text box OR
click the button at the right of each text box to SELECT the cell(s)
for each argument. When all your arguments are entered, the Result
appears at the bottom of the palette.
5. Click OK. Excel enters the formula for you, putting the arguments
in the right place.
That's it. We can't make entering functions any easier.
YOU CAN'T DO THAT
Dummies Daily subscriber and spreadsheet formatter Alejandro
Rodriguez asks, "Is there a way to find and replace formats in Excel
97, as you can in Word 97?"
Alas, Alejandro, while this capability would be very useful to have
in Excel, we can't find it, and we know of no "trick" for doing the
same. If someone out there knows differently, PLEASE write us so that
we can help Alejandro out.
BLANKETY BLANK
Want to insert some blank cells among some not-so-blank cells in
your worksheet? Here's a fast, controlled way to do it:
1. Select the area where you want the blank cells to appear.
2. Press Ctrl + Shift + + (the plus sign).
3. In the Insert dialog box, select the direction you want the current
cells to shift to make room for the new blank cells.
4. Click OK.
REMEMBER: INSERTING CELLS CAN AFFECT FORMULAS BASED ON THE CELLS
THAT SHIFT. Make sure you follow up any insertion by checking those
formulas.
Good luck!
PROBLEM PRINTING COLOR
Frustrated Excel user Larry Schaibley writes, "I opened an Excel 97
invoice template, customized it, and added my logo. The logo is in
color and any payments made are shown in red. When I go to print,
though, it prints only in black and white. How do I get it to print in
color?"
About the only reason this SHOULD be happening is that, unbeknownst
to you, Excel is telling your printer to print the template in black
and white (which is an option Excel offers to improve printing speed).
Set Excel to print in color, as follows:
1. With the problem file current, choose File + Page Setup.
2. Click the Sheet tab.
3. Under Print, deselect Black and White.
4. Click OK.
5. Save the worksheet.
The sheet should print in true color from now on.
IF IT'S HIDDEN, I DON'T WANT IT
We've showed you (several times) how to hide a column or row in
Excel. So suppose you've done that, and now you want to select a range
including cells AROUND the hidden cells but NOT the hidden cells
themselves. Can you do it all at once?
Sure:
1. Select the range, including the hidden cells.
2. Press Alt + ; (semicolon).
Excel eliminates the hidden cells from the selection. You see a
white line indicating the unselected hidden cells. Very, very handy.
THERE IS SUCH A THING AS WORKING TOO HARD
Excel user Christian DiPaolo writes, "You recently ran a tip that
showed how to create a "Next Window" button in Word 97. Is there a way
to make a similar button in Excel 97?"
Well, there probably is, but you really don't need such a button at
all. Because, while Word 97 no longer supports the old Windows 3.x
window-switching keyboard shortcut, Excel 97 still does. So to get to
the next open Excel window, you just do the following:
Press Ctrl + Tab.
A lot easier than making a button, huh?
WELL-ROUNDED WORKSHEETS
Dummies Daily subscriber Bob Michener tells us, "I've formatted
numbers to display one or two decimal places in a spreadsheet.
However, when other folks copy the data from my spreadsheet into their
own, they see many more decimal places. Is there a way to make sure my
decimal places survive the copy?"
Yes, there is: Use the ROUND function to round the results of all
your formulas (or those formulas that yield more decimal places than
you want). Suppose, for example, that in cell C10 you have the formula
C8/C9, and you want to make sure that the result is rounded to two
decimal places:
1. Double-click the appropriate cell; in this example, you'd click
cell C10.
2. Position the cursor just to the left of the equal sign and type
"ROUND(" (without the quotation marks).
3. Press End.
4. Type a comma.
5. Type "2" (or whatever number of decimal places you want to round
to, without the quotation marks).
6. Type )--that is, a close parenthesis--and then press Enter. Your
revised formula should look something like this:
ROUND(=C8/C9,2)
Excel rounds the result--and the rounding survives everything.
Repeat for every problem formula.
THEY'RE MY WORKSHEETS, BUT THEY'RE MY DOCUMENTS, TOO
If you're like millions of Excel users, you also use Microsoft
Word. And if you're like thousands of these millions, you're probably
a tad ticked off about the fact that Excel doesn't automatically save
your worksheets in the same My Documents folder in which Word saves
your documents. Wouldn't having them both in the same place be just a
TAD more convenient?
Well, you can MAKE Excel save your worksheets in the My Documents
folder:
1. Choose Tools + Options.
2. Click the General tab.
3. In the Default File Location text box, replace the existing path
(usually "C:\Windows\Personal") with "C:\My Documents" (without the
quotation marks).
4. Click OK.
>From now on, Excel saves new workbooks to the My Documents folder
by default. (You have to move previously saved workbooks to this
directory yourself.)
GET OUT OF IT
You select a cell or a range of cells. You right-click the cells
and choose Copy. You move around the sheet for a while and then decide
you don't want to copy after all. So you decide to move on to whatever
you want to do next--except that crazy dotted line keeps flashing
around the selected range of cells! How do you get rid of it? Pretty
much the same way you get rid of anything ELSE you decide not to
finish in Excel:
Press Esc.
The ants stop crawling around your selection, and you can get on
with your work.
ONE REASON TO BE HAPPY ABOUT THE END-OF-MONTH
Instead of asking a question, subscriber Jan Altman offers this
tip: "After reading your tip about filling a range with 20 Sundays
[ANOTHER AUTOFILL TIP (04/08/99)], I thought you might also want to
tell your users that Excel can AutoFill the last day of each
month--even though they're not the same number of days apart." Okay,
Jan. Here goes:
1. Type the last day of a month--as in "6/30/99"--in one cell
(without the quotation marks).
2. Type the last day of the next month--"7/31/99"--into the cell below
or to the left (again, without quotation marks).
3. Select both cells.
4. Using the AutoFill handle, drag to the left or down.
As Jan puts it, "Excel understands that you want the last days of
each month (including leap days, of course!)." Thanks, Jan. We LOVE
contributions like these.
WEIGHTED AVERAGE WONDERMENT
Excel convert--or convert-to-be--David Rubin asks, "We used to use
Lotus 1-2-3's @WEIGHTAVG function to calculate Dollar Weighted
Averages, but the formula does not convert to Excel 97. Is there an
Excel function that we can use as a replacement?"
The simple answer--and we try to stick with the simple answer
whenever possible--is NO. Excel has no weighted average function.
However, you can use Excel's SUMPRODUCT and SUM functions to get the
same result.
Take an example straight from 1-2-3's Help file. Suppose you want
to calculate the weighted average commission on sales based on the
following table:
SALES COMMISSION
25000 .04
34580 .05
77325 .04
Using 1-2-3's @WEIGHTAVG, which takes the syntax
@WEIGHTAVG(data-range; weights-range;[type]), you'd calculate the
formula here as @WEIGHTAVG(SALES;COMMISSION). In Excel, you use this
formula:
We tested the formulas out, and in both programs, the answer is
44784.62.
WHAT KIND OF A NAME IS SHEET 1?
It's true: The default names Excel assigns to your sheet tabs don't
show a whole lot of imagination. Fortunately, you can make them a lot
more descriptive without a whole lot of effort:
1. Double-click the sheet tab (doing so selects all the text on the
tab).
2. Type a new name for the tab--preferably one that describes the
contents of the sheet.
3. Press Enter.
Heck, you may even want to rename your sheets whether you need to
or not. Because even a sheet named "Ed" is better than "Sheet 1."
CLICK WITH YOUR KEYBOARD
Hey, toolbars and icons are great--except that they seem, at least
outwardly, to require a mouse. But things aren't always what they
seem: You can, if so inclined, operate any toolbar on your Excel
screen without lifting a finger from the keyboard:
1. Press Alt. Doing so selects the Excel menu (specifically, the
File menu).
2. Press Ctrl + Tab. This keystroke selects a toolbar--the Standard
toolbar, if you have that one displayed. The first button on the
toolbar appears "pressed." If necessary, continue to press Ctrl + Tab
until the appropriate toolbar is selected.
3. Use the arrow keys to select the button you want to use.
4. When the button you want is selected, press Enter to "click" it.
Sure, it sounds complex, but once you get the hang of the
keystrokes, you can type through those toolbars pretty quickly.
MORE TOOLBAR STUFF
Last time, we told you how to "work" the toolbars with the
keyboard. Today, we pass along three more keystrokes that can help you
navigate those toolbars a little more quickly. Once a toolbar is
selected, you can do the following:
* To move to the LAST button on a toolbar, press End.
* To move to the FIRST button on a toolbar, press Home.
You probably figured these out already, but we thought we'd clue
you in, just in case.
A RATHER LIMITED SORT
Dummies Daily subscriber and obvious sort maven Jill Dawkins
writes, "Is there any way to sort more than three columns at one
time?"
Jill, it all depends on what you mean by "more than three columns."
If you're asking whether you can sort a range of rows more than three
columns wide, the answer is yes. Just select the entire range before
performing the sort.
However, if you're asking whether you can sort BASED ON more than
three columns, the answer is NO, you can't. That is, unless anyone
else out there knows a trick that we don't know (which wouldn't
exactly be a surprise).
NEW WAYS TO BE SELECTIVE--PART 1 OF 2
In the past, we've given you LOTS of handy ways to select really
large Excel ranges; in the next two tips, we give you two more.
Today, we give you the handy Go To method. Suppose you want to
select the range A1:Z52:
1. Select the cell at the top-left corner of the range you want to
select (in this case, cell A1).
2. Choose Edit + Go To (OR press Ctrl + G).
3. In the Reference box, type the address of the cell at the
bottom-right corner of the range you want to select (in this case,
Z52).
4. Hold down the Shift key.
5. Press Enter (or click OK).
Presto! The troublesome large range is selected without a bit of
scrolling on your part.
Think that was easy? Wait 'til next time.
NEW WAYS TO BE SELECTIVE--PART 2 OF 2
Last time, we showed you the Go To method for selecting large
ranges; today, we pass along the even easier Name Box method.
Suppose again that you want to select the range A1:Z52:
1. Select the cell at the top-left corner of the range (in this
case, cell A1).
2. In the Name box (the box to the left of the formula bar), type the
address of the bottom-right cell in the range you want to select (in
this case, Z52).
3. Hold down the Shift key.
4. Press Enter (or click OK).
Easier still, isn't it?
SPLITTING CELLS . . . REMINDS US OF BIOLOGY CLASS
Either Marlene or Gerry Heiling (we can't tell which because they
share an e-mail address) writes:
"If I have a column of text in which each cell contains eight
characters, how might I create a second column in which each cell
contains only the first five of these characters? Is this possible?"
Of course it is, Marlene or Gerry. Suppose the column containing
the eight-character cells is column A (starting in cell A1), and you
want the corresponding first-five-character entries in column B
(starting in cell B1). Try this:
1. In cell B1, type "=LEFT(A1,5)" (without the quotation marks).
2. Press Enter. The LEFT function, as entered, copies from cell A1 the
first five characters from the left.
3. Choose Edit + Copy.
4. Select the remaining cells in column B that you want to copy the
formula into and press Enter.
TA-DAH!
A ZOOM OF YOUR OWN
Sure, Excel 97's Zoom box--on the Standard toolbar--is handy: Just
click its little arrow, and you can select from a handful of perfectly
useful Zoom percentages. Except what if you want to zoom to a
percentage NOT LISTED IN THE LIST?
Well, you COULD use the View + Zoom command to enter a Custom zoom
percentage. But why bother when you can do the following:
1. Click in the Zoom box on the Standard toolbar (doing so selects
the current zoom percentage).
2. Type the new zoom percentage you want--any number from 10 to 400.
3. Press Enter.
There you have it--the precise Zoom you seek, without messing with
the menu.
RANGE MAP
You've set up several names--maybe even scores of them--in an Excel
worksheet. Now, you're ashamed to admit, you can't quite remember
where they all are. Refresh your memory as follows:
1. Click in the Zoom box (on the Standard toolbar).
2. Enter any number from 10 to 39.
3. Press Enter.
Excel outlines EACH of your named ranges and displays the names in
large blue print. Let's see you forget a name NOW.
WHAAAAT? NO ZOOM??
Subscriber and Excel user Melaney--just "Melaney"--asks, "I'd like
to know why my Zoom (percentage) box no longer shows on my toolbars in
Excel 97. I don't recall doing anything that would have made it go
away."
What you probably did, Melaney, was move the Standard toolbar--on
which the Zoom selector sits--to a SIDE of the Excel display. When you
move any toolbar to a side of the Excel display, Excel hides the
selectors because they're wider than the now-vertical bar, and Excel
can't tip them on their sides. So to get the Zoom selector back,
double-click the double-line at the top of the Standard toolbar OR
drag the Standard toolbar to its original position at the top of the
Excel display.
Hope this works. And don't fret, Melaney: We here at Dummies Daily
have done LOTS of things we can't remember--things a lot more serious
than this.
GET A HANDLE ON AUTOFILL
We've told you in the past how to use Excel's AutoFill feature:
Select the first cell(s) in a sequence and then drag the Fill
Handle--the square handle in the bottom right of the selection--over
the remaining cells you want to fill.
But what if you don't see a Fill Handle? Whaddaya do THEN? The only
reason you WOULDN'T see a Fill Handle is if you turned off Excel's
Drag and Drop editing feature. So turn it on again as follows:
1. Choose Tools + Options.
2. Click the Edit tab.
3. Select Allow Cell Drag and Drop.
4. Click OK.
Now, try the AutoFill again. That Fill Handle should be there.
ANOTHER AUTOFILL GOODIE
Sure, AutoFill offers all kinds of neat time-savers. Type "Q1" in a
cell (without quotes) and then drag to fill Qs 2 through 4. Type
"Monday" (without quotes) in a cell and drag to fill in the rest of
the days of the week. But sometimes, all you need is to fill some
cells with numbers--simple, sequential numbers. Here's how:
1. Type the first number in the sequence in a cell.
2. Hold down the Ctrl key.
3. Using the Fill Handle, drag in the direction you want to fill--down
to fill the column, right to fill the row.
4. Release the mouse button; then release the Ctrl key.
Excel fills the range with consecutive numbers. Tip-in-a-tip: You
can fill "backwards"--that is to say, negatively--by dragging up or to
the left. Try it!
MORE COMPARISON
Last time, we told you how to find the cells in a row or column
that differ from the first cell in the row or column. Now suppose you
want to find cells that differ from a cell OTHER than the row's or
column's first (leftmost or topmost) cell.
Easy. To find cells that differ from ANY cell in the row or column:
1. Select the row or column.
2. Press the Tab key until you've selected the cell to which you want
to compare the others.
3. Press Ctrl + \ (for the row); press Ctrl + | (that's Ctrl + Shift +
\) for the column.
COMPARE!
Let's set the scene: You've got a really, really long row or column
of numbers or text entries in Excel. Most of the entries are the same
as the leftmost or topmost entry, but a few aren't--and you want to
find those few. Are you sentenced to the fate of searching for these
variants by eye?
No. To find the cells UNLIKE the first cell in a row or column:
1. Select the row or column (just click the appropriate row button
in the worksheet frame).
2. Press Ctrl + \ if you selected the row; press Ctrl + | (that's Ctrl
+ Shift + \) if you selected the column.
Excel selects all the cells differing from this first "comparison"
cell.
Think that's a big help? We'll have a related tip next time.
LOOK IN TWO OR MORE FOLDERS AT ONCE
Here's a brain teaser for you: You're looking for an Excel
worksheet, but you can't remember whether you've saved it in a) your
C:\My Documents folder or b) your C:\Excel Files folder (which you
created yourself).
How can you look through BOTH folders at ONCE? Try this:
1. From the Excel menu, choose File + Open.
2. In the File Name box, type the paths and names of the folders you
want to search, separating each with a semicolon. For example, if you
want to search both folders, you'd type
C:\My Documents;C:\Excel Files
3. Press Enter.
The list of files includes all the files and folders in BOTH
folders! Happy hunting.
IT LOOKS LIKE A TOOLBAR, BUT IT ISN'T EXACTLY LIKE ONE. . .
Travel back a few weeks to the tip in which we told you that you
could "float" any Excel 97 toolbar just by double-clicking the
double-line on the toolbar's left end. No doubt scores of you have
tried to float Excel's MENU bar the same way--with NO LUCK WHATSOEVER.
Well, it's not your fault: What we neglected to tell you was that
the menu bar CAN'T be floated in this way. To float the menu bar, you
have to do the following:
1. Point to the double-line at the menu bar's left end.
2. Drag the bar to the desired location.
However, to return the menu bar to its original location, just
double-click the menu bar's title bar.
Sorry for any confusion, consternation, or outright violence this
omission may have caused.
NAME A NUMBER
By now--especially if you read these tips--you're familiar with
using Excel's Name feature. This feature lets you name a cell (or
range of cells) so that you can refer to the cell (or range) by name
rather than the more complex cell address in formulas.
But the Name feature has another use: You can use it to assign a
name to a number that's NOT EVEN ENTERED IN YOUR WORKSHEET. Suppose,
for example, that a particular worksheet requires you to regularly
multiply numbers by the number .267. You can assign a name to the
number .267--and use the name in your formulas--as follows:
1. Choose Insert + Name + Define.
2. Type a name for your multiplier--such as "profit" (without the
quotation marks).
3. In the Refers To box, type the value--in this case, ".267" (without
the quotation marks).
4. Click OK.
Now you can use this multiplier in a formula. For example, suppose
you want to multiply a value in the cell A1 by this named constant:
1. Select an empty cell.
2. Type "=A1*profit" (without the quotation marks).
3. Press Enter.
Excel multiplies the contents of A1 by .267.
HIDE AND GOAL SEEK
You build a sales forecast in Excel, and the totals look SO good
you decide to chart them. Only problem is that once you chart them,
you wish they looked a little better.
Our advice: Change the chart! Or, more precisely, use the chart to
initiate a goal-seek operation, as follows:
1. Select the chart element--the bar or line--that you wish were a
bit larger.
2. Drag the largest handle on the element to enlarge the element,
making it about as large as you want it to be (don't worry about being
precise just yet).
3. In the Goal Seek dialog box--which appears as soon as you adjust
the chart element--set the To Value field to the exact value you want
for the adjusted element.
4. In the By Changing text box, type the address of the cell you want
to change to get the desired value. Obviously, this must be a cell
referenced in the formula that generates the value of the bar you
adjusted.
5. Click OK.
Excel "raises the bar" on your chart and adjusts the selected cell
accordingly. Which, we might add, means you don't have to do any math.
AUTOSHAPES: MORE AUTO THAN YOU THOUGHT
The AutoShapes feature of Microsoft Draw--the drawing program that
comes with Excel 97--makes creating all kinds of diagrams in your
worksheet easy. And the AutoShapes feature offers another amazing
convenience: If you don't like the FIRST shape you draw, you can
change it into ANOTHER shape without drawing a thing!
1. Select the AutoShape you'd like to change into another shape.
2. On the Draw toolbar, click Draw and choose Change AutoShape.
3. Select another AutoShape.
Draw automatically converts the old AutoShape to the new one. If
you added text to the AutoShape, the text remains unchanged--although
you may have to adjust the size of the new shape to make all the text
visible.
CAN'T FIGHT THE LAW. . .
Sorry, folks: Lawyers are with us to stay. They've even penetrated
the once legalese-free zone of your Excel worksheet: Suddenly, you're
required to include proper trademark and copyright notations in your
tables and reports. What a pain.
Actually, with Excel 97, it's less of a pain than it could
be--because Excel automatically adds trademark and copyright symbols
to your text if you type it as follows:
* To type a trademark symbol in any text label, type "(tm)"
(without the quotation marks).
* To type a registered trademark symbol in any text label, type "(r)"
(without the quotation marks).
* To type a copyright symbol in any text label, type "(c)" (without
the quotation marks).
If you've got to play with lawyers, we guess this is the best way
to do it.
WANT SOME OTHER THINGS TO CORRECT?
Last time, we told you how to get Excel to automatically generate
trademark and copyright symbols as you type text into a cell. No doubt
this caused the typography junkies among you to ask, "Hey--is there
some way I can also get Excel to replace double-dashes with
typographically correct em dashes?"
Of course:
1. Choose Tools + AutoCorrect.
2. In the Replace box, type a double-dash.
3. Click in the With box, hold down the Alt key, and on the numeric
keypad, type "0151" (without the quotation marks).
4. Click Add.
>From now on, whenever you type a double-dash in cell text, Excel
replaces it with a proper em dash. Your readers will be relieved.
CROWDED WORKSHEETS? ADD THESE BUTTONS
One way to relieve worksheet crowding is to rotate your column
labels so they read up, down, or vertically. Of course, because those
rotation commands are buried deep in your Format Cells dialog box,
they aren't particularly accessible.
Our advice? Add them to the Format toolbar, as follows:
1. Choose Tools + Customize.
2. Click the Commands tab.
3. Under Categories, choose Format.
4. Under Commands, find the Vertical Text button and drag it into
place on your Formatting toolbar.
5. Repeat Step 4 to drag the Rotate Text Up, Rotate Text Down, and, if
desired, Angle Text Upward and Angle Text Downward buttons to the
Formatting toolbar.
6. Click Close.
Now, whenever you want to angle or rotate text, just select the
cell(s) and click the appropriate button.
UNLIKE WORD 97. . .
Excel 97 can't automatically create hyperlinks to URLs (Web
addresses, that is) that you type in your worksheet. You have to
create them yourself, and THAT means you have to type your Web address
twice--UNLESS you heed this tip:
1. Type a Web address--such as www.bignews.com--in any Excel
worksheet cell.
2. Select the TEXT of the Web address. DON'T select the entire cell.
3. Right-click the selection and choose Copy from the shortcut menu.
4. Choose Insert + Hyperlink.
5. Press Shift + Insert to paste the Web address into the Link to File
or URL box.
6. Click OK.
Your hyperlink is in--and you only had to type it once.
DON'T WORRY--YOU'RE JUSTIFIED
To look at Excel's Formatting toolbar, you'd think there is
absolutely no way to justify (align) text in an Excel cell. But you
most certainly can, like so:
1. Select the cell(s) in which you want to align text.
2. Right-click the selection and choose Format Cells from the shortcut
menu.
3. Click the Alignment tab.
4. In the Horizontal box, select Justify.
5. Click OK.
Because Excel has NO shortcut key for justifying text (such as
Word's Ctrl + J shortcut key), you may want to move the Justify button
to the Formatting toolbar as follows:
1. Choose Tools + Customize.
2. Click the Commands tab.
3. Under Categories, choose Format.
4. Under Commands, find the Justify button and drag it to your
Formatting toolbar.
5. Click Close.
Good luck!
ENOUGH WITH THE AUTOFILL
Subscriber and regular contributor Dan Riordan writes with a TIP
instead of a QUESTION:
"When you enter a day, such as Monday, in a cell and then drag that
cell over adjacent cells, Excel automatically enters successive
days--Tuesday, Wednesday, and Thursday--in the adjacent cells. But if
you just want to COPY the original day into the adjacent cells--that
is, create a row full of Mondays--just hold down the Ctrl key as you
drag. This simply copies the contents of the current cell to the cells
you drag over."
Thanks, Dan. And a hint to the rest of you: We just LOVE it when
folks send in tips like this, all wrapped up and ready to use.
YET ANOTHER WAY TO BE SELECTIVE
Our two-part series (don't you wish television miniseries were so
short?) on New Ways to be selective prompted this response from
Australian subscriber Peter Sweeney: "This should be a three-part
series!" He offers this little-known way to select a range in Excel:
1. In the Name box, type the beginning cell of the range, followed
by a colon, followed by the ending cell of the range. For example:
c3:z50
2. Press Enter.
Thanks, Peter--it works like a charm!
STRANGE THINGS INDEED. . .
Subscriber James Flikkema reports the following bizarre Excel
behavior:
"No matter how many sheets I specify in the Sheets in New Document
option (Tools + Options, click the General tab), Excel always opens to
a blank display, without a sheet at all; I always have to click File +
New to open a new sheet. What's going on?"
What's going on is that something else is telling Excel to "open
empty." Try this:
1. On your desktop, right-click your Microsoft Excel icon.
2. Choose Properties from the shortcut menu.
3. In the Properties dialog box, click the Shortcut tab.
4. Examine the text in the Target box. If it ends with the two
characters /e, as in
delete those characters and the space before them.
5. Click OK.
Next time you start Excel, it opens a new blank workbook, with as
many sheets as you've specified in the Options dialog box.
IT'S AS IF A HENHOUSE HIT US IN THE FACE
In response to our tip on August 4 (A RATHER STRANGE SORT),
subscriber Saundra Dwinell tells us that Excel's own Office Assistant
offers a very effective solution for sorting more than three columns:
Sort the least important columns first! For example, suppose you
need to sort a list of names in the following order: Department,
Title, Last Name, First Name, and Middle Initial. You would
1. Select the range to be sorted.
2. Choose Data + Sort to open the Sort dialog box.
3. In the Sort By drop-down list immediately below, select the column
containing First Names.
4. In the Then By drop-down list, select the column containing Middle
Initials.
5. Click OK.
You're not done yet. Now, you sort again:
1. Choose Data + Sort.
2. In the Sort By drop-down list, select the column containing
Department Names.
3. In the topmost Then By drop-down list, select the column containing
Titles.
4. In the final Then By drop-down list, select the column containing
Last Names.
5. Click OK.
Your sort is complete.
BAD NEWS
Subscriber Duane Blow asks, "Is there a way to use Excel's AutoFill
feature to rename sheet tabs with sequential names or dates?"
Before we deliver the bad news, Duane, we'd just like to say that
we think it would be great--and extremely useful--if you COULD
AutoFill worksheet tabs. After all, lots of folks create a sheet for
each quarter or month in their worksheet models.
But you can't. And our cursory examination of Excel 2000 indicates
that you can't do it in Excel 2000, either. Seems to us that the folks
at Microsoft need a wake-up call on this feature.
PICK-KEY
Subscriber Tony Bragg writes, "Is there a way to display the Pick
from List drop-down list by using the keyboard?"
Excellent question, Tony--and yes, there IS a way to display and
use the list:
1. Press Alt + down arrow to display the list.
2. Use the up- and down-arrow keys to select the entry you want.
3. Press Enter to enter the entry.
Happy typing, Tony.
NO DEFAULT INSURANCE?
Subscriber (and Wendy's hamburger chain founder?) David Thomas
asks, "Is there a way to reset changed row heights and column widths
back to the default size? 1-2-3 has an option to do this."
Well, Dave, if resetting row and column sizes back to their
defaults is important to you, it's back to 1-2-3 you must go--because
Excel offers no such feature. What we CAN do, however, is tell you the
default measurements of each, so you can reset them yourself. To reset
rows to the default row height:
1. Select the rows.
2. Choose Format + Row + Height.
3. In the Row Height dialog box, type "12.75" (without the quotation
marks).
4. Press Enter.
To reset columns to the default column width:
1. Select the columns.
2. Choose Format + Column + Width.
3. In the Column Width dialog box, type "8.43" (without the quotation
marks).
4. Press Enter.
Sorry, it's the best we can do.
12.75 WHAT?
Last time, we told you that Excel's default row height was 12.75
and that its default column width was 8.43. What we didn't define--and
what the Row Height and Column Width dialog boxes do not make
clear--is what the units of measurement are.
Assuming that you're losing sleep over this, we'll tell you:
Excel's default row height is 12.75 points (a point is 1/72 of an
inch). Excel's default column width is 8.43 characters.
Now you know, and knowing is half th . . . oh, never mind.
HEAD OUT
Subscriber and Excel page decorator Hartley Leppard asks, "Is there
a way to have a header or footer on the first page only, without it
repeating on the subsequent pages--the way you can have them in Word?"
Alas, Hartley, there is no way. Excel headers and footers appear on
every page of your worksheet printout.
THROW A FIT
In the past, we've told you how to use Excel's Fit To printing
option to fit slightly more than a page on a page. But you can also
use the option to fit a larger range onto a specified number of pages.
For example, to print a 10 column by 250 row worksheet on two
pieces of paper, you'd do the following:
1. Choose File + Page Setup.
2. Under Scaling, set Fit To to 1 Pages Wide by 2 Pages Tall.
3. Click Print.
4. Click OK.
It fits!
THE SPREADSHEET WORLD IS DRAB AND COLORLESS?
Dummies Daily subscriber and would-be data decorator John Martorano
asks: "Can I change the color of each sheet tab in a mult-sheet
workbook -- and if so, how?"
Unfortunately, John, the answer to your first question is "no,"
making the second question unnecessary. Furthermore, it doesn't look
like the next release -- Excel 2000 - allows colored sheet tabs,
either. It's a perfectly reasonable thing to want, by the way -- the
OTHER spreadsheet, Lotus 1-2-3, has been offering the capability for
quite some time. Guess we'll just have to wait.
MOVE THAT SPREADHEET
Stacy Stewart writes: I need to include a two-page long Excel
spreadsheet in a Word document. But no matter what I try -- linking,
embedding, cutting and pasting -- only the part of the spreadsheet
that will fit on the first page appears in word, and the rest gets cut
off. Can you help?"
You bet we can -- so long as the worksheet doesn't need to be
linked. Try it this way:
1. In Excel, open the worksheet containing the data you need to
copy.
2. Select the data, and choose Edit + Copy (or press Ctrl + C).
3. Switch to Word, and position your cursor where you want the data to
appear in your document.
4. Choose Edit + Paste Special.
5. In the Paste Special dialog box, under As select Formatted Text
(RTF).
6. Click OK.
Ta-da! Word pastes the data -- formatted as it is in Excel -- into
a Word table that spans as many pages as necessary.
THAT'S SOME NEGATIVE
Dummies Daily subscriber and spreadsheet interloper R. Wayne
McCaden writes: "Quattro Pro has a @SUMNEGATIVE function that
calculates the negative numbers in a range of cells. When I converted
a Quattro Pro worksheet to Excel, cells that contained this formula in
Quattro Pro contained values, but not formula, in Excel. Does Excel
have an equivalent formula?"
Excel doesn't have an equivalent to Quattro Pro's @SUMNEGATIVE, but
you can use the SUMIF function with the same results. For example, if
you wanted to sum the negative numbers in the range A1:A10, you'd
1. Select the cell in which you want your sum to appear.
2. Type "+SUMIF(A1:A10,<0)" (without the quotation marks).
3. Press Enter.
That'll do the job!
NO ALTERNATING THE COLOR...
Dummies Daily subscriber Toby "Striper" Davis writes: "I'd like to
format every other row of my spreadsheet with a light color -- but I
DON'T want to reformat all the rows if I insert or delete a row. Is
there a way to set Excel to maintain alternately-colored rows
automatically?"
Boy, Toby -- wouldn't THAT be great? Unfortunately, there is no way
to do this. Even the Fast Formats which apply alternately-colored rows
will not readjust after you insert or delete a row.
NO CNA DO?
Excel user Lee Strain writes: When I type CNA -- standing for CNA
Insurance -- into a cell, Excel changes it to CAN when I press Enter.
Why does this happen? And how can I make it stop?"
This happens because Excel's automatic spell checker assumes you
meant to type "CAN," and corrects your mistake on the fly. To make it
stop, you have to turn off the automatic spell checker, as follows:
1. Choose Tools + AutoCorrect.
2. Deselect Replace Text as You Type.
3. Click OK.
It won't happen again. Unfortunately, Excel will no longer correct
other words you WANT it to correct, either. That's the tradeoff.
NO CNA DO, PART TWO
Last time, we told you that to prevent Excel's automatic spell
checker from making corrections, you could shut it off. We should note
that to prevent corrections in MULTI-WORD CELL ENTRIES, you may not
have to go so far.
Let us explain: Suppose you want to type "CNA Insurance" in one of
your cells. After you type "CNA" followed by a space, Excel corrects
"CNA" to "CAN." At THIS point -- with the entry STILL IN THE FORMULA
BAR BUT NOT YET IN THE CELL, to reverse the correction you can
1. Press Alt + Backspace OR Ctrl + Z (the Undo keys).
2. Finish typing the rest of the entry (in this case, "Insurance")
To repeat, this method only works in multi-word, not-yet-entered
entries.
By the way, this and the preceding tip are by no means intended to
promote CNA Insurance.
OOOH, BOY
Phil Stults of Nashville -- and, soon to be, of Dummies Daily fame
-- writes: "I tried your recent Excel 97 tip on adding worksheets (ADD
ANOTHER WORKSHEET, WICKED FAST, 07/06/99), but no matter how many
times I press Ctrl + Shift +F1, nothing happens. What am I doing
wrong?"
What you're doing wrong, Phil, is LISTENING TO US. Because hard as
it is to believe (hah!), we published the wrong keystroke shortcut for
inserting a new worksheet. The correct one is as follows:
press Alt + Shift + F1.
Either people have low expectations of our tips, our nobody needs
to insert worksheets -- because yours is the first and only complaint
we've received for this miscue.
BINARY FINERY
"Is there any way to convert decimals to binaries in Excel?"
Yes, there is -- if you've installed Excel's Analysis ToolPak
Add-in. For example, suppose you have a decimal number in cell A1, and
want its binary equivalent in cell B1.
1. Select cell B1.
2. Type "=DEC2BIN(A1)" (without the quotation marks).
3. Press Enter.
The binary equivalent appears. If you haven't installed the
Analysis ToolPack but would like to, get instructions for doing so as
follows:
1. Press F1.
2. Click in the Office Assistant text box and type "install Analysis
ToolPak" (without the quotation marks).
3. Click Search.
4. Click Install and use the Analysis ToolPak.
Good luck!
****************************************************************
WRITE LONG CELL ENTRIES
Those of you who deal with really large formulas -- and we're
talking REALLY large -- or who create really large text entries (as in
a database field) should know the following things:
1. An Excel cell can contain up to 32,767 characters -- about 30
pages of text!
2. While you can see ALL of these characters in the Excel formula
bar, in an Excel cell you will typically not see more than 1,024
characters or so.
"Is there a way to sort the worksheets in a workbook by name? I
eagerly await your response."
There is NO WAY to sort worksheets by name within a workbook --
other than moving them manually. To move any worksheet just drag its
worksheet tab to a new position relative to the other tabs.
Unless you have a workbook with a gaud-awful number of tabs, this
shouldn't take too long. Then, whenever you add a new tab, just be
sure to insert it in the proper alphabetical order.
"Is there a way to insert several worksheets into a workbook on
one step, or do I have to insert them one at a time?" Good news. You can insert more than one sheet:
1. Hold down the Shift key.
2. Starting with the sheet you want your new sheets to precede, click
as many sheet tabs as you want new sheets.
3. Choose Insert + Worksheet.
Excel inserts as many new sheets as you selected. The only problem is
that you can't insert any more sheets than the number of EXISTING
sheets. But it's still better than one at a time.
Changes to this
page are IN PROGRESS