MS EXCEL 2000
LAST UPDATED:
08 November 2007 18:24:00 -0600
Translate this page
using
FreeTranslation.com
Changes to this
page are IN PROGRESS
Click below for specified area of interest
MODIFYING THE DEFAULT FOLDER
Excel saves all your files to the default folder--My
Documents--unless you specify a different folder. Likewise, when
you want to open a file, Excel displays the contents of the My
Documents folder. If you're like most folks, you don't
even use this folder! If you're tired of constantly changing
folders before you can open or save a file, then read on.
Did you realize you could change the Excel default folder from My
Documents to any folder you like?
To change the default folder from My Documents (or any other
folder), first choose Options from the Tools menu. Click
the General tab and enter the path of the folder you want to make
the default folder in the Default File Location
control. Once you make this change, Excel will store your files in
the new default folder, unless you direct
otherwise.
STORING SHORTCUTS IN THE FAVORITES FOLDER
In our last tip, we talked about changing Excel's default folder.
If you frequently use the same file (or files) but
you don't want to make their folder your default folder (perhaps
they're in a network folder), create a shortcut to the
file(s) and store the shortcut in your default folder or the
Favorites folder.
To create a shortcut, right-click the file in its original folder
and then choose Create, Shortcut. Next, select the
newly created shortcut in the same folder and then choose Add To
Favorites from the Tools icon in the current dialog
box (Open or Save). To open the file, simply click the Favorites
folder on the Places toolbar. Then, locate the
shortcut in the list of folders and files and click it!
If you prefer to store the shortcut in your default folder, just
cut and paste the shortcut from one folder to another.
Good luck!
A FORMATTING SHORTCUT
Do you find yourself repeating formatting tasks from one worksheet
to another in the same workbook? If so, it isn't
necessary. You can apply formats to one, several, or all the
worksheets in your workbook at the same time.
To apply the same format to every worksheet in the workbook,
right-click any sheet tab and choose Select All Sheets.
Then, go about your formatting tasks. Whatever formatting you apply
with all the sheets selected, Excel will apply to
all the sheets--not just the current sheet.
If you only want to include some worksheets in the format group,
hold down the Ctrl key as you click the appropriate
sheet tabs to create a group of sheets. Then, go about your
formatting tasks.
To ungroup either group (all or some), right-click any tab in the
group and choose Ungroup Sheets.
--------------------------------------------------------------------------------
CREATING A QUICK GROUP OF SHEETS
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
http://www.microsoft.com/office/ork/2000/default.htm
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
http://www.microsoft.com/office/ork/2000/appndx/toolbox.htm#custalrt
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
http://www-sci.lib.uci.edu/HSG/RefCalculators.html
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:
http://www.microsoft.com/security/bulletins/ms99-030.asp
You'll also find a patch available for download.
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.
Good luck!
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
http://msdn.microsoft.com
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
http://officeupdate.microsoft.com/2000/downloadDetails/alerts.htm
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.
Don't forget--passwords are case-sensitive.
"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 t
