WHAT ON EARTH IS #REF!?

If #REF! appears in a cell in your worksheet, you deleted data needed to calculate a formula. Before you delete a row or column, make sure the row or column does not contain data that is used in a formula.

If this just happened to you by accident, you can click the Undo button to get back what you just deleted.

ACTIVEX HELP

In our last tip, we told you it's fine to use any registered ActiveX controls you find on your system in your own applications, even if you didn't pay for those controls directly. (You did pay for the controls--when you purchased the hosting application.) Unfortunately, unless you have the documentation and Help files that you get when you actually purchase the control, taking advantage of the control may not be an easy task. You'll need to know about the control's properties and methods in order to use it.

If you're lucky, the Help files may be installed. To find out, insert the control in a form and then open that control's property sheet. Select any property and press F1. If the files are there, pressing F1 should access them and, of course, you're free to use them.

ACTIVEX LEGALITIES

Often, you don't have to purchase an ActiveX control directly to have it installed on your system. You see, any application that uses an ActiveX control registers that control on your system. Once the control is registered, you're free to use it. You'll find these additional controls on the Tools menu under Additional Controls.

Although you can use these ActiveX controls in your own applications, you can't distribute them. If you decide you want to distribute one of these controls, you'll need to acquire a license. And that's another story...

ANCHORS AWAY!

The "anchor" cell in any range of cells is the top-left cell in the range. You'll often see documentation that refers to the anchor cell, so it's important to understand just what that phrase means. You need this information when you tell Excel on what area to work. For instance, you might be instructed to select cells A3..C4 and then told to enter a value in the anchor cell. If you don't know which cell is the anchor cell, you're stuck. In this case, the anchor cell is cell A3.

A SHARPER ANGLE

I'm showing my age here, but years ago you couldn't display Excel text at an angle. Now, angled text is a simple format. Right-click the cell that contains the text you want to display at an angle. Next, select Format Cells from the context menu, and then click the Alignment tab. To the right of the dialog box, you'll see the Orientation options. If you want a vertical label, click the thin box on the left--it displays the word Text as a vertical label. The control next to the vertical option allows you to drag the text angle line to indicate the desired angle. Or if you prefer, you can simply enter the angle value in the Degrees control.

HIRING A NEW ASSISTANT

Office 2000 installs only one assistant by default, and that's Clippit. If you want to fire Clippit and hire a new assistant, you may need to install it first. First, try changing the assistant by right-clicking Clippit, selecting Choose Assistant, and then clicking the Gallery tab (if necessary). Click the Next and Back buttons to find a new assistant, and then click OK. You may want to have your Office 2000 CD ready, just in case.

If you didn't install the other assistants, you'll have to do so before you can dump Clippit and hire a new one.

INSTALLING ASSISTANTS

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.

MAINTAIN AN AUDIT TRAIL

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.

AUTOCOMPLETE

AutoComplete enables you to type the first few letters of a text entry into a cell, and Excel automatically completes the entry based on other entries that you've already made in the column.

1. Begin entering text or a value.
2. If Excel recognizes your entry, it automatically completes it.
3. If Excel guesses correctly, press Enter to accept it.

If you want to enter something else, just continue typing and ignore Excel's guess.

AUTOFILL

You can preformat entire columns to ensure that the data will have the same format. For example, if a column contains dates, format the entire column with the desired date format.

In addition, Excel (2k) comes equipped with the AutoFill feature, which is turned on by default. The List AutoFill feature automatically copies formatting and formulas when new rows are added below the last row in the list. You can toggle this feature by selecting the Tools, Options (Alt, T, O) command, clicking the Edit tab, and checking or clearing the Extend list (Alt, L) formats and formulas check box.

Remember: An Excel worksheet has 65,536 rows, and a cell can hold more than 32,000 characters.

AUTOFIT

You don't have to carefully jigger the size of columns and rows in order to have them show all of their information, yet not take up excess space. You can use AutoFit, a command in the Format, Row and Format, Column menus. Just select the column or row -- or the entire worksheet -- and choose those commands. To skip going to the menus, you can also do this by double-clicking on the boundary to the right or below the header.

WHEN AUTOFIT JUST WON'T DO

When AutoFit's best-fit won't do, drag the right border of the column (on the frame) until it's the size you need instead of double-clicking it. This manual technique for calibrating the column width also works when more than one column is selected. Just be aware that all selected columns assume whatever size you make the one that you're actually dragging.

USING AUTOFORMAT

We've talked quite a bit about different format options. Well, here's a quick way to format data using the AutoFormat feature. This feature offers a number of predefined formatting schemes. You simply apply one of these schemes to your data and Excel does all the work. Neat, huh?

Before you can choose a format, you must identify the data you want to format. You can do so by selecting any cell within the spreadsheet or by selecting the entire range of data. After you've identified the data, choose Format, AutoFormat. Select one of the 17 predefined formatting schemes. Then, click OK to apply the selected scheme and return to your worksheet.

If you don't like the results, simply click the Undo button on the Standard toolbar.

MORE ON AUTOFORMAT

We introduced you to the AutoFormat feature in our previous tip. You may have noticed that the AutoFormat dialog box has an Options button. Clicking this button will display the following formatting specifics:

• Number
• Border
• Font
• Patterns
• Alignment
• Width/Height

By default, they will all be selected. If you'd like to omit one or more of the formatting specifics from a particular scheme, deselect that option before applying the format to your data. For instance, if you want to retain the worksheet's font, deselect the Font option before applying the AutoFormat.

AUTOFORMATS

The Excel AutoFormatting feature applies attractive formatting to a table automatically.

1. Move the cell pointer anywhere within a table that you want to format (Excel determines the table's boundaries automatically).
2. Choose the Format, AutoFormat command.
3. Select one of the AutoFormats from the list and click OK.

WHEN TO BEEP

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.

ADD BORDERS

You can add borders to enhance the appearance of your worksheet. You can also use borders to divide your worksheet into sections.

1. Select the cells you want to display borders.
2. Click the drop-down list arrow on the Borders button on the Formatting toolbar.
3. Click the type of border you want to add.

CHANGE YOUR DEFAULT BUTTON

Most dialog boxes and userforms use command buttons to close them. Sometimes you'll see a simple set of buttons with just OK and Cancel buttons. As a rule, each set of command buttons has a default button. For instance, the Save dialog box displays Yes, No, and Cancel buttons--Yes is the default button. This means that once the Yes button has the focus, you can press Enter instead of clicking the button to execute the button's click event. You can create the same effect with your own command buttons by setting the appropriate command button's default value to Yes.

All command buttons have a Default property--even customized command buttons that you create. The settings are Yes and No. Yes means the button is the default button in the set, and a setting of No means the button is not the default.

Only one button in each set can have a Yes setting. If you try to specify a Yes setting for a second button, Access will accept the Yes setting for the second button but reset the first button's Default property to No. You'll want to use the Yes setting in the button that the user will choose most often.

DISPLAYING LARGE BUTTONS

Would you like your toolbar buttons to be a bit larger? You can enlarge buttons by following these few steps. Choose Tools, Customize; click the Options tab; and select the Large Icons option in the Other section.

If you change your mind, simply deselect this option, and Excel will return to the default buttons. This option won't affect the size of your screen tips.

MOVING BUTTONS

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.

SPEAKING OF CALCULATIONS

Occasionally everyone tries an expression that returns an error. There are a number of reasons why Excel can't evaluate your expression. Here are a couple of quick checkpoints to review before you start pulling out your hair:

• Make sure you included the appropriate number of parentheses (each opening parenthesis requires a closing parenthesis and vice versa).
• Make sure you supplied all the required arguments for any functions or procedures.
• Check all your object and variable references to make sure they're correct.

ENTERING A CARRIAGE RETURN

Making a long entry when the cell's width doesn't accommodate the length may have unexpected results. If there's no data to either side of the cell in which you're entering the long string, Excel will display the entire string--Excel doesn't care how long the string is or how many cells the string obscures. When data exists to the side of the cell, Excel truncates the long string you're entering. If you choose to, you can press Alt-Enter to position a carriage return in the cell. Then, you can continue typing the string. In addition, once you enter the multi-line string, the row will automatically adjust its height property so you can view the entire string.

MULTIPLE CASE EXPRESSIONS

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!

AN ALTERNATIVE TO CELL COMMENTS

When working with formulas, you don't have to use the comment feature to make notes about your formula. You can add the note to the formula itself using the N() function in the form

N("note")

Now, let's suppose you wanted to add a comment to the simple formula

=A1 + A2

You'd simply add the N() function as you would any other cell reference in the form

=A1 + A2 + N("this simple formula adds cell A2 to cell A1")

LABELED CELL

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 access 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.

THE CELL NAME GAME

By assigning descriptive names to cells and cell ranges, you can go a long way toward keeping on top of the location of important information in a worksheet. Rather than try to associate random cell coordinates with specific information, you just have to remember a name. And, best of all, after you name a cell or cell range, you can use this name with the Go To feature.

When assigning range names to a cell or cell range, you need to follow a few guidelines:

• Range names must begin with a letter of the alphabet, not a number. For example, instead of 01Profit, use Profit01.

• Range names cannot contain spaces. Instead of a space, use the underscore (Shift+hyphen) to tie the parts of the name together. For example, instead of Profit 01, use Profit_01.

• Range names cannot correspond to cell coordinates in the worksheet. For example, you can't name a cell Q1 because this is a valid cell coordinate. Instead, use something like Q1_sales.

To name a cell or cell range in a worksheet:

1. Select the cell or cell range that you want to name.

2. Click the cell address in the Name Box on the formula bar. Excel selects the cell address in the Name Box.

3. Type the name for the selected cell or cell range in the Name Box.

4. Press Enter.

To select a named cell or range in a worksheet, click the range name on the Name Box drop-down list. To open this drop-down list, click the drop-down arrow button that appears to the right of the cell address on the Formula bar.

DISPLAY MULTIPLE LINES OF DATA IN SAME CELL

Most of the time our entries are short and sweet--either a value or a short string. Occasionally, though, you may want to display multiple lines of data in the same cell. Fortunately, there are two ways to do so: Use a line break by pressing Alt-Enter to begin a new line, or format the cell by selecting it and then choosing Format, Cells; clicking the Alignment tab, and selecting Wrap Text. The data you enter will wrap to fit the cell's width.

GOING FOR THE BIG CELL SELECTION

To select each and every cell in the worksheet, click the Select All button, which is the unmarked button in the upper-left corner of the workbook frame, formed by the intersection of the row with the column letters and the column with the row numbers.

AT THE CENTER OF IT ALL

After explaining the Merge Center feature, a reader reminded us of another way to center a title across multiple columns. First, right-click the cell or cells that contain the text you want to center. Next, choose Format Cells from the context menu. Then, click the Alignment tab and choose the Center Across Selection option from the Horizontal control. Thanks!

CALIBRATING COLUMNS

For those times when Excel 2000 doesn't automatically adjust the width of your columns to your complete satisfaction, the program makes your changing the column widths a breeze. The easiest way to adjust a column is to do a best-fit, using the AutoFit feature. With this method, Excel automatically determines how much to widen or narrow the column to fit the longest entry currently in the column.

Here's how to use AutoFit to get the best-fit for a column:

1. Position the mouse pointer on the column frame, on the right border of the column that needs adjusting. The mouse pointer changes to a double-headed arrow pointing left and right.

2. Double-click the mouse button. Excel widens or narrows the column width to suit the longest entry.

CHANGE THE COLUMN WIDTH

If a column is not wide enough to display all the data in a cell, Excel may hide part of the data. Changing the column width allows you to display the hidden data.

1. To change the width of a column, position the mouse over the right edge of the column heading.

2. Drag the column edge until the dotted line displays the column width you want.

OR

You can have Excel change the column width to fit the longest item in the column. Simply double-click the right edge of the column heading.

QUICK COMMENTS

Cell comments are a quick and easy way to make notes about the contents of a cell. Simply choose Insert, Comment and start typing. If you like keyboard shortcuts, you can skip the menu and press Shift-F2. Excel will display a comment dialog box just as if you'd used the menu commands. Once you type your note, click twice outside the Comment dialog box.

WINGDINGS IN COMMENTS

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).

EASY COUNTING EXPRESSION

Working with dates doesn't always mean complicated expressions and functions. Here's an easy trick for counting the number of days between two dates. Enter an expression in the form

="end date" - "start date"

For instance, to determine how many days there are between September 27 and Christmas, you'd enter the short expression

="12/25/2000" - "9/27/2000"

and Excel will return the value 89.

Be careful not to omit the quotations, or this shortcut won't work.

WORKING WITH CURRENCY

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.

DEBUGGING SAFETY

Complex formulas can be hard to debug, and in the process you can easily lose your original formula. To avoid this, enter your formula without the equal sign. Then, copy the formula (without the equal sign) to another cell for safekeeping. Then, return to the original cell, add the equal sign, and begin your debugging work.

At any time, you can revert to your original formula by simply copying the copied formula to the original cell and adding an equal sign.

DECIMAL FIX

Have you noticed that most bank ATMs assume a decimal point? They put it two positions from the left end of whatever amount you ask to withdraw or deposit. That adds just a tiny bit of ease to your day. Excel can do the same thing. And if you're entering thousands of monetary numbers or some other fixed-decimal values, it can add a lot of ease to your day.

• Choose Tools, Options.

• Click the Edit tab.

• Check the Fixed decimal box.

• Choose the number of Places you want for the decimal (two for money).

Click OK.

ENTERING FIXED DECIMAL VALUES

Users who enter long columns of values often like to enter those values minus the decimal point to save a keystroke. In other words, to enter the value 123.45, they enter 12345 and Excel fills in the decimal. This behavior isn't a default of Excel's--you'll have to turn it on. To do so, first choose Tools, Options. Then click Edit, select the Fixed Decimal option, and set the Places value before clicking OK.

MORE ON THE FIXED DECIMAL

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.

DEVELOPER TOOLS

Microsoft Office Developer offers a number of developer tools for all the applications, including Excel. Some of these tools are:

• The distribution license

• COM Add-In Designer

• Visual Source Safe

• Several ActiveX controls

• VBA Code Librarian HTML Help Workshop

If you've already purchased another version of Microsoft Office, you can still purchase the developer tools in Microsoft Office 2000 Developer Tools.

DOUBLE QUICK!

When you click the Underline button on the Formatting toolbar, Excel underlines the data in the selected cell or range. By default, that underline is a single line. If you need a double underline, you probably select the Cells command from the Format menu, click the Font tab, and then choose Double from the Underline control. However, that's a permanent change. Excel will continue to display a double underline until you change it back, which is probably inconvenient for most of us.

To quickly apply a double underline without changing any Excel defaults, simply hold down the Shift key as you click the Underline button.

FINDING BLANK CELLS

You can use the Go To Special command to select all the blank cells in the active worksheet. First, press F5 to display the Go To dialog box. Then, click the Special button in the bottom-left corner. In the Go To Special dialog box, select the Blanks option and click OK. Excel will highlight all the blank cells.

If you have a blank cell that Excel doesn't select, you might suspect that the cell's simply hidden--but you'd be wrong. You can't use the Go To Special command when your worksheet is protected. Check the cell carefully to make sure it's really not full of spaces. Truthfully, it'll be hard to tell, so try this: Select the cell and press the Delete key. Then, try selecting all the blank cells again. More than likely, the Go To Special command will work after you delete the cell's unseen contents.

COMBINING CELLS

Occasionally, we need to combine the contents of two or more cells. To do so, you might use the Concatenate() function in the form

=CONCATENATE(A1,B1)

You can also use the ampersand sign in the form

A1 & B1

If you want to include a literal character, such as a space, use the form

A1 & " " & B1

Good luck!

EXCLUDE HIDDEN CELLS

It's easy to summarize data, but not so easy to copy just the summarized data. For instance, you can hide detail data while displaying only subtotals and grand totals. However, when you copy those totals, you'll also copy all the hidden detail data.

There is a way to copy just the visible data. Before you copy the summary totals (or data), choose Edit, Go To; click the Special button; and select the Visible Cells Only option. Next, select the cells that contain your summary and continue with your copy routine. Excel will paste only the visible cells to the target range.

EXCLUDE HIDDEN CELLS FROM COPY

If you hide data in a row or column, be careful that you don't accidentally expose that data during a copy-and-paste task. You see, if you copy a range of cells that contains hidden cells, Excel will copy the hidden data as well as the visible data--if you use the Copy and Paste commands from the Edit menu or the Standard toolbar. When you want to copy just the visible cells, use the following method to copy your data:

First, select the range that contains your data--including the hidden cells. Choose GoTo from the Edit menu or press F5. (You can also press Ctrl-G.) Next, click the Special button at the bottom of the GoTo dialog box. Select the Visible Cells Only option, and then click OK. Copy the data as you normally would.

When you copy cells using the Visible Cells Only option, Excel ignores any hidden data when you copy the data to the Clipboard.

INSERTING CELLS

Do you know that you can insert rows or columns in Excel worksheets using only the mouse? To do so, hold down the Shift key while you grab a cell by its handle and drag it. Excel will insert as many cells as you need.

For example, enter data in cells A1..C1. Now, to insert two cells between cells A1 and B1, hold down the Shift key, grab A1's handle, drag it to cell C1, and then release. Excel will insert two blank cells between cells A1 and B1.

USING THE SELECT VISIBLE CELLS TOOL

In our previous tip, we showed you how to avoid pasting hidden data during a copy task. If you frequently copy ranges with hidden cells that you want to keep hidden, you should consider adding the Select Visible Cells tool to the Standard toolbar. This tool simplifies the process. Instead of working from the GoTo dialog box, you simply select your range of data and click the tool, and Excel will select only those cells that contain visible data. To add this tool to the Standard (or any) toolbar, right-click any toolbar and choose the Customize command. Click the Commands tab, select the Edit category, and then find the Select Visible Cells in the Commands list. Select the Select Visible Cells command in the Commands control and drag it to your toolbar. Click Close in the Customize dialog box, and you're all set.

Next time you want to copy visible data in a range that contains invisible data, select the entire range. Then, click the Select Visible Cells tool before you start your copy-and-paste task.

I CHANGED MY MIND

In our previous tip, we showed you how to move menus around. If you decide you don't like the changes you made, or you delete a menu and you can't figure out how to get it back, you don't have to reinstall Excel. To return a menu bar or toolbar to its original settings, open the Customize dialog box by choosing Tools, Customize. Select the menu bar in question and click the Reset button. Doing so will return the menu bar to its original condition.

MODIFYING A CHART ELEMENT

Most elements in a chart can be modified in several ways. For example, you can change colors, line widths, fonts, and so on. Modifications are made in the Format dialog box (which varies for each type of chart element).

To modify an element in a chart, follow these steps:

1. Select the chart element.
2. Access the Format dialog box using any of the following techniques:
   • Double-click the item.
   • Choose the Format [Item Name] command.
   • Press Ctrl+1.
   • Right-click the item and choose Format [Item Name] from the shortcut menu.
3. Click the tab that corresponds to what you want to do.
4. Make the changes.
5. Click OK.

DISPLAYING LABELS IN A CHART

Sometimes, you want your chart to display the actual data values for each point on the chart. Or you may want to display the category label for each data point.

To add data labels to a chart series, follow these steps:

1. Activate the chart by clicking on the chart sheet's tab or clicking on the chart.
2. Select the data series.
3. Choose the Format, Selected Data Series command (or double-click the data series in the chart).
4. In the Format Data Series dialog box, click the Data Labels tab.
5. Select the option that corresponds to the type of data labels that you want.

SELECTING CHART PARTS

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.

CHANGING A CHART'S DEFAULT SCALE VALUES

When you create a chart in Excel, the wizard automatically sets the data range values for you. You can easily change those default values when the wizard results are inadequate. For instance, let's suppose all your X axis values are between 50 and 100. If that's the case, scaling values between 0 and 49 isn't necessary. Here's how to control the scale values:

First, double-click the plotted area of your chart. Double-click the scale you want to modify. Choose the Scale tab in the Format Axis dialog box. Enter

50

as the Minimum value. Enter

100

as the Maximum value. Change the Major unit and Minor unit options if necessary. Then, click OK.

The modified chart's X-axis will display only values between (and inclusive of) 50 and 100.

CHECKING EXCEL

Using VBA, you can make sure a specific sheet exists in your workbook. First, open the Visual Basic Editor by pressing Alt-F11. Then, add a new module by choosing Insert, Module. Next, add the procedure listed below:

Public Function SheetExists(name As String) As Boolean

SheetExists = False

For Each sht In ThisWorkbook.Worksheets

If sht.Name = name Then

SheetExists = True

End If

Next sht

End Function

When you call the function, you'll need to pass the name of the sheet for which you're looking. If the For loop encounters that sheet, SheetExists will return a True value. If the function doesn't encounter the sheet, the function returns False. If you'd like to test it, run the following statement in the Immediate window:

?SheetExists("nameofsheet")

where "nameofsheet" equals the valid name of one of the sheets in the current workbook, and watch the function return True. If you substitute the name with one that doesn't exist in the current workbook, the function will return False.

Be careful, this function is case-sensitive. If you're looking for Sheet1 and you enter sheet1, the function will return False even if Sheet1 exists.

CIRCLE THIS!

Circling something can bring attention to it, and you can do this easily in Excel. First, display the Drawing toolbar by right-clicking any toolbar and choosing Drawing from the context menu. Then, click the Circle tool on the Drawing toolbar and drag the mouse pointer across the cell(s) that contain the data you want to encircle. The circle will most likely be solid and cover the data, so right-click the circle, choose Format AutoShape, and click the Colors And Lines tab. Next, choose No Fill from the Fill Color control and click OK.

If a red circle would be even better, repeat the above process and choose red from the Line Color control.

MULTIUPLE COPIES ON THE CLIPBOARD

Unlike with earlier versions of Office, Office 2000 programs (including Excel 2000) can store multiple cuts and copies in the Clipboard (up to the last 12 that you make). This means that you can continue to paste stuff from the Clipboard into a workbook even after finishing a move or copy operation (even when you do so by pressing the Enter key rather than using the Paste command).

To paste an item from the Clipboard into a worksheet other than the one with the data last cut or copied there, you need to open the Clipboard toolbar by following these steps:

1. Choose View, Toolbars, Clipboard on the Excel pull-down menus; or right-click the menu bar, then choose Clipboard on the shortcut menu.

2. Note that this toolbar contains XL page icons, each representing each item or group of data that has been separately cut or copied into the Clipboard.

3. To see a list of the actual data in each item in the Clipboard, position the pointer over the XL page icon until the list appears as a screentip.

4. To paste the data from a particular Clipboard item into the worksheet at the current cell's location, click its XL page icon in the Clipboard toolbar.

CONVERSION ISSUE

If you open an Excel 97 workbook in Excel 2000, you'll probably be prompted to save the file when you exit, even if you don't change a thing. That's because Excel 2000 has an improved recalculation engine that corrects many calculation bugs in Excel 97. As a result, when you open a 97 workbook in 2000, Excel completely recalculates the workbook.

We suggest you click Yes when prompted to save the file. Doing so will correct any of those old recalculation bugs.

COPY CAT

A reader suggests using the cut and paste shortcut keystroke combinations Ctrl-C and Ctrl-V to copy data into a cell's comment. First, select the text you want to copy and press Ctrl-C. Next, create a cell comment by choosing Insert, Comment. Or open an existing comment by choosing View, Comment. Then, position the cursor inside the Comment box and press Ctrl-V. Thanks for the timesaving tip!

COPY FORMATTING

Once you format one cell to suit your needs, you can make other cells look exactly the same. Follow these steps to save valuable time:

1. Click a cell displaying the formatting you want to copy.

2. Click the Paintbrush tool to copy the formatting.

3. Select the cells you want to display the formatting.

It's as easy as that!

QUICK COPY

A reader read our recent tip about using Ctrl-R and Ctrl-D to copy data, and he had a bit more to add to it. 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.

Thanks for the additional tip!

DOUBLE-CLICK'S A BUST

When you double-click a cell, Excel opens that cell in Edit mode. This may or may not be a problem for you. If you find users inadvertently double-click and destroy data in Edit mode without realizing what they're doing, it's a problem for you. Fortunately, you can inhibit this behavior. Choose Tools, Options, then click the Edit tab. Next, deselect the Edit Directly In Cell option. Then, click OK to return to your worksheet. The next time someone double-clicks a cell, Excel will just select the cell.

ALL LINED UP IN A ROW

A few tips ago we told you how to insert and frame a picture. If you've entered multiple objects and you're having trouble aligning them, use the snap-to-cell feature. Hold down the Alt key as you drag the object into place. When you let go of the object, Excel will align the object to the nearest cell.

ADDING LINE NUMBERS TO CODE

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.

REDUCE CODING TIME

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!

QUICK CONSTANTS

A number of function arguments are limited to a few intrinsic constants. Fortunately, as you're entering the function, VBA displays the possibilities for you.

Occasionally, however, you may need to know a constant's literal value. You could launch the Object Browser and find the value there, but VBA offers an easier way. Once you've added the constant to your code, simply right-click the constant and then choose Quick Info from the context menu. VBA will display the constant and its literal value.

ADDING CONTROL TIPS WHEN THE FORM LOADS

You can use a userform's Load event to add or modify a control's tip text. Depending on the circumstances, you may want to modify all of the form's controls or just one. To do so, simply add a statement in the form

Me!controlname.ControlTipText = "new control tip text"

to the form's Load event. For instance, if you want a control named cboNames to display as its control tip "Choose a name," you'd use the following statement:

Me!cboNames.ControlTipText = "Choose a name"

COLOR CONSTANTS

Referring to colors in your code can be a bit of a nuisance because you must remember the color's corresponding integer. If you're working with the Windows standard colors, your work is made easier by a few intrinsic constants. Instead of looking up a color integer, you can simply use one of these constants:

Black vbBlack

White vbWhite

Red vbRed

Yellow vbYellow

Blue vbBlue

Green vbGreen

Cyan vbCyan

Working with these constants should prove much easier and more efficient than using the color's integer values.

CHANGING COLORS

The following is the fastest way to change the color of text:

1. Select the cell or range.

2. Select a color from the Font Color tool on the Formatting toolbar.

If you click the down arrow button on the Font Color tool, it expands to show more colors.

ADJUSTING CONTROLS

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.

CONVERTING DRAG-AND-DROP DATA

If you hold down the right mouse button while dragging cell data from one area to another, Excel will display a few conversion options when you drop the data. That's because Excel will display a shortcut menu offering several copy and paste possibilities, including these format options:

• Copy Here As Formats Only

• Copy Here As Values Only

As the names imply, the first will copy only the formats of the cells you've just dragged, while the latter will copy only the data.

To access this menu, select the range you want to copy as you normally would. Then, grab the range by holding down the right mouse button instead of the left mouse button. Then, drag and drop the cells to see the shortcut menu.

COPYING DATA AS A PICTURE

In some situations, you may want to copy a cell or range as a picture. Doing so creates a graphic object that's an exact duplicate of the copied range.

To copy data as a picture, follow these steps:

1. Select the cell or range.

2. Click the copy button on the standard toolbar (you can also press Ctrl+C or choose the Edit, Copy command).

3. Activate the cell or range where you want to paste the picture.

4. Press the Shift key and choose the Edit, Paste (Alt, P) command.

This procedure pastes a picture of the original cell or range. If you want the picture to be linked to the cell, press the Shift key and choose the Edit, Paste Picture Link (Alt, N) command in Step 4. With a linked picture, any changes you make to the source range also appear in the picture.

You can manipulate the image qualities of the picture object using the Excel Picture toolbar (which appears automatically when you select a picture). For example, you can convert a color image to black and white.

Remember: The Edit, Paste Picture (Alt, P) and the Edit, Paste Picture Link (Alt, N) commands are available only if you press Shift while you click the Edit menu.

DATA ENTRY RELIEF

If you need to enter the same formula or value in many cells, don't type the data entry over and over. Instead, select the range (contiguous or noncontiguous) into which you're making the same entry. Then, type the formula or value into the active cell, but don't press Enter. Instead, when you're ready to enter the contents of the active cell, hold down the Ctrl key while you press Enter. Excel will copy the contents of the active cell to every cell in the range.

CHANGING THE LOOK OF YOUR DATA

You can make data in your worksheet look more attractive by using various fonts, styles, sizes, underlines, colors, and special effects. Follow these steps and let your creative juices flow:

1. Select the cells containing the data you want to change.

2. Click Format, Cells and then click on the Font tab.

3. To select a font for the data, click on the font you want to use. Look in the preview areas to see what it looks like.

4. To select a style for the data, click the style you want to use.

5. To select a size for the data, click the size you want to use.

MORE ON DATA ENTRY RELIEF

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?

HIDING DATA

Last month we talked a bit about protecting and hiding data. However, using a sheet's Protection feature to hide data is overkill if you just want to hide a cell or two. For a quick hiding method, right-click the cell that contains data you don't want anyone else to see. Choose Format Cells from the context menu and select Custom from the Category control. Then, type three semicolons, as in

;;;

in the Type control, and click OK. Excel will hide the data in the newly formatted cell or range. However, you'll still be able to see the contents of the cell in the Formula bar.

To unhide the data, simply apply a different format.

HIDING CHARTED DATA

Typically, you want Excel to display data when you chart it, but not always. A good example of just such a situation is data that's charted by the month. You probably don't want to re-plot your chart as you add data for each month, so you probably plot the entire worksheet (from January to December) when you create the chart. However, doing so displays axis points for all the months, not just those for which you have data.

If you'd rather your chart not display points for months that have no data, you can hide the empty worksheet columns until you're ready to update that month. Excel won't chart the hidden columns. For example, if you have data only for the months of January through June, you can hide the worksheet columns from July through December. Then, when you run the chart, Excel will seem to plot only January through June. When you add July's data, you simply unhide the column, and Excel will add the new data to your chart--month by month.

To hide a column, simply select the column, right-click the selection, and then choose Hide from the context menu. Unhiding the column is just as simple--choose Unhide from the context menu.

DATA MARKERS

Excel's standard data markers are, well, tame. If your chart doesn't need to be so sedate, you can liven it up by replacing those markers with clip art, drawings, or other pictures.

• Copy or cut the picture or image you want to use.

• Click the data marker you want to replace.

• Click the toolbar's Paste button.

MORE ON REPLACING DATA

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.

RETAINING DATA FORMATS

You can easily cut or copy data from an Access table to an Excel spreadsheet. Simply open the table in Datasheet view, select the data (or entire table), and press Ctrl-X or Ctrl-C. Then, open the Excel spreadsheet and press Ctrl-V to paste the copied data.

When you cut or copy data from an Access table and paste it into an Excel spreadsheet, Excel will retain any formatting you assigned to the data in Access. You can keep the formats or coordinate the new data with the rest of your worksheet.

HIDING DATA

Our last two tips have dealt with excluding hidden data during the copy-and-paste task. If you don't know how to hide data, these two tips are worthless. Fortunately, hiding a cell is a simple process. Just select the row or column that contains the data you want to hide and choose Row or Column from the Format menu. Next, select Hide from the resulting submenu.

Of course, at some point, you may want to unhide the data. To do so, you should select the adjacent rows or columns (so you're also selecting the hidden row or column). Then, select Row or Column from the Format menu, and choose Unhide from the resulting submenu.

ROTATE DATA IN A CELL

You can rotate data within cells in your worksheet. This is useful for emphasizing row and column labels.

1. Select the cells containing the data you want to rotate.

2. Click on Format, Cells.

3. In the Format Cells dialog box, click on the Alignment tab.

4. In the Orientation area, position the mouse over the line and drag the line to a new angle.

5. Click OK to confirm your change.

UNDERSTANDING DATE ARITHMETIC

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.

SPECIAL DATE FUNCTIONS

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.

AN EASY JULIAN DATE

Most of us work with Julian dates as the day of the year, beginning with January 1 of the current year. (Technically, the anchor date for the Julian calendar is fixed.) A Julian calendar (for just the current year) would denote January 1 as 1, obviously. However, February 1 is 32, and March 1 is 61. Fortunately, it's easy to calculate a Julian-type date. Just use the simple VBA expression

strDate = Format(dte, "y")

where dte represents the date you're converting. If you're converting the current date, replace the dte argument with the Date function.

If you'd like to test this expression, press Alt-F11 to open the Visual Basic Editor, and enter a statement similar to

?Format(#7-13-2000#,"y")

in the Immediate window. VBA will return 195, which means July 13, 2000, is 195 days into the year 2000.

Neat, huh?

A QUICK DATE

When you make an entry that resembles a date, Excel automatically assumes the entry is a date and formats the entry accordingly. The only thing that would make date entry easier is if Excel read your mind and entered the date for you. Well, it almost does--at least it seems that way when you want to enter the current date. Instead of entering the actual date, next time you need to enter the current date, press Ctrl-; and Excel will conveniently enter the current date for you. Now if Excel could only enter dates in that little black book of yours . . .

TODAY'S DATE

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.

ONE DAY IS THE SAME AS ANOTHER

By now, you probably know you can use the fill handle to create a series of days. Simply enter the first day or abbreviation (Mon, Tues, and so on). Then, select that cell's fill handle and select the target cells. Excel will respond by entering the appropriate days or abbreviations to create a days-of-the-week series.

But what if you don't want to create a series? Suppose you want to copy that particular day of the week instead of creating a series. Don't worry--the solution is simple. Hold down the Ctrl key while you drag the fill handle across the target cells.

WHAT DAY IS IT

Need to know the date and don't have a calendar? Anytime you want to enter the current date, simply press Ctrl-;. Excel will respond by entering the current date--at least the system's current date. You still may want to format the cell to display the date in a particular manner.

To see the current time, press Ctrl-Shift-;.

WHAT DAY IS IT

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.

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.

SPEEDY DELIVERY

New to Excel 2000 is the capability to send the current worksheet to e-mail recipients either as the body of a new e-mail message or as an attachment. This feature makes it easy to send financial figures, lists, and charts to coworkers and client.

If you want to share only the data with your e-mail recipient, send the worksheet as the body of the e-mail message. Just be aware that when you do this, the only place where you can add your own text is in the Subject area of the e-mail message.

To send a worksheet as the body of a new e-mail message, click the E-mail button on the Standard toolbar. Next, if necessary, click the button "Send the current sheet as the message body." By default, Excel enters the name of the current workbook in the e-mail message's Subject: field. If you want, edit the Subject field to something more descriptive.

To send a worksheet as an e-mail attachment, choose the File, Send To, Mail Recipient (as Attachment) command on the menu bar.

CUSTOMIZING YOUR DICTIONARY

Do you have a strange name or a technical term that you use a lot but the Spelling feature doesn't recognize? It gets really annoying after a while, especially if you have a number of these unique words. All Office 2000 applications share a dictionary, and you can customize that file by adding jargon, names, or even technical and business terms. But how do you get your unique words into the dictionary? The next time you run the Spelling feature and it stops on an occurrence of one of these unidentified words, simply click Add in the Spelling dialog box. After you add the word, the Spelling feature won't identify the word as misspelled again.

DISPLAYING THE MONTH

If you need to return the month component from a date, you have two choices. You can use the Month() function in the form

Month(date)

which will return a month's integer value. For instance, if date is April 21, 2000, the Month() function will return the value 4.

If you need to see the month's name, you can use a custom format. Simply select the cell that contains your date, and then choose Format, Cells. Next, choose Custom and enter the format code mmmm. When you return to the worksheet, Excel will display just the month's name.

LOCK UP THOSE DOCS

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.

FORMATTING ERROR MESSAGES

Error messages can be hard to see if you're not specifically looking for them. If you don't find them, you could end up with a lot of incorrect data. You can suppress error messages with formulas, but you might find conditional formatting a bit easier. Let's look at a simple example. First, select any cell and enter a formula that you know will return an error message--such as =A1/0. Now, select the cell that contains the formula and choose Format, Conditional Formatting. In the Conditional Formatting dialog box, make the following selections:

Condition 1 Formula Is

Edit box =IsError(ADDRESS)

where ADDRESS is the cell address to which you're applying the conditional formatting. Next, click the Format button, then apply a conditional formatting. For instance, you might choose Red from the Color drop-down list. Or you might click the Border tab and apply a border to the cell. Excel will display that format when the formula displays an error message.

GENERIC ERROR MESSAGE

No matter how careful you are, errors do creep into your application. When this happens, you'll want to know which error has occurred. Fortunately, you can add the following message box statement to your error-handling code to display the current error number and its description:

MsgBox Err.Description & vbCrLf & Err.Number

You can drop this into almost any error-handling routine.

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.

CLICK EVENT ORDER

When you click on most controls, you produce a Click event. Right? Well, let's see. When you click a text box, the control fires its Click event--that much is true. However, if you click inside the text box component of a combo box, you don't trigger that control's Click event. So what's the difference? The combo and list box controls don't fire their Click event until the user actually selects an item from the control's list. This behavior is considerably different from other controls because they trigger their Click event as soon as the control receives the focus. So consider the outcome carefully when you attach code to a combo or list box's Click event.

PLAYING FAVORITES

Are you tired of looking for a file all the time? Now you can save it in your Favorites folder. To add a folder (or a particular file) to the Favorites folder, just follow these steps:

1. Choose File, Open; then find the folder or file you want put in Favorites in the Open dialog box.
2. Select Tools, Add to Favorites in the Open dialog box.

After you add a folder or a file to the Favorites folder, you can open it in the Open dialog box by clicking the Favorites button on the left side of the Open dialog box and then either double-clicking its folder or file icon or selecting it and then clicking the open button or pressing Enter.

FILE HIDE AND SEEK

The Open dialog box now (Excel 2000) has a Find feature built into it that you can always use to locate a particular file within the open folder. This feature enables you to reduce your search in the Open dialog list box to just those files that fall into a specific category (like those files you modified today or sometime this week) or just those files that contain a certain phrase, word, or property (like those from a particular author).

To open the Find dialog box, where you specify the criteria upon which you want the search conducted, choose Tools, Find in the Open dialog box.

SAVING YOUR EXCEL 2000 FILE TO A DISK

To save a copy of your Excel 2000 workbook on disk the first time around, choose File, Save or File, Save As from the menu bar (or click the Save button on the Standard toolbar or press Ctrl+S); then designate the drive and folder directory where the file should be located in the Save in drop-down list box. Replace the temporary BOOK1.XLS filename in the File name edit box with your own filename (up to 255 characters long, including spaces), and then click the Save button. To save changes to the workbook thereafter, click the Save tool on the Standard toolbar (or choose File, Save or press Ctrl+S or Shift+F12).

FINDING FILES BY THEIR PROPERTIES

You don't have to leave Excel to run a sophisticated search for a file. If you've entered properties for your files--who created them, when the files were created, and other keywords about them--you can search on these elements. To do so, first choose File, Open. In the Open dialog box, select the Find command from the Tools drop-down list. In the Find dialog box, click the Property drop-down menu and choose a property, such as Keyword. In the Value text box, enter the keyword you're searching for. Then, on the Look In line, specify the drive (or folder) to search. Finally, click the Find Now button.

SAVING FILES AUTOMATICALLY

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.

MULTIPLE FILL

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.

SPECIAL FILL FEATURE

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.

FIRST THING EVERY MORNING!

Would you like Excel to launch automatically when you turn on your system? Well, more specifically, we can show you how to launch Excel when you launch Windows. If you use Excel every day, you'll be saving yourself a little time and effort. To add Excel to your startup options, click Start (in the Taskbar), choose Programs, right-click Microsoft Excel, and select Copy from the context menu. Next, right-click Startup and choose Paste. Now, the next time you turn on your PC and launch Windows, Windows will automatically launch Excel.

AVAILABLE FONT SIZES

The Font Size tool on the Formatting toolbar lists sizes 8 to 72 (in most host applications), but you're not limited to just those sizes. If you want a smaller or larger font, you may be able to use the VBA FontSize property. As a rule, you can specify a font size of 1 to 127 using VBA. However, just because VBA will allow you to specify a specific font size doesn't mean your printer can print it. When working with unusual font sizes, you should test the point size you choose to make sure your printer and the current font can accommodate that size.

USING FORMAT PAINTER

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.

CLEAR FORMATTING

You can remove all the formatting from cells in your worksheet. Follow these steps:

1. Select the cells containing the formatting you want to remove.
2. Click on Edit, Clear, Formats.

RICH FORMATTING

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.

ABOUT THOSE FORMATS

A format determines how Excel displays (or prints) your data. You may think of Excel as a smorgasbord when it comes to formatting options, but push yourself away from that table before you make a mess! Professionals agree--you should save formats for special emphasis. In other words, apply formats sparingly or your users may lose their appetites. In fact, too many formats in the pot can create a really nasty-tasting soup. In other words, if you use too many formats, you may end up with a really ugly spreadsheet.

ALTERNATE FORMATS

You can save an Excel 2000 workbook to another format using the File, Save As command. Simply review the Save As Type control's drop-down list for a complete list of alternate formats. However, when you change formats, you may lose data and formatting that are unique to Excel 2000. When this happens, Excel will warn you, but it won't identify the specific data or formatting that will be lost.

This isn't a limitation on Excel's part. In fact, Excel is doing its best to convert your data to the chosen format. If you're unsure about just what you'll lose to other formats, read "Formatting and features not transferred in file conversions" in the Help section.

CHANGING DEFAULT FORMATS

Most of the tools on the Formatting toolbar offer a shortcut to specific default styles. Specifically, the Currency, Percent, and Comma buttons assign the corresponding named styles when you click them. To check this out, first select Format, Style. Then, open the Style name control's drop-down list and choose an existing style to see its characteristics.

This means you can change the style of these three buttons. For instance, the default Percent style displays no decimal places. You can change the way the Percent button displays a value by changing the Percent style. To do so, choose Format, Style, and select Percent from the Style name control. Next, click the Modify button, click the Number tab, and enter a new value in the Decimal Places control.

You can click any of the tabs to change a number of formatting options.

PASTING FORMATS

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.

QUICK FORMATS

A reader sent in a great formatting tip. 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. Thanks!

AN EASY WAY TO ENTER FORMULAS

If you need assistance entering or editing formulas, you can use the Formula Palette. To access the Formula Palette, click the Edit Formula button (=) in the formula bar.

• If the active cell is empty, you can enter a formula into the cell.
• If the active cell already contains a formula, you can use the Formula Palette to edit the formula.

The Formula Palette displays the result of the formula as it is being entered. If you need to use a function in your formula, select it from the function list (which occupies the space normally used by the Name box on the left side of the formula bar).

HARD CODING FORMULAS

When you create a formula, think twice before using a literal value in the formula. For example, if your formula calculates 7.5 percent sales tax, you may be tempted to enter a formula such as:

+A1*.075.

A better approach is to insert the sales tax rate into a cell and use the cell reference in place of the literal value. This makes it easier to modify and maintain your worksheet. For example if the sales tax range changes to 7.75 percent, you need to modify every formula that uses the old value. If the tax rate is stored in a cell, you simply change one cell and all the formulas automatically get updated.

YOU WANT A FRAME WITH THAT

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.

ENTERING A FUNCTION TIP

When you enter a function, Excel always converts it to uppercase. It's a good idea to use lowercase when entering functions: If Excel doesn't convert it to uppercase, it means that it doesn't recognize your entry as a function (which means that you probably spelled it incorrectly)

EASY ACCESS TO FUNCTIONS

If you use lots of functions in Excel, you might like to know there's a simple and efficient way to enter functions without accessing the Insert menu. Try using the keyboard shortcut--Shift-F3--which opens the Paste Function dialog box. From here you select the function category and then specify the actual function. Excel will display the function's arguments and hints about the function. Using the Paste Function dialog box, you'll avoid typos and argument errors.

ENTERING FUNCTIONS USING THE FORMULA PALETTE

The Formula Palette makes it easy to enter a function and its arguments. Using this tool ensures that the function is spelled correctly and has the proper number of arguments in the correct order.

To enter a function using the Formula Palette, activate the cell that will contain the function and then use either of these two methods:

• Select the Insert, Function command (or click the Paste Function button) and select the function from the Paste Function dialog box.
• Click the Edit Formula icon (=) on the edit line and then select a function from the function list in the Name box. If the function does not appear on the list, select the More Functions option and choose the function from the Paste Function dialog box.

Excel displays the Formula Palette directly below the edit line (you can drag it to a new location if it's in your way). The Formula Palette prompts you for each argument of the function you selected. You can enter the arguments manually or (if they are cell references) point to them in the worksheet. The Formula Palette displays the result. When you've specified all of the required arguments, click OK.

• You can use the Formula Palette to insert a function into an existing formula. Click the Edit Formula button (=) to bring up the Formula Palette. Then move the cursor to the location where you want to insert the function and choose the function from the function list.
• You can use the Formula Palette to edit a function in an existing formula. Click the Edit Formula button (=) to bring up the Formula Palette. Then click the function in the formula. Use the Formula Palette to adjust the function's arguments.

ENTERING FUNCTIONS MANUALLY

If you're familiar with the function that you want to use, you may choose to simply type the function and its arguments into your formula. Often this is the most efficient method.

Remember: If you're using a function at the beginning of a formula, you must provide an initial equal sign (=).

SUM() FUNCTION

The Sum() function will return the total of a range of values. Did you know you can use this function to return a running total for a range of values? For this tip, we'll need to create a simple worksheet, so open a blank worksheet and enter the values 100, 200, and 300 in cells A1..A3. Next, enter the formula

=SUM($A$1:A1)

in cell B1, which will return the value 100. Then, copy the formula to cells B2..B3. An easy way to copy the formula is to select cell B1, then pull down the fill handle to include cells B2 and B3 in the selection. The formula will return the values 300 and 600 in cells B2 and B3.

The $A$1 reference will remain absolute in each formula. However, the A1 reference will update accordingly for each successive row. The two points will create a range that includes cell A1 and all the cells between and including the current cell's corresponding cell in column A.

USING A GRAPHIC FILE

A wide assortment of graphic files (sometimes known as electronic clip art) is available, and you can use these images in your work without violating any copyright restrictions. Excel can import most graphics files that can be placed on a worksheet's draw layer.

To add a graphic file to a worksheet, you can use either of the following options:

• Choose the Insert, Picture, From File command to locate a particular file stored on disk.
• Choose the Insert, Picture, Clip Art command to use the Microsoft Gallery of clip art.

After an image is placed on a worksheet, you can move and resize it. You can also use the tools on the Picture toolbar to manipulate the image. The Picture toolbar appears when you select a picture.

Remember: Depending on the type of image, resizing may or may not produce good results.

Using graphics in a worksheet can dramatically increase the size of your workbook, resulting in more memory usage and longer load and save times.

HOLD THOSE GRAPHICS

Graphics take more computer processing time than letters, numbers, and lines. That means it can take time for Excel to redraw a special graphic when you're scrolling around your sheet. In fact, if the graphic is complicated, that graphic can significantly slow things down. When this is the case, tell Excel not to display the graphic(s). The graphic will still be there; you just won't see it. Consequently, the graphic won't slow things down.

To turn off a graphic's display, first choose Tools, Options. In the Options dialog box, click the View tab. Choose one of the Objects options:

• Show Placeholders--For improved speed, while still seeing shaded rectangles that show where the graphics will be.
• Hide All--For the best speed, without even the shaded rectangles.

Finally, click OK.

The Print command will also ignore graphics, so be sure to turn on the graphics before printing if you want the graphic included in your printout.

PRINTING GRID LINES

Most of us display the gridlines in our spreadsheets. Oddly enough, Excel doesn't print those gridlines by default. If you want to print gridlines, you'll need to turn on that feature. To do so, choose File, Page Setup. Click the Sheet tab, then select the Gridlines option in the Print section. Click OK.

If the gridlines are printing and you don't want them printed, simply turn them off by deselecting the Gridlines option in the Print section.

Happy printing.

FROM HEADER TO FOOTER

Headers and footers are simply standard text that appears on every page of the report. The header prints in the top margin of the page, and the footer prints -- you guessed it -- in the bottom margin. Both are centered vertically in the margins.

Unless you specify otherwise, Excel automatically adds a header that shows the name of the worksheet you are printing (as shown on its sheet tab) and a footer that shows the current page number.

If you don't want a header or footer printed in your report, you simply open the Header/Footer tab in the Page Setup dialog box and then select the (none) option at the very top of the Header and Footer drop-down list box.

MORE ON HEADERS AND FOOTERS

Two tips ago, we gave you a macro that prints the workbook's name in the file's footer. You might be wondering if there's a way to print the filename in one of the other footers or even a header. For instance, what if you want to print the name in the center of the footer or in the right portion of the header?

Fortunately, the solution is simple. First, let's review the original macro:

Sub NameInFooter()

ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName

End sub

Now, to change the location of the file's name, simply replace the LeftFooter property with one of the following properties:

• CenterFooter
• RightFooter
• LeftHeader
• CenterHeader
• RightHeader

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!

CHANGE THE ROW HEIGHT

You can change the height of rows to increase the space between the rows of data in your worksheet.

1. Position the mouse over the bottom edge of the row heading.
2. Drag the row edge until the dotted line displays the row height you want.

OR

You can have Excel change the row height to fit the tallest item in the row. Simply double-click the bottom edge of the row heading.

REPEATING HEADINGS IN LARGER SPREADSHEETS

As your worksheet grows, so does your printout. If your data requires more than one page, you'll probably want to include certain data--column headings for example--on each page. However, in your worksheet, these headings appear only once, at the top of your worksheet. Fortunately, there's an easy way to display a row or column of data on each page. Technically, these repeated ranges are called titles.

To arrange repetitive data for each page in your printout, first choose Page Setup from the File menu. Then, click the Sheet tab. In the Print Titles section, indicate the row and/or column that you want repeated on each page. Click OK to return to your worksheet. At this point, you can click Print to print the worksheet or try Print Preview to see how the new titles will look.

HELP IS JUST A KEYSTROKE AWAY

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.

QUICK HELP ON THE WEB

When you need help, you usually need it quickly. If you can't find adequate information in the Help files, try searching the Web. Fortunately, that's not as difficult as it may sound. Simply choose Office On The Web from the Help menu to display a number of online sources. For instance, after selecting the Online Support option from the Web options, you're just a click away from a number of technical support pages. (Of course, you must have Internet access for this tip to be of benefit.

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 access 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.

MORE ON PASTE SPECIAL

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.

ADDING A HYPERLINK

To create the text in a cell to which you attach a hyperlink, follow these steps:

1. Select the cell in the worksheet of the workbook that is to contain the hyperlink.
2. Enter the text for the hyperlink in the cell; then click the Enter button on the formula bar.
3. Select the cell with the text or click the graphic to be linked.
4. Choose Insert, Hyperlink on the menu bar or click the Insert Hyperlink button (the one with the picture of a piece of chain link in front of the globe) on the Standard toolbar.
5. To have the hyperlink open another document, a Web page on the company's intranet, or a Web site on the Internet, click the Existing File or Web Page button if it isn't already selected, then enter the file's directory path or Web page's URL in the "Type the file or Web page name" text box.
6. Click OK.

HYPERLINK SHORTCUT

Hyperlinks are popping up all over--and it's no wonder. They perform a unique and useful task, and they're easy to create. You can choose Insert, Hyperlink, or you can insert a label, enter text, and press Ctrl-K. Pressing Ctrl-K will display the Insert Hyperlink dialog box. There, you can identify the link. The method is especially convenient when you want the hyperlink to display text other than the link's address.

HYPERLINKS IN EXCEL

It's easy to enter a hyperlink--Excel will recognize a valid URL and automatically format the entry as a hyperlink. If you don't want the entry to be a hyperlink, type an apostrophe (') before the address. Then, Excel will interpret the URL as an ordinary string.

If the URL entry is already formatted as a hyperlink, right-click the cell, select Hyperlink from the shortcut menu, and then select Remove Hyperlink.

WHEN A HYPERLINK IS NOT A HYPERLINK

If your entry resembles a hyperlink, Excel will automatically format it as a hyperlink. If you don't want the entry to be a hyperlink, you can right-click the cell, choose Hyperlink, and then select Remove Hyperlink from the subsequent shortcut menu.

Isn't there a simpler way? Why, of course there is. Once you've pressed Enter to commit the entry, press Ctrl-Z to undo the hyperlink format. Just remember you must use the Ctrl-Z shortcut immediately, as this shortcut works on the most recent activity.

INTENT ON INDENTS

In Excel 2000, you can indent the entries in a cell selection by clicking the Increase Indent button on the Formatting toolbar. Each time you click this button, Excel indents the entries in the current cell selection to the right by one character width of the standard font. You can remove an indent by clicking the Decrease Indent button on the Formatting toolbar.

THE DEAL ON INDENTING IN EXCEL 2000

In Excel 2000, you can indent the entries in a cell selection by clicking the Increase Indent button on the Formatting toolbar (the button normally immediately to the left of the Borders button with the picture of the arrow pushing the lines of text to the right). Each time you click this button, Excel indents the entries in the current cell selection to the right by one character-width of the standard font.

You can remove an indent by clicking the Decrease Indent button on the Formatting toolbar (the button normally immediately to the left of the Increase Indent button with the picture of the arrow pushing the lines of text to the left). Also, you can change how many characters an entry is indented by using the Increase Indent button, or outdented with the Decrease Indent button: Open the Format Cells dialog box (Ctrl+1), select the Alignment tab, and then alter the value in the [I]ndent text box (by typing a new value in this text box or by dialing up a new value with its spinner buttons).

INSERTING EVERY OTHER ROW

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.

A KEYBOARD SHORTCUT FOR CLOSING FORMS

There are a couple of ways to close a userform. You can click the Close button in the upper-right corner of the form's title bar. Or you may choose to include a close or exit command button on your form, which you simply click to close the form.

There's also a keyboard shortcut that will close a userform; simply press Alt-F4. How 'bout that?

FORMULA HELP

Even experts need a hint now again, especially when you're trying to decipher a complex formula with several sets of nested expressions--each one requiring a complete set of parentheses. Fortunately, Excel can guide you through the formula. Simply select the cell that contains the formula you're reviewing and click inside the Formula bar. Then, use the arrow keys to move through the formula. When the insertion point moves over a parenthesis, Excel will highlight its mate. In addition, when you edit the formula, Excel displays each matched set in a different color, so you can easily distinguish between the sets.

WHY USE FUNCTIONS IN YOUR FORMULAS?

Excel provides more than 300 built-in functions that can make your formulas perform powerful feats and save you a great deal of time.

Functions do the following:

• Simplify your formulas
• Allow formulas to perform calculations that are otherwise impossible
• Allow "conditional" execution of formulas -- giving them some rudimentary decision-making capability

You can see a list of the available functions by clicking on the Edit Formula icon (=) and then on the Functions drop-down list in the formula bar.

Most, but not all, worksheet functions use one or more arguments, enclosed in parentheses. Think of an argument as a piece of information that clarifies what you want the function to do. For example, the following function (which rounds the number in cell A1 to two decimal places) uses two arguments:

=ROUND(A1,2)

THE FRIEND KEYWORD

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 access 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.

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.

USING LINKS TO RECOVER DATA

Sooner or later (with luck, later), it's bound to happen. You attempted to open an Excel workbook, and you get an error telling you that Excel can't access the file. Most of the time, this indicates that the file (somehow) got corrupted. If you're lucky, you have a recent backup. If you're very lucky, you haven't made any changes to the file since you backed it up. But assume that you fell a bit behind on your backup procedures, and the dead file is the only version you have.

Although, no method of fully recovering a corrupted file is known, here's a method that sometimes enables you to recover at least some of the data from worksheets in the file (values, not formulas). Your actual success depends on how badly the file is corrupted.

This technique involves creating an external reference formula that refers to the corrupt file. You need to know the names of the worksheets that you want to recover. For example, assume that you have a workbook named Summary Data that you can't open. Further, assume that this workbook is stored on the C drive in a folder named Sheets. This workbook has one sheet, named Sheet1. Here's how to attempt to recover the data from this worksheet:

1. Open a new workbook.
2. In cell A1, enter the following external reference formula:

   ='C:\Sheets\[Summary Data]Sheet1'!A1

   If you're lucky, this formula returns the value in cell A1 of Sheet1 in the corrupt file.

3. Copy down this formula and to the right to recover as many values as you can.
4. Convert the external reference formulas in the new workbook to values and then save the workbook.

If the corrupt file has additional worksheets, repeat these steps for any other worksheets in the workbook (you need to know the exact sheet names).

ADDING AN ITEM TO A LIST BOX

To fill a list or combo box, you can use the AddItem method. Did you know you could add an item to a specific position within the list? You can if you include the method's index value in the form

ListBox1.AddItem "One", 0

where 0 represents the first position in the list. For instance, the following procedure will display the items "Two" and "Three" in a list box named ListBox1.

Private Sub UserForm_Activate()

ListBox1.AddItem "Two"

ListBox1.AddItem "Three"

End Sub

This second procedure will add the item "One" to the beginning of that same list when you click the form.

Private Sub UserForm_Click()

ListBox1.AddItem "One", 0

End Sub

(Just remember that the index values begin with 0 and not 1.)

IMPORTING A CUSTOM LIST

You can create your own AutoFill list series by choosing Tools, Options; clicking the Custom Lists tab; and entering the items in your list. When you're done, you click the Add button.

If the list already exists in another sheet, don't retype it. Instead, choose Tools, Options, then click the Custom Lists tab. Next, click Import and identify the cells that contain the list. That's all there is to it!

EXTEND THAT LIST

If you want, you can have Excel list more or fewer files at the bottom of the File menu. To change the number of files listed at the bottom of the File menu, follow these simple steps:

1. Choose Tools, Options on the menu bar to open the Options dialog box.
2. Click the General tab in the Options dialog box.
3. Click the spinner buttons associated with the text box called "entries" to increase or decrease the number, or type a number into this text box.
4. Click OK to close the Options dialog box.

SORTING A LIST

Sorting a list involves rearranging the rows such that they are in ascending or descending order, based on the values in one or more columns. For example, you might want to sort a list of salespeople alphabetically by last name or by sales region. The fastest way to sort a list is to use the Sort Ascending or Sort Descending buttons on the Standard toolbar:

1. Move the cell pointer to the column upon which you want to base the sort.
2. Click the Sort Ascending button or the Sort Descending button. Excel sorts the list by the current column.

You may need to sort a list by more than one column. For example, you might want to sort by state, by city within the state, and by zip code within the city. To sort a list on multiple columns, use the procedure above for each column that you want to sort. Always start with the "least important" column (for example, zip code) and end with the "most important" column (for example, state).

ADDING ITEMS TO LISTS

The combo and list box controls allow you to enter a list of items. When you choose this route, you enter in the control's Row Source property a setting in the form of

"item1";"item2";"item3"

You must also specify the Value List setting for the Row Source Type property. The result is a list of items--one right after the other--in the same order as they appear in the Row Source property setting.

You might not realize that you can display more than one column of items in a Value List control. To do so, you simply add a second item to the list in the form

"item1a";"item1b";"item2a";"item2b";"item3a";"item3b"

Excel will display items 1a and 1b in the first row, items 2a and 2b in the second row, and items 3a and 3b in the third row. Just be sure to update the Column Count property to reflect the appropriate number of columns.

ADDING BLANK LINES TO LISTS

In our previous tip, we showed you how to add a second (or more) column of items to a list or combo box list. You can use the same method to display blank lines in a control. Specifically, insert a zero-length string between each item in the list in the form

"item1";"";"item2";"";"item3";""

In addition, be sure to select the Value List setting from the Row Source Type property. The Column Count property should be set to 1.

RECORDING A MACRO

If you find yourself doing the same thing over and over again, record a macro. The process of recording a macro is surprisingly straightforward:

1. Open the Record New Macro dialog box to turn on the macro recorder by choosing Tools, Macro, Record New Macro.
2. Name the macro using basically the same naming conventions as when naming a range of cells in Excel (that is, start the name with a letter and use underscore characters instead of spaces) and (optionally) assign it a keystroke.
3. Perform the sequence of actions you want recorded in the macro just as you normally do in Excel. You can choose commands from the pull-down or shortcut menus, click tools in the toolbars, or use shortcut keystrokes.
4. Turn off the macro recorder when you finish by clicking the Stop Recording button that appears in the tiny Stop Recording toolbar as soon as you start recording.
5. Perform the task anytime you want simply by double-clicking the macro name in the Macro dialog box (opened by choosing Tools, Macro, Macros or pressing Alt+F8) or by selecting the macro name and then choosing the Run button. Alternately, if you bothered to assign a keystroke shortcut to the macro when you recorded it, you can run the macro by pressing the keystroke shortcut.

You'll be happy to know that, when recording your actions, Excel doesn't record mistakes (don't you wish everyone else was so forgiving?). In other words, if you're entering the heading "January" in the macro and you type Janaury by mistake and then correct your error, Excel does not record your pressing of the Backspace key to delete the characters "aury" and then typing uary. The only thing the macro records is the final, corrected result of January!

CUSTOM MENUS

Excel's menu commands may suit the masses, but if the commands aren't where you want them, just move them. First, choose Tools, Customize. Click the Toolbars tab and select Worksheet Menu Bar. At this point, you can select any menu and move it just by using the drag-and-drop method. You can also move the menu's subheadings the same way. Just open the menu and move things around to suit you.

We do have one word of warning: When you change the menus and toolbars a lot, you make it much more difficult to use Excel on another system, unless that copy of Excel is customized the same way as yours. Additionally, anyone who tries to use your copy of Excel may be a bit lost.

THE NAME BOX CONTROL

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

QUICK MACROS

In our previous tip, we shared a macro that will print the file's name in the workbook's footer. To run the macro, we told you to select Tools, Macro, select Macros from the resulting submenu, highlight the macro by name, and click Run. This process takes several steps--but fear not, there's a shorter way.

Instead of selecting Macro from the Tools menu to run a macro, press Alt-F8. Doing so will open the Macro dialog box, where you can highlight the macro by name and then click Run. Using this shortcut, you'll eliminate several unnecessary clicks.

TAKE THAT, YOU MACRO!

Do you have any workbooks that process macros when you open them? Although these macros can be productive, they can also be annoying if you don't need to run the macro at that particular time, especially if you're in a hurry. If you want to open the file without running a macro, simply hold down the Shift key when you open the file. Be sure to hold the Shift key down until the workbook is completely open. Holding down the Shift key will tell Excel to skip those opening macros so you can get straight to work.

PERSONAL MACRO WORKBOOK

Note: This tip applies to both Excel 97 and Excel 2000 for Windows.

To record a macro so that it's universally available for use anytime, anywhere in Excel 2000, you must record the macro in your Personal Macro Workbook rather than in the current workbook. Because your Personal Macro Workbook automatically opens whenever you start up Excel (although it remains hidden at all times), you can run any of its macros no matter what workbook or workbooks are open at the time.

To record a macro in the Personal Macro Workbook, follow these steps:

1. Choose Tools, Macro, Record New Macro to open the Record Macro dialog box.
2. Choose the Personal Macro Workbook in the "Store macro in" pop-up menu in the Record Macro dialog box.
3. Name your macro and (optionally) assign it a keystroke shortcut before you click OK and start recording the new macro.

MAPS AREN'T JUST FOR SISSIES

Microsoft Map is a program that helps you draw maps, and this program comes with Excel. Using this program, you can create maps of the following: the United States, Canadian Provinces, European countries, the United Kingdom, Australian States, Mexican States, and various countries of the world.

You'll know Microsoft Map is installed if you can click the Map toolbar button (the globe on the right side of the toolbar), drag across a range of cells, and see the Map options appear.

IDENTIFYING MARGINS IN PRINT PREVIEW

You probably use Print Preview to view your data in printed form before you actually print it. While you're in Print Preview, you can also see where your margins are set in relation to your data. Simply click the Margins button while in Print Preview, and Excel will display dotted lines to indicate all four margins.

You'll also see an additional line above both the top margin and below the bottom margin. The spaces in-between these two dotted lines are the header and the footer areas, respectively.

If you return to your worksheet without clicking the Margins button a second time, Excel will display the dotted lines in the normal worksheet window. These may be helpful to you as you're entering data. If you find them annoying, simply return to Print Preview, click the Margins button, and then click Close to return to your worksheet.

NEED A PREVIEW

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.

SAVING YOUR SPOT IN A MODULE

When working with code in the VB Editor, you can switch to another module without losing your spot in the active module--and you don't have to do a thing to mark your place. The next time you're working in a module, pay close attention to the position of the cursor before you close the module. After switching to another module and then back to (or even closing) the module, you'll find the cursor in the same spot you left it when you return to it. That way, you can go right back to the code you were working on if necessary. Pretty convenient, huh?

MOVING AFTER ENTER

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.

ABOUT N()

In our previous tip, we showed you how to use the N() function to add a note to a formula. However, the N() function converts numeric text values to numeric values. This obscure function's main purpose isn't as an annotator for your formulas. If the N() function's results are nonnumeric, the function returns 0. For the most part, you can add 0 to any expression and not alter the results. If the function's argument is a True value, it will return the value 1. If the argument is a date, the function will return a serial value. All other values will return 0.

QUICKLY ASSIGNING A NAME

Naming ranges is a powerful feature. To name a range, you select the range and choose Insert, Name. Or you can take a shortcut by using the Name control on the Formula bar. Select the range you want to name and click the Name control (all the way to the left on the Formula bar) to highlight the current entry. Type in the Name control the name you want to give the range of cells you just selected.

This process of assigning a name to a range is much quicker and easier than going through the menus.

CHANGING THE NUMBER FORMAT

Excel offers many different formats that you can use to make the numbers in your worksheet easier to read. Follow these steps:

1. Select the cells containing the numbers you want to change.
2. Click on Format, Cells, and then on the Number tab.
3. Click the category that describes the numbers in the cells selected.
4. To select the number of decimal places you want the numbers to display, double-click this area. Then type the number of decimal places.
5. To select the way you want negative numbers to appear, click one of the available styles.
6. Click OK to apply your changes.

CREATING NUMBER FORMAT CODES

Note: This tip applies to both Excel 97 and Excel 2000 for Windows.

You can create your own custom format codes by doing a little creative editing. Although you do have to use these junky number format codes (ugh) when performing this editing, you really don't have to be a rocket scientist to figure out how to do it.

To build a custom hidden number format, choose the Format Cells dialog box (Ctrl+1), click Custom in the Category list box in the Number tab of the Format Cells dialog box, and then select General in the Type list box and replace this text with your custom code.

Note that the custom number formats that you create are added to the bottom of the Type list box. This means that when you next open the Format Cells dialog box and select Custom in the Category list box of the Number tab, you may have to scroll through the Type list box all the way to the bottom before you can locate and select the number format codes that you're responsible for adding.

DECODING NUMBER FORMAT CODES

Note: This tip applies to both Excel 97 and Excel 2000 for Windows.

If you just have to know what some of those Excel number format codes mean, here goes. Each number format can control how positive numbers, negative numbers, and everything else looks. These parts are divided by semicolons (any format not so divided covers all the other types of entries). The 0 is a placeholder for a digit and is filled by a zero if the value lacks a digit in that place. The # sign is a placeholder for a digit not to be filled if the value lacks a digit for that place. Ms are used for months in dates or minutes in time, Ds for days, Ys for years, Hs for hours, and Ss for seconds.

HIDE VALUES WITH A NULL FORMAT

In a previous tip, we talked about hiding the contents of cells by using the Hidden format (part of the Protection feature). Unfortunately, many people know about this feature and can even turn it off if you don't assign a password when you turn it on.

There's another option--you can assign a null number format to the cell. To do so, select the appropriate cell(s). Then, choose Format, Cells. Select Custom from the Category control, and then enter three semicolons (;;;) in the Type control. Finally, click OK.

Unfortunately, this format doesn't hide the value in the Formula bar, so if a person's close enough to be turning off your Protection feature, he or she can surely read the contents of the Formula bar. However, if the Formula bar's not displayed, this approach may be an option. Additionally, someone has to locate the cell that contains the hidden data first, whereas turning off the Protection feature automatically displays all the previously hidden data.

We don't recommend one method over the other--they're just different and both have pros and cons.

WATCH OUT FOR NULLS

In general, you'll want to avoid using or referring to a Null value in your expressions. That's because a Null in any mathematical expression causes the entire expression to evaluate to Null. For instance, the simple expression

2 + Null

will return Null, whereas the expression

0 + 2

will return 2.

This behavior is true whether you're working with a simple expression or VBA code.

USING OBJECT VARIABLES

In our previous tip, we recommended you use the dot identifier sparingly because it slows down your code. Today, we'll discuss an option for reducing the number of dot identifiers in references. When you find yourself repeating the same object reference, use an object variable instead. For instance, we can optimize the statement we used in our previous tip example,

Workbooks(1).Sheets(1).Range("A1").Value = "abc"

by setting the Workbook reference as follows:

Set sheet = Workbooks(1).Sheets(1)

sheet.Range("A1").Value = "abc"

We've reduced each call by one dot, which doesn't seem like a big deal, but every dot helps. Additionally, you won't need the Set statement again.

EDITING THE OFFICE 2000 DICTIONARY

In our previous tip, we told you how easy it is to add a word to the Office 2000 dictionary. You can edit the dictionary directly, and doing so is almost as easy as clicking Add in the Spelling dialog box.

Begin by launching Word and choosing Tools, Options. Click the Spelling And Grammar tab and choose Dictionaries from the Custom dictionary list. Click Edit, then enter each new word or term on its own line.

Good luck!

MINIMIZING OLE REFERENCES

VBA methods and properties use the OLE IDispatch interfacetime. (Don't worry if you don't know --and that takes what the OLE IDispatch interface is.) Consequently, minimizing the number of methods or properties can speed things up. Here's the general rule of thumb: Every time you use the dot identifier (.), VBA hits the OLE IDispatch interface. For example, the following statement contains three dots:

Workbooks(1).Sheets(1).Range("A1").Value = "abc"

This statement will call on the interface three times, which of course takes more processing time than a statement with only one dot identifier.

Although it isn't always practical, keeping the dot identifiers to a minimum is one good way to speed up your code.

INSERTING A PAGE BREAK

It's easy to insert a page break in a spreadsheet. You simply pull down the Insert menu and choose Page Break. However, getting that page break in just the right spot can sometimes be a challenge. When inserting a page break, be sure to select the cell that represents the top-left corner of the following (or new) page. Excel will position the page break at the top-left cell of the new page, not the bottom-left cell of the previous page.

PARENTHESES ARE A NEGATIVE

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.

PICTURE PERFECT

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.

INTERACTIVE PIVOT TABLES

Excel for Windows 95 introduced a nifty little data summary table called a pivot table. Pivot tables in Excel worksheets are famous for their ability to change the way the summary data is displayed on the fly. Windows 2000 carries this level of interactivity over to pivot tables that you publish in a Web page.

To create a Web page with an interactive pivot table, just select the pivot table prior to opening the Save As dialog box with the File, Save as Web Page (Alt, F, G) command (making sure that you don't forget to choose the Add Interactivity check box prior to saving or publishing the page).

If you need more information on how to create pivot tables, just ask the Office Assistant how to do it.

THE PIVOTTABLE TOOLBAR

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.

IT'S PLAYBACK TIME

After you turn off the macro recorder, you are ready to test the macro. If your macro enters new text or deletes some text as part of its actions, be careful where you test it out. Be sure that the cell pointer is in a part of the worksheet where existing cell entries won't be trashed when you play back the macro. When you're ready to test the macro, you can do any of the following:

• The easiest way to play back a macro is to press the shortcut keystrokes, assuming that you assigned a shortcut to your macro.
• You can choose Tools, Macro, Macros on the menu bar or press Alt+F8 and then double-click the macro's name in the Macro dialog box or select the name and click the Run button (this method works for any macro whether or not it has shortcut keys assigned).

If, when you play back the macro, you find it running amok in your worksheet, press Esc to stop the macro prematurely. Excel displays a macro error dialog box indicating the point at which the macro was interrupted. Click the Halt button in this dialog box to shut the macro down.

IF YOU WANT IT, JUST POINT IT OUT

The method of selecting the cells you use in a formula, rather than typing their cell references, is known as pointing. Pointing is not only quicker than typing cell references, it also reduces the risk that you might type the wrong cell reference. When you type a cell reference, you can easily type the wrong column letter or row number and not realize your mistake just by looking at the calculated result returned in the cell.

If you select the cell you want to use in a formula, either by clicking it or moving the cell pointer to it, there is less chance that you'll enter the wrong cell reference.

GREAT POINTERS

You've probably noticed that the new versions of Microsoft applications make use of a solid triangle as a pointer. You can use these yourself since they're included in the Marlett font. Just specify one of the values 3 through 6 and apply the Marlett font to the value. The following identifies the pointer direction with a value:

3 Left pointer

4 Right pointer

5 Up pointer

6 Down pointer

Now, here's how to use them to add quality pointers to your controls.

First, add a label control to your form (object). Referring to the list above, enter the appropriate value as the label's Caption property. Then, specify

Marlett

as the label's Font property. The Marlett font will display one of the four pointers, depending on the value you entered, instead of the value.

ONE MORE ON PRINT AREAS

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?

PRINT AREA WARNING

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.

PRINT BUTTON PROBLEM

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.

QUICK COLUMN WIDTH CHANGE IN PRINT PREVIEW

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.

AN EASY PRINT TASK

Many people like to place a shortcut to their printer on their Desktop. That way, they can drag a worksheet from the Windows Explorer to the shortcut and print the workbook without opening Excel or the workbook first. There's one catch, though. Remember a few days ago when we talked about the Ignore Other Applications option? Well, if you have this option selected, Windows won't automatically print a workbook using the above method. Your solution is to reset the option or to open Excel and the workbook before printing.

MORE ON PRINTING

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.

PRETTY PRINTING TECHNIQUE

You can use the "Center on page" options in the Margins tab of the Page Setup dialog box to center a selection of data (that takes up less than a full page) between the left and right margin. Select the Vertically checkbox to center the data between the top and bottom margins.

ABOUT PROTECTION

There are a number of ways to protect your data in Excel. One way is to lock or hide a cell using the Locked or Hidden cell properties, respectively. Once you lock a cell, no one can change the contents of that cell, nor see the contents in the formula bar. To lock or hide a cell, choose Format, Cells, click the Protection tab, and then select the appropriate option. By default, all cells are locked, but this property doesn't go into action until you turn it on. To do so, you must select Tools, Protection, Protect Worksheet. Most of the time, you won't want all your cells locked, though, so you may need to unlock cells.

Here's the catch--it's best to plan ahead. When possible, unlock and hide cells before you turn on the worksheet protection feature. Once you turn on the protection feature, you can't lock, unlock, hide, or unhide a cell. If you change your mind about a cell's state, you must unprotect the worksheet by selecting Tools, Protection, Unprotect Worksheet. Next, select Format, Cells, click the Protection tab, and choose the appropriate option. After all that work, you have to turn on the protection again.

MORE ON PROTECTION

In our previous tip, we told you how to protect your data from being modified--lock your cells. You can also hide formulas. If you don't want others to see a particular formula, you can hide the formula cell. The users will still see the results of the formula, just not the formula itself. If you don't want anyone to change the formula, you should lock and hide the cell. To hide a cell (while the protection feature is turned off), choose Format, Cells, click the Protection tab, and then select the Hidden option.

PUTTING ON THE PROTECTION

After you've more or less finalized a worksheet, you often want to guard against any unplanned changes by protecting the document. Each cell in the worksheet can be locked or unlocked. By default, Excel locks all the cells in a worksheet so that, when you follow these steps, Excel locks the whole thing up tighter than a drum.

1. Choose Tools, Protection, Protect Sheet on the menu bar.
2. If you want to assign a password that must be supplied before you can remove the protection from the worksheet, type the password in the Password (optional) edit box.
3. Click OK or press Enter.

CLEARING A RANGE

You probably know all about the fill handle--you can use it to repeat a string or a series across a range of cells. Did you realize you could also use the fill handle to clear a range? Here's how to clear a range using the fill handle.

First, select the range you wish to clear. Next, grab the fill handle--the small black square at the bottom-right corner of the selected range--and pull it back over the cells you want to clear. When you release the mouse, the cells will be cleared of any color and data. Poof, gone, outta there, history.

NONCONTIGUOUS RANGE

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.

NONCONTIGUOUS RANGES

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.

RANGE SHORTCUT

Whether you're still using the old format of entering two periods between the two coordinates of a range or entering the colon, you still must make two keystrokes. Personally, I find the Shift-; combination more tedious than the double period. Fear not--there's a shorter way. You can type just one period and Excel will convert it for you. If you'd like to try it out, select a blank cell and enter

=Sum(A1.A3)

(Notice that we've used only one period character between the two rangepoints A1 and A3.) When you press Enter, Excel will recognize the single period and convert it to a colon character.

NEED THE RESULTS WITHOUT THE HASSLE

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.

A LITTLE TO THE RIGHT

We all know how to copy the contents of one cell to another, right? I'll bet you don't know about this copy shortcut. You can copy the contents of one cell to the cell that's directly to the right of that cell by selecting the cell to the right and pressing Ctrl-R. Did you get that? For example, to copy the contents of cell A1, you'd select cell B1 and press Ctrl-R.

Similarly, you can copy a cell's contents to the cell below by selecting the cell below and pressing Ctrl-D. To copy the contents of cell A1, you'd select cell A2 and press Ctrl-D.

ROUND TRIP

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.

ROUNDING VERSUS TRUNCATING

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.

FREEZE ROWS AND COLUMNS

You can freeze rows and columns in your worksheet so they don't move. This allows you to keep row and column labels displayed on your screen as you move through a large worksheet.

1. Click on the cell(s) you want to freeze.
2. Click on Windows, Freeze Pane.

Lines appear in your worksheet. The rows above and columns beside the lines are frozen and remain on your screen as you move through your worksheet.

To unfreeze rows and columns, click on Window, Unfreeze Panes.

INSERTING ROWS AND COLUMNS

If you're still using the Insert menu to insert rows and columns, there's a quicker way. First, right-click the header cell for the column or row to the right or just below the column or row you want to insert, respectively. (The header cell is the gray cell at each column and to the left of each row.) Excel will display a context menu. Select Insert to insert a column or row. Congrats--you just saved yourself a couple of keystrokes.

This tip was submitted by one of our readers and unfortunately, I deleted the message and I can't properly credit the tip. If you're that reader, identify yourself, and we'll be sure to credit you as soon as possible.

RAMBLING ROWS

To increase the height of a row, drag the bottom border of the row frame down until the row is high enough and then release the mouse button. To shorten a row, reverse this process and drag the bottom row-frame border up. To use AutoFit to create a best-fit for the entries in a row, you double-click the bottom row frame border.

SORTING WITH HIDDEN ROWS AND COLUMNS

It's easy to hide data. Simply select the row or column, choose Row or Column from the Format menu, and then select Hide from the submenu. However, you should know that Excel 2000 won't sort data in hidden rows or columns. That means if you sort a range of data that comprises a smaller hidden range of data and you eventually redisplay that hidden data, your data will no longer be sorted correctly. Specifically, the data that wasn't hidden will remain sorted but the hidden data will still be in its original order.

QUICKLY UNHIDING ROWS AND COLUMNS

A few tips ago, we were talking about hiding and unhiding rows and columns. Once you've hidden a row or column, you'll probably need to unhide it at some point. To do so, you simply select the adjacent rows or cells, choose Format, Row or Column, and then select Unhide. But, there's a quicker way to unhide a row or column. First, select the adjacent rows or columns as you would with the menu method. Then, instead of pulling down the Format menu, simply double-click the border between the two adjacent rows or columns.

For instance, if you've hidden row 3, you'd select rows 2 and 4 and click the heading cell's border--the one between rows 2 and 4. Doing so will automatically unhide row 3. When working with columns, you'd double-click the border of the heading cell between the two adjacent columns.

HIDDEN RESOURCE

One of Excel's best-kept secrets is WordArt--an Office graphics accessory. WordArt will help you twist and turn pieces of data into a colorful and eye-catching display. If you aren't familiar with WordArt, it's about time you two became acquainted.

First, position your cursor and choose Insert, Picture, then select WordArt. Once in the WordArt gallery, select one of the layouts. You can alter your choice later. For now, give priority to the color and shading. Once you've made your selection, click OK. Next, type the text you want to display graphically.

It's easy to modify the results. To resize the graphic, simply drag the sizing handles. If you want to maintain the graphics' proportions, hold down the Ctrl key while you're resizing. Perhaps the most common use for WordArt is to insert vertical text. Once you have your text graphic in your worksheet, select the ABC icon on the WordArt toolbar to vertically transpose the text. Rotating the text is just as easy. Click the Free Rotate icon on the WordArt toolbar. WordArt will replace the normal sizing handles with green dots at the four corners of your graphic. Grab one of those green dots and drag the graphic's corner.

SEARCH AND REPLACE

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.

QUICKLY SELECTING AN ENTIRE COLUMN

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).

QUICKLY SELECTING AN ENTIRE ROW

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).

EASY SELECTION METHODS

There are a ton of shortcut methods for selecting whatever configuration of cells you could possibly need. Here are a few more:

• Select a single cell and then press Ctrl-Spacebar to select the entire column.
• Select a single cell and then press Shift-Spacebar to select the entire row.
• Select an entire row or column and then press Ctrl-Spacebar to select the entire worksheet.

TRACKING THE SCROLL BAR'S EXACT LOCATION

Scrolling through the rows and columns of a worksheet can be a hit-or-miss process. There's no way to know exactly where the scroll bar is in relation to all the rows and columns. You can guess, but that's not very efficient. If you'd like a little hint while you're scrolling, you can hold down the Shift key as you drag the thumb. Doing so will display the row or column location.

EASY SCROLLING

Beginning this week, Excel tips will now specify the version of Excel that the tip is referring to, as a service to our readers. For instance, Excel 2000 will be represented by (2K), Excel 97 will be (97) and in the future Excel 2002 will be (02). If no version is given you can assume that the tip will work with all versions of Excel!

You can use the scroll box in either the horizontal or vertical scroll bars to make big jumps to other cells in larger worksheets. Simply drag the scroll box in the appropriate direction along the bar. As you drag, scroll tips appear. Scroll tips are boxes that tell you where you are scrolling to in the document. If you are scrolling along the horizontal scroll bar, the letter of the column that would appear in the leftmost column in the worksheet is displayed. If you are scrolling along the vertical scroll bar, the number of the row that would appear at the top of the screen is displayed. Use the scroll tips to gauge when to release the mouse button to stop scrolling.

THE SET STATEMENT

When you declare an object variable in your VBA code, you must also use the Set statement to define that variable. Failing to do so will produce an error when you compile that code. Since you don't have to use Set with all variables, it can be easy to forget about Set when working with object variables. One way to help you remember is to use the obj prefix when you name all your object variables. The prefix will help you remember to use the Set statement when you define the variable.

If you do forget the Set statement, VBA will return a rather unhelpful error message: Invalid use of property. Anytime you see this property and there's an equal sign in the offending line of code, you're probably dealing with a forgotten Set statement.

DANCING IN THE SHEETS

You can forget about the darned tab scrolling buttons and sheet tabs and just go back and forth through the sheets in a workbook with the keyboard. To move to the next worksheet in a workbook, press Ctrl+PgDn. To move to the previous worksheet in a workbook, press Ctrl+PgUp.

The nice thing about Ctrl+PgDn and Ctrl+PgUp is that these keystroke shortcuts work whether or not the next or previous sheet tab is currently displayed in the workbook window!

DISPLAYING MULTIPLE SHEETS

You can split a sheet to view different areas of the same sheet by moving the Split bar. But how do you display more than one sheet at the same time? Click the sheet tab to activate the first sheet. Choose Window, New Window (repeat the first two steps for each sheet you want to view). Select Window, Arrange, Tiled, and select the Windows Of Active Workbook option. Finally, click OK. Excel will display a shot of each of the selected sheets. Neat, eh?

HOW MANY SHEETS

Complex workbooks usually contain several worksheets, and keeping up with all those sheets can be a pain. If you'd like to see a quick list of the sheets in the current workbook, simply open the file's property dialog box. To do so, choose File, Properties. Then, click the Contents tab, and Excel will display a complete list of sheets in the current workbook.

QUICKLY INSERTING A NEW SHEET

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.

PREVENT YOUR SHEET FROM SCROLLING

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.

ALWAYS IN SIGHT

As your worksheet grows, there comes a point where you can't see the whole thing on screen, and you lose sight of your column and row headings. This setup makes it difficult to know whether you're in the right column or row. Fortunately, the answer is simple--freeze your headings.

Select a cell just to the right of the row or just below the column that contains your headings. Choose Window, Freeze Panes. Now the rows to the left and the columns above the cell(s) you selected before freezing your headings will always remain on screen--no matter how far down or to the right you scroll. You can turn this feature off just as quickly by selecting Window, Unfreeze Panes. Neat, huh?

SORT AND RE-SORT

Often, you want to keep your records in their original order but perform a temporary sort just to see how it looks. The solution is to add an additional column to the list with sequential numbers in it (don't use formulas to generate these numbers, but you can use the Fill command.) Then, after you sort, you can return to the original order by resorting on the field that contains the sequential numbers. You can also use Excel's undo feature to return the list to its original order. If you use an additional column, you can perform other operations while the list is temporarily sorted (and these operations won't be undone when you undo the sort operation).

SORTING STRATEGY

Sorting is done either numerically or alphabetically, depending on the data. In some cases, you may want to sort your data in other ways. If your data consists of month names, you probably want them to appear in month order rather than alphabetically. Excel, by default, has four custom lists, and you can define your own. To sort by a custom list, click the Options button in the Sort dialog box; then select the list from the First key sort order drop-down list.

Excel's custom lists are as follows:

• Abbreviated days: Sun, Mon, Tue, Wed, Thu, Fri, Sat
• Days: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
• Abbreviated months: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
• Months: January, February, March, April, May, June, July, August, September, October, November, December

To create a new custom list, follow these steps:

1. Choose the Tools, Options command.
2. In the Options dialog box, click the Custom Lists tab.
3. Click the NEW LIST option.
4. Enter your list in the List entries box.
5. Click Add and then click OK to close the Options dialog box.

SUCCESSFUL SPREADSHEETS

You create a spreadsheet to accomplish some end result, which could be any of thousands of things. If the spreadsheet is successful, it meets most or all of the following criteria (some of which are appropriate only if the spreadsheet is used by others):

• It lets the end user perform a task that he probably wouldn't be able to do otherwise.
• It's the appropriate solution to the problem.
• It accomplishes its goal.
• It produces accurate results.
• It doesn't let the user accidentally (or intentionally) delete or modify important components.
• It doesn't let the user enter inappropriate data.
• It's laid out clearly so the end user always knows how to proceed.
• Its formulas and macros are well documented so that, if necessary, they can be changed.
• It is designed so that it can be modified in simple ways without making major changes.

SHOW OR HIDE THE STATUS BAR

Ever wonder what that little gray bar at the bottom of your Outlook screen is? It's called the status bar, and it shows helpful information such as the total number of items and the number of unread items in the selected folder. If you use it, great, but if you don't, it's easy to get rid of it and give yourself a tiny bit more precious screen space.

Click View, Status Bar to deselect it. To bring it back, repeat the same steps to select it.

WHERE O' WHERE CAN THAT STATUS BAR BE

The status bar lies just below your worksheet and above the Windows taskbar. While you're working, Excel uses the status bar to keep you informed. For instance, when nothing's going on, the status bar displays the simple message "Ready." That means Excel is ready and waiting for you to go to work. Well, are you? Huh, huh, are you? If Excel is waiting for you to do something so it can continue a process, you may see the message "Enter." If your current task takes a little time, the status bar may display an explanation of the task and a progress meter.

You say you don't have a status bar? Don't worry. Sometimes people turn off the status bar because they find it distracting or because it uses screen space. To turn a status bar back on, select Tools, Options. Click the View tab, then select Status Bar. Click OK to return to your worksheet.

At this point, you should have a clear view of your status bar.

A DYNAMIC SUM

The Sum() function will total the values in a defined range, and the only way to adjust the range is to change one of the function's arguments. However, there is a workaround using the Choose() function. Here's a quick example:

First, enter the values 1 through 5 in cells A1:A5. Then, enter the formula

=Sum(A1:Choose(B1,A1,A2,A3,A4,A5))

in cell B2.

Now, enter any value between 1 and 5 in cell B1, and the formula in cell B2 will return the sum of the values in cell A1 and the cell that contains the value you entered in cell B1. For instance, if you enter the value 2 in cell B1, the formula in B2 will return the value 3 (1+2). If you enter the value 4, the formula will return 10 (1+2+3+4), and so on.

IMPORT TEMPLATE

If you import a lot of text files in the same format in Excel 2000, you can avoid repeating the import procedure for each file by creating a "refreshable" text import template. Follow these steps to create the template:

1. Choose the Data, Get External Data, Import Text File (Alt, D, D, T) command. This command displays the Import Text File dialog box.
2. The drop-down list box labeled Files of type displays text files that have a TXT extension. If the text file that you are importing doesn't have a TXT extension, select the All Files option.
3. Select the file and click Import (Alt, M).
4. Click Finish when you have completed the steps in the Text Import Wizard. The Import Data dialog box now appears.
5. In the Import Data dialog box, select where you want to put the data and click OK to complete the import.

SAVING TIME WITH TEMPLATES

If you spend a lot of time applying the same formatting styles, you should consider working from a custom template. Then, instead of starting with Excel's default workbook, each new workbook will already contain any formatting that's unique to your needs. Creating a template is a four-step process.

First, open a blank workbook and enter any formatting or content that you want to include in every new workbook. Save the workbook as a template by selecting File, Save As. Then, choose Template (*.xlt) from the Save As Type list. Excel will change the default directory to the Office Templates folder. You don't want to save your template here. Instead, save the workbook in the XLStart directory. Usually, this folder is at C:\Program Files\Microsoft Office\Office\XlStart. Once you've located the directory in the Save In control, enter the name

book

(book.xlt) and click Save.

After you've completed the above process, Excel will open the workbook you formatted as the default workbook.

If a template named book.xlt already exists, you might want to rename it instead of replacing it. That way, you can still use that workbook as a template later.

IMPORTING STRAIGHT TEXT

Most of the time the data we import into Excel is delimited or at a fixed width, which means we can usually just copy and paste the data right into Excel. If the originating format isn't acceptable, Excel will paste all the data into one column instead of separating the text into columns for us. When this happens, don't pull your hair out--just use the Text To Columns feature. Select the text (only one column at a time), then choose Data, Text To Columns. Excel will launch the Convert Text To Columns Wizard. Simply specify the required data, and the wizard will separate the data into columns for you.

Good luck!

INDENT TEXT IN CELLS

In Excel 2000, you can indent the entries in a cell selection by clicking the Increase Indent button on the Formatting toolbar (the button normally immediately to the left of the Borders button with the picture of the arrow pushing the lines of text to the right). Each time you click this button, Excel indents the entries in the current cell selection to the right by one character width of the standard font.

LINK TEXT BOX TO CELL

Here's a great tip from a reader:

"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."

Thanks--we couldn't have said it better ourselves!

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, as he points out. If you try to add it to the text box, Excel will treat your entry as regular text.

ADDING TIME

You can add time values, but summing the values isn't enough. For instance, let's suppose cells A1 and A2 contain time values, and you enter the formula

=A1+A2

in cell A3. Chances are, the formula won't display the value you expect if the sum of the time values exceeds 24 hours. When this happens, try formatting cells A1 and A2 using a custom format defined as [hh]:mm. The brackets tell Excel to allow hours greater than 24 for summing purposes.

Thanks to a reader for this tip.

WHAT TIME IS IT

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.

TIMELY DECIMALS

Time flies like a decimal inside Excel. Time values are stored as decimal values -- fractions of a 24-hour day. 12 a.m. is 0, 6 a.m. is 0.25, and noon is 0.5. All of the moments in between are similar fractions. Of course, you rarely see this notation, as Excel displays the time using a standard time format such as 2:00:00AM. But it helps to know what's behind the scenes so you better understand time calculations.

WHAT'S TODAY

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.

IS THAT MY TOOLBAR

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.

MUSICAL TOOLBARS IN 2000

Don't get too used to the button arrangement that you experience when first using the Standard or Formatting toolbars in Excel 2000. Excel uses a new intelli-(non)-sense feature to automatically promote the button you last used to a higher position on the toolbar. This means, for instance, that if you use a button on the More Buttons palette, the button is immediately added to the regularly displayed portion of the toolbar. This is accomplished by banishing one of the unused buttons that appears near the end of the toolbar onto the More Buttons palette. The result is musical toolbars wherein you can never be sure where a needed button will appear (or not appear).

Unfortunately, Excel 2000 doesn't give you an option for fixing the position of the buttons on the toolbars so that you can always tell where a needed button appears. You can, however, restore the original arrangement of the buttons on the toolbars (as well as the commands on the menus) by right-clicking somewhere on the menu bar or the bar with the Standard and Formatting toolbars and then selecting the Customize command on the shortcut menu that appears. Doing this opens the Customize dialog box where you select the Options tab. Click the button called "Reset my usage data" on the Options tab. Excel then displays an alert box indicating that the record of the commands you've selected in Excel is about to be deleted. Select the Yes button and the program will restore the buttons on your toolbars (and the commands on your menus) to their previous order.

RESETTING TOOLBARS

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.

NEW TOOLBARS IN THE VBE

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.)

FASTER UPDATE

You can use a combo box to update other form controls. For instance, if you choose an employee by name from a combo box, you might then use a lookup function to return that employee's address and phone number. But did you know there's a quicker way? If all the data you want to display is stored in the same row, try using the Column property in the form

CTRL.Column(X)

where CTRL is a reference to the combo box and X identifies the position of the corresponding field in the combo box. Just remember to add all the necessary fields to the combo box when you're creating it. In addition, X is 0 based, so the first field in the combo box equals 0, the second equals 1, and so on.

COPYING VALIDATION RULES

You probably know that you can apply special validation rules to cells. These rules limit the data that cell will accept. If you later copy or move the data, Excel will also copy the validation rules. However, what if you just want to copy the validation rules and not the data? To do so, select the cell or range that contains the validation rule you want to copy and press Ctrl-C. Then, select the target cell and select Edit, Paste Special (or right-click the target cell). In the resulting dialog box, select the Validation option and click OK.

YOU'RE BLOCKING MY VIEW

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.

VIEWING FORMULAS INSTEAD OF THE RESULTS

One way to audit your workbook is to display the formulas rather than the results of the formulas. Then you can examine the formulas without having to scroll through the worksheet. To do this, follow these steps:

1. Select the Tools, Options command.
2. Click the View tab of the Options dialog box.
3. Check the box labeled Formulas under Window options.

You may want to create a new window for the workbook before issuing this command. That way, you can see the formulas in one window and the results in the other.

QUICK VIEW

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.

YOUR BASIC VISUAL BASIC

In Excel 5 and Excel 95, a Visual Basic for Applications (VBA) module appeared as a separate sheet in a workbook. Beginning with Excel 97, VBA modules no longer show up as sheets in a workbook. Rather, you use the Visual Basic Editor (VBE) to view and work with VBA modules.

VBA modules are still stored with workbook files; they just aren't visible unless you activate the VBE.

The VBE is a separate application that works seamlessly with Excel. Seamlessly, meaning that Excel takes care of the details of opening the VBE when you need it. You can't run VBE separately; Excel 2000 must be running in order for the VBE to run.

When you're working in Excel 2000, you can use any of the following techniques to switch to the VBE:

• Press Alt+F11.
• Select Tools, Macro, Visual Basic Editor.
• Click the Visual Basic Editor button, which is located on the Visual Basic toolbar.

Don't confuse the Visual Basic Editor with the Microsoft Script Editor. These are two entirely different animals. The Script Editor is used to edit HTML scripts written in VBScript or JavaScript.

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!

OPEN WEB PAGES

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.

WEB WORK

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.

USING THE WITH STATEMENT

Our last two tips have been about reducing the number of dot identifiers in references. One more way you can eliminate references is to use the With statement. The example in the previous tips could be rewritten as follows using the With statement. This example calls the Workbooks method and the Sheets method only once each:

With Workbooks(1).Sheets(1)

.Range("A1").Value = "abc"

.Range("A2").Value = "def"

End With

Each statement in the non-With alternative

Workbooks(1).Sheets(1).Range("A1").Value = "abc"

Workbooks(1).Sheets(1).Range("A2").Value = "def"

calls both the Workbooks and the Sheets. This simple example cuts the calls in half.

DRAG-AND-DROP TABLES

Note: This tip applies only to Excel 2000 for Windows.

Excel 2000 has a nifty new feature that enables you to drag data from tables on a Web page that's displayed in your browser directly into the cells of a worksheet open in the Excel program. The exciting thing about this drag-and-drop feature is that not only is the data from the Web table copied into the correct columns and row but data entry formatting (including hyperlinks) are also correctly copied.

DRAG-AND-DROP TABLES FROM THE WEB

A feature of Excel 2000 is that you can drag and drop Web page tables from the browser into Excel, which is great for all of you drag-and-drop aficionados. All you have to do is select the table data in the Web browser window, then drag it over and drop it off at the desired place in the Excel worksheet window.

WRAPPING TEXT

Wrapping text within a cell is a good way to display more information without making the column wider. This is useful for lengthy table headings. To format a cell or range so that the words wrap around, follow these steps:

1. Select the cell or range you want to apply word wrap formatting to.
2. Choose Format, Cells (or press Ctrl+1).
3. Click the Alignment tab or the Format Cells dialog box.
4. Check the box labeled Wrap text.
5. Click OK to apply the formatting to the selection.

IMPLICITLY TRUE

When writing an If statement, you can take advantage of VBA's implicit form of True to reduce your code just a bit. If so, you should be aware of the differences between the implicit and explicit forms of True and False. When expressed explicitly in the following form:

If var = True Then

var must evaluate to -1 for the condition to return True.

In contrast, the statement

If var Then

is True much more often than the previous statement because the implicit form of True includes any nonzero value. Consequently, only 0 evaluates to False. Keep this behavior in mind when depending on the implicit True value--the results might not be what you expected.

CAN'T UNDO

You're probably aware of Excel's Undo button. In fact, we've written a few tips about this feature, which allows you to undo many tasks. Unfortunately, you can't undo a task once you've saved the workbook. Once you execute a save, Excel erases the Undo list and disables the button until you complete a task that can be undone.

UNDO WHAT

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.

A TIP FOR THE VBA BEGINNER

Almost everybody indents their VBA code a bit, and there are a number of guidelines you can use to know just when to add an indent or tab. If you don't indent as you're actually entering the code in a module, you can do so later. In fact, there are two different ways to indent existing code. First, you can select the code you want to indent and choose Edit, Indent. The second way is a bit simpler. Highlight the code you want to indent and press Tab.

If you get carried away and add a few too many indentations, you can outdent your code as easily as you indented it. After selecting the code you want to outdent, you have one of three options: You can choose Edit, Outdent; you can press Shift-Tab; or you can close your file, take off work the rest of the day, hit the park, and enjoy the great outdoors! But we wouldn't recommend the third choice unless you're the boss or in the position to do so without repercussions.

If you aren't able to do the recreational thing and you'd like to experiment a bit, open the Visual Basic Editor by pressing Alt-F11. Then, insert a blank module by choosing Insert, Module. Next, type the two statements below--be sure to enter the apostrophe characters at the beginning of each line:

'We'll indent this line twice

'We'll then outdent this line just once.

Next, highlight both lines and press Tab twice, and VBA will indent both lines two tab stops to the right. Now, highlight the second line and press Shift-Tab just once so VBA can remove one of those tab stops from the second statement.

WHEN TO ELSE OR ELSEIF USING VBA'S IF

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.

A BETTER VIEW

Ever wish you had a bigger monitor? Well, maybe you don't really need a bigger monitor--what you need is a bigger view of your worksheet. Just choose View, Full Screen. Excel will display only your worksheet and the menu bar--everything else has been neatly tucked away out of view. That means you can see more of your work. When you're ready to return to the normal view, choose Close Full Screen from the small floating toolbar that Excel displays during Full Screen view. Or simply deselect Full Screen on the View menu.

TOGGLING BETWEEN VIEWS

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).

DISMISSING THE DEFAULT WINDOW

If you find that you almost always end up closing the default Book 1 workbook that appears when you start Excel, you can set things up so that Excel starts without an empty workbook. To do so, you need to edit the command line that you use to start Excel. For example, if you start Excel by using a shortcut on your Windows desktop, right-click the shortcut icon and choose Properties from the menu. Click the Shortcut tab and add /e after the command line listed in the Target field. The following is an example of a command line modified in this manner (the actual drive and path may vary on your system):

C:\Program Files\Microsoft Office\Office\excel.exe /e

AN IMMEDIATE WINDOW SHORTCUT

When you want to run an expression, function, or variable in the Immediate window, you don't have to retype it. You can drag it from its module to the Immediate window and then run it. You may need to revamp it just a bit by adding a

?

to the beginning of the statement or providing an argument. But you'll save yourself a little time and cut down on typos.

HIDING A WORKBOOK

We talked earlier about hiding rows and columns. You can also hide an entire workbook. To hide a workbook, simply choose Window, Hide to make the entire workbook seemingly disappear. This is a great trick for those moments when you don't want unauthorized personnel to see sensitive data but you don't want to close your workbook.

When it's safe to return to your work, choose Window, Unhide. Excel will display the Unhide dialog box, which may contain more than one workbook. Choose the workbook you want to unhide and click OK to redisplay your workbook.

WORKBOOK NAMES

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 ("").

PRINTING THE WORKBOOK PATH IN A HEADER

Some people find printing the workbook's name in a footer helpful. Doing so creates a quick reference for the file's location on your printout. However, there's no built-in feature for printing the file's name, but you can do so with a simple macro. To create this macro, open the VB Editor by clicking Alt-F11. Then, select the correct project in the Project window (which you'll find in the upper-left corner). Next, select Insert, Module. In that module, enter the following procedure:

Sub NameInFooter()

ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName

End sub

To close the VB Editor, simply click the Close button at the top-right corner of your screen.

When you're ready to run the macro, choose Tools, Macro. Then, choose Macros again, select NameInFooter, and click Run. You won't see the effects immediately. If you click the Print Preview button, you can see the filename in the footer.

MORE ON SECURING WORKBOOKS

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 access 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.

MY, WHAT BIG WORKSHEETS YOU HAVE

Are any of your workbooks consuming far more memory than they should? If so, the first thing you should consider is the possibility of unnecessarily formatted rows and columns. This can happen when you import foreign files because Excel converts all previously formatted cells, even if they're empty.

To reclaim that wasted memory, try this easy trick. Select all blank rows and columns beyond the last cell that contains data and press Delete. Then, save the workbook. The newly saved file should be much smaller than before.

WHEN AN EMBEDDED WORKSHEET DOESN'T WORK

You probably know that you can embed an Excel spreadsheet into another application and then launch Excel from that other application by double-clicking the embedded spreadsheet. On occasion, the other application ignores the double-click. When this happens, don't panic, because the situation is probably easy to correct.

Excel will ignore requests from other applications if you've set the Ignore Other Applications option. You'll find this option by choosing Tools, Options and then clicking the General tab. If this option is selected, Excel won't automatically launch when you double-click an embedded Excel object in another application.

To correct the problem, simply select the option and close Excel. The next time you call Excel from an embedded object, everything should work just fine.

HIDING A WORKSHEET

So far this month we've shown you how to hide a row, a column, and an entire workbook. Unfortunately, hiding an entire workbook, even momentarily, can be a disruption to your work. If you have time for an extra click, try hiding just the worksheet. To do so, click the worksheet tab at the bottom of the screen. Then, select Format, Sheet and choose Hide from the resulting submenu.

When you're ready to unhide the worksheet, simply reverse the process, choosing Unhide from the submenu instead of Hide. Excel will prompt you to identify the worksheet you want to unhide. Select the workbook, then click OK.

MOVING EXCEL WORKSHEETS

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.

LEADING ZEROS

There are a number of solutions for adding leading zeros to a value, and some of them are more convoluted than they need to be. If you're using VBA, we can offer a simple method for adding leading zeros, but it isn't all that intuitive. However, it's simple and it works. In a nutshell, you add the value you want to display with leading zeroes to a 10-based number that's one place larger than the number of characters you need for each entry. For instance, if you want all values to have five characters, using leading zeros to fill in as needed, you'd use the number 100000--that's one place more than five. The function

Right(value + 100000, 5)

will return five characters from the result of adding value to 100000--including leading zeros. If value equals 30, the function will return 00030; a value of 4321 will return 04321, and so on.

If you'd like to test this function, open the Visual Basic Editor by pressing Alt-F11. Next, in the Immediate Window, type the statement

?Right(30 + 100000, 5)

and then press Enter. VBA will return the string 00030.

The following procedure will do the same--as long as you pass it a value. If the value you pass is larger than six places, the function will truncate it to the last five values.

Function LeadingZeroes(value As Long) As Variant LeadingZeroes = Right(value + 100000, 5) End Function

You can test this procedure in the Immediate window by running the statement

?LeadingZeroes(30)

which should return the variant value 00030.

Did you get all that?

ZOOM IN

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.