In our last tip, we showed you how to apply formatting to all or a
group of worksheets. To group a few sheets, you simply hold down the
Ctrl key while clicking the appropriate sheet tabs.
If your group is large and includes most but not all of the sheets
in
your workbook, there's a quicker way. Instead of clicking the sheet
tabs of all the sheets you want to include in your group, select all
the sheets and click the few sheet tabs you DON'T want to include. To
do so, just right-click any sheet tab and choose Select All Sheets.
Next, hold down the Ctrl key and simply click the sheet tabs to
deselect the sheets you don't want included in your group. That's all
there is to it!
----------------------------------------------
INSTALLING VBA HELP FILES
Each Office application has a set of VBA Help files that are
separate
from the individual application Help files. The VBA Help files are
available only from the VBA IDE window. To excel them, simply pull
down the Help menu as you would in the Excel window.
However, if you installed Microsoft Office using the standard
installation settings, Office did not install the VBA Help files. To
excel these files, you must specify them during the install process.
First, choose Settings from the Windows Start menu. Select Control
Panel and then double-click the Add/Remove Programs icon. Next, click
the Install/Uninstall tab and then double-click your version of
Microsoft Office 2000. In the first panel of the install program,
click the Add Or Remove Feature button. Then, click the plus sign next
to the Office Tools item. You'll find Visual Basic Help at the bottom
of the list of tools. Simply click the small triangle and select Run
>From My Computer or Install On First Use. Click Update Now and exit
any remaining windows.
If you selected Install On First Use, Office won't actually install
the files until you attempt to excel them.
----------------------------------------------
CONSERVE COLOR INK
If you have a color printer, you may find it wasteful to use that
expensive color toner when printing draft copies that will wind up in
the trash as soon as they're proofed. If so, we recommend you leave
your settings on black and white until you're ready to print the
worksheet (file). Excel simply interprets your colors as different
shades of gray, so there's no difficulty in printing color documents
in black and white.
To choose this setting, select File, Page Setup. Then, click the
Sheet
tab and select Black And White in the Print options. When you're ready
to print a "for keeps" copy, complete with color and dazzling special
effects, simply deselect the Black And White option and print.
----------------------------------------------
SHEET GROUPS
A few tips ago, we showed you how to apply the same format to all
sheets or several sheets at the same time by forming a sheet group.
Once you've created the group and applied your format, it's important
that you remember to ungroup your sheet group immediately. That's
because many features aren't available in group mode. If you forget
you're working in group mode, you may find it frustrating to work as
you normally do because many of your tasks simply won't work--and it
may take you a while to remember that you're in group mode.
To remove a group, right-click any sheet tab in the group and
select
Ungroup Sheets.
----------------------------------------------
DISPLAYING COMMENTS
You probably know that you can add documentation to a cell by
inserting a small comment. Simply select the cell; choose Insert,
Comment; and enter your note in the resulting note control. Once
you've added a comment, Excel will display a small red triangle in the
upper-right corner of that cell as an indicator that the cell has an
attached comment.
You can also display all comments all the time. To do so, choose
Tools, Options, then click the View tab and select the Comment And
Indication item under the Comment options. Now, Excel will always
display your comments.
If a comment should happen to block data, simply drag the comment
to a
new location.
----------------------------------------------
USING THE SET PRINT AREA BUTTON
So you've just created a masterpiece spreadsheet and you can't wait
to
show your boss--but you don't need to show him the entire document.
Solution? Use the Set Print Area button. To set a print area, you
select the range you want to print and then choose File, Print Area,
Set Print Area. This routine is a lot of work just to set a print
area. To avoid all those menus and clicks, add the Set Print Area
button to your toolbar. Once you've done so, you can select the range
you want to print and just click the Set Print Area button.
To add this button to your toolbar, first choose View, Toolbars,
Customize. In the resulting dialog box, click the Commands tab and
choose File in the Categories control, then drag the Set Print Area
button from the Commands control to your toolbar.
If you later decide to remove the button, simply open the Customize
dialog box and drag the button back to the Commands control.
----------------------------------------------
QUICK TIP FOR ENTERING NAMES IN A FORMULA
If you're like most of us, you assign names to ranges whenever
possible. Formulas are usually easier to write if you use names
instead of cell references. There's even a quick and easy method for
entering those names in our formulas. Simply position the cursor in
your formula where you want to insert the name. Then, press F3 to
display the Paste Name dialog box. Select the appropriate name from
the list and click OK. Complete your formula and then press Enter. No
more worry about typos, misspellings, or forgotten names.
----------------------------------------------
AUTOSUM FROM THE KEYBOARD
Summing a row or column of values is easy--simply select a blank
cell
to the right or below your series of values and click the AutoSum
button. There's also a keyboard shortcut for the AutoSum button, so
you don't have to interrupt your routine to grab the mouse. As with
the mouse technique, select a blank cell to the right or below your
values, press Ctrl-Equal Sign, and then press Enter. Voila! Excel will
immediately enter the formula and display the sum of your values.
----------------------------------------------
ONE TOOLBAR PER LINE, PLEASE
To maximize the available workspace, Excel places the Standard and
Formatting toolbars on one line by default. This means you have to
expand a toolbar--by clicking the More Buttons button at its right
edge--to see the rest of the toolbar's buttons.
If you'd prefer to sacrifice some screen space in exchange for
having
all the buttons handy, you can do so in a couple of seconds. Just
right-click any toolbar and choose Customize. Next, click the Options
tab and click the check box next to the first option, Standard And
Formatting Toolbars Share One Row. Click Close, and you're all set.
Now the two toolbars will reside on individual rows. Should you
wish
to place the toolbars on a single row, just follow the same steps.
This time, clicking the option will place a checkmark in the check
box.
Incidentally, there's an even easier way to set this option: You
can
turn off the Standard And Formatting Toolbars Share One Row option by
dragging one of the toolbars to its own line; turn the option back on
by dragging one of the toolbars to the same line as the other.
----------------------------------------------
IMPORTING FOREIGN DATA
Importing can be a frequent task for some Excel users and
unfortunately, Excel isn't well versed in all languages. That means
you can't always successfully import the data you need. When this
happens, try to find a go-between format--one that both the
application you're importing data from and Excel both can communicate
with. That way, you can save or export your data to the go-between
format and then import that file (not the original file) into Excel.
Once you find a compatible go-between, Excel should have no trouble
reading and importing the necessary data. Good luck!
----------------------------------------------
CHANGE THE WIDTH OF TOOLBAR CONTROLS
As you try to squeeze another button on your toolbar, you might
wish
for a wider toolbar. Well, you can't expand the toolbar beyond Excel's
container window, but you CAN reduce the amount of space taken up by
text boxes and drop-down lists such as the Font, Font Size, and Zoom
controls on your toolbars.
To narrow (or widen) these text-based toolbar buttons, start by
right-clicking any toolbar and choosing Customize. With the Customize
dialog box displayed, select the text box on the toolbar by clicking
it. Move the pointer over the box's right border to change the pointer
to a vertical bar with left- and right-pointing arrows. At this point,
click and drag the border in or out. Finally, click the Close button.
----------------------------------------------
NAME VERSUS CAPTION
Most controls have two properties that are easy to confuse--the
Name
and the Caption properties. The Name property is the string you use to
identify an object. The Caption property is the string you display on
the object (or in a control's attached label control).
Let's compare the two properties on a simple command button. You
might
name your button cmdButton and then refer to that name in your
button's event code--cmdButton is the button's Name property. On the
other hand, if cmdButton's task is to print a specific report,
cmdButton's Caption property might be the string "Print Report." As
you can see the two property settings aren't interchangeable. But, we
can see how people might get them confused. We hope you won't!
----------------------------------------------
A SHORTCUT FOR UNDOING YOUR LAST ACTION
Excel has a number of features that let you "change your mind." The
most useful of these is the Undo command, which you can use to
(obviously) undo your most recent actions. The Redo command lets you
change your mind back and restore your original action for those
moments when you're really unsure about what you're doing.
The keys to these commands are the Undo and Redo buttons on the
Standard toolbar. You can even undo or redo multiple actions by
clicking the drop-down arrow on the button and highlighting the
actions you want to reverse.
Clicking these buttons is really easy to do. But when your hands
are
on the keyboard, the last thing you want to do is interrupt your
momentum by having to reach for your mouse to click a toolbar button.
Fortunately, Excel offers handy keyboard shortcuts for the Undo and
the Redo commands:
* Undo--Press Ctrl-Z
* Redo--Press Ctrl-Y
Enjoy!
----------------------------------------------
UNCOVERING THE VALUES
Each series in any Excel chart represents a value. However, the
chart
doesn't always display that value--instead, you must glean the value
from the axis values. If you'd like to know the value of any given
element in a chart, simply point to that element. Excel will soon
display the element's series name and underlying value.
You can turn this feature off if you want (or turn it back on) by
choosing Tools, Options; clicking the Chart tab; and selecting the
Show Values option in the Show Tips section to display the element's
value. The Show Names option shows the series name. By default, they
are both turned on. You can choose both, neither, or one or the other.
Don't you just love choices?
----------------------------------------------
OFFICE 2000 TOOLS AND UTILITIES
The Microsoft Office 2000 Resource Kit is now available and you can
find information online at
If you think the Resource Kit is just for network administrators
and
IT professionals, check out this Web site and you'll find several
tools, utilities, and support documents. You'll also find a few treats
for the average user--wizards, sample files, and tons of information.
There's even a white paper on the new Office 2000 Web components.
----------------------------------------------
OFFICE ERROR MESSAGES
Microsoft offers an Excel worksheet that lists all the Office error
messages and their corresponding values. The name of this workbook is
Errormsg.xls and it's available by download at
Once you've downloaded the file, run the EXE file, which you should
find in the folder
Program Files\ORKTools\Download\Documents\Cstalert
The EXE file will install several files, including Errormsg.xls. At
that point, simply open the workbook in Excel. Each Office application
has its own worksheet--just click the appropriate tab to view the
error messages for an application. Since you're working with an Excel
workbook, you can easily add your own notes and information to each
record.
----------------------------------------------
DOCKING WINDOWS
We've talked a bit about dockable toolbars, but some windows are
also
dockable. Simply double-click the window's toolbar to dock a floating
window. For the most part, you'll use this feature in the Visual Basic
Editor. (A double-click to a spreadsheet's title bar maximizes that
window.) Not all windows are dockable, though. A quick glance is all
you need to discern whether a window is dockable. If the title bar has
only a Close button, the window is dockable. Windows with Minimize,
Maximize (or Restore), and Close buttons aren't dockable. When you
double-click its title bar, Excel maximizes that window.
----------------------------------------------
HOW BIG IS THAT FONT?
The Font Size tool on the Formatting toolbar lists sizes from 8 to
72,
but Excel isn't limited to those sizes. You can specify a Font Size
property from 1 to 127 using VBA. For instance, to change a work font
size, you'd use code similar to
Set myDocument = Worksheets(1)
myDocument.Shapes(1).TextEffect.FontSize = 24
Just because you specify a font size doesn't mean Excel or your
printer can accommodate that size with the specified font. You must
have the appropriate font installed and the font itself must be able
to accommodate the size you choose. If it can't, Excel will substitute
a similar font if possible.
----------------------------------------------
HOW BIG IS THAT FONT?
If you try to edit a sheet and find you can't, the sheet (or
workbook)
may be protected. If that's the case, Excel will probably display an
error message that the cell or chart you're trying to edit is
protected. When this happens, you can remove the protection by
selecting Tools, Protection, Unprotect Sheet. At this point, you can
edit the sheet. Once you've completed your changes, you'll probably
want to reprotect the sheet. To do so, simply repeat the above steps,
this time choosing Protect Sheet from the Protection submenu.
----------------------------------------------
WHEN FORMULAS DON'T WORK
Occasionally you probably experience some difficulty with formulas.
If
Excel returns an error message telling you that the formula contains
an error, there are several checkpoints you can review for problems:
- Make sure you've included the appropriate number of parentheses
(each opening parenthesis requires a closing parenthesis).
- Make sure you've supplied all the required arguments.
- If your formula contains a function, choose Insert, Function for
helpful information on the function.
- Check all your references and make sure they're correct.
If you're not trying to enter a formula, you may need to precede your
text with the single quotation mark (') in order to enter it.
----------------------------------------------
CAN'T PRINT?
If Excel can't print your sheet, there may be several reasons and
most
are fairly obvious. For instance, if you're working with a local
printer, make sure the printer is turned on and all cables are
properly connected. Be sure to include the cable between your CPU and
the printer in your check. If you're working on a network, you may
have lost your connection. If that's the case, you simply reconnect or
wait for the system to come back online. It's also possible your
printer driver is obsolete (not likely if you print on a regular
basis). More likely, the driver files have been corrupted. A
not-so-obvious problem may be your system's memory. Printing requires
free memory and if your resources are limited, you may be trying to
print a document that requires more memory than you have available. At
this point, friends, it may be time to upgrade!
----------------------------------------------
COLUMNS AND AUTOFILTER
If you're using the AutoFilter command (meaning, the feature is
turned
on), you can't insert or delete a column in your list range. You can
still add and delete columns outside the list range. If you try to add
or delete a column in your list range, Excel will reprimand you with
an error message.
Before attempting to insert or delete a column while filtering,
turn
off the feature by selecting Data, Filter, AutoFilter. Go ahead,
delete away--nothing can stop you now!
----------------------------------------------
ADDING MULTIPLE CONTROLS THE EASY WAY
Generally, you add controls to a userform by opening the Visual
Basic
Editor and adding a userform. Next, you click the appropriate control
button on the Toolbox and then click inside the userform. If you want
to add a second (or several) controls of the same type, you probably
return to the Toolbox and click the same button before inserting the
additional control in the userform. Fortunately, this repetitive task
isn't necessary.
If you want to add several controls of the same type to a form or
report, simply double-click the control button instead of using a
single click. Double-clicking a control button temporarily selects
that control, so you can insert as many controls as you like without
having to click that button again on the Toolbox. To reset the current
tool selection, click another control button or the Selection arrow on
the Toolbox.
----------------------------------------------
EDITING A HYPERLINK
Hyperlinks provide an easy shortcut to specific locations in your
workbook, to other files, and even to Web sites. However, editing a
hyperlink is a bit of a nuisance. You have to right-click the link and
choose Edit Hyperlink from the resulting submenu. Well, you don't have
to--there is an easier way.
If you hold down the Ctrl key and then select the cell that
contains
your hyperlink, Excel will automatically place that hyperlink in edit
mode. You can then edit the text in the Formula bar.
----------------------------------------------
QUICKLY CLOSING WORKBOOKS
If you're like many Excel users, you may have several workbooks
open
at one time. When you're done working and you're ready to close up
shop, you probably find closing all those files a little tedious.
Wouldn't you appreciate a quicker way than clicking each workbook's
Close button or choosing Close from the File menu for each workbook?
We thought so.
To quickly close all the open workbooks, hold down the Shift key
before you open the File menu. When you do, the Close command will
read Close All. Select this command, and Excel will close all the open
workbooks at once.
----------------------------------------------
VIEWING MORE THAN YOU THOUGHT
Do you sometimes wish you had two monitors and two pairs of hands
when
reviewing a large worksheet? If you need to view different parts of
the worksheet at the same time, simply split the worksheet into two
panes. If you want a horizontal split, drag down the split box (the
small rectangle that rests on top of the vertical scroll bar). You'll
take similar steps to create a vertical split, except you should drag
the split box that's to the right of the horizontal scroll bar. Once
you've split your worksheet into two panes, you can scroll either pane
to find any section of the same worksheet.
----------------------------------------------
DELETING A SPLIT SCREEN
In our last tip, we showed you how to split your worksheet into two
scrollable windowpanes. This tip is particularly useful when you're
working with a large worksheet. To return your view to just one pane,
simply remove the split. To do so the hard way, drag the split bar
back to its originating split box. The easiest way to delete a split
screen is to simply double-click the split bar.
----------------------------------------------
INSERTING MULTIPLE SHEETS
To insert a blank sheet into your workbook, you select the
Worksheet
option from the Insert menu. If you want to insert several sheets,
this process can be tedious. Fortunately, there's a shortcut.
To begin, hold down the Shift key. Starting with the sheet you want
your new sheets to precede, click as many sheet tabs as you want new
sheets. Then, choose Insert, Worksheets.
For every sheet you click, Excel will insert one blank sheet. That
means you can only add as many sheets as you have existing sheets in
your workbook. It's a limitation, but an insignificant one.
----------------------------------------------
CALCULATING BINARY
Ever need the binary value for a decimal value? If so, use the
Analysis ToolPak--an Excel add-in. Enter the value you want to convert
to binary in a cell. Then, in another cell, enter the function
=DEC2BIN(celladdress)
For instance, if you enter the value 10 in cell A1, you'd enter the
function
=DEC2BIN(A1)
in any other cell and press Enter. The function will return
1010--the
binary counterpart of the value 10.
Exciting stuff, huh?
----------------------------------------------
MY ANALYSIS TOOLPAK ISN'T INSTALLED
So you tried our previous tip to convert a decimal value to binary
and
it didn't work, right? That's because you haven't installed the
Analysis ToolPak. Fortunately, with Office 2000's new installation
features, all you need is your Office 2000 CD.
Insert your Office 2000 CD and choose Tools, Add-Ins. Check the
Analysis ToolPak item in the Add-Ins available control, and then click
OK. That's all there is to it. (You may not even need your CD
depending on your original installation settings.)
Once you install the Analysis ToolPak, you should be able to
convert
values to binary using the DEC2BIN() function.
----------------------------------------------
SUMMING ONLY THE NEGATIVE VALUES IN A RANGE
If you have a column of values--some positive and some
negative--you
may find summing only the negative (or positive) values a real
problem--that is, unless you know about the SumIf() function.
If you want to sum all the negative values, use the function in the
form
=SumIf(range,<0)
Similarly, if you want to sum all the positive values, use the
function in the form
=SumIf(range,>0)
----------------------------------------------
PRINTING HELP TOPICS
You can easily print Help topics by clicking the Print icon in the
Help window. However, as you know, most Help topics are spread across
several pages with many subheadings. That means you must excel each
one and print it to get a set of the entire Help topic. Right? Not
anymore. To print the entire topic, locate the appropriate book in the
Contents tab and click the Print button. Then, in the Print Topics
dialog box, click Print The Selected Heading And All Subtopics.
Finally, click OK twice.
Furthermore, Excel prints the topic continuously, rather than
printing
each heading on a separate page.
----------------------------------------------
RESETTING ADAPTIVE MENUS
Last month we talked about Excel 2000's new adaptive menus. As you
work, Excel remembers the commands you use most often and displays
those while hiding commands you rarely use. (The hidden commands are
still available.) You can turn off this feature if you don't like it
by choosing View, Toolbars, Customize. Next, click the Options tab and
deselect the Menus Show Recently Used Commands First option in the
Personalized Menus And Toolbars section.
If you'd rather not turn it off, you can always reset the feature
to
its defaults. On the Options tab (see above), click the Reset My Usage
Data button in the Personalized Menus And Toolbars section. Doing so
will delete your command usage history and restore the default set of
visible commands.
----------------------------------------------
THE ENHANCED FILE DIALOG BOX
Excel's File dialog box has also undergone a makeover for Excel
2000.
The most visible difference is the addition of the Object bar (on the
left). You'll find this new feature in all the file dialog boxes
(Open, Save, Import, and Export).
The Object Bar displays several folder icons for quickly accessing
your files. The History folder displays a list of the most recently
opened files. The remaining folders are self-explanatory.
There's a new toolbar along the top of the dialog box that offers
three new options. The Recent button allows you to retrace your tasks.
The View button replaces the Large Icons, Small Icons, List, and
Details icons in previous versions. The Tools button displays a
drop-down list containing several file maintenance commands.
Enhancements were also made to the Open button. Now you can
exercise
several options when opening a file. You can open a file normally or
as a read-only file.
----------------------------------------------
ONE CLICK OPENS THE VISUAL BASIC EDITOR
You probably press Alt-F11 to open the Visual Basic Editor, but
there's an easier way. Most Office VBA applications offer the Visual
Basic Editor button--you'll find it on the Visual Basic toolbar. If
you use the editor a lot, you should consider copying this button to
one or more of the standard toolbars. That way, you can open the
editor at any time with one quick click.
To add the button to another toolbar, just right-click the
background
of any toolbar and select Visual Basic. Next, hold down the Alt key
and drag the Visual Basic Editor button from the Visual Basic toolbar
to any other open toolbar. That's all there is to it!
----------------------------------------------
NEED A CALCULATOR?
Normally, we like to share expressions and formulas that you can
use
in Excel. However, we've found a Web site that may make some of your
work in Excel unnecessary. The Calculators On-Line Center at
offers 4,800 Web calculators. You'll find calculators to handle all
sorts of tasks, from a lye calculator (for making soap) to a capital
gains calculator.
If you're familiar with Excel 2000's new Web components, you can
easily modify some of these calculators using a Data excel Pages
object and the Office spreadsheet component. After running the
calculator, you can even export the result to Excel by clicking the
Export To Excel button on the component toolbar.
----------------------------------------------
COPYING TO OTHER WORKSHEETS
You can drag and drop selections of data from one area of your
worksheet to another. You can also use the drag-and-drop method to
copy data from one worksheet to another in the same workbook. First,
select the data you want to copy. Then, hold down the Alt key and drag
the selection to the appropriate sheet tab (at the bottom of the
worksheet). Doing so will open the target worksheet. At this point,
you just drag the selection where you want it and drop it in. If you'd
like to try this, follow these steps to copy data from sheet1!A1..C3
to sheet2!A1..C3 in a practice worksheet (you don't really need to
have data in the cells):
First, select cells A1..C3 in sheet1. Hold down the Alt key, and
while
holding down the Alt key, grab the selection and drag it down to the
bottom of the worksheet where the sheet tabs are located. Without
releasing the Alt key or the selection, move the mouse pointer over
sheet2's tab. Excel will select that tab (Excel will bring it forward
and it will change colors), which means that sheet is now active. Once
sheet2 is open, drag the selection to cell A1..C3 and release the
selection.
----------------------------------------------
A RETURN TO BASICS
If you're a longtime Excel user, you might remember the days when
Excel was just one worksheet. The truth is, most tasks don't require
multiple worksheets. However, the default workbook opens with three
worksheets. That means for every one worksheet task you have, you're
wasting two worksheets, and that adds up to a lot of resources after a
while.
If you generally use just one worksheet, change Excel's default
settings to open each new workbook with just one worksheet. To do so,
choose Tools, Options, then click the General tab. Set the Sheets In
New Workbook option to 1 (the default is 3), and click OK. Each new
workbook you open will contain only one worksheet. Of course, you can
still add more worksheets if you need them.
----------------------------------------------
ONE WORKSHEET, ONE PIECE OF PAPER
Have you ever tried to print a large worksheet? It can be a bit
awkward. You can try printing the worksheet on one piece of paper--if
it works, you'll certainly simplify your print task. If it doesn't
work, you've lost only a little time. Fortunately, the process is
simple--there's just no guarantee that you'll be able to read the
one-page printout. To get started, select File, Page Setup. In the
Page Setup dialog box, select Landscape orientation (you can select
Portrait, but we don't see the point). Next, in the Scaling section
click the Fit To option and then use the arrows to indicate the number
of pages wide--1. At this point, click the Print Preview button to
check the results. In Print Preview, click the Zoom button. If you can
read it on-screen, go ahead and print. If you can't read your data
on-screen, then you have two choices: Reduce the font size or resign
yourself to having more than one printed page.
----------------------------------------------
MOVING PAGE BREAKS
In our last tip, we showed you how to use the Fit To option to
force a
worksheet to print on one page. When you're working with a large
worksheet, you might also want to control the number of lines on each
page. You may want to use the Fit To option to confine your worksheet
to one sheet of paper.
To do so, first choose File, Page Setup. In the Scaling section,
click
the Fit To option and then use the arrows to indicate the number of
pages you want the worksheet to be. Choosing 1 will force Excel to
print your worksheet on one sheet. (This option is available, but we
do not recommend using it when you're working with hundreds or rows.)
When more than one page is needed, you can control the page breaks
by
opening the worksheet in Print Preview. To check the position of page
breaks, click Page Break Preview while in the Print Preview window.
Scroll down until you see a blue line indicating a page break. If it's
not where you want it, just click and drag it to a more appropriate
position.
----------------------------------------------
AN EASY DELETE
You don't have to select an entire column or row to delete it. All
you
need to do is select one cell in the appropriate column or row. Then,
select File, Delete. In the resulting dialog box, select Entire Row or
Entire Column and click OK. That's all there is to it.
You can even delete multiple rows and columns. Just be sure to
select
at least one cell in each row or column that you want to delete.
----------------------------------------------
BREAKING THE LINK
Did you know that you can cut and paste data from other
applications
into a worksheet? After copying the data to the Clipboard, simply
select your Excel worksheet and choose Paste or Paste Special. The
Paste option will copy the contents of the Clipboard to your
worksheet. You can also create a link to the original data source by
choosing Paste Special and then selecting the Paste Link option. After
you choose this option, Excel will update your data each time you open
the worksheet.
Although creating a link is a useful feature, if the data becomes
unavailable, the data obviously won't be available in your worksheet.
Why, you ask? Well, if the data is on a server that temporarily goes
down, the link will unfortunately be useless until the server's back
on line.
Now, the one situation you might not think of is traveling with a
laptop. If you copy your worksheet to a laptop or disk for
portability, you must remember to also copy the data source file. If
you don't, that data will be unavailable.
----------------------------------------------
HTML PASTE OPTION
Did you know that Excel 2000 has a new Paste option? The Paste As
command offers several different options for pasting data. For
instance, you can paste data as formatted text (RTF) or as unformatted
text. You can even paste data as HTML format. Now stay in your seats,
please. This option inserts the contents of the Clipboard, preserving
as much of the original formatting as possible, including HTML tags.
To excel this option, choose Paste Special from the Edit menu, select
Paste in the Paste Special dialog box, and then choose HTML Format
from the As control.
----------------------------------------------
VISUAL INSIGHTS
Faster data analysis. Better decisions.
Introducing Visual Insights® ADVIZOR/2000. Get an immediate,
intuitive
grasp of what's significant and actionable in your data to make better
business decisions. Free demo at:
http://www.pcworld.com/r/ad/1%2C2061%2Cad-visualxl-1199%2C00.html
----------------------------------------------
PROTECTING WEB DATA
It's easier than ever to publish an Excel spreadsheet to the Web.
Simply highlight your data; choose File, Save As Web Page; and click
Publish. If you need an interactive page, click the Add Interactivity
With option and choose between a spreadsheet and pivot table before
you click Publish.
One word of caution: If you publish an interactive page, be sure to
protect all the cells except those your users will need to modify
before you publish your worksheet. First, select any cells that a user
may want to change. Then, select Cells from the Format menu and click
the Protection tab. If the Locked option is checked, deselect it. Once
you've unlocked all the appropriate cells, choose Tools, Protection,
then choose Protect Sheet from the resulting submenu. Enjoy!
----------------------------------------------
HIDING ROW AND COLUMN HEADERS
A while back, we told you how to hide the column and row headers
(the
gray border cells at the top of each column and to the left of each
row). Choose Options from the Tools menu and then click the View tab.
In the View tab, deselect the Row & Column Headers option.
If you'd like to accomplish this using VBA, simply include the
following statement in your code:
ActiveWindow.DisplayHeadings = False
In fact, you can run it in the Immediate window if you like. Press
Alt-F11 to open the VB Editor. Then, enter the above statement in the
Immediate window and press Enter. (If the Immediate window isn't open,
press Ctrl-G.) After running the statement, return to your worksheet
and you'll find that Excel has hidden all the row and column headings.
To reverse the action, simply change the False value to True in the
above statement and run it again (in the Immediate window). Happy
hiding!
----------------------------------------------
E-MAILING EXCEL DATA
Do you have any idea how easy it is to e-mail Excel data? Simply
open
the workbook and choose File, Send To. The resulting submenu will
offer the following e-mail choices: Mail Recipient and Mail Recipient
(As Attachment). If you choose the first, Excel will copy your
workbook data to the body of your e-mail message. As a result, you'll
be sending static data. The second option will send the workbook as an
attachment to your e-mail. You'll want to use this option when the
recipient needs to open the data in Excel.
You probably already know that you can press F1 to display a
related Help topic. When you're working in the VB Editor with a module
full of code, did you know that Help is just a keystroke away? Simply
position the cursor inside the keyword, method, or property that you
want more information about and press F1. The VB Editor will display
an appropriate Help topic. Just remember, you must have the VBA Help
files installed for this to work properly.
If you don't like the way buttons are arranged on a toolbar, move
them around. To move a button, simply hold down the Alt key while you
drag the button from one position to another. Just be careful you
don't drag the button off the toolbar. If you do, Excel will remove
the button from the toolbar, and pressing Ctrl-Z or choosing Undo
Delete from the Edit menu won't bring the button back.
If you'd like to copy a button, hold down the Alt-Ctrl keys while
dragging the button from one toolbar to another.
In our last tip, we showed you how to move and copy a toolbar
button. We also mentioned that if you inadvertently delete a button,
you can't use the Edit Undo Delete command to recover it. However, you
can reset the toolbar, which in most cases is almost as good.
To reset the toolbar to its default settings, right-click the
toolbar and choose Customize from the resulting shortcut menu. Next,
click the Reset button in the Customize dialog box. Just remember,
resetting the toolbar doesn't undo just the last change--such as
deleting a button. Resetting the toolbar will return the toolbar to
its original settings when you installed Excel (or Office). If you've
made a lot of custom changes that you don't want to lose, resetting
the toolbar probably isn't a good idea.
Once you've created a group of controls for a userform, you may
want to align them or make them all the same size. To do so, simply
select the controls (hold down the Shift key while clicking each
control) and then select an option from the Format menu. The Size
option offers two ways to resize your controls:
Size To Fit
Size To Grid
The Size To Fit option will size a control to accommodate the
largest entry from that control's data source. You can also size the
control to the nearest grid points by choosing the Size To Grid
option.
The Make Same Size command offers three options: Width, Height, and
Both. When working with these options, the dominant control takes
precedent. That means the VB Editor will size all the controls
according to the dominant control. You'll know the dominant control by
its white sizing handles.
In our last tip, we talked about adjusting the size of a userform's
controls. In particular, we discussed modifying the size of several
controls at one time. You can also align controls in much the same
way. First, you select the controls you want to align. To do so,
simply hold down the Shift key as you click each control. Once you've
selected all the controls you want to move, choose Format, Align.
There are several alignment options:
Lefts
Centers
Rights
Tops
Middles
Bottoms
Simply put, the VB Editor will align the selected controls to align
the stated border (or area). For instance, if you select Lefts, the VB
Editor will align all the selected controls with the dominant
control's left border. The Centers and Middles options will vertically
and horizontally center, respectively, the selected controls with the
dominant control.
By default, Excel selects the cell below the current cell when you
press the Enter key. Fortunately, you can change this directional
behavior if it proves inconvenient. First, choose Tools, Options, and
then select the Edit tab. Next, open the Move Selection After Enter
tab. Then, open the Direction control's drop-down list and make a
selection from the four items: Down, Right, Up, Left. If you deselect
the Move Selection After Enter option, Excel won't move the selection
at all. You'll have to do so manually using one of the arrow keys.
This modification will affect all your workbooks, not just the current
one.
There are a number of ways to select a row, but by far, the
quickest method using the keyboard is as follows: Select any cell in
the row you want to select. Next, press Shift-Spacebar.
Excel will respond by selecting the row that corresponds to the
selected cell. For instance, if you select cell C3, Excel will select
row 3. To quickly select an entire row using the mouse, simply click
that row's heading cell (the gray cell to the left of the row that
displays the row number).
In our last tip, we showed you two quick ways to select an entire
row. Were you wondering if there was a similar keystroke shortcut for
selecting an entire column? Well, there is and the techniques are very
similar. Just select any cell in the column you want to select and
press Ctrl-Spacebar.
Excel will select the column that corresponds to the selected cell.
For example, if you selected cell C3, Excel would select column C. You
can also use the mouse to select an entire column by clicking that
column's heading cell (the gray cell at the top of the column that
displays the column letter).
We've talked quite a bit about the fill handle; it's so handy,
we're always experimenting with it. Did you know you could fill two
rows or columns with unrelated data at the same time? You can, and
we're not talking about static labels. The fill handle can handle a
series of labels even when you're working with more than one column or
row.
As an example of this behavior, enter the label Year in cells A1
and B1. Then, enter the values 1998 and 1999 in cells A2 and B2,
respectively. Now, let's suppose you want to repeat the label Year
across row 1 and you also want to create a series of years in row 2.
To do so, simply select cells A1..B2, grab the fill handle, and pull.
Excel will copy the string Year to each cell in row 1 of the extended
selection. In addition, Excel knows to increase the values in row 2 by
1 for each cell in the extended selection.
You can insert rows and columns in a number of ways. However,
there's no built-in feature or keyboard shortcut that inserts a row or
column between each row or column in a selection of rows or columns.
Whew! Did you get that? If you select a block of rows or columns and
try to insert a row or column, Excel will insert one row or column
above the block of rows or to the left of the block of columns,
respectively.
There is a way to insert a row or column between each row or column
in a selection, but you must select each row or column separately.
We'll show you how to use this technique with rows, but it also works
with columns. Let's suppose you want to insert one row between rows 2
and 3, 3 and 4, 4 and 5, and rows 5 and 6. (Before you start, you
might want to add data or formatting to at least one cell in each row
so you can actually see the newly inserted rows.)
Begin by clicking row 3's heading cell to select row 3. Hold down
the Ctrl key, then click the heading cells for rows 4, 5, and 6 while
holding down the Ctrl key. Then, choose Insert, Rows.
Excel will insert a row between rows 2 and 3, 3 and 4, 4 and 5, and
5 and 6.
You're probably aware of the Office security holes that seem to
center around ODBC and Excel. However, any Jet user is at risk. In
addition, the problem exists with Office 2000's Jet, not just Jet
3.51. If you're using any Office application, you should check out the
following site for more information:
You probably know you can comment your code using the apostrophe
character or the REM statement. Did you know that you can number your
lines of code? It's easy--just add the number to the very beginning of
each line of code. For instance, the procedure
Public Function GetIniSetting(ByRef iniFilename As String, ByRef
Section As String, ByRef Setting As _ String) As String
Dim Count As Long, ReturnedString As String
ReturnedString = String(256, 0)
Count = GetPrivateProfileString(Section, Setting, "",
ReturnedString, 255, iniFilename)
GetIniSetting = Left$(ReturnedString, Count)
End Function
becomes
Private Function GetCount()
10 Dim Count As Long, ReturnedString As String
15 ReturnedString = String(256, 0)
20 Count = GetPrivateProfileString(Section, Setting, "",
ReturnedString, 255, iniFilename)
25 GetIniSetting = Left$(ReturnedString, Count)
End Function
You need remember only a few rules when numbering your code:
* Numbers must be at the very beginning of your line of code.
* Each number must be unique within the module (which is a nuisance).
* Don't number the beginning or ending statements.
You probably know about the Zoom control--it reduces and increases
the size of your worksheet by a specific percentage. However, do you
know about the Selection option? This option allows you to select the
cells you want to zoom in on (this option doesn't reduce a range) and
chooses just the right percentage to see the selection as large as
possible. Simply select the range you want to enlarge and then choose
Fit Selection from the Zoom control on the Standard toolbar. It
couldn't be simpler.
When you're done, select 100% (or the appropriate percentage) from
the Zoom control and choose Edit, Undo Zoom or press Ctrl-Z.
Okay, you've commented your worksheet to death and now you can't
move without popping up a comment. You can keep those comments and
still view the data in your worksheet if you don't mind hiding the
comments. If you'd like to hide a comment, right-click the commented
cell and choose Hide Comment from the context menu. You'll still be
able to view and edit the comment by selecting the cell. Hiding the
comment simply keeps it from popping up and covering the surrounding
data in your worksheet. And that will keep you from losing your
sanity--which should be a good thing.
Our previous tip showed you how to insert a picture into an Excel
worksheet. If you'd like to offset the picture from the rest of the
worksheet's data, you might consider outlining the picture with a
frame. To do so, right-click the inserted picture and choose Format
Object from the context menu. Next, click the Colors And Lines tab.
Choose a border control from the Line section. Finally, select a style
and weight option, then click OK.
The Currency format automatically displays the value you enter with
the currency symbol specified in your Regional Settings and two
decimal points. That means if you enter 123, Excel will display
$123.00. If you enter 123.45, Excel will enter $123.45. Regardless of
the number of digits you enter in the decimal portion, Excel will only
display two--and rounds your entry to boot. For example, if you enter
123.456, Excel will display $123.46.
The Workbook object has several properties that return the
workbook's name, path, and full name. For instance, the statement
workbook.FullName returns the full pathname of workbook. (The full
pathname includes the drive, folder(s), and filename.) If you want
just the workbook's name, you'll use the Name property in the form
workbook.Name If you want the path (without the drive), use the Path
property in the form workbook.Path Until you save a workbook, the Path
property returns an empty string ("").
As far as we know, you can't add a picture file to a comment. You
can, however, display Wingdings, and depending on the effect you're
trying to create, this solution might be adequate. First, open an
existing comment or create a new one. Then, right-click it and choose
Format Comment. Choose Wingdings from the Font control in the Format
Comment dialog box, then click OK. Excel will display any subsequent
text in Wingdings (but it won't affect existing text in the same
comment).
Many line charts include data markers, and they're great for
discerning one line from another--especially when the chart is printed
in black and white. Color charts don't always require data markers
since the color is the only visual clue you really need. When working
with color charts, you might want to consider omitting the data
markers.
The block If allows any number of conditional checks. First, the If
statement itself specifies a condition. If that condition isn't met,
control will pass to an ElseIf or Else clause. (You can also omit both
and simply end the statement with an End If clause.) How do you know
which to use? If you want to specify additional conditions, use
ElseIf in the form
If CONDITION1 Then
...
ElseIf CONDITION2 Then
...
ElseIf CONDITION3 Then
...
End If
You'll use the Else clause when you want to catch what falls
through the cracks. That's because the Else clause doesn't accept a
condition. For instance, we could add an Else clause to the above
example as follows:
If CONDITION1 Then
...
ElseIf CONDITION2 Then
...
ElseIf CONDITION3 Then
...
Else
...
End If
Any value or expression not caught by condition1, condition2, or
condition3 will be handled by the Else statement. The thing to
remember is that you can't add an ElseIf clause after the Else clause.
Every If statement can have numerous ElseIf clauses, but only one
Else.
You're probably familiar with VBA's Beep() function, but a lot of
developers ignore it. The reasons are varied, but one good reason is
that this function depends on the computer's hardware, so you can't
control the tone or volume. That means beeps can be inaudible or so
low they might as well be inaudible. If you do choose to include beeps
in your application, we have a word of advice. Use them sparingly--an
application full of beeps will only annoy your users. However, one
spot where a beep may be appropriate is at the end of a long process,
which may go unattended by your users because of its length. A short
beep can alert them that the process is complete so they can return to
their system and continue their work.
In our previous tip, we told you that Excel doesn't generally
consider the case of alphabetic characters when sorting. Fortunately,
you're not stuck with this setting. If you want to distinguish between
the two cases during a sort, here's what's necessary. Select the data
you want to sort, and choose Data, Sort. Click Options in the Sort
dialog box, and choose the Case Sensitive option. Click OK twice.
You'll have to remember to select this option each time, because Excel
won't retain the setting.
A few tips ago, we showed you how to enter the current date by
pressing Ctrl-;. Excel also has a function to perform this task--it's
the Today() function. This simple function will return the current
date and requires no arguments. There's no advantage to using it over
the keyboard shortcut. However, it does offer a bit of documentation
in that entering the function implies that you meant to enter the
current date and weren't just entering any date.
We've shared a few easy ways to enter dates in your worksheet.
However, Excel doesn't really keep time. The time Excel enters depends
on your system's internal clock. If your computer is set to the wrong
time and you use Ctrl+; to enter the current date, you might get a
surprise. It's a good idea to check the Windows date occasionally just
to make sure your computer's keeping up with the time correctly. To do
so, simply double-click the time on the Taskbar (in the far-right
corner). In the Date/Time Properties dialog box, click the correct
date (if Windows is showing the wrong date) and then click OK. While
you're at it, you can also update the time.
VBA is very flexible when it comes to working with dates. VBA even
supplies three functions you can use to determine the current date or
time. All three functions--listed below--rely on your system's clock:
Now: Returns the current date and time as a value. The integer
portion represents the date; the decimal portion represents the time.
Date: Returns only the date.
Time: Returns only the current time.
When you know you'll be working with only the date or the time, it's
much easier to work with the Date and Time functions, respectively.
Leave Now for those tasks that require both the date and time. Don't
try to use Date and Time in your worksheet as they are strictly VBA
functions. You can use them only in a module.
It's easy to publish an Excel worksheet to the Web. Simply choose
File, Save As and choose HTML from the Save As Type control in the
Save As dialog box. However, you can't publish a password-protected
worksheet. If the entire workbook is protected, you may be able to
publish individual worksheets by temporarily removing the sheet's
protection. To do so, choose Tools, Protection, Unprotect Sheet. Enter
the correct password, and then publish the sheet. Remember to restore
the protection once you've finished.
In our previous tip, we talked about using two Page Setup options
to reduce and enlarge your worksheet data for printing purposes. We
also warned you to check your page breaks when resetting these
options. Chances are if you change print options, you'll need to
adjust any manual page breaks you've set. To quickly reset all page
breaks, switch to Page Break Preview by clicking Print Preview, then
Page Break Preview. Right-click any cell on the sheet and choose Reset
All Page Breaks from the context menu. This action removes manual page
breaks and restores all automatic page breaks.
WARNING WHEN VIEWING FORMULAS
A word of warning when using the tilde to display formulas. Always
save your sheet before turning on the formula display by pressing
Ctrl-~ (tilde character). When you return to the sheet after viewing
formulas, you may find the formatting is different. For instance,
Excel may have changed the column widths of some of your columns.
Should this happen to you, simply reopen your workbook to return to
the formatting you lost. Another method for turning this feature on
and off is to pull down the Tools menu, choose Options, select Views,
and then choose Formulas.
Viruses are a valid concern for just about everyone. You can
receive a virus via the Internet or from an infected file you receive
on disk. Most of us have virus protection, but Excel can also help
protect you. That's because Excel macros can contain potentially
dangerous viruses. To protect your system from these viruses, follow
these steps. First, choose Tools, Macro, Security. Click the Security
Level tab, enable the Medium option, and click OK. Once you upgrade
your security level (Medium is the default, however), Excel will give
you the opportunity to disable macros every time you open a workbook
that contains macros.
If you want to repeat formats, you might create a style that you
can apply at any time. If you just want to quickly copy an existing
format from one cell to another, choose the Format Painter button
instead. First, select the cell that contains the formatting you want
to copy. Next, click the Format Painter button on the Standard
toolbar. Then, select any other cell or range of cells to apply the
copied format. That's all there is to it.
Most of us are familiar with the Undo button on the Standard
toolbar. Clicking this button will undo your previous actions.
Unfortunately, it's easy to forget every little step you've made, and
consequently you may undo an action you meant to leave intact. If
you'd like to refresh your memory, simply open the Undo button's
drop-down list. Every action that's undoable is listed there. The Redo
button has a similar drop-down list.
Most applications store dates as an integer and time as a decimal
value. As a result, you can use simple calculations to produce date-
and time-specific values. For instance, to learn the number of days
between two dates, you could use the simple formula
enddate - startdate
In a similar manner, you could return a date in the future by
adding a value to a date in the form startdate + days
Since one of your formula references contains a date, you may need
to format the cell containing the above formula as a Number cell to
display the actual value.
Displaying the results of formulas is a view default. You can
easily display the formulas instead of those results by choosing
Tools, Options. Then, select the View tab and choose the Formulas
option in the Windows Options section. Do you know there's a keyboard
shortcut that will toggle a sheet between displaying formulas and the
results of those values? It's great when you need to switch back and
forth quickly instead of remaining in one view. Simply press Ctrl-`
(to the left of the 1 key).
Want to print the current day on your printed sheet? You can by
following these simple steps. First, select View, Header And Footer.
In the Page Setup dialog box, click the Header/Footer tab. Now, click
the Custom Footer button. In the resulting dialog box, select one of
the three sections: Left, Center, or Right. Then, click the Date
button. Finally, click OK twice. To see the results, click the Print
Preview button and check the bottom of your sheet. You should see the
current date.
Although Office supplies a number of assistants, you can download
more, as reader Curtis Marten pointed out. In addition, you can use
third-party assistants, most of the time. If you'd like to see a few
of the additional assistants Microsoft offers, visit
http://www.microsoft.com/downloads/search.asp
(We used Office for the product name and Windows 98 for the
operating system.) Regarding third-party assistants--not all of them
work. An assistant may return the error This program has performed an
illegal operation and will be shut down. If the problem persists,
contact the program vendor. If so, you may have an assistant that uses
less than 256 colors. This is in conflict with Office--all Office
assistants must have 256 colors. Almost any assistant you download for
Office 2000 should work in Excel 2000.
Pivot tables can be hard enough to understand, but using the right
tools can help. If you're working with a pivot table, be sure to
display the PivotTable toolbar by selecting View, Toolbars,
PivotTable. Initially, Excel will open the toolbar in floating mode,
but you can dock it like any other toolbar. This is a great place to
start if you want to create a pivot table, since the toolbar includes
a tool for launching the PivotTable Wizard.
A while back, we showed you an easy way to jump to a cell--give the
cell a name and then use the Name Box control. Simply select the name
in the Name Box control (the drop-down control to the left of the
Formula bar) and Excel will select that cell or range. You don't have
to name a cell to use the Name Box in this manner. You can also enter
a cell reference, which is convenient when the cell you want to select
doesn't have a name or isn't part of a named range. For instance, to
jump to cell J8, you'd simply select the Name Box control. When you
do, Excel will highlight the current cell's address. Enter the address
of the cell you want to select, and that's all there is to it. As soon
as you enter the new address, Excel will act.
If you want to select a cell on another sheet, be sure to enter the
sheet name in the form Sheetname!celladdress
For example, to select cell J8 on a sheet named Sheet3, you'd enter
the address Sheet3!J8
A relatively new addition to VBA (with version 5.0) is the Friend
keyword. You'll use this keyword to determine a procedure's scope.
Generally, sub functions are limited to the class module that contains
them. Using the Friend keyword in the form Friend Sub NAME exposes
NAME to other modules within the same project. However, you can't
excel the procedure from outside the project (as you can when using
the Public keyword). You can use Friend with properties as well as sub
and function procedures.
Most of us use the Fill handle to copy data, but you can also use
it to copy formats. Right-click the fill handle and drag it to the
range to which you want to copy the selected cell's formats. When you
release the handle, Excel will display a shortcut menu. Choose Fill
Formats from that menu to copy just the formats and not the values.
We receive a lot of requests for formulas that return special date
and time formats. If you have a complex date or time need, don't
suffer through creating your own until you've checked the Analysis
ToolPak, which contains a number of special functions. For instance,
this add-in contains EOMonth(), which you can use to return the last
day of the current month using the form EOMonth(Today,0) To install
this add-in, choose Tools, Add-ins, then select the Analysis ToolPak
control and click OK.
Data printed on greenbar computer paper can be much easier to read
than rows and rows of data printed on plain white paper. Fortunately,
you can reproduce the effect right in your sheets, and you won't need
special paper. The first step is to color the first row or rows--one
or two should do it. Then, select those rows plus an equal number of
blank rows and click Format Painter (the button with a paintbrush on
it). Next, select the remaining rows in your sheet area. Excel will
copy the colored and blank rows to fill the rest of the sheet area.
Generally, it's best if you apply this format to a sheet before adding
data. Now, let's look at a quick example. In a blank sheet, select row
1, then select any color from the Fill Color palette on the Formatting
toolbar. (Generally speaking, a lighter color is better.) Now, select
rows 1 and 2 and click Format Painter on the Standard toolbar. Select
rows 3 through 20, and Excel will alternate the color and blank rows
accordingly.
You can quickly move around a chart by using the arrow keys. First,
select any part of your chart. Then, press an arrow key to select
other parts of the chart. Specifically, the up and down arrow keys
will select other major chart elements. The left and right arrow keys
will select chart elements in order.
You're probably familiar with the search and replace feature in
your word processing application. This feature finds every instance of
a particular word or phrase and replaces it with another word or
phrase. Excel can also support this feature. If you misspell a name or
need to update a frequently used reference, use Search and Replace.
First, select Edit, Replace. In the Replace dialog box, enter the text
you want to replace in the Find What text box. In the Replace With
text box, enter the replacement text. Next, from the Search drop-down
list, tell Excel whether you want to search by rows or by columns.
Deselect the Find Entire Cells Only option if what you're searching
for may be only part of what's in a cell. Click Find Next, and Excel
will find the first instance of the text for which you're searching.
Click Replace All to change all references at once, or click Replace
to select each occurrence individually.
Excel has an add-in you may find indispensable if you like to save
files at regular intervals. We're referring to the AutoSave add-in.
Once you've installed this add-in, you can specify how often Excel
will save the current workbook (or all open workbooks). To install the
AutoSave add-in, choose Tools, Add-ins. Then, select AutoSave Add-in
and click OK. After installing, you'll have to enable the add-in by
selecting Tools, AutoSave and then specifying the following options:
Automatic Save Entry: Determines how often Excel saves.
Save Options: You can save just the active workbook or all the open
workbooks.
Prompt Before Saving: If selected, Excel will prompt you before each
save task. One annoying feature of this add-in is that you can't turn
it off--the timed interval cannot equal 0.
Note: Chances are this add-in isn't installed, so you may need your
Office CD.
You probably see the terms round and truncate in relation to Excel
tasks, but you may not fully understand them both. When rounding a
value, Excel changes the displayed value but stores the complete value
you entered. For instance, if you entered the value 123.456 in a
Currency formatted cell, Excel would display $123.46 but store the
value 123.456. (You can see the stored value in the Formula bar.) On
the other hand, Excel chops off part of a value when it truncates it.
Imagine putting the value 123.456 in a guillotine and releasing the
safety. The blade will slice part of that portion right off, and
that's what Excel does, depending on when and where Excel decides to
truncate. So, if you enter the value 123.456 and Excel truncates to
two decimal places, Excel displays AND stores the value 123.45.
You probably use the Go To feature to make a quick jump from one
cell to another, especially when those cells are far apart. Did you
know you could use this feature to return to the original cell? Here's
a quick example. Select cell A1 and then press Ctrl-G to display the
Go To dialog box. Enter cell E5 in the Reference control and click OK
to select cell E5. Now you're in cell E5. While there, press Ctrl-G
again. This time, locate $A$1 in the Go To control instead of
specifying A1 in the Reference control. If you double-click the Go To
reference, Excel will take you back to cell A1. You can also select
the reference and click OK. Pressing F5 will also open the Go To box.
In older versions of Excel, you could apply different formats to
the text in the same cell, but it was awkward. You had to use
keystroke combinations to turn on a particular format, type the text,
and then turn the format off. Office 2000 lets you use format buttons.
Let's look at a quick example. First, select any blank cell and enter
the text
abc
Next, press Alt-Enter and type
def
(Both strings should be in the same cell, but on two different
lines.) In the Formula bar, highlight abc and then click the Bold
button on the Formatting toolbar. Highlight def and click the
Underline button. You won't notice any change in the Formula bar, but
the cell will display the two strings and their formatting--abc should
be in bold and def should be underlined. Ta-da! You applied two
different formats to the same cell.
If you use VBA, you should consider using Excel's built-in macro
recorder. Many coded tasks can be created quickly, with little effort,
by using the macro recorder. You may have to tweak the code a bit, but
why reinvent the wheel? Start with the macro recorder, change what you
must, and get to the next task that much quicker. To find the macro
recorder, select Tools, Macro, Record New Macro. Enter a name for the
macro and identify where to store it. At this point, reproduce the
steps you want to automate with code. When you've finished, choose
Tools, Macro, Stop Recording.
An Excel 2000 workbook opens with 16 sheets by default. However,
you can insert a new sheet, should the need arise. First, determine
the location of your new sheet--which two sheets you will insert the
new sheet between. Click the tab of the sheet that should be behind
the new sheet. For instance, if you're inserting a sheet between
Sheet2 and Sheet3, click Sheet3's tab. Then, choose Insert, Worksheet
and Excel will insert the sheet. The default name will depend on the
number of sheets present in your worksheet. If you have 16 sheets
before inserting a new one, Excel will name the new sheet Sheet17.
Although applying formats is an easy task, a complex sheet can
require a bit more thought and time. However, unformatting a sheet is
always a quick task--much quicker than you might think. It's not
necessary to select each formatted cell or range and remove each
format. You can quickly return a cell, a range, or even the entire
sheet to the General format. To do so, select the cell or range you
want to unformat, then press Ctrl-Shift-~ (tilde). Excel will remove
all formats and return the selected cell or range to the General
format (the default format for all sheets).
Before printing a sheet, you probably take a look at it in Print
Preview. When you want to print just a portion of a sheet, what do you
do? You can't select a range and view just that section in Print
Preview. Excel displays the entire sheet.
Whether you're printing or not, you can get a quick view of a range
by selecting that range and then choosing View, Zoom. In the Zoom
dialog box, select the Fit Selection option, and Excel will display
just the selected range in the window. Depending on the view options
you started with, the resulting view may be smaller or larger.
When you need to format a cell or range, don't reach for the Format
menu or right-click the range. Instead, press Ctrl-1 to display the
Format Cells dialog box. If you're a keyboard lover, you'll find this
a welcome relief to grabbing the mouse.
Excel columns have a default column width of 8.43 characters, or 64
pixels. That means many entries aren't completely visible. (If there's
no data to the right of a long entry, Excel will display the entire
entry.) You could change the column's width, but you might spend a lot
of time guessing at the appropriate width to display everything. You
can make it easy on yourself by double-clicking the right border of a
column's header cell (the gray cell at the top of the column). Doing
so will automatically adjust the column's width to display the longest
entry in that column.
To copy an entire column, select the column to the right of the
column you're copying and press Ctrl-R. To copy an entire row, select
the row below the column you want to copy and press Ctrl-D.
You probably know you can change a column's width by dragging that
column's right border cell. You can do the same in Print Preview.
First, display your worksheet in Print Preview. The right column
margin is represented by a small square at the top of the page. If the
column squares aren't visible, click the Margins button. Then, simply
drag a column square, and Excel will adjust that column accordingly.
Excel charts are fairly easy to create and modify. Select the data
you want to chart and click the Chart Wizard button on the Standard
toolbar. You specify a number of chart properties, and Excel creates
your chart. There's a quicker way as long as you don't mind a chart
that's based on the Excel chart defaults. If this is the case, select
the data and press F11. Excel will create and display your chart
automatically. Even if you have to reset a few properties, this method
is property faster than running through the entire chart wizard.
Choosing File, Print displays the Print dialog box, where you can
choose from a number of print options, including the range you want to
print. When you click the Print button on the Standard toolbar, Excel
prints the default print area. This may or may not be what you meant
to do, especially if you don't even remember what the default print
area is! If you find you often end up with unexpected results when
using this button, just remove it from your toolbar and replace it
with the Print... button. This button displays the Print dialog box
before printing. We've told you how to delete and add buttons, but
just in case we'll repeat ourselves a bit. First, right-click any
toolbar and choose Customize from the context menu. (Or select View,
Toolbars, Customize.) To delete a button, simply drag it off the
toolbar. To add a button, click the Toolbars tab and identify the
toolbar you're modifying. Then, select the Commands tab and select the
appropriate item in the Categories control. Next, drag the button
you're adding from the Commands control to the appropriate position on
the toolbar. That's it. You're all set.
You can define a specific print area by choosing File, Print Area,
Set Print Area. Once you define a print area, Excel will print that
area automatically, unless you change the print settings. This feature
is convenient when your worksheet contains data you don't want to
print. However, use it with caution. If you add rows or columns to the
worksheet, you'll need to redefine the print area, because Excel won't
automatically add new data to the print area.
Several tips ago, we showed you how to copy data from one sheet to
another. You simply hold down the Alt key while you drag the cell or
cells to the appropriate sheet tab at the bottom of your screen. Excel
automatically jumps to that sheet and continues to drag the data to
its new home in the target sheet. You can also use the Alt key while
dragging data to prevent the sheet from scrolling. If you've ever had
to drag data right to the edge of the worksheet, you already know that
Excel can overreact and send you scrolling into oblivion. Holding down
the Alt key allows you to move a block of data right to the edge
without all that trouble.
You're probably familiar with inserting charts into an Excel
worksheet. Did you know you can also insert a picture, such as clip
art or a scanned photograph? To insert a picture or other graphic
file, first select the cell where you want to insert the picture.
Next, choose Insert, Picture. Select the appropriate file option, then
click OK. You're all set.
When you copy a range of data (several columns), you can't
automatically paste the column widths that accommodate your data. That
means your copied data may look like a mess in its new home. Don't
worry--you can fix it in a jiffy. Here's how:
First, select the data you want to copy and press Ctrl-C. Click a
destination cell and press Ctrl-V to paste the data to the new range.
Right-click the new range and choose Paste Special. Select the Column
Widths option in the Paste section, then click OK. When you return to
your worksheet, the columns in the new range will be the same width as
the columns in the original range.
You probably know how easy it is to link an Excel chart to a
PowerPoint slide. Simply right-click the chart and choose Copy. Then,
select the PowerPoint slide and press Ctrl-V. You can use this method
can create a huge presentation if you have several charts. You can
save memory by pasting a picture of the chart instead of linking to
the underlying data.
To do so, select the chart and copy it to the Clipboard. Next,
select the PowerPoint slide; choose Edit, Paste Special; and select
the Picture option. Just keep in mind that you can't edit the Excel
data from PowerPoint when you copy the chart as a picture instead of
linking to the chart.
Some people represent negative values by enclosing those values in
parentheses instead of using the negative sign. By default, if you
enter a value in this manner--(80) instead of -80--Excel will display
the value as -80. To retain the parentheses, you'll need a special
format. Begin by right-clicking the cells involved. Choose Format
Cells from the context menu. Click the Number tab and choose Number in
the Category control. Select the red (1234.10) option, and click OK.
With this format set, you can enter a value as (80) or -80, and Excel
will display the value as (80) and in red. Unfortunately, you're stuck
with the red. We tried changing the Color option to another color, but
Excel ignored us.
Sorting Excel data is simple, but did you ever wonder how Excel
makes sorting decisions? Here are a few guidelines you can use to help
ensure your sort tasks are successful:
An ascending sort considers numbers first, punctuation next, and
then letters from A to Z (case isn't relevant). Sort order for
punctuation characters is as follows: space, !, ", #, $, %, &, (, ),
*, comma , period, /, :, ;, ?, @, [, \, ], ^, _, ', {, |, }, ~, -, <,
=, >
As a rule, Excel ignores hyphens and apostrophe characters. If two
entries are identical except for a hyphen, the entry with the hyphen
is sorted after the entry without a hyphen.
The Web is a treasure trove of facts and data that you may find
beneficial. If you can get that data into Excel, you can even track
and analyze it. Fortunately, you don't have to reenter the data, or
even cut and paste it, because Excel supports HTML files. Simply
download the page and open it in Excel. Or better yet, open it
directly from Excel. First, connect to the Internet. Then, select
File, Open. Enter the URL of the page that contains the data you want
to capture. Click Open. Finally, save the file to your local system as
an Excel file.
As we mentioned in our previous tip, each sheet in your workbook
can have a default print area. If you want to print it, click the
Print button and Excel will print just that range, regardless of what
else is on that sheet. It won't print the entire workbook--just the
current sheet. This behavior can be a problem if you'd like to print
or preview only the default print areas of each sheet. When this is
the case, choose File, Print and select the Entire Workbook option
from the Print What category. If you just want to preview the data,
click the Preview button in the bottom-left corner of the Print dialog
box instead of clicking OK. In the Preview window, Excel will display
the default print area for the first sheet with a defined print area.
Additionally, Excel identifies the total number of pages in the
combined print area. Simply scroll through the pages to see each. From
here, you can print the combined area or you can return to your
workbook. The one problem we encountered is that Excel returns you to
the first page with a defined print area instead of to the sheet that
was active when you began your print task. Oh, well, it can't always
be perfect, right?
We all know that you can enter numbers as text by preceding the
value with an apostrophe character ('). Let's suppose that later you
decide to use these text values as numeric values. If you have only a
few values to contend with, you can just retype the entries as values.
However, if you're working with rows and columns of values, this isn't
practical.
To convert text to values, enter the value
1
in any blank cell. Then, select that cell and choose Edit, Copy.
Next, select the range of values you want to convert and select Edit,
Paste Special. In the Paste Special dialog box, select the Multiply
option in the Operation section and then click OK. Excel will multiply
the contents of each cell by the value 1 and consequently return a
value--the same value--to each cell.
You probably know that you can select noncontiguous ranges by
holding down the Ctrl key as you select additional cells. For example,
select cells A1:A3. Then, hold down the Ctrl key and select cells
C4:C6, and Excel will highlight both ranges.
One limitation with these multiple selections is you can't cut and
paste data. If you try, Excel will display an error message that
explains you can't use that command (whatever copy command you try) on
multiple selections.
In our previous tip, we told you how to repeat an entry across an
entire range. You can do so with a contiguous or noncontiguous
range--if you know how to create a noncontiguous range. Fortunately,
it's easy. First, select the first range as you normally would. Then,
hold down the Ctrl key and select the second range. You can add as
many areas as you like--just remember to hold down the Ctrl key as you
select the additional ranges.
The most recent versions of the Visual Basic Editor sport new
toolbars. Now there's a Debug toolbar, which includes many commands
that weren't on the Standard toolbar. The Edit toolbar includes
commands for writing better code. A UserForm toolbar has quick
formatting tools. To open one of these new toolbars, simply
right-click any open toolbar and make a choice from the context menu.
(Of course, you must be in the Visual Basic Editor.)
Complex calculations sometimes require nested functions. These are
similar to nested formulas in that a nested function is a function
that uses other functions as its argument. However, nested functions
are a bit more restrictive than nested formulas. For instance, you can
only nest up to seven levels of functions. In addition, the nested
function must return the same value type as the argument. Therefore,
pay close attention when nesting one function within another.
To learn the sum of a range of values, you must enter a formula
that refers to the values you want to sum. Right? Not really. Excel
has a feature called AutoCalculate that will display the result of
several functions without ever fooling with a formula. Let's suppose
cells A1:A4 contain the values 1, 2, 3, and 4, respectively. If you
want to see the sum of all four values, select cell A1. Then, drag the
mouse over cells A2:A4. Now, look on the right side of the status bar.
You'll see a short message, Sum = 9. If you'd rather see the average
value instead of the sum, right-click the message in the status bar
and choose Avg from the context menu. You'll find several other
functions available.
When you change formatting, do you sometimes decide you don't like
the changes after all and delete them? This isn't always necessary.
You see, some properties will offer a preview of the new look before
you apply it. If you don't like it, just cancel the action. For
instance, to apply a new font type, you select the range you're
changing and choose Format, Cells. Next, click the Font tab and apply
your changes. Excel will display those changes in the Preview box in
the lower-right corner. The Number And Patterns section will also
display a sample of your modifications.
Assigning names to cells and ranges is a common task in Excel. If
you intend to take advantage of this useful feature, you'll need to
know the rules:
A name can contain up to 255 characters.
The first character must be a letter or an underline character.
All remaining characters after the first letter must be letters,
numbers, periods, or the underline character. That means spaces aren't
allowed--use the underscore character instead of spaces. You can't use
a cell reference or value as a name.
Happy naming!
If you're going to include the contents of a cell in several
formulas and functions, considering giving that cell a name. Then,
simply enter the name in each formula and function. Why? First, a name
is easier to remember than a cell reference. Second, copying the
formula or function won't upset your formulas and functions.
Generally, if you copy a formula or function, Excel uses relative
addressing rules to update the new formula. That doesn't happen with a
named cell or range; the formula will continue to refer to the named
cell. Of course, if you want the formula or function to use relative
addressing, you'll need to skip this tip; use this tip only when you
want to retain the absolute address.
The Select Case statement accepts multiple conditions in a single
Case statement. However, VBA evaluates them separately. In other
words, you should think of multiple conditions in terms of the OR
operator and not the AND operator. For example, the following Case
statement may seem valid:
Case Is > 10, Is < 20
You might think this statement will return True if a value is
greater than 10 but less than 20. In other words, you're looking for
values between 10 and 20. However, that's not how it works. Once a
condition is met, VBA ignores any others. Therefore, if the value
you're comparing is greater than 10, this statement returns true, even
when the value is greater than 20. In addition, any value that's not
greater than 10 will certainly be less than 20, and any value that's
not less than 20 will also be greater than 10. Therefore, the
condition always returns True. Did you get all that? Good... More
exciting Excel tips to come!
As useful as the MSDN is, finding what you really need can take
several tries. The keywords that seem logical to me seem to never
work. If you're suffering from the same problem, we can't offer you
any help in finding the Help information you need--the first time. But
we can make it easier for you to excel that data, once you've finally
located it the first time. After you find a topic you know you'll be
reviewing again, simply create a bookmark to it. Then, the next time
you want to see that topic, select it from the Favorites tab. You can
find the MSDN site at
You can quickly move to the last cell in a contiguous block without
leaving the current cell. You simply double-click the current cell's
border--the border will determine the direction of the move. For
instance, if you're in the middle of a large block of data and you
want to move to the top cell within that block of data, simply
double-click the top border of the current cell. If you want to move
to the last cell to the right within that same block of data,
double-click the current cell's right border. The same is true when
moving to the left or down--double-click the left or the bottom border
of the current cell, respectively.
Do you know you can copy an entire sheet from one workbook to
another? To do so, open the source workbook and choose Edit, Move Or
Copy Sheet. When the Move Or Copy dialog box opens, open the Book
drop-down list and choose New Book. Now select the Create A Copy check
box and click OK. Excel copies the selected sheet to a new workbook.
This option is helpful when you want to share with someone a sheet but
not the entire workbook. Or you can use this feature when you want to
base a new workbook on existing data.
In previous tips, we've recommended that you use the Formula
palette to create complex formulas. Did you know you could also use
the palette to edit existing formulas? Simply select the cell that
contains the formula you want to edit. Then, click the Edit Formula
button (the equal sign next to the Formula bar). Excel will bold the
last function in the formula and display that function's arguments in
the palette. You can change the emphasis (and update the palette) by
clicking any other function name in the Formula bar.
In our previous tip, we showed you how to turn on the Fixed Decimal
feature so Excel will automatically insert decimals for you. If you
frequently toggle back and forth between Fixed Decimal mode and Normal
mode, you might want to use the following macro:
Sub FixedDecimal()
Application.FixedDecimal = Not Application.FixedDecimal
End Sub
This macro simply resets the Fixed Decimal property.
In our previous tip, we discussed securing your workbooks with a
password. That way, only you--and anyone you share that password
with--will have excel to your data.
There's more to the password feature than just keeping unwanted
people out of your data. You can also prevent someone from modifying
your workbook. You may want to share the information but preserve it.
If this is the case, you'd password-protect the workbook and the data.
To do so, choose File, Save As. From the Tools drop-down list, choose
General Options. Next, in the Save Options dialog box, type the
password you want to use. Specify both File Sharing options: Password
To Open and Password To Option. Click OK. Then, type a filename for
your document and click OK.
In a previous tip, we showed you how to replace data using the Edit
menu's Replace command. However, choosing Replace All can have
unexpected and destructive results because you could write over data
you didn't intend to change. If you choose this method for changing
information on a worksheet, you could eradicate data that you can't
see. Of course, replacing each item one at a time can be a real
nuisance. So, here's some protection when you use Replace All. Save
your file with a different name before you try a global Replace. Then
you'll at least have a backup in case you do delete something you
need.
While we're discussing print behavior, you probably know you can
define a default print area. As we told you in our previous tip, Excel
will print just that defined range when you click the Print button on
the Standard toolbar. To set this default print area, select the range
in question and choose File, Print Area, Set Print Area. Each sheet in
your workbook has its own default print area--or the capability to
define a default print area.
Specifically, you select the area you want to print, then choose
File, Print Area, Set Print Area. Once you define a print area, Excel
prints that area automatically, unless you change the print settings.
Unfortunately, if you add a row or column to the sheet, Excel doesn't
automatically update the assigned print area. You'll have to do so
manually. Insert a new row or column instead of just appending new
data to the existing sheet. That way, Excel will expand the existing
print area for you. Now, the one limitation with this method is that
it works only with multiple row or column areas. If you set a print
area that's only one row or one column, this trick won't work. In this
case, simply select a blank row above or below the sheet and a blank
column to the left or the right of your sheet.
Our previous tip showed you how to use the Multiply option to
quickly convert text to values. You can use the same option to
multiply the contents of a cell by itself. For example, let's suppose
cell A1 contains the value 2. You select this cell; choose Edit, Copy;
and then choose Edit, Paste Special. In the resulting dialog box,
select the Multiply option and click OK. Doing so will return the
value 4 in cell A1. If you continue, Excel will multiply 4 by itself
to return 16, and so on.
A few tips ago we showed you how to enter the same value in every
cell of a range. You can use this technique to limit a data entry
area. First, select the range into which you're going to enter data.
Now, take a look at the highlighted range. The first cell you clicked
is the active cell--you can tell this because it isn't highlighted
like the rest of the range. Now, type your first entry--the value you
want to enter in the active cell--and press Enter. Excel will enter
the value and select the next cell (depending on the Move Selection
After Enter setting). When you reach the last cell in a particular
direction, Excel will jump to the first cell in the next highlighted
row or column. Excel won't select a cell outside the highlighted
range. Neat, huh?
Tired of puzzling error messages? If so, download and run
EXTRALERT.EXE to install Microsoft Office 2000 Customizable Alerts.
This program will offer to find more information. Visit
to download the program. Once you've installed it, Excel 2000 will
(sometimes) display a Web Help button along with the normal error
message. Clicking the button will take you to updated information on
Microsoft's site. (Of course, you'll need an Internet connection for
this feature to work.)
There's no way most of us can memorize all the arguments for every
function, and who would want to anyway? Fortunately, a photographic
memory isn't necessary to work with Excel functions. When you enter a
function and find yourself needing a bit of a memory boost, press
Ctrl-A, and Excel will display the formula palette. This palette
displays all the possible arguments you'll need to supply. You'll even
find a bit of explanation about the argument. Furthermore, the palette
will display the function's results, so you can check the accuracy of
your function before pressing Enter.
There's only so much data you can fit on one sheet of paper.
However, Excel will try its best to get as much as possible from each
sheet. You can reduce or enlarge your spreadsheet data by changing the
Adjust To % Normal Size option. Or you can use the Fit To Pages option
to compress data to fill a specific number of pages. Simply choose
File, Page Setup and experiment with these two options by changing
their settings. Finally, click the Print Preview button to see the
results. When resetting these options, be careful to take into account
placement of any page breaks.
A shared workbook tracks changes, making it easier to revert to
earlier changes if necessary. However, be careful if you're depending
on this option, because it maintains changes for only 30 days.
Fortunately, you can change this default setting. To do so, select
Tools, Share Workbook, then click the Advanced tab. Enter the number
of days you want to retain changes in the Keep Change History For
control. It's important to note at this point that this feature works
only with shared workbooks.
Regardless of what problem you're trying to solve with VBA, it's a
good idea to look around for existing code so you don't have to work
quite so hard. A great place to start that search is Helen Feddema's
site at
http://ulster.net/~hfeddema/
You'll find technical articles and code samples for a large array
of situations--and she's already worked out most of the kinks.
Specifically, this site offers two VBA solutions for exporting Outlook
contacts and calendar events into Excel.
You don't need complicated security features to protect your
confidential data. When you save an Excel document, simply add a
password option. Then only you--and anyone you share the password
with--can open the document again. To save a workbook with a password,
first choose File, Save As. From the Tools drop-down list, choose
General Options. Next, in the Save Options dialog box, type the
password you want to use. Click OK. Then, type a filename for your
document and click OK.
"Did you know you can link a text box to a worksheet cell? First,
create a text box. Next, click inside the text box. Now, in the
Formula bar, type an equal (=) sign. Finally, select the worksheet
cell that contains the data or text you want shown in your text box
and press Enter. You may instead choose to type the reference to the
worksheet cell. But remember to include the sheet name followed by an
exclamation point--for example, Sheet1!F2--before pressing Enter." In
case you're wondering, you can add a text box to your worksheet.
You'll find the text box tool on the Drawing toolbar. We'd also like
to stress that you must enter the cell reference in the Formula bar.
If you try to add it to the text box, Excel will treat your entry as
regular text.
Eariler, we discussed using the Zoom feature to view a selected
area. First, you select the range you want to view. Then, you choose
View, Zoom and select the Fit Selection option. As a result, Excel
will display just the selected range in the window.
We could also use the Print Area feature for this purpose. We've
discussed this feature a bit over the last few months, but we haven't
mentioned how well it works for viewing ranges in a sheet. First,
select the range you want to view. Then, choose File, Print Area, Set
Print Area. To view the selected range, simply click Print Preview on
the Standard toolbar. With a defined print area, Excel will display
just that range in the preview window, not the entire sheet. On the
other hand, if you preview your sheet and only get a look at a range
instead of the whole sheet, check the Print Area option. If it's set,
you can unset it by selecting the Print Area Clear command.
In our previous tip, we showed you a quick way to jump from one
cell to another and then return to the first cell just as quickly. If
remembering cell addresses isn't a natural talent for you, try
labeling your cell with an easy-to-remember name. It's similar to
naming a cell. To label a cell, select the cell and then excel the
Name box (the combo box to the left of the Formula bar). Enter a
descriptive label for your cell. Now, press F5 to move to any other
cell. Then, press F5 again. This time, the Go To control will list the
label instead of a cell address. This is just one simple way to use
this feature. You can use this label just like a name.
Ever make changes to a toolbar and then have them disappear
forever? If that's the case, you're probably working in a workbook
that has multiple users. You see, you can make permanent changes to a
toolbar only if you have an exclusive lock on the file. Excel will
allow you to update a toolbar during the current working session. You
simply can't save those changes to the database.
In our previous tip, we mentioned that you may need to install a
new assistant before you can get rid of Clippit and switch to another
assistant. That's because Office 2000 only installs Clippit. To
install another or all the available assistants, select Start,
Settings, Control Panel. Next, double-click the Add/Remove Programs
option, select Microsoft Office 2000, and then click the Add/Remove
button. Next, click the Add Or Remove Features button in the Office
setup window. You'll see a series of options, with plus and minus
signs to the left of each. Click the plus sign to the left of the
Office Tools option. Then, click the plus sign next to the Office
Assistant option, which will open all the available assistants. Select
the assistant(s) you want to install, and then choose the installation
process you want. Finally, click Update Now and click OK. If you chose
the Installed On First Use option, be sure to have your Office 2000 CD
handy when you change the assistant.
If you're still using the Insert menu to insert rows and columns,
there's a quicker way. First, right-click the header cell for the
column or row to the right or just below the column or row you want to
insert, respectively. (The header cell is the gray cell at each column
and to the left of each row.) Excel will display a context menu.
Select Insert to insert a column or row. Congrats--you just saved
yourself a couple of keystrokes.
Do you know that you can insert rows or columns in Excel worksheets
using only the mouse? To do so, hold down the Shift key while you grab
a cell by its handle and drag it. Excel will insert as many cells as
you need. For example, enter data in cells A1..C1. Now, to insert two
cells between cells A1 and B1, hold down the Shift key, grab A1's
handle, drag it to cell C1, and then release. Excel will insert two
blank cells between cells A1 and B1.
Most of the time the data we import into Excel is delimited or at a
fixed width, which means we can usually just copy and paste the data
right into Excel. If the originating format isn't acceptable, Excel
will paste all the data into one column instead of separating the text
into columns for us. When this happens, don't pull your hair out--just
use the Text To Columns feature. Select the text (only one column at a
time), then choose Data, Text To Columns. Excel will launch the
Convert Text To Columns Wizard. Simply specify the required data, and
the wizard will separate the data into columns for you.
You can create your own AutoFill list series by choosing Tools,
Options; clicking the Custom Lists tab; and entering the items in your
list. When you're done, you click the Add button. If the list already
exists in another sheet, don't retype it. Instead, choose Tools,
Options, then click the Custom Lists tab. Next, click Import and
identify the cells that contain the list. That's all there is to it!
It's fairly easy to share data between Office applications. Often,
you can just cut and paste data. When there's a lot of data, or you
want to filter data, use Microsoft Query instead. For instance, to
import excel data into an Excel spreadsheet, complete the following
steps:
Position the cursor in the top-right cell of the Excel range in
which you want to store the imported data. Next, select Data, Get
External Data, New Database Query. Select MS excel Database and click
OK. Then, locate the MDB file that contains the data you want to
import and click OK. Double-click the table or query. If you want to
omit some of the fields, click the plus sign next to the table or
query, and then double-click the fields individually. Click Next to
continue. Specify filtering criteria and click Next. Still with us?
Good. At this point, specify the sort order and click Next. Select the
Return Data To Microsoft Excel option and click Finish. If you plan to
run this import again, click Save Query. Next time, simply select the
query instead of creating a new one (as we did in this example).
Finally, specify the top-right cell in the import range and click OK.
(If you did this in step 1, you need only click OK.) Whew! Excel will
import the table or query data into the sheet. Be sure to save the
data after the import.
When writing an If statement, you can take advantage of VBA's
implicit form of True to reduce your code just a bit. If so, you
should be aware of the differences between the implicit and explicit
forms of True and False. When expressed explicitly in the following
form:
If var = True Then
var must evaluate to -1 for the condition to return True. In
contrast, the statement
If var Then
is True much more often than the previous statement because the
implicit form of True includes any nonzero value. Consequently, only 0
evaluates to False. Keep this behavior in mind when depending on the
implicit True value--the results might not be what you expected.
In our previous tip, we showed you how to move menus around. If you
decide you don't like the changes you made, or you delete a menu and
you can't figure out how to get it back, you don't have to reinstall
Excel. To return a menu bar or toolbar to its original settings, open
the Customize dialog box by choosing Tools, Customize. Select the menu
bar in question and click the Reset button. Doing so will return the
menu bar to its original condition.
Hyperlinks are popping up all over--and it's no wonder. They
perform a unique and useful task, and they're easy to create. You can
choose Insert, Hyperlink, or you can insert a label, enter text, and
press Ctrl-K. Pressing Ctrl-K will display the Insert Hyperlink dialog
box. There, you can identify the link. The method is especially
convenient when you want the hyperlink to display text other than the
link's address.
Graphics take more computer processing time than letters, numbers,
and lines. That means it can take time for Excel to redraw a special
graphic when you're scrolling around your sheet. In fact, if the
graphic is complicated, that graphic can significantly slow things
down. When this is the case, tell Excel not to display the graphic(s).
The graphic will still be there; you just won't see it. Consequently,
the graphic won't slow things down. To turn off a graphic's display,
first choose Tools, Options. In the Options dialog box, click the View
tab. Choose one of the Objects options:
Show Placeholders--For improved speed, while still seeing shaded
rectangles that show where the graphics will be.
Hide All--For the best speed, without even the shaded rectangles.
Finally, click OK.
The Print command will also ignore graphics, so be sure to turn on
the graphics before printing if you want the graphic included in your
printout.
Office 2000 installs only one assistant by default, and that's
Clippit. If you want to fire Clippit and hire a new assistant, you may
need to install it first. First, try changing the assistant by
right-clicking Clippit, selecting Choose Assistant, and then clicking
the Gallery tab (if necessary). Click the Next and Back buttons to
find a new assistant, and then click OK. You may want to have your
Office 2000 CD ready, just in case. If you didn't install the other
assistants, you'll have to do so before you can dump Clippit and hire
a new one.
Last month we talked a bit about protecting and hiding data.
However, using a sheet's Protection feature to hide data is overkill
if you just want to hide a cell or two. For a quick hiding method,
right-click the cell that contains data you don't want anyone else to
see. Choose Format Cells from the context menu and select Custom from
the Category control. Then, type three semicolons, as in
;;;
in the Type control, and click OK. Excel will hide the data in the
newly formatted cell or range. However, you'll still be able to see
the contents of the cell in the Formula bar. To unhide the data,
simply apply a different format.
Typically, you want Excel to display data when you chart it, but
not always. A good example of just such a situation is data that's
charted by the month. You probably don't want to re-plot your chart as
you add data for each month, so you probably plot the entire worksheet
(from January to December) when you create the chart. However, doing
so displays axis points for all the months, not just those for which
you have data. If you'd rather your chart not display points for
months that have no data, you can hide the empty worksheet columns
until you're ready to update that month. Excel won't chart the hidden
columns. For example, if you have data only for the months of January
through June, you can hide the worksheet columns from July through
December. Then, when you run the chart, Excel will seem to plot only
January through June. When you add July's data, you simply unhide the
column, and Excel will add the new data to your chart--month by month.
To hide a column, simply select the column, right-click the selection,
and then choose Hide from the context menu. Unhiding the column is
just as simple--choose Unhide from the context menu.
You've probably noticed that the new versions of Microsoft
applications make use of a solid triangle as a pointer. You can use
these yourself since they're included in the Marlett font. Just
specify one of the values 3 through 6 and apply the Marlett font to
the value. The following identifies the pointer direction with a
value:
3 Left pointer
4 Right pointer
5 Up pointer
6 Down pointer
Now, here's how to use them to add quality pointers to your
controls. First, add a label control to your form (object). Referring
to the list above, enter the appropriate value as the label's Caption
property. Then, specify
Marlett
as the label's Font property. The Marlett font will display one of
the four pointers, depending on the value you entered, instead of the
value.
When using VBA to interact with your worksheet files, you might
rely on global variables. Global variables can be useful, but take
care when you depend on them because they can be easily wiped clean by
an unhandled error. When VBA encounters an error that's not rerouted
by appropriate handling code--an unhandled error--VBA cancels the
value of all global variables. In addition, an inappropriately placed
End statement can reset your global variables before you meant for
that to happen. There's really no workaround for this behavior. Just
make sure you include thorough error-handling code if your application
includes global variables. (Also, avoid using the obsolete End
statement.)
No matter how careful you are, errors do creep into your
application. When this happens, you'll want to know which error has
occurred. Fortunately, you can add the following message box statement
to your error-handling code to display the current error number and
its description:
MsgBox Err.Description & vbCrLf & Err.Number
You can drop this into almost any error-handling routine.
Even experts need a hint now again, especially when you're trying
to decipher a complex formula with several sets of nested
expressions--each one requiring a complete set of parentheses.
Fortunately, Excel can guide you through the formula. Simply select
the cell that contains the formula you're reviewing and click inside
the Formula bar. Then, use the arrow keys to move through the formula.
When the insertion point moves over a parenthesis, Excel will
highlight its mate. In addition, when you edit the formula, Excel
displays each matched set in a different color, so you can easily
distinguish between the sets.
Error messages can be hard to see if you're not specifically
looking for them. If you don't find them, you could end up with a lot
of incorrect data. You can suppress error messages with formulas, but
you might find conditional formatting a bit easier. Let's look at a
simple example. First, select any cell and enter a formula that you
know will return an error message--such as =A1/0. Now, select the cell
that contains the formula and choose Format, Conditional Formatting.
In the Conditional Formatting dialog box, make the following
selections:
Condition 1 Formula Is Edit box =IsError(ADDRESS)
where ADDRESS is the cell address to which you're applying the
conditional formatting. Next, click the Format button, then apply a
conditional formatting. For instance, you might choose Red from the
Color drop-down list. Or you might click the Border tab and apply a
border to the cell. Excel will display that format when the formula
displays an error message.
Would you like Excel to launch automatically when you turn on your
system? Well, more specifically, we can show you how to launch Excel
when you launch Windows. If you use Excel every day, you'll be saving
yourself a little time and effort. To add Excel to your startup
options, click Start (in the Taskbar), choose Programs, right-click
Microsoft Excel, and select Copy from the context menu. Next,
right-click Startup and choose Paste. Now, the next time you turn on
your PC and launch Windows, Windows will automatically launch Excel.
You can use the Go To Special command to select all the blank cells
in the active worksheet. First, press F5 to display the Go To dialog
box. Then, click the Special button in the bottom-left corner. In the
Go To Special dialog box, select the Blanks option and click OK. Excel
will highlight all the blank cells. If you have a blank cell that
Excel doesn't select, you might suspect that the cell's simply
hidden--but you'd be wrong. You can't use the Go To Special command
when your worksheet is protected. Check the cell carefully to make
sure it's really not full of spaces. Truthfully, it'll be hard to
tell, so try this: Select the cell and press the Delete key. Then, try
selecting all the blank cells again. More than likely, the Go To
Special command will work after you delete the cell's unseen contents.
You can use a combo box to update other form controls. For
instance, if you choose an employee by name from a combo box, you
might then use a lookup function to return that employee's address and
phone number. But did you know there's a quicker way? If all the data
you want to display is stored in the same row, try using the Column
property in the form CTRL.Column(X) where CTRL is a reference to the
combo box and X identifies the position of the corresponding field in
the combo box. Just remember to add all the necessary fields to the
combo box when you're creating it. In addition, X is 0 based, so the
first field in the combo box equals 0, the second equals 1, and so on.
It's easy to summarize data, but not so easy to copy just the
summarized data. For instance, you can hide detail data while
displaying only subtotals and grand totals. However, when you copy
those totals, you'll also copy all the hidden detail data. There is a
way to copy just the visible data. Before you copy the summary totals
(or data), choose Edit, Go To; click the Special button; and select
the Visible Cells Only option. Next, select the cells that contain
your summary and continue with your copy routine. Excel will paste
only the visible cells to the target range.
Excel has supported array formulas for a long time, but that
doesn't make them any easier to work with. A common problem is editing
arrays. When editing an array, here are a few guidelines that might
help. If you entered the array across multiple cells, be sure to
select all cells involved before you begin editing. Once you've edited
the array, remember to press Ctrl-Shift-Enter to enter your changes.
Users who enter long columns of values often like to enter those
values minus the decimal point to save a keystroke. In other words, to
enter the value 123.45, they enter 12345 and Excel fills in the
decimal. This behavior isn't a default of Excel's--you'll have to turn
it on. To do so, first choose Tools, Options. Then click Edit, select
the Fixed Decimal option, and set the Places value before clicking OK.
You can add a descriptive label to a chart by adding a title to
your Excel chart. However, the title becomes part of the chart, which
may complicate matters if the completed chart will end up in a Word
document or a PowerPoint presentation. When that's the case, omit the
title and add it later to your document or presentation. That way, if
you change the default design, your application will also update the
title. On the other hand, if that title is part of the embedded chart,
you'll have to remember to manually update it yourself.
Did you realize that Excel would accept fractional values and
display them appropriately? When entering a complex fraction, such as
1 and 3/4, simply enter the integer, followed by a space character,
then the fractional components separated by the slash character, and
Excel will display the entry as a complex fraction. However, Excel
will store the decimal value of the entry. When a fraction doesn't
contain an integer, enter 0 in lieu of entering nothing. If you enter
nothing, Excel will interpret your entry as a date. For example, if
you enter 3/4 Excel returns 4-Mar, not the fraction. To enter this
fraction, you'd enter 0, followed by a space, and then 3/4.
Working with dates doesn't always mean complicated expressions and
functions. Here's an easy trick for counting the number of days
between two dates. Enter an expression in the form ="end date" -
"start date" For instance, to determine how many days there are
between September 27 and Christmas, you'd enter the short expression
="12/25/2000" - "9/27/2000" and Excel will return the value 89. Be
careful not to omit the quotations, or this shortcut won't work.
You can turn a normal Excel cell reference into an absolute
reference without much effort. (An absolute reference forces Excel to
always refer to the cells you specify.) Let's say you enter
=SUM(A1:A5) in cell A6 and then decide A1:A5 should be an absolute
reference, not a relative reference. You could insert each $ manually,
but that's too much trouble. Instead, double-click cell A6 and then
use the mouse to select the reference A1:A5. Next, press F4 and press
Enter. The cell contents will change to the absolute reference form of
=SUM($A$1:$A$5)
There are a number of free utilities available, and almost everyone
should find something of benefit. Tip in a tip:Never download any file
from the Web unless you have current virus software installed.
When you double-click a cell, Excel opens that cell in Edit mode.
This may or may not be a problem for you. If you find users
inadvertently double-click and destroy data in Edit mode without
realizing what they're doing, it's a problem for you. Fortunately, you
can inhibit this behavior. Choose Tools, Options, then click the Edit
tab. Next, deselect the Edit Directly In Cell option. Then, click OK
to return to your worksheet. The next time someone double-clicks a
cell, Excel will just select the cell.
When you click the Underline button on the Formatting toolbar,
Excel underlines the data in the selected cell or range. By default,
that underline is a single line. If you need a double underline, you
probably select the Cells command from the Format menu, click the Font
tab, and then choose Double from the Underline control. However,
that's a permanent change. Excel will continue to display a double
underline until you change it back, which is probably inconvenient for
most of us. To quickly apply a double underline without changing any
Excel defaults, simply hold down the Shift key as you click the
Underline button.
If you need to return the month component from a date, you have two
choices. You can use the Month() function in the form Month(date)
which will return a month's integer value. For instance, if date is
April 21, 2000, the Month() function will return the value 4.
If you need to see the month's name, you can use a custom format.
Simply select the cell that contains your date, and then choose
Format, Cells. Next, choose Custom and enter the format code mmmm.
When you return to the worksheet, Excel will display just the month's
name.
You can split a sheet to view different areas of the same sheet by
moving the Split bar. But how do you display more than one sheet at
the same time? Click the sheet tab to activate the first sheet. Choose
Window, New Window (repeat the first two steps for each sheet you want
to view). Select Window, Arrange, Tiled, and select the Windows Of
Active Workbook option. Finally, click OK. Excel will display a shot
of each of the selected sheets. Neat, eh?
Excel will display up to 11 numbers in a cell even though the
default column width is 8.43. This means Excel automatically increases
the column width. If you enter more than 12 numbers, this isn't true.
Instead, Excel will display pound signs (#) or scientific notation.
When this happens, you must increase the column width yourself. To do
so, simply right-click the entire column (click the column's heading
cell), choose Column Width from the context menu, enter the new column
width, and click OK. You can also assign the AutoFit property by
double-clicking the header cell's right border.
Would you like your toolbar buttons to be a bit larger? You can
enlarge buttons by following these few steps. Choose Tools, Customize;
click the Options tab; and select the Large Icons option in the Other
section. If you change your mind, simply deselect this option, and
Excel will return to the default buttons. This option won't affect the
size of your screen tips.
One of the most frequently asked questions we receive is how to
print the file's name in the sheet's header or footer. We covered this
issue last year, but because it seems such a common question, we'll
repeat the information. There's no built-in feature for printing the
file's name, but you can do so with a simple macro. To create this
macro, open the VB Editor by clicking Alt-F11. Then, select the
correct project in the Project window (which you'll find in the
upper-left corner). Next, select Insert, Module. In that module, enter
the following procedure:
Sub NameInFooter()
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName
End sub
To close the VB Editor, click the Close button (X) at the top-right
corner of your screen. When you're ready to run the macro, choose
Tools, Macro, Macros. Then, select NameInFooter and click Run. You
won't see the effects immediately. If you click the Print Preview
button, you can see the file name in the footer. You might be
wondering if there's a way to print the file name in one of the other
footers or even a header. For instance, what if you want to print the
name in the center of the footer or in the right portion of the
header? Fortunately, the solution is simple--replace the LeftFooter
property in the above macro with one of the following properties:
To avoid a few keystrokes, attach this macro to the workbook's
BeforeSave event or attach it to a macro button. If you change the
file's name, be sure to update the header after the initial save.
If you're not new to programming but you're new to Excel and VBA,
you may try to declare your VBA variables using the Dim statement in
the following form:
Dim strFirst, strLast As String
Some programming languages allow this form, but VBA does not.
Obviously, you're trying to declare both strFirst and strLast as
String variables. VBA will declare only strLast as a string. Since you
didn't explicitly declare strFirst, VBA will define strFirst as a
Variant. The proper statement is Dim strFirst As String, strLast As
String
In our previous tip, we alerted you to an incorrect syntax form
that causes trouble when you are declaring variables using Dim. We
also recommend you position all your declaration statements together
at the beginning of your procedure, although VBA allows you to declare
variables at any time. Grouping your Dim statements in this manner
makes it easier to determine a variable's data type and scope while
you're debugging because you don't have to go hunting for the
statement--they're all right together at the beginning of the
procedure.
Complex formulas can be hard to debug, and in the process you can
easily lose your original formula. To avoid this, enter your formula
without the equal sign. Then, copy the formula (without the equal
sign) to another cell for safekeeping. Then, return to the original
cell, add the equal sign, and begin your debugging work. At any time,
you can revert to your original formula by simply copying the copied
formula to the original cell and adding an equal sign.
VBA's DateDiff() function returns a Variant value representing a
timed interval between two specific dates. For instance, the statement
DateDiff("yyyy", #1/1/2000#, #1/1/2001#) would return the value 1, as
there is one year between the two dates. There's an unexpected
behavior you should know about. When trying to determine the years
between December 31 of one year and January 1 of the next, Excel will
return 1, even though there is only one day between the two dates.
When you think it out, the result makes perfect sense because the
dates are in two different years. However, at first, the function's
response can be a bit disconcerting since the dates are only one day
apart.
A few tips ago, we showed you how to improve data entry tasks by
using data forms. When you choose this option, be very careful when
you delete data. You see, a delete task is permanent. That's why Excel
asks you to confirm your request--it's just an opportunity to change
your mind one last time before the data is gone. Most of the time, you
can use the Undo command to retrieve deleted data, but you can't use
the Undo command to restore data once you've deleted it via a data
form.
If you need to enter the same formula or value in many cells, don't
type the data entry over and over. Instead, select the range
(contiguous or noncontiguous) into which you're making the same entry.
Then, type the formula or value into the active cell, but don't press
Enter. Instead, when you're ready to enter the contents of the active
cell, hold down the Ctrl key while you press Enter. Excel will copy
the contents of the active cell to every cell in the range.
Have a lot of data to enter and need a little help? If so, create a
data form to guide you through the process. To do so, enter a row of
label headings for each column. For instance, if you're entering first
and last names, you might enter the labels Fname and Lname,
respectively. Select the heading cells, and then choose Data, Form.
Excel will automatically create and display a data entry form based on
your heading labels. At this point, you simply start entering data in
the new form, and Excel will automatically append your entries to the
worksheet.
Excel's AutoComplete feature eases the repetition of data entry.
When entering a list of items, this feature allows you to enter the
label automatically--to a point. As you type an entry, Excel compares
your keystrokes to the existing list. When Excel finds an existing
entry that matches, Excel offers to enter the remaining characters to
complete the entry. You can press Enter to do so, or continue typing
to complete the entry yourself. For example, enter the following
labels in cells A1:A3:
cat
dog
catfish
Now, select cell A4 and type catf
Once you type in enough characters that Excel can pinpoint a
possible entry, it fills in the rest of the entry. Press Enter to
finish the entry, and go to the next cell or finish typing your entry
yourself.
DATA ENTRY EASE--PART 2 OF 2
In our previous tip, we showed you how to use Excel's AutoComplete
feature to ease the repetition of data entry. Another feature that can
ease data entry a bit is the Pick From List feature. Similarly to the
AutoComplete feature, Pick From List works with a list of items.
However, instead of entering a few characters until Excel attempts to
fill in the remaining characters, you can select the entire entry from
a complete list of entries. After entering just one item in a list,
this feature is available. For example, enter cat in cell A1 and
select cell A2. Don't enter anything. Instead, right-click A2 and
choose Pick From List from the context menu (or press K). In response,
Excel will display a drop-down list that consists of all the entries
in the list above the current cell. In this case, that list includes
just the word cat. To enter an item, simply select it.
Excel's menu commands may suit the masses, but if the commands
aren't where you want them, just move them. First, choose Tools,
Customize. Click the Toolbars tab and select Worksheet Menu Bar. At
this point, you can select any menu and move it just by using the
drag-and-drop method. You can also move the menu's subheadings the
same way. Just open the menu and move things around to suit you.
We do have one word of warning: When you change the menus and
toolbars a lot, you make it much more difficult to use Excel on
another system, unless that copy of Excel is customized the same way
as yours. Additionally, anyone who tries to use your copy of Excel may
be a bit lost.
There's more than one way to display currency. The typical method
is to simply click the Currency Style button on the Formatting
toolbar. Doing so displays the $ sign at the left edge of the cell and
right-aligns the amount. This tool assigns the Accounting format, not
the Currency format, and Excel displays the two a little differently.
If you'd rather, you can pull down the Format menu, choose Cells (or
the appropriate command), and choose Currency from the Category
control. This format displays the $ sign to the left of the amount
(not at the far-left edge of the cell). There's a third method you
might not be aware of and that's the Dollar() function, which takes
the form
=Dollar(amount)
Apparently, this function converts your entry to text and
left-aligns it. However, you can still use the amount in calculations.
The Advanced Filter can quickly create a unique list of items when
a list contains duplicates--if you remember to click the Unique
Records Only option. Now, here's how to create a unique list. Select
the original list with duplicates and the heading cell. (The list must
have a label heading. For instance, you might head a list of states
with the label "States.") Choose Data, Filter, Advanced Filter. Then,
click the Copy To Another Location option. Select the Unique Records
Only option. Note the target range or cell in the Copy To control box.
Click OK. The resulting list will contain only one entry for each
unique item in the original list.
In previous tips, we've talked about attaching procedures to custom
buttons. When you want to run the macro or procedure, you simply click
the button instead of choosing Tools, Macro. To assign a macro to a
button, follow these general steps:
First, select View, Toolbars, Customize. Click the Commands tab and
select Macros in the Categories control. Drag the Custom Button in the
Commands control to a toolbar. Once you've added the macro button,
click the Modify Selection button. Choose Assign Macro from the
resulting shortcut menu. Finally, identify the macro you want to
attach to the macro button, click OK, and then click Close.
You probably know that you can apply special validation rules to
cells. These rules limit the data that cell will accept. If you later
copy or move the data, Excel will also copy the validation rules.
However, what if you just want to copy the validation rules and not
the data? To do so, select the cell or range that contains the
validation rule you want to copy and press Ctrl-C. Then, select the
target cell and select Edit, Paste Special (or right-click the target
cell). In the resulting dialog box, select the Validation option and
click OK.
While we're on the subject of printing, you might be interested in
a quick tip for copying print settings from one sheet to another.
First, select the sheet with the settings you want to copy. Then, hold
down the Shift (or Ctrl) key and click on the tab (or tabs) of the
other sheet (or sheets) you want to copy the settings to. At this
point, choose File, Page Setup, and click OK. By doing so, you will
copy all the basic print settings from the active sheet to all the
others in your temporary group. (You'll also copy header and footer
settings.)
You can use the cut and paste shortcut keystroke combinations
Ctrl-C and Ctrl-V to copy data into a cell's comment. First, select
the text you want to copy and press Ctrl-C. Next, create a cell
comment by choosing Insert, Comment. Or open an existing comment by
choosing View, Comment. Then, position the cursor inside the Comment
box and press Ctrl-V.
Numeric text is a valid data type. For instance, a phone number
isn't really a number. When storing this type of data, you probably
prefix the entry with the apostrophe character ('). Sometimes,
however, imported numerical values are imported as numerical text, and
this can be a problem. Fortunately, it's easy to convert numerical
text to numbers.
Simply follow these steps. Select the range of data you want to
convert. Next, choose Data, Text To Columns. Click Next twice. Choose
the General format in the Column Data Format section. Then, click
Finish. Excel will convert numerical text entries to numerical data.
If you hold down the right mouse button while dragging cell data
from one area to another, Excel will display a few conversion options
when you drop the data. That's because Excel will display a shortcut
menu offering several copy and paste possibilities, including these
format options:
Copy Here As Formats Only
Copy Here As Values Only
As the names imply, the first will copy only the formats of the
cells you've just dragged, while the latter will copy only the data.
To excel this menu, select the range you want to copy as you normally
would. Then, grab the range by holding down the right mouse button
instead of the left mouse button. Then, drag and drop the cells to see
the shortcut menu.
If you open an Excel 97 workbook in Excel 2000, you'll probably be
prompted to save the file when you exit, even if you don't change a
thing. That's because Excel 2000 has an improved recalculation engine
that corrects many calculation bugs in Excel 97. As a result, when you
open a 97 workbook in 2000, Excel completely recalculates the
workbook. We suggest you click Yes when prompted to save the file.
Doing so will correct any of those old recalculation bugs.
Have you ever tried to concatenate text to a date? It can be
difficult. For instance, enter today's date in any cell by selecting a
cell and pressing Ctrl-; (semicolon). Next, select another blank cell
and enter the expression
="Today is " & CELLADDRESS
where CELLADDRESS is the address of the cell that contains the
date. Instead of displaying the date, the expression will return the
date's serial value. To avoid this situation, wrap CELLADDRESS in a
Text function in the form
="Today is " & Text(CELLADDRESS, "mmmm d, yyyy")
You can modify the format ("mmmm d, yyyy") to suit your needs.
Referring to colors in your code can be a bit of a nuisance because
you must remember the color's corresponding integer. If you're working
with the Windows standard colors, your work is made easier by a few
intrinsic constants. Instead of looking up a color integer, you can
simply use one of these constants:
Black vbBlack
White vbWhite
Red vbRed
Yellow vbYellow
Blue vbBlue
Green vbGreen
Cyan vbCyan
Working with these constants should prove much easier and more
efficient than using the color's integer values.
If you use a lot of graphic images in your work, you might want to
take a look at Clip Gallery Live, a Microsoft site that offers over
100,000 graphic images and sounds, including clip art, photographs,
and Web animations. You can use these clips in your Office documents
(including Excel)--just be sure to read the licensing data carefully
regarding redistribution and usage. To visit this site, just point
your browser to
When you click on most controls, you produce a Click event. Right?
Well, let's see. When you click a text box, the control fires its
Click event--that much is true. However, if you click inside the text
box component of a combo box, you don't trigger that control's Click
event. So what's the difference? The combo and list box controls don't
fire their Click event until the user actually selects an item from
the control's list. This behavior is considerably different from other
controls because they trigger their Click event as soon as the control
receives the focus. So consider the outcome carefully when you attach
code to a combo or list box's Click event.
Circling something can bring attention to it, and you can do this
easily in Excel. First, display the Drawing toolbar by right-clicking
any toolbar and choosing Drawing from the context menu. Then, click
the Circle tool on the Drawing toolbar and drag the mouse pointer
across the cell(s) that contain the data you want to encircle. The
circle will most likely be solid and cover the data, so right-click
the circle, choose Format AutoShape, and click the Colors And Lines
tab. Next, choose No Fill from the Fill Color control and click OK. If
a red circle would be even better, repeat the above process and choose
red from the Line Color control.
Most of the tools on the Formatting toolbar offer a shortcut to
specific default styles. Specifically, the Currency, Percent, and
Comma buttons assign the corresponding named styles when you click
them. To check this out, first select Format, Style. Then, open the
Style name control's drop-down list and choose an existing style to
see its characteristics. This means you can change the style of these
three buttons. For instance, the default Percent style displays no
decimal places. You can change the way the Percent button displays a
value by changing the Percent style. To do so, choose Format, Style,
and select Percent from the Style name control. Next, click the Modify
button, click the Number tab, and enter a new value in the Decimal
Places control. You can click any of the tabs to change a number of
formatting options.
You're probably aware of Excel's Undo button. In fact, we've
written a few tips about this feature, which allows you to undo many
tasks. Unfortunately, you can't undo a task once you've saved the
workbook. Once you execute a save, Excel erases the Undo list and
disables the button until you complete a task that can be undone.
A couple of tips ago, we talked about using names, instead of cell
addresses, in formulas in functions. Later, if you delete one of these
names, your formulas and functions will return the #NAME? error.
That's because Excel won't automatically adjust your references when
you delete a name. When you try to review these formulas, all you'll
see is the #NAME? error in the formula bar, so restoring your formulas
and functions could be quite a chore--especially if there are several
of them to fix.
You can avoid this problem if you remember to update all your
references before you delete the name. To do so, select Edit, Find (or
press Ctrl-F), enter the name of the cell or range you're about to
delete, choose Formulas from the Look In control box, and then click
Find Next. Excel will locate each cell that contains the specified
name. Edit each formula or function accordingly, and then you can
safely delete the name.
When you enter data, Excel does its best to determine the type of
data you're entering. Dates are a good example of Excel's intuitive
efforts. If you enter a value that contains a slash (/) or hyphen (-),
and that value also complies with the Windows standard date format,
Excel will interpret your entry as a serial value and format the entry
accordingly. Similarly, Excel will format any value that contains a
colon (:) as a time value. Or you can follow the time value with an A
or P, representing AM and PM, respectively.
After explaining the Merge Center feature, we find another way to
center a title across multiple columns. First, right-click the cell or
cells that contain the text you want to center. Next, choose Format
Cells from the context menu. Then, click the Alignment tab and choose
the Center Across Selection option from the Horizontal control.
Last month, we told you about AutoSave, an Excel add-in that backs
up the current workbook at regular intervals. For better or worse,
this add-in overwrites the file--it doesn't create a separate backup
copy of the file. If this is a problem for you, there's a freeware
add-in named AutoSafe. This EXE file creates a separate file for
backup purposes instead of overwriting the original file. For more
information and downloading, visit
http://www.bmsltd.co.uk/MVP/MVPPage.asp and scroll almost to the
bottom.
Note: Never download anything from the Internet without virus
protection.
Links can be very useful. They can also be very annoying,
especially when Excel says you have a link and you know (or think) you
don't have any links. There's an easy-to-use and free add-in available
for locating those hard-to-find links that you think don't exist.
Simply visit
http://www.bmsltd.co.uk/MVP/MVPPage.asp and download Findlink.zip.
WARNING: Never download anything from the Web unless you have
updated virus-protection software installed.
Most of us work with Julian dates as the day of the year, beginning
with January 1 of the current year. (Technically, the anchor date for
the Julian calendar is fixed.) A Julian calendar (for just the current
year) would denote January 1 as 1, obviously. However, February 1 is
32, and March 1 is 61. Fortunately, it's easy to calculate a
Julian-type date. Just use the simple VBA expression strDate =
Format(dte, "y") where dte represents the date you're converting. If
you're converting the current date, replace the dte argument with the
Date function.
If you'd like to test this expression, press Alt-F11 to open the
Visual Basic Editor, and enter a statement similar to
?Format(#7-13-2000#,"y") in the Immediate window. VBA will return 195,
which means July 13, 2000, is 195 days into the year 2000. Neat, huh?
As your worksheet grows, there comes a point where you can't see
the whole thing on screen, and you lose sight of your column and row
headings. This setup makes it difficult to know whether you're in the
right column or row. Fortunately, the answer is simple--freeze your
headings.
Select a cell just to the right of the row or just below the column
that contains your headings. Choose Window, Freeze Panes. Now the rows
to the left and the columns above the cell(s) you selected before
freezing your headings will always remain on screen--no matter how far
down or to the right you scroll. You can turn this feature off just as
quickly by selecting Window, Unfreeze Panes. Neat, huh?
You can save an Excel 2000 workbook to another format using the
File, Save As command. Simply review the Save As Type control's
drop-down list for a complete list of alternate formats. However, when
you change formats, you may lose data and formatting that are unique
to Excel 2000. When this happens, Excel will warn you, but it won't
identify the specific data or formatting that will be lost.
This isn't a limitation on Excel's part. In fact, Excel is doing
its best to convert your data to the chosen format. If you're unsure
about just what you'll lose to other formats, read "Formatting and
features not transferred in file conversions" in the Help section.
A few tips ago we told you how to insert and frame a picture. If
you've entered multiple objects and you're having trouble aligning
them, use the snap-to-cell feature. Hold down the Alt key as you drag
the object into place. When you let go of the object, Excel will align
the object to the nearest cell.
You can add time values, but summing the values isn't enough. For
instance, let's suppose cells A1 and A2 contain time values, and you
enter the formula =A1+A2 in cell A3. Chances are, the formula won't
display the value you expect if the sum of the time values exceeds 24
hours. When this happens, try formatting cells A1 and A2 using a
custom format defined as [hh]:mm. The brackets tell Excel to allow
hours greater than 24 for summing purposes.
The combo and list box controls allow you to enter a list of items.
When you choose this route, you enter in the control's Row Source
property a setting in the form of
"item1";"item2";"item3"
You must also specify the Value List setting for the Row Source
Type property. The result is a list of items--one right after the
other--in the same order as they appear in the Row Source property
setting. You might not realize that you can display more than one
column of items in a Value List control. To do so, you simply add a
second item to the list in the form
Excel will display items 1a and 1b in the first row, items 2a and
2b in the second row, and items 3a and 3b in the third row. Just be
sure to update the Column Count property to reflect the appropriate
number of columns.
In our previous tip, we showed you how to add a second (or more)
column of items to a list or combo box list. You can use the same
method to display blank lines in a control. Specifically, insert a
zero-length string between each item in the list in the form
"item1";"";"item2";"";"item3";""
In addition, be sure to select the Value List setting from the Row
Source Type property. The Column Count property should be set to 1.
If you're like most of us, you're opening the Format dialog box on
a regular basis either to remove an existing format or to apply a new
one. To open the dialog box, you must choose a formatting command from
the Format menu. If you prefer keystrokes to mouse clicks, you can
open the Format dialog box by pressing Ctrl-1. (That's the numeric
digit 1, not a lowercase L.)
I'm showing my age here, but years ago you couldn't display Excel
text at an angle. Now, angled text is a simple format. Right-click the
cell that contains the text you want to display at an angle. Next,
select Format Cells from the context menu, and then click the
Alignment tab. To the right of the dialog box, you'll see the
Orientation options. If you want a vertical label, click the thin box
on the left--it displays the word Text as a vertical label. The
control next to the vertical option allows you to drag the text angle
line to indicate the desired angle. Or if you prefer, you can simply
enter the angle value in the Degrees control.
We all know how to copy the contents of one cell to another, right?
I'll bet you don't know about this copy shortcut. You can copy the
contents of one cell to the cell that's directly to the right of that
cell by selecting the cell to the right and pressing Ctrl-R. Did you
get that? For example, to copy the contents of cell A1, you'd select
cell B1 and press Ctrl-R. Similarly, you can copy a cell's contents to
the cell below by selecting the cell below and pressing Ctrl-D. To
copy the contents of cell A1, you'd select cell A2 and press Ctrl-D.
There are a couple of ways to close a userform. You can click the
Close button in the upper-right corner of the form's title bar. Or you
may choose to include a close or exit command button on your form,
which you simply click to close the form. There's also a keyboard
shortcut that will close a userform; simply press Alt-F4. How 'bout
that?
Ever wish you had a bigger monitor? Well, maybe you don't really
need a bigger monitor--what you need is a bigger view of your
worksheet. Just choose View, Full Screen. Excel will display only your
worksheet and the menu bar--everything else has been neatly tucked
away out of view. That means you can see more of your work. When
you're ready to return to the normal view, choose Close Full Screen
from the small floating toolbar that Excel displays during Full Screen
view. Or simply deselect Full Screen on the View menu.
We're all used to summing and averaging, but do you occasionally
need to count items? It's a common task and, fortunately, easily
handled with Excel's Count() and CountA() functions. CountA() returns
the number of cells containing data--any data. For example, enter the
characters
1
A
2
in cells A1:A3, respectively. Then, enter the function
=CountA(A1:A3)
in cell A4 to return the value 3--all three cells contain data.
Now, let's suppose you want to know how many of those cells contain
numbers. In that case, you'd enter the function =Count(A1:A3) to
return the value 2--because only two cells contain values (which
includes date and time values). Need to know how many cells contain
text? Combine the two functions as follows:
=CountA(A1:A3) - Count(A1:A3)
which in this case returns 1--only cell A2 contains text.
Over the last year, we've had a number of tips about hiding and
unhiding rows and columns. Here is how to quickly unhide all your
columns and rows with one effort. After hiding any number of columns
or rows, select the entire worksheet by clicking the sheet selector
(the gray cell at the intersection of the row and column headings), or
click Ctrl-A. Pull down the Format menu, select Column or Row as
appropriate, and choose Unhide. Excel will display all hidden columns
or rows, respectively.
A lot of people think the keyboard is much quicker than the menus
and the mouse. For those folks, applying formats can be a tedious
process. If you'd like to skip the Format menu and mouse and apply a
format via the keyboard, here are a few shortcuts you can try:
General Format: Ctrl-Shift-~ (tilde)
Number Format with two decimal places (0.00): Ctrl-Shift-!
Percentage Format: Ctrl-Shift-%
Scientific: Ctrl-Shift-^
Most charts appear with a spreadsheet of data, and you're probably
used to printing them that way, too--data and chart together. You can
easily print a chart without the data, though. All you have to do is
select the chart object and then click the Print button. Excel will
adjust the chart to the size of the page and print it
accordingly--without any other data.
You probably know about the Percent format, but did you know about
the % operator? You use this operator to multiply any value by .01.
For instance, the expression
=10 * .10
will return 1. It might surprise you to learn that the expression
When working in an Excel module, you might need temporary excel to
an unopened workbook. You could stop what you're doing, return to the
workbook environment, and use the File, Open command. Or you can enter
the following command in the Immediate window by running a statement
in the form Workbooks.Open "workbookname" where "workbookname"
identifies the workbook by name to open the workbook. Be sure to
specify the complete path and the filename's extension.
Most of us open a workbook by choosing a file and clicking the Open
button. Did you realize there's more than one way to open a workbook
in Excel? There are four options, in fact:
Open: Opens the workbook with all permissions and features. This is
probably the Open command most of you use on a regular basis.
Open Read-Only: Opens the workbook as a read-only file. That means you
can read the data, but you can't alter anything.
Open As Copy: Opens the workbook as a copy by creating a new workbook
file in the very same folder.
Open in Browser: Opens the workbook as an .htm file in your system's
default browser.
You can open a workbook a number of ways, and perhaps the most
common method is to choose File, Open. Fortunately, there's a
keystroke alternative for those of you who prefer working from the
keyboard. Simply press Ctrl-O. If you want to open a new workbook
instead of an existing workbook, press Ctrl-N.
We've been discussing the AutoFilter feature for the past couple of
tips. Did you know you could sort a filtered list? You can, and it's
as simple as filtering a non-filtered list. Select the filtered list.
Select Data, Sort, then choose the appropriate column from the Sort By
control box. Specify the Ascending or Descending option, and click OK.
That's all there is to it!
Most of us don't have one simple sheet of data to work with--we
have sheets and more sheets spread across a number of workbooks. As a
result, viewing all the data can be difficult. An easy solution is to
open multiple windows. To do so, select the sheet you want to view by
clicking its tab. Then, choose Window, New Window, and Excel will
display a new window on top of the original sheet. Repeat this
procedure until you've opened a new window for all the sheets you
need. At this point, select Window, Arrange; specify one of the
display options (Tiled, Horizontal, or Vertical); and click OK. If
you're working with more than one open workbook, you may find the
Windows Of Active Workbook option helpful. Selecting this option will
cause Excel to include only sheets of the active workbook in the
window arrangement.
Despite being a spreadsheet, Excel offers some good database tools,
such as a basic data-filtering feature. The quickest way to sort
through several rows of data is to apply an AutoFilter. To do so,
you'll need a list and some kind of criteria to specify which records
you want to filter. Then, you turn on the AutoFilter feature. Click
anywhere inside the list (a valid list is a contiguous range of
columns and rows). Select Filter, Data, then select AutoFilter from
the submenu to display drop-down arrows in the heading cells of each
column. Click one of these drop-down arrows and choose an item from a
unique list of entries in that column. Excel will temporarily hide any
record that doesn't match the selected entry.
Everybody knows what a cell reference is--it identifies the cell's
location (okay, if you didn't know, you do now!). Specifically, the
cell reference is born when a column and row intersect. For instance,
intersecting column C and row 3 produces cell C3. There are several
ways to reference cells:
Explicitly, which means you reference each cell by its cell
address: C3-C4-C5-C6.
By range: C3:C6.
By column: C:C refers to column C. C:E refers to columns C, D, and E.
By row: 3:3 refers to row 3. 3:6 refers to rows 3, 4, 5, and 6.
In our previous tip, we showed you how to use the AutoFilter
feature to filter records. Once you've finished reviewing those
records, you'll probably want to see all of your records again. To
release the filter, simply choose All from the current filter's
drop-down list.
If you want to turn off the AutoFilter feature completely, simply
choose Data, Filter, then deselect AutoFilter.
Office 2000 applications have a new feature called Detect And
Repair, which resolves or repairs problems with DLLs, the registry,
and program files. Running the Detect And Repair feature does the
following:
* Rewrites registry values.
* Reinstalls Windows Installer shortcuts.
* Reinstalls Office .exe and .dll files if any are missing, corrupted,
or out of date.
Before considering a complete reinstall the next time you have a
problem, try running Detect And Repair first. To do so, choose Help,
Detect And Repair. The process may take a few minutes, so be patient.
Most likely, you'll need your Office CD.
Running this feature won't affect existing workbooks files.
With 256 columns and 65,536 rows, there's a lot of space in a sheet
to cover, and you can easily get lost. Fortunately, there are a number
of ways to navigate a huge sheet, and at some time or another you'll
probably find them all useful:
Page Up moves the active cell one screen up from the current cell.
Page Down moves the active cell down one full screen.
Alt-Page Down moves the active cell one full screen to the right.
Alt-Page Up moves the active cell one full screen to the left.
Home moves the active cell to the first cell in the current row.
Ctrl-Home makes cell A1 the active cell.
Ctrl-End selects the last used cell in the sheet.
AutoSum automatically enters the sum of any column or row of
values. Simply select the cell below or to the right of the values
you're summing and click the AutoSum button. You can even sum multiple
columns and rows of values at the same time by selecting contiguous
cells and clicking AutoSum.
If you're fond of the keyboard, you can do the same thing by
pressing Alt-=. This keystroke combination is the equivalent of
pressing the AutoSum button.
Changes to this
page are IN PROGRESS 
