Is there a way to print an Excel worksheet showing the formulas rather than the data. There is a way, and here it is: Before you can print the formulas, you have to get Excel to display them. Choose Tools, Options, and when the Options dialog box opens, click the View tab. Now, select the check box labeled Formulas and then click OK to close the dialog box and record the change.

You'll see that the worksheet now displays the formulas instead of the results of the calculations. So choose File, Print while the formulas are displayed, and the printout will also display the formulas.

Note that the viewing selection we described will remain in effect until you change it or load a new worksheet. A new worksheet will automatically default to displaying the cell values rather than the formulas.

LOCATING AUTOSAVE AFTER INSTALLING IT

A while back, we described how to install Excel's AutoSave Add-in. AutoSave automatically saves your worksheet at preset intervals. Several readers followed the instructions and got AutoSave installed, but now they can't find it to make use of it.

Let's start at the beginning. If you don't have AutoSave installed, choose Tools, Add-Ins. When the Add-Ins dialog box opens, click the check box to the left of AutoSave and click OK.

So, where is AutoSave? It's in the Tools menu. Choose Tools, AutoSave. When the AutoSave dialog box opens, make your selections and click OK. You could add AutoSave to the toolbar, but that seems rather pointless since this is the kind of thing you set and then ignore.

INDENTING COLUMNS

Once you've crunched the numbers in an Excel worksheet, you often go back through the sheet to make it look better. In some cases, you might like to have the entire worksheet start in the second column. The problem is that you've already used column A. You can easily insert a new column. All you have to do is click column A and choose Insert Columns. The whole worksheet moves over by one column.

But what if you don't want the indentation to be quite as large as the default column width? In the label area, move the mouse pointer over the line between A and B, then drag the line to the left until the spacing is just right for your worksheet.

A HEADER AND FOOTER MACRO FOR EXCEL

Here's a macro that's designed to put headers and footers into an Excel worksheet. This macro will insert your choice of headers and footers into the Excel worksheet and then open Print Preview so you can check them.

To enter the macro, run Excel and choose Window, Unhide. Doing this will open a dialog box from which you should choose Personal.xls and click OK. Now, type in the macro shown below exactly as it appears.

Sub PrintPreView()
'PrintPreView Macro
' Macro by P. V.

Application.ScreenUpdating = False

Application.Calculation = xlManual

With ActiveSheet.PageSetup

' the header

.CenterHeader = "My Header" & "&""Arial,Regular""&8"

' Left Footer

.LeftFooter = "My Left Footer " & "&""Arial,Regular"" &8"

' Center Footer

.CenterFooter = "My Center Footer" & "&""Arial,Regular""&8"

' Right Footer

.RightFooter = "My Right Footer" & "&""Arial,Regular""&8"

' to center the print horizontally on the page

.CenterHorizontally = True

End With

Application.Calculation = xlAutomatic

ActiveSheet.PrintPreView

End Sub

For the items shown as My Header, My Left Footer, and so forth, enter your own values. After you've entered the macro, choose File, Save and then choose Window, Hide.

Now you need to assign the macro to a button. Choose View, Toolbars and click Customize in the Toolbars dialog box. In the Customize dialog box, choose Custom and then decide which button you want to assign to the new macro. Drag the button to the toolbar.

When you drag the button to the toolbar, Excel will ask what macro you want to assign. Select your new macro and click OK. When you get back to the Customize dialog box, click OK. Now you can click on the newly assigned button to run the macro.

TURN A NORMAL CELL REFERENCE INTO AN ABSOLUTE REFERENCE

You can quickly turn a normal Excel cell reference into an absolute reference. (An absolute reference forces Excel to always refer to the cells you specify.) Let's say you've entered

=SUM(A1:A5)

into cell A7. Double-click cell A7 and then use the mouse to select the reference A1:A5. Now press F4 and then press Enter. The cell contents will change to the absolute reference form of

=SUM($A$1:$A$5)

THE TROUBLE WITH COMMAS

When you enter commas into a cell's contents, make sure you get them in the right places. If you don't, you'll confuse Excel.

Let's say that you've just entered

4567.89

You want to place a comma after the 4, to make the number read

4,567.89

However, if you should inadvertently enter the comma after the 5, you'll get

45,67.89

and Excel will assume you want it to be a text entry. If you enter a number with commas, and you don't see the number move over to the right side of the cell, check those commas.

SPIFF UP WORKSHEETS WITH A BACKGROUND IMAGE

When you need to use an Excel worksheet in a presentation, you want that worksheet to look as good as it possibly can. It must be functional but beautiful. Excel 95 offers a feature that you might want to consider using for those special worksheets: adding a background.

Let's say you have a logo that you'd like to use as a background. Choose Format, Sheet, Background. When the Sheet Background dialog box opens, locate your picture file and select it. Click OK to insert the background and close the dialog box. Unless the picture is a BMP file, you'll get a dialog box that offers to convert the file. Click OK to continue. Excel will tile the logo to cover the entire worksheet.

There are a few caveats that we need to mention. If you use a dark (or busy) picture, you'll have trouble seeing your data entries. The best background would be a very pale picture. A watermark type of picture would be best.

To remove the background, choose Format, Sheet, Background. When the Sheet Background dialog box opens, click None.

SHOW ME ALL THE FORMULAS IN A WORKSHEET

Version 4.x, 95

When you're dealing with a large worksheet, you may have trouble finding all the formulas. All you see in the worksheet are the results. You can show the formulas in a worksheet by holding down Ctrl and pressing the Tilde key (~). This is the key to the left of the 1 key.

When you press Ctrl-Tilde, Excel will display all the formulas in the worksheet. To get back to normal view, press Ctrl-Tilde again.

SCROLLING ALONG IN A WORKSHEET

There are two ways to scroll through an Excel worksheet without changing the cell selection. One way is to use the scroll bars. You can scroll anywhere you want by clicking on the vertical or horizontal scroll buttons. Another, easier way to scroll without changing the cell selection is to press the Scroll Lock key and then use the arrow keys to scroll through the worksheet. You suspected that Scroll Lock key was good for something, didn't you?

SAVING WORKSHEETS IN A FOREIGN FORMAT

If you ever need to save an Excel worksheet in a format other than the Excel default, you'll find that Excel offers you a wide variety of choices. Choose File, Save As. When the Save As dialog box opens, click the arrow at the right side of the Save As Type list box to expand the list. If you scroll through the list, you'll find that you can save the file in the format of Lotus, dBase, Quattro Pro, and a whole slew of other formats.

Note that Excel will save only the active worksheet in some of the formats. This is because many of the associated programs can't handle multilevel worksheets as Excel does. We just wanted to let you know, in case you encounter a problem saving to one of the formats.

RUNNING A SPELLING CHECK ON ALL YOUR WORKSHEETS

Version 4.x, 95

When you run a spelling check in Excel, you check only the current page. If you'd like to check all the worksheets in a workbook, right-click on any one of the tabs and choose Select All Sheets. Now you can choose Tools, Spelling to check all the worksheets. After you finish checking your spelling, right-click on the current tab and choose Ungroup Sheets.

QUICK NAVIGATION THROUGH WORKSHEETS

Version 4.x, 95
If you want to move to a new sheet in Excel, press Ctrl-Page Down. To move to a previous sheet, press Ctrl-Page Up. To get to the very last data cell in your worksheet, press Ctrl-End. Ctrl-Home will take you to cell A1.

PUT IMPORTANT WORKSHEET TOTALS ON FIRST SHEET

Version 4.x, 95

Let's suppose you're developing a very large workbook with numerous worksheets. You could make the workbook look better by placing the most important totals on the first sheet and leaving all the detailed stuff on the other sheets. This is no problem for Excel, since it lets you refer to cells in any worksheet.

Suppose you have a long detailed list of numbers on sheet 2. In cell B35, you have the formula to sum all the numbers in B1 through B33. On sheet 1, you can put something like 2000 Total in cell A2. Then you can go to cell B2 and enter

=sheet2!B35

to display the sum from sheet 2. Alternatively, you could leave out the sum in sheet 2 and simply enter in sheet 1, cell B2

=SUM(Sheet2!B1:B33)

to display the sum in sheet 1, cell B2.

PROPER CAPITALIZATION FUNCTION

Version 4.x, 95

Here's an Excel function you may not have seen. It's called Proper and its job is to render text with initial capitals.

Why would you need to use Proper? Well, if you purchase mailing lists, you know that many of them come in all caps. This is a poor stylistic choice for mailing labels.

This is where Proper comes in. Of course, its most efficient use is in a macro, but you can check it out without writing a macro. Press the Caps Lock key and type something into cell A1. Try something like

THIS IS ALL CAPS

Next, go to cell A2 and enter

=Proper (A1)

and you'll get

This Is All Caps

Proper deals with any text--uppercase, lowercase, or mixed. Whatever you apply it to will come out with the initial character of each word capitalized.

PRINTING BLANK DATA SHEETS

Version 4.x, 95

If you need to print a blank sheet between two or more worksheets that contain data, you'll find that Excel ordinarily declines to print a data-free worksheet. However, Excel will print a blank page if you select a group of empty cells and then choose File, Print Area, Set Print Area.

If you want to print a blank page showing the Excel gridlines that you can use as a form, choose File, Page Setup and then click on the Sheet tab. Select the Gridlines check box and click OK.

NAMING YOUR WORKSHEETS

In the past, we've said that you can name those Excel sheets anything that suits you. Well, you can--but there's a caveat or two. Let's assume that you've named one of your sheets something along the lines of "My 1997 Sales Data." Will this work? You bet. Just be careful when you make a reference to the newly named sheet.

Suppose you have data in cell A1 on the worksheet named "My 1997 Sales Data." You'd like to place this data into cell A1 of Sheet1. You need to enclose the new sheet name in single quotes. For example,

'My 1997 Sales Data'!A1

will work. If you omit the single quotes, it won't work.

MARKING CELL FORMATS

In a previous tip, we described how to set the format of a group of selected cells. Here's a tip to help you remember what type of data goes into which cells.

Let's say that you need to enter text, or numbers as text, into a group of cells. Select the cells and choose Format, Cells. Select Text and then click the Patterns tab. Select a color for the text cells (a pale color is best--dark colors might obscure the text entries). Now click OK to close the dialog box and record your changes.

You can use the same trick to mark other formats. This can make entering data into a worksheet much less confusing--especially for those less-experienced users who need to enter data in worksheets that you design.

LEAP YEAR BUG

Version 4.x, 95

We recently ran a tip describing 1900 as a leap year. Quite a few of you noticed that it was not. Congratulations! This fact has escaped Microsoft through at least three versions of Excel.

Leap years occur in every year that is exactly divisible by four, except when the year is also exactly divisible by 100 (an even century year). These years are leap years only if they are exactly divisible by 400. Therefore, 2000 is a leap year, since 2000 divided by 4 equals 500 and 2000 divided by 400 equals 5. However, since 1900 divided by 400 equals 4.75, it wasn't a leap year. Treating 1900 as a leap year is definitely a bug in the versions of Excel shipped with Office 4.x, Office 95, and Office 97.

INSERTING A NEW WORKSHEET

Suppose you're working away on a multiple-sheet workbook. You suddenly realize that you really should have a sheet between Sheet 1 and Sheet 2. All is not lost. Just click the Sheet 2 name tab to select it and then choose Insert, Worksheet. This will insert a new sheet before Sheet 2. Since Excel's default workbook contains 16 sheets, the new sheet will be named Sheet 17.

INSERT DATE AND TIME INTO A WORKSHEET

If you want to insert the date and time into an Excel worksheet, you can click a cell and enter the date. To insert the current date into a cell, you can type =TODAY() and press Enter. If you're in too much of a hurry for all that typing, try pressing Ctrl-; (semicolon) to insert the date, and Ctrl-: (colon) to insert the time. The inserted information will display using the cell's current format.

INSERT A CARRIAGE RETURN IN YOUR CELL'S TEXT

If you need to type a lot of text into a single cell, you can control the width of your text by pressing Alt-Enter to insert a carriage return. When you need to use a tab, press Ctrl-Alt-Tab.

HOW TO SPOT A BAD DATE

Here's more Excel date information. When you enter an incorrect date into an Excel cell, Excel doesn't query or warn you. However, the incorrect entry will remain left-justified instead of kicking over to the right side of the cell. This is the most immediate way to spot an incorrect date entry.

You can also tell when the date is incorrect if you attempt to change the cell format to another date type. For example, enter

2/29/99

Now click in the cell and choose Format, Cells and select Date. Then choose a new date format and click OK. If the date is incorrect, the cell won't accept the new format. If the date is correct, the format will change.

HOW TO ENTER FRACTIONS

Version 4.x, 95

You can enter fractions into an Excel cell and use them in your calculations. The only problem is that you have to be careful about how you enter fractions. For example, if you enter

5/6

and press Enter, Excel will think you're entering a date and convert the number to the current date format. So, 5/6 becomes something like 6-May.

To get around this problem, make a habit of entering fractions in the form zero, space, fraction. It should look like this:

0 5/6

Now Excel will know that you want to enter a fraction and will deal with the number correctly.

HOW TO COUNT THE DAYS

If you'd like to know how many days are left until Labor Day, run Excel and type

="09/04/00" - "07/19/00"

(or that day's date) into a cell. Excel will return the number of days (47) between the two dates. How many days until New Year's Day? Enter

="01/01/01" - "07/19/00"

and you'll get 166 days. The trick here is to remember to use the quotation marks. If you don't use them, you'll get some very strange results.

HOW TO ADD FRACTIONS

Version 4.x, 95

Although Excel converts fractions to decimals in order to calculate them, it reports results as the nearest fraction.

To check this, enter into cell A1 the fraction

0 3/32

In cell A2, enter

0 2/16

and in cell A3, enter

0 5/72

Now move to cell A5, enter

=SUM(A1:A3)

and press Enter. The result should be 2/7.

You can often gain some accuracy by clicking on the sum (cell A5 in this case) and then choosing Format, Cells. When the dialog box opens, click on the Number tab, choose Up To Three Digits, and click OK. In this case, using a three-digit fraction results in the number 83/288.

If accuracy is the most important consideration, click on the formula cell (A5), choose Format, Cells and select Number. Now you can select the number of digits to use and click OK. We selected four digits, which resulted in 0.28819 in cell A5.

GETTING THE REAL INTEREST RATE

Is the published interest rate what we're really paying? Let's use Excel to find out. Let's say you're about to make a purchase, and the interest rate as published is 12 percent per year. However, you read the fine print and find that the interest is compounded quarterly. You can use Excel's Effect function to determine what your actual rate is going to be.

In cell A1 enter

=effect(12%,4)

since the interest will be compounded four times a year. Excel reports that the actual interest rate is 12.55%. If the interest is compounded monthly, you'd enter

=effect(12%,12)

and Excel would return a rate of 12.68%.

The Effect function is part of Excel's Add-Ins. If you get no result when you use Effect, choose Tools, Add-Ins and select it from the list. If it isn't on the list, close Excel and run Office Setup to install the Add-Ins as we described in the previous tip.

FORMATTING CELLS QUICKLY

If you want to quickly set the format of a single cell in Excel, select the cell and then right-click it. When the pop-up menu opens, select Format Cells to open the Format Cells dialog box. Once in the dialog box, you can click the Number tab to set up the cell's format. While you're at it, you can also set Alignment, Font, Border, Patterns, or Protection. Make your selections and click OK to record your choices and close the dialog box.

FITTING PAGES TO PRINT

Version 4.x, 95
Sometimes it's not easy to guess how many pages an Excel worksheet will require when you print it. To see how the worksheet is going to look, choose File, Print Preview. If the sheet will require more than one page, Print Preview will tell you.

If your worksheet isn't much longer than a single page, you can choose File, Page Setup and click on the Page tab. Select the Fit To check box and tell Excel to make the worksheet fit on one page. Click on OK to close the dialog box and record your changes. You can see how it will look by checking Print Preview again.

Note: Using the Fit To option to reduce your worksheet is a valid selection only if the worksheet is a little more than one page. If it is larger, then the reduction may make the printout difficult to read.

ENTERING NUMBERS AS TEXT IN A WORKSHEET

When you need to enter numbers as text in an Excel worksheet, you can type an apostrophe before the number. For example, if you type

'12345

Excel will consider the entry as text. This is fine when you want to enter numbers in only a few cells. But if you need to enter many numbers as text, the safest way is to set the cells you intend to use to Text format. When you do, you won't have to worry about forgetting the apostrophe.

To set the cells format to Text, select the cells and choose Format, Cells. When the Format Cells dialog box opens, select Text and click OK.

ENLARGING YOUR PRINTOUT

Version 4.x, 95
If you want, you can expand a small worksheet's printout to make it more legible or just more impressive. Choose File, Page Setup and click on the Page tab. Use the Adjust To spin box to set the amount of enlargement you'd like to try. After you make a selection, click on Print Preview to make sure the entire worksheet will fit on the page. Click on Close when you finish with Print Preview. When you're happy with your settings, click on OK.

END KEY TURNS ON END MODE

Version 4.x, 95

If you press the End key to get to the end of an Excel row, you'll get a surprise instead. Pressing the End key simply turns on the End mode.

To check out the End mode, click the first cell in a row of data and press End. Now press the right arrow key, and Excel will navigate to the last cell in the row that contains data. If there's no data in the row, Excel will go to the last cell in the row.

If you press End and then press the down arrow, Excel will navigate to the last cell in the column that contains data. If there is no data, Excel will simply move to the last cell in the column.

In either case, the End mode will turn itself off after completing its task.

END BLINKING TASKBAR ICON

By default, when you use Excel's Add-in AutoSave, the program will prompt you when it's time to save the document. If Excel is minimized when the time for a save comes, its taskbar icon will blink. So, if you see a blinking icon, don't worry--it's just Excel asking if you want to save the document. You can put an end to this prompt if you like. Choose Tools, AutoSave. When the AutoSave dialog box opens, deselect the Prompt Before Saving check box and click OK. No more blinking icons for you.

DOLLAR FORMAT OPTIONS

Here's an Excel Dollar tip for you--you can have more than one Dollar format in an Excel worksheet. Try this:

Type into cell A1

4,234.22

Now click cell A1 and choose Format, Cells. When the Format dialog box opens, click the Number tab (if necessary). Next, click Currency and then click OK to accept the default and close the dialog box. The number will appear right-justified in the cell--just what you'd expect.

Now type into cell A2

=DOLLAR(4234.22)

and press Enter. This time, the number will be in Dollar format, but it will be left-justified in the cell. The reason for this apparent anomaly is that the DOLLAR function converts the number to text format.

This doesn't mean that the number formatted by the DOLLAR function won't work in calculations. To confirm its proper operation, click cell A3, type

=A1+A2

and press Enter. Cell A3 will display $8,468.44, which demonstrates that both numbers were included in the SUM.

DISPLAY SELECTED NUMBERS AS POSITIVE

There may be times when you don't want a number to display as negative, even if the calculation produces a negative. In Excel, the ABS (absolute value) function takes any number and makes it positive in value. For instance, 7 stays 7, and -7 becomes 7.

Let's suppose that you want to use Excel to calculate the number of days between two dates. You can enter into cell A1

1/1/00

Now, if you enter the current date into cell A2, you can calculate the number of days since January 1. So, you type into cell A3

=A1-A2

which will produce a negative number. So, let's take the absolute value. Type into cell A3

=abs(A1-A2)

and Excel will display a positive number.

Note: Yes, we know that you can subtract A1 from A2 and get a positive number. It's only an example.

DELIGHTFUL, DELOVELY, DELIMITED FILES

Version 4.x, 95

There are times when you might need to use an Excel file in a program that doesn't read Excel files. If you find yourself in this situation, check to see what kinds of files the program will handle. Many programs will read delimited ASCII files. This means that each Excel column must be separated by a space, a tab, or a comma.

Let's give this a try. Open a new worksheet and enter text and numbers into three or four columns, perhaps something like this:

Column 1 Column 2 Column 3

1 4 6
2 5 7
3 8 9

Choose File, Save As. Type in a name for your file and then click the arrow at the right side of the Save As Type list box. Scroll down to CSV (Comma Delimited)(*.csv) and click Save. You'll get a warning telling you that you're not saving the file as a standard Excel file. Choose Yes.

Now you can quit Excel if you like. When you do, you'll get another warning about saving a nonstandard file. Tell Excel that you want to save the file. Then open NotePad and open the new file, YourName.csv. The file will appear in the form shown here:

Column 1,Column 2,Column 3
1,4,6
2,5,7
3,8,9

At this point, you can try reading the new file into that choosy program. If your program requires tab- or space-delimited files, just select the appropriate option when you choose File, Save As.

DELETE UNWANTED SHEETS

In the last tip, we showed you how to insert a new worksheet between two existing sheets. In that tip, we mentioned that the Excel default is 16 sheets to a workbook. If a given workbook needs only one or two sheets, why keep all those other sheets around?

Let's say you have a workbook that uses only one sheet. Go to the bottom of the worksheet and click Sheet 2. Now hold down the Shift key and use the Move To End button (it looks like a right arrow running into a vertical line) to move to the end of the workbook. Click Sheet 16. Now choose Edit, Delete Sheets. You'll get a dialog box warning you that you're about to permanently delete the worksheets. Click OK.

If you should need another sheet, you can choose Insert, Worksheet. The new sheet will be placed before Sheet 1 and will be named Sheet 17.

DATE FORMATTING OPTIONS

Version 4.x, 95

You can do more with Excel's date formatting than you might think. Suppose you'd like a particular cell to show only the month and the day. Click the cell (to select it), choose Format, Cells, and select Custom. Double-click the entry that's in Type and press Delete to get rid of it. Now, under Type enter

mmmm dd

and then click OK. Let's say the date you enter into the cell is 7/17/00. The cell will display July 17.

Now let's suppose you'd like to show the day of the week, the month, and the day (numerical). Choose Format, Cells and select Custom again. This time, type

dddd, mmmm dd

and click OK. The cell will display Monday, July 17. If you want to add the year, go back to the Format Cells dialog box and type

dddd, mmmm dd, yyy

Now click OK. This time, the cell will display Monday, July 17, 2000.

CUSTOMIZING HEADERS AND FOOTERS

Version 4.x, 95

Word isn't the only Office program that can use headers and footers. Excel can use them, too. The process just isn't as obvious or as versatile as it is in Word.

Let's say you'd like to add a personalized header to an Excel printout. Choose File, Page Setup. When the Page Setup dialog box opens, click on the Header/Footer tab. If you want to set the font, choose either Custom Header or Custom Footer and click on the Fonts button (it's the one with the big A icon). Select the font and click OK.

Now you need to decide where you want the information to appear: Left, Center, or Right. Type the new message into the area of your choice and then click OK. When you get back to the Page Setup dialog box, click OK again.

COUNTING TEXT AND NUMBERS

You may, on occasion, need to determine when a group of cells contains numbers or text. Let's imagine that you need to find out how many cells in a column contain text. Enter into cells A1 through A5

1 2 3 A 5

Now enter into cell A7

=counta(a1:a5)

This tells you how many cells in the specified column contain something (5). Now, move to cell A9 and enter

=count(a1:a5)

to find out how many cells contain numbers (4). To find the number of cells containing text only, subtract the difference:

=counta(a1:a5) - count(a1:a5)

This produces a result of 1, which is the number of cells that contain text.

CONVERT NUMBERS TO ROMAN NUMERALS

Now that a better way of writing numbers has been around for a thousand years or so, wouldn't you think people would stop using Roman numerals? They haven't, though. You still run across them in copyright dates and other places where you don't really expect them.

If you're like most of us, you don't have a great memory for converting numbers to Roman numerals. Everyone can do I to X or so, but suppose you'd like to be cool and use Roman numerals just like the movie moguls do? All you have to do is run Excel, enter into a cell

=ROMAN(MyDate,0)

and then press Enter. Excel will present you with the date in classical Roman numeral form. If MyDate is 1998, Excel will present MCMXCVIII. The 0 after the date tells Excel that you want to use the classical form. If you enter

=ROMAN(MyDate,1)

where MyDate is 1998, Excel will display MLMVLIII. This is a more concise form of Roman numeral. So it's up to you--do you want the original style or the new, improved version?

CONCATENATION COMBINES INFO FROM MULTIPLE CELLS

When you develop those truly cool worksheets, you don't want to miss anything. So, let's see what concatenation can do to help you make that cool worksheet even more cool. If you have information in several cells that you want to pull together into one cell, try this:

Let's say that cell A1 contains the word Priscilla and cell A2 contains Priscilla's sales total for the month. You can go to cell C3 and type

=CONCATENATE("Salesperson ",$A$1, " is this month's leader with $", $A$2, " in sales.")

When you press Enter, Excel will display the line

Salesperson Priscilla is this month's leader with $23456.96 in sales.

CHECKING A CELL'S CONTENTS

If you import a worksheet from another program, you can use Excel's N function to gather information about a cell's contents. The N function returns a pure number--sans formatting. It's included for compatibility with other spreadsheet programs. However, you don't have to import another worksheet to see how the N function operates.

To test the N function, type

12345

into cell A1. In cell A2, enter

11/14/00

Now go to cell B1 and type

=n(a1)

Select cell B1 and press Ctrl-C to copy it. Click cell B2 to select it and then press Ctrl-V to paste the data copied from cell B1. Cell B2 will now display 36844, the serial date for 11/14/00.

CHANGING THE CELL REFERENCE STYLE

When it comes to cell addresses, it's all a matter of how you prefer to think. You can choose how you want to address an Excel worksheet. The default form is A1, A2, and so forth. For example, you can enter numbers in cells A1 through A5 and then type into cell A7

=sum(a1:a5)

to get the sum. However, if you prefer to think in terms of rows and columns, choose Tools, Options and click the General tab. Under Reference Styles, you'll see two radio buttons: A1 and R1C1 (for "row 1, column 1"). For now, select R1C1 and click OK.

Now go back to your number list in cells A1 through A5. This time, type into cell A7

=sum(r1c1:r5c1)

to indicate that you want to sum the numbers in row 1, column 1 through row 5, column 1, and you'll get the correct sum.

Note that you have to choose between the two formats--when you choose R1C1, the standard method (A1) will no longer work.

CHANGING ROW SIZE

You often need to enlarge the row sizes in Excel. This is especially true when you want to put titles into a worksheet. There are two ways to handle row size--to make an empty row larger, select the row and choose Format, Row, Height. Type in the new height and click OK.

When you use titles, you don't have to bother setting the row size. All you have to do is click the location for your title and then choose a font size (click the arrow at the right side of the Font Size list box and make a selection). Excel will automatically resize the row to fit the font.

CHANGING FORMATS ON THE FLY

When you're working with an Excel worksheet, you frequently need to set a cell's format. You can click the cell, choose Format, Cells, and make your selection from the dialog box.

There's a quicker way to make those cell format changes, though. All you have to do is press a key combination, and you can apply the standard form of a number of formats. Let's say you want to use the Currency format. Just click the cell to select it and then press Ctrl-Shift-$ to apply the default Currency format. Here's a list of some other formats and their appropriate keystrokes.

General: Ctrl-Shift-~
Two decimal places (0.00): Ctrl-Shift-!
Percent (%): Ctrl-Shift-%
Scientific: Ctrl-Shift-^

CHANGING COLUMNS TO ROWS AND VICE VERSA

During worksheet development, you might find that you wish you had put your data in rows rather than in columns, or in columns rather than in rows. The problem with this scenario is that you don't want to make all these changes manually. What if Excel could do it for you? It can.

Select the data range you want to transpose and then press Ctrl-C to copy it. Now click where you want the data range to appear and choose Edit, Paste Special. When the dialog box opens, select the Transpose check box and click OK.

ADJUSTING DECIMAL PLACES IN WORKSHEETS

When you enter numbers into a default Excel cell, the worksheet will display the number minus any trailing zeroes. For example, if you enter

3.0

into a cell, the cell will display

But, if you enter

3.12

Excel will display the numbers after the decimal. If you'd like to have the number displayed to two decimal places, for example, just select the cell and click the Increase Decimal number button in the Excel toolbar (this button's icon is composed of a few zeroes with an arrow pointing to the left). Each time you click the button, you get an added decimal place. If you want to use two decimal places, click the button twice. There's also a Decrease Decimal button. It's just to the right of the Increase Decimal button. Each time you click the Decrease Decimal button, you remove one decimal place from the selected cell.

If you want to change an entire column (let's say column B), click the B in the gray area at the top of the worksheet, and then click either the Increase Decimal or the Decrease Decimal button.

ADDING AN ELEMENT TO ALL THE SHEETS AT ONCE

Version 4.x, 95

In the last tip, we told you how to check the spelling in all the sheets of a workbook (right-click on a tab, choose Select All Sheets, and choose Tools, Spelling). We also said that you need to ungroup the sheets after you finish the spelling check (right-click on the current tab and choose Ungroup Sheets). The reason for doing this is, when all sheets are selected, whatever you do to one sheet affects all the sheets. This function might prove dangerous if inadvertently left on. That's because deleting a cell on the first sheet will delete the same cell on all the sheets.

You can use this feature to your advantage when developing a new workbook, though. Suppose you'd like to put a company heading on all the sheets in your workbook. All you have to do is right-click on a tab and choose Select All Sheets. Now put the company heading on the first sheet, right-click on the current tab, and choose Ungroup Sheets. The heading will appear in the same place on all the sheets in the workbook.

ADD COMMENTS TO WORKSHEETS

The ability to add comments to an Excel worksheet is especially useful when several people are reviewing it. For example, suppose you regularly create worksheets that you send to Wendy. This time, the total really doesn't look quite right to you, so you'd like Wendy to take a good look at the numbers. Click your Total cell and choose Insert, Comment. When the Comment dialog box opens, type in your message to Wendy and save the worksheet. You'll notice that a small red triangle appears in the upper-right corner of the cell that contains the comment.

When Wendy opens the worksheet, she will see the comment indicator. She can read the comment by simply moving the mouse cursor over the marked cell. To enter a response to your comment, she can right-click the cell and select Edit Comment.

When Wendy passes the file along to Hugo, he'll see the marker and move the cursor over the cell. Now he can read both comments and add one of his own.

ACCESSING NAMED SHEETS

"I named the sheets in a workbook. Now when I try to use data from one of the named sheets, the Open File dialog box appears. Is this a bug in Excel 95?"

No, there's no bug. The most likely reason for the opening of the Open File dialog box is that you've used names that contain spaces. For example, let's say you name a sheet

June Sales

and then need to use the figure in cell A5 of that sheet. You enter

='June Sales'!A5

to access the data. If you don't use the single quote marks, the Open File dialog box appears asking you to look for a file named Sales. If your names have no spaces, you can enter them without the quotes. So if you have data in cell A5 of a sheet named June, you can enter

=June!A5

to access the data.

If you commonly use names with and without spaces, it's a good practice to use the quotes in all your formulas.

ABSOLUTE AND RELATIVE FORMULAS

When you enter a formula into an Excel worksheet, you usually use the relative reference form. The formula

=sum(a1:a10)

is a relative reference formula. If you copy this formula and move it to a new location, the formula will change to reflect its location. However, there are times when you might need a formula that uses absolute referencing. One way to achieve this is to convert an existing formula to absolute. You don't even have to remember how to enter an absolute formula. All you must do is double-click the cell that contains the formula and then use the mouse to highlight the formula. Press F4, then Enter. Now you have a formula with absolute referencing.

While a formula is selected, you can use F4 to switch among all the referencing forms. If you'd like to give this a try, type in a formula, select it as we described, and then press F4 and watch the changes.

USING THE NOT FUNCTION

"I'm curious about the NOT function. I tried to use it to check when two cells were the same, but I got a response of False when they were the same and True when they were not. Is this backward?"

It all depends on how you look at it. Enter into cell A1

and then enter

into cell A2. Now go to cell A5, type

=not(a1=a2)

and press Enter. Excel will respond with True. This is the correct response because it is True that 23 is not equal to 25. If you change cell A2 to

then cell A5 will display False because 23 is equal to 23. Therefore, 23 is NOT not equal to 23. Whew!

USING THE MEDIAN FUNCTION

The problem with Excel's Median function is that many users confuse it with Average. Although Median and Average can often produce the same result--they are not the same thing. The median value is the number in the middle of a group of numbers. That is, half the numbers have a value higher than the median, and half the numbers have a lower value. You'll often see a group of salaries reported as a median. For example, you may read that the median income in the great state of Confusion is $45,000.

If you open a blank Excel worksheet and type into cells A1 through A10

1 2 3 4 5 6 7 8 9 10

and then go to cell A12 and type

=average(a1:a10)

you'll get a value of 5.5. Now move to cell A14 and enter

=median(a1:a10)

and you'll also get a value of 5.5. This is what confuses people--Average and Median often produce the same result. But if you type

10 2 7 8 3 9 12 14 4 1

in cells A1 through A10, cell A12 (Average) will display 7, and cell A14 (Median) will display 7.5.

USING TEXT BOXES

Version 4.x, 95

Some users have trouble working with the text box feature in Excel. To use a text box, you must first have access to the Drawing toolbar. Choose View, Toolbars and select Drawing. You can let the toolbar float in the window, or you can drag it to the top or the bottom of the screen to anchor it.

Now that the Drawing toolbar is available, click on the Text Box button (it looks like a small page of text). Use the mouse to draw and size the text box. Don't worry too much about size or placement right now, though. You can change both later.

Next, click on the edge of the new text box to select it, then right-click on it and choose Format Object. When the Format Object dialog box opens, click Font and choose the font and font size you want to place in the box (choose the color, too, if you want). Click any of the other tabs to make your text box look just the way you want. Make sure you click Alignment if you'd like to center the text. To center the text both horizontally and vertically, select the appropriate radio buttons and click OK.

Now you can add your text. When you save the file, your new text box will save along with it; when you open the worksheet again, the text box will still be there.

USING SPECIAL DATE FORMATS

Version 4.x, 95

Although special date formats aren't so obvious in the Excel shipped with Office 4.x, they are there. Type

12/25/00

in cell A1. Let's suppose you'd like this date to appear in its most complete form. Click on cell A1, choose Format, Cells, and click on Custom. You can choose from one of the formats listed, or you can enter your own format in the Code Entry box. Since we want to use a format that isn't there, delete the current information in the Code Entry box and enter

dddd mmmm d, yyyy

Now click OK. Your date should display as

Monday December 25, 2000

Note that you can add commas or other separators to your format. For example, if you enter in the Code Entry box

dddd - mmmm d, yyyy

the hyphen will appear between the day of the week and the month. So the information displayed becomes

Monday - December 25, 2000

USING DRAG AND DROP

Although most of us know that you can select Word text and then use the mouse to drag the selected text to a new location, many users don't know that you can do the same thing in Excel.

To see how drag and drop works in Excel, open a new worksheet and type

First Cell

into cell A1. Now type

Second Cell

into cell A2. Select the two cells and move the mouse near the edge of the cells. When the cursor changes from a plus sign to a pointer, press and hold the mouse button. Use the mouse to drag the cells to a new location and then release the mouse button.

When you use the drag-and-drop technique in Excel, you must make sure to grab just the edge and do so only after the cursor turns into a pointer.

USING AUTOSAVE

"Does Excel have an AutoSave feature like Word's?" The answer is yes and no. The feature exists, but you may have to do a bit of work to get to it.

Excel's AutoSave is part of the Add-Ins--goodies that come along with Office but aren't necessarily installed. To see if it's available, choose Tools, Add-Ins. If you see AutoSave in the list, select it by clicking the check box and then click OK.

If AutoSave isn't in the Add-Ins list, put the Office 95 CD into the CD-ROM drive and click Start, Settings, Control Panel. In Control Panel, double-click the Add/Remove Programs icon to open the utility. Locate Microsoft Office (this line will vary depending on your version) and select it. Now click Add/Remove. When Office Setup opens, select Excel and click Change Options. Now select Add-Ins and click OK. Follow through with the Setup wizard to install the Add-Ins. The next time you run Excel, AutoSave will be available.

USING A DATE MACRO

Version 4.x, 95

If you'd like to add the dates of the current week to your worksheet on a Monday morning, you can select a cell and type

=today()

Now select five cells beginning with the one you just entered today's date into and choose Edit, Fill, Series. Then select Date and Weekday. Click OK and the column will fill with the dates of the current week. But why go through all that when you can use a simple macro to do it for you? Let's look at the macro first, and then we'll talk about how to install and use it.

Sub AddDate()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("A2:A6").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date _ :=xlWeekday, Step:=1, Trend:=False
Selection.NumberFormat = "dddd mmmm d, yyyy"
Columns("A:A").EntireColumn.AutoFit
Range("A7").Select End Sub

To add the macro to Excel, choose Window and select Unhide if it's available. If Window, Unhide is grayed out, choose Tools, Record Macro. When the dialog box opens, type in

AddDate

for the name and then click on Options. Make sure Personal Macro Workbook and Visual Basic are selected and click OK. Now click the Stop button on the floating Recorder window. At this point, Window, Unhide will be available, and you should select it.

A dialog box will appear with Personal.xls selected (if something else is selected, select Personal.xls). Click OK and the blank module page will appear. Type in the macro exactly as shown in the listing. When you finish entering the macro, choose File, Save to save Personal.xls. Next, choose Window, Hide to rehide Personal.xls.

Now you can choose Tools, Macro and double-click Personal.XLS!AddDate to run the new macro.

AddDate selects cell A2 and inserts the current date (we assume you'll use this on Monday morning). Then it selects more cells to hold the remaining dates of the week. Next, it places the dates in the selected cells and then formats the date and the cell width. The macro then moves the cursor to the bottom of the row. This deselects the previously selected cells.

When you close Excel, you'll be asked about saving the personal workbook. Choose Yes.

PASTING AN EXCEL CHART INTO POWERPOINT

Is there a way to paste Excel charts into a PowerPoint slide without pasting the entire worksheet. Yes, there is. To do this, you right-click the chart and then choose Copy. Now move to the PowerPoint slide and press Ctrl-V (or choose Edit, Paste). This technique will get the chart into PowerPoint without dragging the whole worksheet along with it.

MORE ON DISPLAYING EXCEL FORMULAS

We recently published a tip on how to get Excel worksheets to display formulas rather than data. We suggested you choose Tools, Options, click the View tab, and then select the check box labeled Formulas. Click OK, and the formulas display in place of the associated data.

A number of subscribers pointed out that there is a much easier way--all you have to do is press Ctrl-Tilde (~), and Excel will toggle between data display and formula display. Thanks to all who sent email on this topic.

SETTING UP SEQUENTIAL DATES IN EXCEL

In a recent tip, we mentioned that you could enter a date into an Excel cell and then drag that cell to enter sequential dates. You can do the same for sequential weeks (or any time period). For instance, if you'd like a list of Mondays, you can enter the date for the first Monday in one cell, then the date for the following Monday in an adjacent cell. Now highlight both cells and drag. Excel will create a sequence of Mondays (or whatever day you want--use Friday if it will make you feel better).

Here's an example: Go to cell A1 and enter

9/4/00

Now go to cell A2 and enter

9/11/00

Highlight both cells, grab the little handle and drag down three cells. Excel will now display

9/4/00
9/11/00
9/18/00
9/25/00
10/2/00

in column A.

TURNING GRIDLINES ON AND OFF IN EXCEL

If you want to turn off the gridlines in Excel, you choose Tools, Options, click the View tab, deselect the Gridlines check box, and click OK. To turn the gridlines back on, you repeat the process and select the Gridlines check box.

If turning gridlines on and off is something you need to do often, why not use a simple macro to do the job for you? Reader E.L. supplied a macro that toggles gridlines on and off. This macro is simple to use because it's a true toggle program. Run it the first time, and it turns off the gridlines. Run it again, and it turns them back on. Here's the macro:

Sub ToggleGridLines()

ActiveWindow.DisplayGridlines = Not
ActiveWindow.DisplayGridlines

End Sub

To create the macro, choose Window, UnHide, select Personal.xls, and click OK. When Personal.xls opens, there may already be a macro in the window. If so, click the Insert Module button (it's the top-left button in the Visual Basic floating toolbar). Now type in the macro (or cut and paste it from this message). When you're finished, press Ctrl-S to save the new macro and then click the Insert Module button. Answer yes to any questions about saving your macro.

USING EXCEL'S TRIM FUNCTION

If you ever import text from another program into Excel, you may need to use the Trim function. Trim gets rid of all the extraneous spaces in a text string.

Let's say you import a string such as

This is data from another source

into an Excel worksheet. Chances are, you don't want any extra spaces in the string. Let's say the imported text is in cell A1. Go to cell F1 and enter

=trim(a1)

Those unwanted spaces will disappear.

USING EXCEL'S SUMIF FUNCTION

In the last tip, we showed you how you can use Excel's IF function to determine the application of a bonus. This time, let's look at how to use SUMIF to directly apply the bonus.

Let's say that you have a column of names, a column of total sales, and a column of standard commissions. If the sales for the month exceed $10,000, you want to add a one-percent bonus to the standard commission. For this example, the names are in B2 to B5, sales are in C2 to C5, commissions are in D2 to D5, and we'll put the bonus values in E2 to E5. Here's a formula for cell E2 that will add the bonus to the standard commission provided the minimum sales condition is met:

=PRODUCT(SUMIF(C2,">=10000"),0.01)

As usual, you can select E2 and drag it through E5 to copy the formula to the remaining cells.

USING EXCEL'S IF FUNCTION

There are times when you can make good use of Excel's IF function in your worksheets. Let's say you have a sales sheet that you use to determine who might qualify for a bonus. You have a list of names in the first column (say column B) and the monthly sales amounts for each one in column C.

Let's look at how to use IF. The basic form of the IF function is

IF (condition, true response, false response)

So, in our example, you can use the IF function in column D to indicate qualification. Let's assume that the minimum for bonus qualification is $10,000. If your names are in B2, B3, B4, and B5, and your sales totals are in C2, C3, C4, and C5, type into cell D2 the following formula:

=IF(C2>10000,"Qualified","Not Qualified")

You should get your result for cell C2 now. Select cell D2 and then grab the little handle and drag down to D5. This copies the formula to the remaining cells.

VIEWING EXCEL WORKSHEET LAYOUTS

Layout is important when you're designing a complex Excel worksheet. But it's hard to see how the layout looks when you can view only a portion of the worksheet at once. To see how your entire worksheet looks, try this:

Choose View, Full Screen. Now press Ctrl-End to move to the last cell used by your worksheet. Press Ctrl-Shift-Home to select the worksheet from the last cell to cell A1. Choose View, Zoom and select Fit Selection. Click OK, and there's the entire worksheet. You won't be able to read anything, but you can see how the layout looks.

WORD/EXCEL: START EXCEL VIA WORD

Do you always start your day by loading Word and Excel? If so, why not let a Word macro start Excel for you?

To do this, choose Tools, Macro. When the Macro dialog box opens, type in

AutoExec

and click Create. Now enter the following macro exactly as shown:

Sub MAIN Shell "Excel.exe" End Sub

Note that Word enters Sub MAIN and End Sub, so all you have to enter is the one remaining line. Choose File, Save to save the new macro. Since an AutoExec automatically runs, you don't need to assign a button or key to the new macro. All you have to do is close Word and then open it again. When you open Word with the macro in place, Excel will also open.

TURNING OFF AUTOMATIC CALCULATION

Versions 4.x, 95
If you're developing a very large Excel spreadsheet, you may want to temporarily disable the automatic calculation option. This way, you can enter all the formulas you want without ever having to wait for a recalculation. To turn off automatic calculation, choose Tools,
Options and click the Calculations tab. Now select the Manual radio button, then click OK to close the dialog box. To check a calculation while you work, press F9. Don't forget to turn automatic calculation back on when you finish developing that monster spreadsheet.

EXPONENTIAL NUMBERS

Versions 4.x, 95
Want to raise a number to a power? Excel gives you not one way to do this, but two. Let's say you want to raise 10 to the fifth power. You can type

10^5

=POWER(10, 5)

Take your pick. The second method is the better one when you're writing Basic applications.

2000: A SOFTWARE ODYSSEY

Versions 4.x, 95
Microsoft has stated that all of its current applications will deal with the year 2000 properly. Don't want to take Microsoft's word for it? Check the Office applications yourself. (You can also use these techniques to check programs from other companies.)

Open Excel and select cell A1. Choose Format, Cells and then choose Date under Category. Select one of the longer date formats (in Excel 7, choose March 4, 1995) and click OK. Now type into cell A1

COPYING DATA MAPS

Version 95
Do you use Excel 95's Data Map? If so, you might like to transfer some of those neat maps to a Word document or a PowerPoint slide. It's easy--you can cut and paste a map just as you would any other object. Simply select the map, then choose Edit, Copy. Go to your Word or PowerPoint document, select a position where you want the map, and choose Edit, Paste.

NAVIGATING EXCEL

Versions 4.x, 95
If you want to get to the last data cell in a worksheet, press Ctrl-End. There is only one problem with this method, but it's a small one: Pressing Ctrl-End will take you to the last cell you modified. Let's say your worksheet uses only cells A1 to F20. But while you were
developing the worksheet, you entered a number into H25. Then you later deleted the number in H25 and confined your worksheet to the smaller area. If you press Ctrl-End, Excel will move to H25 rather than to F20.

SUMMING UP A ROW OF NUMBERS

Version 4.x, 95
Want to quickly sum a row of numbers in Excel? With all the numbers entered, click on the cell where you want the sum to appear. Now click the sum button on the toolbar (it looks like a capital M on its side). Use the mouse to select the numbers you want to sum, then press Enter. The sum will appear--and you didn't even have to type in a formula.

WORD, EXCEL, POWERPOINT: THE PROBLEM WITH COMPRESSED GRAPHICS

If you run an OEM version of Office for Windows Version 4.3c-CD, you may have a problem inserting pictures into a document. When you attempt a picture insertion in Word, you might get the message

Word cannot start the graphics filter.
(C:\WINDOWS\MSAPPS\GRPHFLT\GIFIMP.FLT)

where the filename shown in parentheses is the graphics filter name that you would use to read the picture file. In Excel, you might not get an error message, but the picture won't be inserted. In PowerPoint, you get the message

Sorry, C:\WINDOWS\MSAPPS\GRPHFLT\EPSIMP.FLT is not a valid filter. You should reinstall it.

This happens because some of the graphic filters that are installed during Setup are still in compressed format. These files are as follows:

CGMIMP.FLT
DRWIMP.FLT
EPSIMP.FLT
GIFIMP.FLT
PCXIMP.FLT
PICTIMP.FLT
TIFFIMP.FLT
WPGEXP.FLT
WPGIMP.FLT

You'll find all these files in the \Windows\MSApps\Grphflt folder.

WORD, EXCEL, POWERPOINT: RESOLVING THE PROBLEM WITH COMPRESSED
GRAPHICS

To eliminate the problem with compressed pictures described in the last tip, use the Extract utility to extract the graphic filter files. You'll find the files in the following directories/CAB files (on the Office CD):

CGMIMP.FLT SETUP.ADM\DISK27\MSOFF27.CAB
DRWIMP.FLT SETUP.ADM\DISK27\MSOFF27.CAB
EPSIMP.FLT SETUP.ADM\DISK27\MSOFF27.CAB
GIFIMP.FLT SETUP.ADM\DISK27\MSOFF27.CAB
PCXIMP.FLT SETUP.ADM\DISK27\MSOFF27.CAB
PICTIMP.FLT SETUP.ADM\DISK24\MSOFF24.CAB
TIFFIMP.FLT SETUP.ADM\DISK27\MSOFF27.CAB
WPGEXP.FLT SETUP.ADM\DISK20\MSOFF20.CAB
WPGIMP.FLT SETUP.ADM\DISK28\MSOFF28.CAB

To extract the files, first find and then copy EXTRACT.EXE to the root directory of your hard disk. Go to the MS-DOS prompt and type the following (all on one line) in this format:

extract /Y D:\ /L c:\windows\msapps\grphflt

D: is the drive that contains the Microsoft Office CD. To this, directly following the /L switch, add the path for the directory and .CAB file that contains the filter you need to extract. The line that begins c:\windows should be your Microsoft Windows directory and the name of the graphic filter you're extracting.

Let's suppose that you're extracting CGMIMP.FLT. You'd type on one command line

extract /Y d:\setup.adm\disk27\msoff27.cab /L
c:\windows\msapps\grphflt cgmimp.flt -

Note: Using the /Y switch will allow Extract to overwrite the existing compressed file. The space before the filename is important--make sure you don't omit it.

GRIDLINES AND OFFICE 95

If you link data in Word as an Excel worksheet, you won't see any gridlines. However, if you make changes to the linked worksheet in Excel, the gridlines will appear in Word. This happens because Excel creates a default printer (not screen) metafile, and Word therefore displays the sheet as it would print. If Excel is set to print gridlines, they will show in Word. To get around the problem, you can switch to Excel after you establish the link and retype a cell's contents. Word will then display it correctly.

Alternatively, you can set Excel to print the gridlines. To do this, choose File, Page Setup. Choose the Sheet tab and select Gridlines. Click OK.

VISUAL BASIC AND EXCEL 5.0: SENDKEYS AND NUM LOCK

If you attempt to use SendKeys to toggle the Num Lock key, you won't get an error message, but the key won't toggle. The word Num will appear in Excel's status bar, but the key will remain untouched. You can get around this problem by using the code

Sub CheckIt()

Application.SendKeys "{NUMLOCK}This is a Num Lock toggle test"

End Sub

When you run this procedure, Num Lock will be turned off (if it is on), but the keyboard light will not change. The text will be entered in the worksheet and the Num Lock key will return to its original state.

ERROR WHEN MODIFYING THE ENABLED PROPERTY

When you run a macro that attempts to set a built-in menu command or a submenu command's Enabled property, you'll get the message:

Run-time error 1004:
Application-defined or object-defined error

in Excel 97. In Excel 5.0 or 7.0, you'll get the message:

Run-time error 1005:
Cannot enable built-in commands.

This is not a bug. Excel doesn't allow you to change built-in menu and submenu commands.

DECIPHER DATE AND TIME SUBTLETIES

If you want to enter a date and time into a single Excel cell, type in the date, type a space, then type the time. Excel runs on 24-hour (military) time, so if you enter 2:00, Excel will assume that you mean 2:00 a.m. To enter 2:00 p.m., type the time, then a space, then the letter p. Of course, you can also type 14:00 to accomplish the same thing. By the way, 12:00 is noon. If you want to enter midnight, you can enter 0:00, 24:00, or 12:00 a. (Excel recognizes 24:00 as a matter of mathematical principle, even though it isn't traditionally used.)

MAKE A DATE WITH EXCEL

If you'd like to insert the date into the formula bar, press Ctrl-; (semicolon). To insert the current time into the formula bar, use Ctrl-: (colon). Bear in mind that the colon is a shifted character, so you actually press Ctrl-Shift-;.

CENTERING PRINT JOBS

If you have an Excel worksheet that occupies less than a page, Excel will by default print it in the upper-left corner of your paper. You can make it look better by forcing Excel to print it in the center of the page.

Choose File, Page Setup and when the dialog box opens, click the Margins tab. Now select the two check boxes (Horizontally and Vertically) under Center On Page. If you think your worksheet looks better when centered only horizontally, select the Horizontally check box and leave Vertically deselected. (Or vice versa.) Click OK and run to the printer.

PRINTING IN YOUR AREA

To set the print area (and assign titles) for your Excel worksheets, choose File, Page Setup. Click the Sheet tab in the Page Setup dialog box. Type in the Print Area box the reference of the cells you want to use as the print area. For example, you might use $A$1:$G$10. Next, go to Print Titles and enter the reference to the row you want to use as the title. Go to the Columns To Repeat At Left box and type the reference to the column you want to use as the title. Click OK.

Y2K Check

Versions 4.x, 95

Here's a test to see how Excel deals with the Year 2000. Try a calculation that goes past January 1, 2000; say you want to see how many days there are between two dates. Select cells A1 and A2 and then choose Format, Cells. Click Date (the type doesn't matter) and click OK. Now click on cell A4 and leave the format set to the default.

To check what happens when you make a calculation that crosses from 1999 to 2000, type into cell A2

12/25/99
Next, go to cell A1 and type

1/1/00
Move to cell A4 and type

=A1-A2
The result should be seven days.

FINDING THE COMMENTS

"I often get Excel worksheets sent to me by co-workers. In some cases, these worksheets contain notes. I know the notes are identified by a tiny red mark in the upper-right corner of the cell that has a note attached. But, this is the problem--that little red mark is too small. Is there a better way to locate all the notes in a worksheet?"

There are several ways to view all the notes. Open the worksheet and choose Insert, Note. When the Cell Note dialog box opens, you'll see all the notes in that worksheet. Another way to identify cells with notes is to press Ctrl-Shift-?. When you do this, Excel will select all the cells that contain notes.

SUM IN A ROW

Version 4.x, 95

Want to quickly sum a row of numbers in Excel? With all the numbers entered, click on the cell where you want the sum to appear. Now click the sum button in the toolbar (it looks like a capital M on its side). Use the mouse to select the numbers you want to sum, then press Enter. The sum will appear, and you didn't even have to type in a formula.

CHECK THE WHOLE THING

Version 95

"I always run the spell checker in Excel. The problem is that I have to spell check one sheet at a time. This is a pain when I have a large workbook to check. Do you know of any way to spell check an entire workbook?"

Yes, we do know of a way. Suppose you are currently on Sheet 1. Hold down Ctrl and click the tabs of all the worksheets that you want to check (there's no point in selecting empty worksheets). Now, just press F7 and the spell checker will examine all the selected worksheets.

To deselect the sheets, click any sheet tab other than the one you're currently viewing and Excel will navigate to that sheet--deselecting all the others.

ENTER A FRACTION

Version 95, 4.x

"Is there a problem with entering fractions into Excel? If I enter 1/2 into a cell, I get 2-Jan instead of 0.5."

When you enter a fraction in a form that Excel understands, you won't have a problem. For example, 1 5/8 or 2 3/16 won't cause any trouble at all. However, if you enter a single fraction that happens to match a date format, you'll get a date entry rather than a fraction as you have noticed.

To correct this situation, just enter a zero before your fraction. So, to enter 1/2, you'd type

0 1/2

Now the cell will display 1/2, and the formula bar will display 0.5.

HOW TO TEST OUT THOSE Y2K ASSURANCES

Microsoft has stated that all its current software is "2000-proof." This means your Excel calculations will work into and past January 1, 2000. However, if you want to see what happens in a specific program (Microsoft or other), all you have to do is set your computer's clock to 23:59:00 on 12/31/99, then wait a minute to see what happens when the new millennium appears. Now you can check all your calculations and see exactly what occurs.

CHECK THIS CELL

Version 95

"I am trying to find a way to add check marks to Excel cells. I know this sounds like a simple thing to do, but I have not had much luck trying to figure it out. I need to create a spreadsheet such that I can put a check mark in the appropriate cells to mark an entry. Can you offer some advice?"

If you select the Symbol font, then you can hold down Alt and type

0214

using the keyboard's numerical pad. This will produce a check mark in the current cell.

However, all this is difficult to remember, and cumbersome to perform, so why not write a macro to do the job for you? To generate the macro, choose Insert, Macro, Module. When the new module opens, enter the following exactly as shown.

Sub Check()

With Selection.Font

.Name = "Symbol"

.FontStyle = "Regular"

End With

ActiveCell.FormulaR1C1 = Alt + 0214 (Hold down Alt and enter 0214)

End Sub

The last line (ActiveCell.FormulaR1C1 = ) is where you enter the check mark symbol. Hold down Alt and use the keypad to enter 0214. Now, save your current workbook to make sure the new macro is saved.

To add a button to the Excel toolbar for your check mark macro, right-click the toolbar and choose Customize. When the Customize dialog box opens, select Custom from the Categories list. Now, drag one of the buttons to the toolbar. When you release the mouse button, the Assign Macro dialog box will open. Select your new macro and click OK to close the dialog box and record your macro assignment. Back in Customize, click Close to dismiss the dialog box.

All you have to do now to enter a check mark is select the cell you want to use and click your new macro button.

WHEN IT'S NOT NOT

Version 95, 4.x

"I have been trying to use Excel's NOT function for checking to see if two cells are the same. However, Excel reports False when the cells are the same and True when they are different. Is this weird? What is going on here?"

It sounds like Excel is probably doing its job. It's simply a matter of how you look at True and False. For example, enter

into cell A1

and

into cell A2. Now, go to cell A5, type in

=not(a1=a2)

and press Enter. Excel responds with

True

And this is correct, since 1 is not equal to 2. If you change cell A1 to

Excel will report

False

because 2 is equal to 2. And, if 2 is equal to 2, then it is definitely not NOT equal to 2. Follow?

PRETTY AS A PICTURE

Version 95

"I recently saw some Excel worksheets that appeared to have photographs placed in the sheet background. Could you explain how one might add backgrounds to Excel worksheets?"

Many Excel users like to add some kind of background to those special worksheets--the ones they show to the boss. Here's how to do it:

Let's say you have a picture, or perhaps a company logo, that you'd like to use as a background for a particular worksheet. Open the worksheet and choose Format, Sheet, Background. When the Sheet Background dialog box opens, locate your picture file and double-click it to insert the background and close the dialog box. Note that all files other than BMP will open a dialog box offering to convert the file. Click OK to continue. Excel tiles the background to cover the entire worksheet.

Since it's possible that you will later want to remove the background, all you have to do is choose Format, Sheet, Background. When the Sheet Background dialog box opens, click None.

HIDING SHEETS

Version 95, 4.x

Here's a tip on Office 95. This tip applies to Office 4.x as well:

"I like to place cells used by my formulas on a spare worksheet and then hide the worksheet so people can't see where I've placed the cells that the formulas use. To hide a worksheet, you navigate to the sheet and choose Format, Sheet, Hide."

If you'd like to see how this works, run Excel and open a blank workbook. Now, go to Sheet3 (click the Sheet3 tab) and enter

212

into cell A1. Next, click the Sheet1 tab and enter

=sheet3!a1

into cell A1. Cell A1 of Sheet1 will now display the contents of cell A1 in Sheet3 (212).

Go to Sheet3 again and choose Format, Sheet, Hide. Go back to Sheet1 and you'll see that hiding the worksheet has no effect on the contents of cell A1.

A RECORDING PROBLEM?

Version 95, 4.x

"I just happened across a very confusing problem with Excel. While I was recording a macro, AutoSave activated. After I started using the macro, I became aware that every time I ran the macro, the AutoSave dialog box would open. If this should happen to you, choose Tools, Macro. When the Macro dialog box appears, click the macro in question and then click Edit. What I found in my macro was the line

Application.Run Macro:=Range("AUTOSAVE.XLA!mcp01.AutoSavePreferences")

I deleted this line and then saved the worksheet. I have had no more problems."

CLICK TO SIZE

Version 95, 4.x

"I seem to remember reading about a quick way to size an entire column of data in an Excel worksheet. Could you please discuss this in one of your tips?"

We certainly can. Let's say you want to make the column fit the widest entry. You would move to that column's header and then place the mouse over the column separator on the right side of the column. When you're over the separator, the cursor will change to a double arrow. At this point, you can double-click to set the column to the width of the widest entry.

So, let's say you want to adjust column A. Move to the separator line to the right of the A. When the cursor turns into a double-arrow, double-click. Column A will adjust to the widest entry. Note that the width will decrease in some cases. For example, if all the entries in column A consist of two digit numbers, the column width will decrease to match the number widths.

CLICK TO SIZE

Version 95, 4.x

"I seem to remember reading about a quick way to size an entire column of data in an Excel worksheet. Could you please discuss this in one of your tips?"

DELETE A MENU COMMAND

Version 95

Here's an Excel 95 question:

"I'm in charge of more than 20 computers running Office 95. At least 90 percent of these computers are used to run Excel. I need to keep all the installations of Excel as nearly the same as possible. It occurred to me that I could go a long way toward stopping the changes that worry me if I could eliminate some of the menu commands. For example, Options in the Tools menu, and Toolbars in the View menu. Is there a way to eliminate these menu commands without causing a loss of functionality?"

Yes, you can delete menu commands. And, it's a fairly safe operation, since you can go back to the original command set whenever you want. There's the further advantage that the way to edit menus isn't especially obvious, so most of your users probably won't stumble across it.

To make sure your modifications apply to all worksheets, you'll need to make the changes in a workbook that's available to all. In most cases, this is a hidden workbook named Personal.xls that resides in the XLStart folder. To see if this is available to you, choose Window, Unhide. When the Unhide dialog box opens, you should see Personal.xls in the list. Select it and click OK to close the dialog box and unhide Personal.xls.

Now, using Personal.xls, let's eliminate the Options command from the Tools menu. Choose Insert, Macro, Module. When the module opens, choose Tools, Menu Editor. When the Menu Editor opens, look under Menus and click Tools to select it. Now, under Menu Items, select Options and then click Delete. Finally, click OK to close the editor and save your menu selections. Save Personal.xls and then choose Window, Hide to hide it again. When you close Excel, you'll be asked if you want to save the changes in Personal.xls. Click Yes.

At this point, the Options command will no longer appear in the Tools menu. However, if you need to restore the command, click the Module tab and choose Tools, Menu Editor. When the Menu Editor opens, click Restore All and then click OK to close the editor and apply your changes.

If you don't have Personal.xls, you'll need to create it. Open a blank worksheet and choose Save As. When the Save As dialog box opens, name the worksheet Personal.xls and then locate the XLStart folder. Click Save to save the file in the XLStart folder. The next time you run Excel, Personal.xls will open. Choose Window, Hide to hide it and choose File, Exit to close Excel. Click Yes when asked if you want to save changes in Personal.xls.

The next time you run Excel, Personal.xls will open as a hidden worksheet. You will need to copy the modified Personal.xls to the XLStart folder on each of the computers.

Questions?

Just Check out some of our sponsors

Shop at BestPrices.Com!

[ Home ] [ Up ]

MS EXCEL 95 and earlier

Send mail to CompanyWebmaster with questions or comments about this web site. Copyright © 2009 HALO Computer Technology Last modified: 03/11/09

Send mail to CompanyWebmaster with questions or comments about this web site.
Copyright © 2009 HALO Computer Technology
Last modified: 03/11/09