There are many methods for closing a workbook. Most methods involve choosing the Close command or clicking on a box. These work just fine for a couple of workbooks, but what if you have eight or ten workbooks all open at the same time? You could save a lot of time if you knew the shortcut for closing them all with one command. Simply press the Shift key and then choose File | Close All for the toolbar. The Close All command will only appear when the Shift key is if pressed. Now all workbooks will be closed simultaneously. You can choose to close and save all at one time or save them individually.

The Cobb Group's Inside Microsoft Excel 97 journal

If you find Excel's Web functionality helpful, but use it infrequently, you probably display and hide the Web toolbar as you need it. There's an easy way to take advantage of Excel's Web features without doing this, and you won't need to take up a lot of room with a dedicated toolbar. Most of the Web toolbar's features are available from the Go button's menu. By simply adding the Go button to a toolbar that's always displayed, you can have quick access to the Web address text box and navigation arrows, in addition to your search and home pages.

To do this, click the Web Toolbar button on the Standard toolbar to display the Web toolbar (in Excel 2000, choose View/Toolbars/ Web from the menu bar). Then, hold down the [Alt] key and drag the Go button up to either the Standard toolbar or Excel's menu bar. Finally, hide the Web toolbar by repeating the steps you used to display it.

ZD Tips

When you use Microsoft Excel to open a workbook or other file on the Internet, the World Wide Web, your Intranet, your network, or your local system, you can add the file to the Favorites folder so you can open it again quickly. Simply, open the Web toolbar and click Favorites, then click Add to Favorites. Now, to reopen the file quickly when you have another workbook open, click Favorites on the Web toolbar, and then click Open Favorites.

The ZD Journal's Inside Microsoft Excel journal

Excel's Form feature simplifies updating or adding information to a worksheet. Instead of having to scroll back and forth to view all the data fields in a list, you can see all of a record's fields at once. Unfortunately, the field width displayed in the form can sometimes be much smaller than your data, making it difficult to see information and effectively negating the usefulness of the form. This is an easy problem to resolve. The field widths displayed in a form are related to the widest column in your data list. Simply close your form, widen one of the columns to the desired size, and then choose Data/Form from the menu bar to redisplay the form.

ZD Tips

You have no doubt experienced the frustration of printing a worksheet only to discover an orphaned column printing on its own page. Unfortunately, Excel doesn't display automatic page breaks until after you've printed or chosen Print Preview. Getting into the habit of previewing your worksheets may not be a terrible inconvenience, but you can set up Excel to save you the trouble. First, select Tools/Options from the menu bar. Then, click on the View tab and select the Page Breaks check box (Excel 95: Automatic Page Breaks) in the Window Options panel. Finally, click OK. From now on, Excel always displays automatic page breaks in your worksheets, allowing you to easily format your data correctly before printing.

ZD Tips

Are you a bad typist? Don't be shy--we know there are a few of you out there. Well, Excel has a feature for you: the Auto Correct feature. To access this feature, select the Tools | AutoCorrect menu bar option. This will bring up the AutoCorrect dialog box. Your insertion point will be positioned in the box entitled Replace. Simply type (as usual) the wrong word in this section. For instance you can type paralell in this box. Now press [Tab] to go to the box entitled With, and in this box type parallel. As soon as you enter data into the With box, you'll notice that the Add button becomes available. Click this button. You're done. You can add all your favorite mistakes so they'll automatically be corrected for you. If you make a mistake entering the data in either the Replace or With box, select the entry in the list and click Delete. When you're done making changes in the AutoCorrect dialog box, click OK to exit.

ZD Journal's Inside Microsoft Excel journal

Excel's AutoCorrect feature corrects misspelled words as you type them. The AutoCorrect list consists of typos most people commnly make, but your personal typing habits may produce additional repetitive typos. You can easily add these to the AutoCorrect list as you come across them during your regular spellcheck procedure. To do this, start spell checking like usual. When Excel stops at a typo, enter the correct value in the Change To text box and click AutoCorrect. When you've finished, close all the open dialog boxes. From now on, when you type the incorrect entry Excel will automatically fix it.

ZD Tips

You probably thought the names you chose for your Excel workbooks were incredibly descriptive six months ago, but you'll most likely find that when you need to find some specific data you're stuck opening and closing a myriad of files because you can't remember what's in each workbook. There's an easy way to avoid this time-waster: Save preview pictures of your workbooks when you save them. This allows you to see a snapshot of the workbook in Excel's Open dialog box. To use this feature, open the workbook you want to save a preview picture for. Then, select File/Properties. On the Summary tab, select the Save Preview Picture check box. Finally, click OK. To see the preview picture when you're searching through files in the Open or Save As dialog boxes, just click the Preview icon (it's the second icon from the right at the top of the dialog box). When you click on worksheets that have saved pictures, you'll see the snapshot in the preview pane.

ZD Journal

If you've ever needed to fit more columns onto a page by rotating your text to an angle or just wanted to add visual appeal to a spreadsheet, you'll want to try this feature. Until the release of Excel 97, text in a spreadsheet always appeared horizontally. Now it's possible to change the angle of the text in your worksheets. First, choose the cell or range of cells that you want to rotate by highlighting them. Next, Choose Format \ Cells from the menu bar and click the Alignment tab on the Format Cells dialog box. Now adjust the angle of the text by dragging the orientation line in the direction that you want to angle your text and click OK. You can also specify the angle by typing the angle in degrees.

The Cobb Group's Inside Microsoft Excel 97 journal

There's an easy way to edit the text or graphic in a hyperlink. Begin by selecting the cell or graphic for the hyperlink. To do so, select a cell that contains a text hyperlink, click a cell next to the hyperlink, and then use the arrow keys to move onto the cell. To select a graphic, hold down the [Ctrl] key and click the graphic. If you just need to change text, you can easily edit the text in the formula bar. If you need to change a graphic, you'll need to use the Drawing or Picture toolbar. However, if the text is part of a graphic, right-click on the selected graphic, and then choose Edit Text from the shortcut menu. To change the picture for a hyperlink, insert the new picture and make it a hyperlink with the same destination. Then delete the old picture and hyperlink.

The ZD Journals' Inside Microsoft Excel journal

If you want all the columns in your worksheet to be the same width, first click the Select All button, which is the rectangle in the upper-left corner of your worksheet at the intersection of the row and column headings. Then, move the mouse pointer to the line that separates any column headings. When the mouse pointer changes to a resizing tool, click and drag to resize all the columns on your sheet. If you want to resize just a group of columns, select their column headings and then use the resizing tool. These techniques work for changing row heights as well.

Inside Microsoft Excel

As the saying goes, old habits are hard to break. If you've spent years navigating through a program using the [Enter] key, you may be frustrated at the way in which Excel moves your cell selector. For instance, if you've used a program where the [Enter] key moved horizontally through cells or fields, you may be disappointed to find that you have to use the [Tab] key instead because [Enter] moves the cell selector down the column. Or, you may want [Enter] to simply commit your entry and not move to a new cell at all. Fortunately, this is easy to configure in Excel. Just open the Tools menu and select Options. Then, click on the Edit tab. If you don't want the cell selector to move when you press [Enter], deselect the Move Selection After Enter check box and click OK. Otherwise, make sure Move Selection After Enter is selected, choose which cell you want to make active from the Direction dropdown list, and click OK.

Inside Microsoft Excel

If you have several workbooks open and you want to close them without quitting Excel, doing so can be tedious. Fortunately, you can quickly close multiple files in Excel. To do so, just press the [Shift] key and open the File menu. You'll find that the usual Close command has been replaced with Close All.

ZD Tips

Despite all of the paperless office hype, there are probably a lot of times that you want to review hardcopy of a worksheet before generating your final output. Now that color inkjet printers are commonplace, it's standard to make the most of Excel's shading and graphics capabilities. However, you don't want to waste a lot of time and ink printing a document that you know will inevitably wind up in the trash.

The solution is to print a lower-quality version of your file. Unfortunately, constantly changing your printer settings can be cumbersome, and Excel's Draft feature may be too low quality; in addition to ignoring color shading, Excel removes gridlines, objects, and graphics from the printout. A happy medium is to print in black and white. Excel removes cell shading, but leaves gridlines and objects intact. Additionally, instead of spending time converting chart colors to grayscale, Excel replaces the colors on the printout with different patterns. To print in black and white, select File/Page Setup from the menu bar. Then, click on the Sheet tab, select the Black And White check box, and click OK.

ZD Tips

If you share your computer with other users, you may want to prevent others from knowing what files you've recently been working on - not that you'd be using your work PC for anything inappropriate, of course, but just to preserve a sense of privacy. You can easily prevent recently used files from showing up in the File menu. First, select Tools/Options. Then, click on the General tab and deselect the Recently Used File List check box. On the other hand, if you'd rather see more recently used files in the File menu, you can change that option here as well. Make sure the Recently Used File List check box is selected and enter a number from 1 to 9 in the Entries text box. When you've finished making changes, click OK.

ZD Journal

One of our readers, Barry Kavy, recently emailed us asking how to force Excel to intelligently wrap text used in chart legends. Typically, you're at Excel's mercy. For example, if you have a column labeled History: First Contact, Excel may display the legend as:

History: First
         Contact

However, you'd probably prefer the label formatted as

History:
First Contact

You can accomplish this when Excel pulls the legend text from a worksheet cell. When you enter the text, simply press [Alt][Enter] wherever you want to force a carriage return.

ZD Tips

You can change what happens when you start Excel by specifying a startup switch. For example, you can open a specific workbook, prevent Excel from displaying the startup screen and default blank workbook, and specify the default working folder. To automatically open one or more workbooks each time you start Excel, you can store the workbooks 3/4 or shortcuts for the workbooks 3/4 in the Excel startup folder. To open a workbook stored in another folder or on a shared network drive, you can specify an alternative startup folder.

The ZD Journals' Inside Microsoft Excel journal

Excel 97 offers many new drawing features. Now you can change a one-dimensional object into a 3-dimensional object quickly and easily. Not only can you make it 3-D, but also you can choose the angle and shading of the object. Start by selecting the object and selecting the 3-D tool (which looks like a 3-D box) located on the drawing toolbar. On the 3-D tool box there are 20 different angles to choose from. At the bottom of the toolbox the 3-D Settings option appears. If you choose it, you can change the lighting, the surface, and many other attributes. After you choose your 3-D option, you will see that the object you have selected now appears in 3-D.

The Cobb Group's Inside Microsoft Excel 97 journal

You may occasionally come across numbers in Excel that were entered with a preceding apostrophe ('). This formats the number as text. If you want to convert the value to a genuine number, you may find that the task isn't as easy as you'd suspect. You might logically assume that changing the cell's format to a number format would do the trick. Unfortunately, such is not the case. If you only need to convert one or two values, you can just re-enter them. However, for a large volume, there's a more efficient way. First, enter the number 1 in any blank cell. Select the cell and choose Edit/Copy from the menu bar. Then, select all the cells containing values you want converted. Then, choose Edit/Paste Special from the menu bar, select the Multiply option button, and click OK.

ZD Tips

If you've ever needed to remind yourself what a formula does or to document some value of interest, there's a quick way to add a reminder to a cell. You can begin by selecting the cell that contains the value that you want to attach a comment to. Then, choose Insert \ Comment from the menu bar. A text box that contains your name will appear. You may now type your comment into the text box. You can move the text box to any location by simply dragging it and dropping it. To locate a cell that contains a comment, look in the upper right corner of the cell for a red triangle. To view the comment, simply highlight the cell.

ZD Journal's Inside Microsoft Excel journal

If you use Excel's comment feature, chances are you spend time wiping out the author information that's automatically inserted at the beginning of each note. Unless you're working on a collaborative project, you probably don't have a need for this information--you just want to display your comment. One of our readers pointed out that you can change the name by changing Excel's User Name setting. (To do so, select Tools/Options and edit the User Name data on the General sheet.) However, if you completely delete the User Name value and close the dialog box, Excel just changes the user name back to the original name registered on your PC. To get around this, you can use the macro below instead of inserting comments like you usually do. The macro prompts you for your comment text and then inserts your note in a new comment attached to the current cell.

Sub NewComment()
    On Error Resume Next
    strComment = InputBox("Enter your comment: ", "Add Comment")
    ActiveCell.AddComment
    ActiveCell.Comment.Visible = False
    ActiveCell.Comment.Text Text:=strComment
End Sub

ZD Tips

If you've ever wanted to create a formatting style that's different than any of the currently available styles, you'll be glad to know that there's an easy way to do it. First, select the cell that has the combination of formats that you want to include in the new style. Then, choose Style from the Format menu and in the Style name box, type a name for the new style. To define and apply the style to the selected cells, click OK. To define the style without applying it, click Add, and then click Close. Also, if no cells have the formats you want for the style, you can specify the formats when you name the style.

The ZD Journal's Inside Microsoft Excel journal

Here's a quick way to insert the current date in your worksheet:

[Ctrl];

To insert the current time, press [Ctrl][Shift];

These shortcuts can be performed in the same cell, and even in the middle of a text string.

Steve Jenkins

As larger monitors become more and more commonplace, it's likely you'll want to use higher monitor resolutions to take advantage of the additional viewing real estate. However, although you're able to see more on your monitor with a higher resolution, some things may actually be more difficult to see. For example, toolbar icons may appear much smaller than what you're used to. If you find yourself squinting at your icons, you can postpone a trip to the optometrist by using larger icons. To use large icons, right-click on any of the Excel toolbars and select Customize from the shortcut menu. Then, click on the Options tab, select the Large Icons check box, and click Close.

ZD Journal

If you're more of an analyst than an artist, adding shapes to your Excel worksheets can be a source of frustration. Your circles look like eggs, you agonize over creating squares with equal dimensions, and you can't draw a straight line to save your life. Fortunately, Excel includes help for the artistically challenged. Simply select the drawing tool you want to use, press the [Shift] key, and click and drag to create your shape (if your drawing tools aren't visible, select View/Toolbars/Drawing). Using the Oval tool creates a circle, the Rectangle tool creates a square, and the Line and Arrow tools create perfect horizontal and vertical lines, as well as lines in 15 degree increments from their starting points. Holding the [Shift] key even helps create proportional AutoShapes.

The ZD Journals' Inside Microsoft Excel journal

Excel makes it easy to add commands to your menus. First, select the toolbar that contains the menu you want to add a command to. Next, Open the Tools menu, choose Customize, and then click the Commands tab. Then, in the Categories dialog box, click the category for the command.

Now, simply drag the command you want from the Commands box over the menu on the toolbar. When the menu displays a list of menu commands, point to the location where you want the command to appear on the menu on the toolbar, and then release the mouse button.

The ZD Journals' Inside Microsoft Excel journal

Previous versions of Excel had both a header and a footer by default. In Excel 97, the default is no header and no footer. However, if you want to add a header or a footer, here's what you do. From the menu bar select File \ Page Setup. When the Page Setup dialog box appears, click on the Header/Footer tab. You can select a pre-set header or footer by clicking on the drop-down arrows. If you want to type in your custom header or footer, click on the customize header or customize footer buttons, type the information that you want to appear in the header or footer and click OK.

The Cobb Group's Inside Microsoft Excel journal

If you've ever tried to change the text in a worksheet hyperlink, you may have found the process very tedious. Ordinarily, an Excel hyperlink is followed as soon as you click in the cell containing it. You may be aware that you can avoid this by clicking in an adjoining cell and then moving to the hyperlink with the arrow keys. However, there's an easier way. Just hold the [Ctrl] key and select the hyperlink. You'll be able to enter the new hyperlink text directly in the cell and not have to worry about triggering the link.

ZD Tips

You've probably run into situations in which you need to re-arrange the order of the columns in a worksheet. However, you may not be going about it in the most efficient manner. Rather than enduring the laborious process of inserting a blank column and then cutting and pasting the data, there's a much easier way. First, select the columns you want to move. Move your mouse pointer to the edge of the selection until it changes from a cross to a regular pointer arrow. Now, press [Shift] and then click and drag the column to the new position. You'll see a faint "I" bar that runs the entire length of the column, along with a box indicating what the new address will be. Simply release the mouse button before releasing the [Shift] key, and the column is moved without overwriting any data. Note that the same basic technique also works with rows.

Bill Garman

Excel has lots of useful shortcut keys that perform various actions. The more you know, the more time you can save. Here are seven shortcuts we hope you find useful:

To enter the current date into the active cell, press [Ctrl];.

To enter the current time, press [Ctrl][Shift];.

To enter or edit a cell comment in the active cell, press [Shift][F2].

To quickly copy an entry into a cell directly to the right, select that cell, then press [Ctrl]R.

To quickly copy an entry into a cell directly below it, select that cell, then press [Ctrl]D.

To copy an entry from a cell directly above the active cell, you can press [Ctrl]'. If the entry is a formula, this shortcut makes an identical copy of that formula, rather than adjusting the cell reference relatively.

Pressing [Ctrl][Shift]' also copies an entry from the cell directly above the active cell. However, if the entry is a formula, this shortcut copies the result of the formula only rather than the formula itself.

An easy way to view all formulas in a worksheet without having to select the cell containing it is to press [Ctrl]~. Press the shortcut again to switch back to the normal view.

The Cobb Group's Inside Microsoft Excel 97 journal

If you've ever wanted to enter many numbers with a fixed number of decimal places or large whole numbers with a fixed number of trailing zeros (0). You'll be glad to know that there's an easy way to do it. Begin, by opening the Tools menu and choosing Options. Now, then click the Edit tab and select the Fixed decimal check box. Next, in the Places box, enter the number of digits you want to appear to the right of the decimal point. If you want to include zeros (0) automatically when you enter large whole numbers, specify a negative number to indicate the number of zeros. For example, to have Excel automatically insert 125000 when you enter 125, enter -3 in the Places box. Now, Excel inserts a decimal point at the indicated position or inserts the specified number of zeros. However, data you entered before selecting the Fixed decimal option is not affected. Now, when you finish entering numbers with decimal places or zeros, simply return to the Tool menu, choose Options, click the Edit tab and remove the check in the Fixed decimal check box.

ZD Journal's Inside Microsoft Excel journal

If you want to apply the same formatting to every sheet in a workbook, you don't need to tediously perform the same steps on each worksheet. To apply formatting to an entire workbook, right- click on one of the worksheet tabs and then choose Select All Sheets. At this point, any formatting you apply or text you enter will be applied to all the sheets. To ungroup the sheets, just click on a worksheet tab or right-click on a tab and choose Ungroup Sheets from the shortcut menu. If you want to exclude some sheets from the changes, click on the tabs of the worksheets you want to exclude while holding the [Ctrl] key. Note that you can also group sheets by holding the [Shift] key and selecting the worksheet tabs.

ZD Tips

The Formula Palette is a great tool for building formulas with functions you may not be familiar with. To access the Formula Palette, just click the equal sign (=) in the Formula Bar and select the function you need to work with from the dropdown list to the left of it. You can then use it to define the arguments for the function. However, one drawback to the Formula Palette is that it may obscure the range of cells you're working with-- particularly if the function has many arguments. Fortunately, the palette isn't docked in place. Simply drag it with your mouse to a section of your worksheet that's out of the way of your data.

ZD Tips

If you're entering a long formula or text string, you may want to insert carriage returns to make everything more readable. Here's how: Just press [Alt][Enter] and your insertion point moves down to a new line. This works when you're entering in the formula bar or directly in a cell.

Inside Microsoft Excel

You'll often want to zoom out the view of your worksheet to get a better look at your spreadsheet's layout. Unfortunately, your bird's-eye view of the data can make keeping track of the information an optometrist's nightmare--even at just a 50% zoom. Ironically, you might be able to prevent some eyestrain by zooming out even more. To do so, you'll first need to use named ranges in your worksheet. Also, you must be in Normal view, not Page Break Preview. When you're ready to zoom out your view, select View/Zoom from the menu bar. Select the Custom option on the Zoom dialog box, enter 39 in the percentage text box, and then click OK. The range names appear in faint blue lettering in the appropriate places. Note that the range names only appear on the screen; they won't print with your data.

ZD Tips

There's nothing more frustrating than wasting time. When you're ready and raring to go (as you probably are every day at work) sitting through Excel's opening splash screen isn't the best use of your time. After all, it's pretty likely you remember what spreadsheet application you're using - you don't need to be reminded. Fortunately, you can speed things up by disabling the splash screen. There is one caveat: when you disable the splash screen, Excel no longer displays a blank workbook after the program starts.

To disable the splash screen, right-click the Windows Start button and select Explore. Then double-click the Programs folder in the right pane and locate the shortcut for Excel. Right-click on the shortcut icon and choose Properties. Next, click on the Shortcut tab. In the Target text box, place your insertion point after all of the path information. Enter a blank space followed by /e. Finally, click OK. From now on, you won't see the Excel splash screen when starting the application from the Start menu. Just think of the possibilities! With the three seconds of your life that you'll recover each time you start Excel, you'll be more productive, which can get you noticed by your boss, which can lead to that big raise...

ZD Tips

One of the tasks that users commonly perform in Excel is converting formula results to fixed values. To do this, you probably copy the formula and then use the Paste Special dialog box to paste the permanent values over the original formulas. However, there's a much easier way that doesn't require any trips to the menu bar or Paste Special dialog box. First, select the range of formulas you want to convert. Then, using the right mouse button, drag the selected range one cell in any direction, and then return the range to its original location. When you release the mouse button, Excel displays a shortcut menu. Simply select Copy Here As Values Only (Copy Values in 95) to make the formula results permanent.

ZD Tips

If you've ever needed to have cells that were different sizes, you've probably tried changing the column width, but that only changes the cells horizontally. If you want to change many different cells' sizes both vertically and horizontally, you'll want to try merging the cells. To merge cells, start by selecting the cells that you want to merge. Next, choose Format \ Cells from the menu bar. Then, click the Alignment tab, choose the merge cells checkbox and click OK. If you are attempting to merge two cells that both contain values, you'll receive a warning message that states that if you merge the two cells, only the data in the upper-left cell will be kept.

ZD Journal's Inside Microsoft Excel journal

Excel 97 contains many new features for working with charts. One of the most convenient new features allows you to move an embedded chart to another worksheet. Start by simply selecting the chart and choosing Chart \ Location from the toolbar. Next, in the Chart Location dialog box select As an Object. Then choose the location by clicking on the drop-down arrow or typing the location name and click OK. Now your chart will be moved to the location that you specified.

The Cobb Group's Inside Microsoft Excel 97 journal

When you pass your mouse pointer over the lower right corner of a cell, you'll notice that your mouse pointer shape changes. When the mouse pointer changes to a white arrow, you can click on the contents of the cell, and drag and drop the information to a new cell. If the mouse pointer changes to a bold plus sign (+), you can copy that information, either sideways or up and down, by dragging your mouse pointer over the number of cells you want to copy to and releasing the mouse button.

The ZD Journals' Inside Microsoft Excel journal

When you use Excel's IF worksheet function to compare two strings, the comparison is case insensitive. For example, assume you have a worksheet with the value John in cell A1 and JOHN in cell B2. The formula:

=IF(A1=B1,"Same","Different")

returns the result Same.

However, if you try to perform the same comparison in a VBA macro, the IF comparison is case sensitive. For example, the following macro

Sub Comparison( )
    If [A1] = [B1] Then
        result = MsgBox("SAME", vbOKOnly)
    Else
        result = MsgBox("DIFFERENT", vbOKOnly)
    End If
End Sub

returns the result DIFFERENT. You can make your macro comparisons case insensitive by using the StrComp( ) function.

Sub Comparison( )
If StrComp([A1], [B1], 1) = 0 Then
        result = MsgBox("SAME", vbOKOnly)
    Else
        result = MsgBox("DIFFERENT", vbOKOnly)
    End If
End Sub

This function compares two strings and, as long as neither string is Null, returns an integer from -1 to 1. The 1 argument in the StrComp( ) function forces a textural comparison. An answer of 0 means the first string is equal to the second string.

John E. Curtis

Do you ever have to sort data in order to determine how many of one thing is in a column? This is best illustrated by example. For instance, you have a column of people's names (heading--Names) and they've responded either yes or no to a question, and that y/n data is in a second column (heading --Responses). Now, you want to determine how many people answered yes and how many people answered no. Before you count the information yourself, let me tell you that Excel has a feature just for you--the Pivot Table. Simply click the Pivot Table Wizard button and let Excel guide you through the four Pivot Table setup dialog boxes. When you reach the third dialog box, make sure you enter the Count of Responses in the data field. When you're finished, your results will look similar to that below, except that the data will be in table form.

Count of RESPONSES
--------------------
RESPONSES      Total
--------------------
N              3
Y              5
--------------------
Grand Total    8

No more manual counting those data regions; simply use the Pivot Table Wizard to your advantage.

The ZD Journals' Inside Microsoft Excel journal

Macros are supposed to make your life easier, but sometimes they may get in the way. For instance, you may have workbooks that automatically run macros when they are opened. When you need to quickly check out some data as you're rushing off to a meeting, sitting through the macro can seem to be an eternity. Fortunately, you can easily prevent startup macros from running--simply hold down the [Shift] key when you open the file. Note that you'll need to keep the [Shift] key pressed throughout any warning dialog boxes that may appear during the process.

ZD Tips

The request to insert the full pathname into a workbook is one of the most frequently asked Excel questions. Unfortunately, Excel doesn't make this as straightforward as it should be. Excel's filename code only inserts the workbook name in your header or footer, not the full path. You can accomplish this with a macro; however, there's an even easier way. The caveat is that the first row of your worksheet will need to be repeated on all pages, so this technique may not be suited for all tasks.

First, select a cell within one of the rows you'll repeat (A1 is a good one to use). Then, enter the formula

=cell("filename")

Next, select File/Page Setup. Click on the Sheet tab, set the Rows To Repeat At Top to the appropriate rows you used, and click OK. Save your file and then press [F9]. From now on, the full pathname will appear at the top of each page of the printed worksheet.

Inside Microsoft Excel

If you'd like to add or subtract certain toolbars from your working Excel session, bringing up your choices is as easy as a single mouse click. Position your mouse pointer over any open toolbar and right-click your mouse. Poof--the toolbars menu appears, and by clicking your mouse on any option, you can select (or de-select if it's already checked) any toolbar option.

The ZD Journals' Inside Microsoft Excel journal

You may know that you can easily make your worksheet columns adjust to the width of your data by selecting the column and choosing Format/Column/AutoFit Selection. However, you can also do this by double-clicking on the line that separates the column headings. The column width will automatically expand or shrink to accommodate the longest entry.

ZD Journal's Inside Microsoft Excel journal

If you ever need to change part of a formula so that the cell references are absolute instead of relative, you've probably edited it and manually typed in the required dollar signs. However, there's a much easier way. Simply edit the formula and place your insertion point on the cell reference. Then, press the [F4] key to cycle through the various cell reference formats you can use:

$A$1
A$1
$A1
A1

ZD Journal

PivotTable reports provide a convenient and powerful summary of Excel data. However, there may be times that you want to see the detail behind a particular summary. To quickly see the source data, double-click the cell containing the summary you want to examine. Excel creates a new worksheet and lists the appropriate data. This feature works even if the worksheet with the source data is hidden. In Excel 97 and 2000, you can disable this feature if you want to prevent users from seeing the detail behind a PivotTable. To do so, select a cell in the PivotTable and then choose Options (Table Options in 2000) from the PivotTable menu on the PivotTable toolbar. Clear the Enable Drilldown check box. Finally, click OK.

ZD Tips

Here's an easy way to view data stored in a part of your workbook that isn't visible to you while you're working. First, select the data you want to reference and then choose Edit/Copy from the menu bar. Then, go to the place you need to work on in your spreadsheet. Press the [Shift] key and open the Edit menu - you'll see that some of your regular choices have been replaced. Select Paste Picture Link from the Edit menu. The picture is placed over your worksheet without affecting any of the data underneath. Best of all, if the original source data is changed, the picture updates to reflect the change.

Ian Cuthbertson

Excel 97's Web Query feature allows you to grab information from a particular Web site and display it in your workbook. The program includes some sample Web queries you run can right away. To demonstrate, open a new workbook and issue the Data/Get External Data/Run Web Query... command. The Run Query dialog box will appear, displaying a list of predefined queries. Select the one named Detailed Stock Quote by PC Quote, Inc.iqy and then click the Get Data button. In the next dialog box, choose Existing Sheet radio button, type A1 in the text box, and click OK. When the following dialog box appears, type the symbol for the stock in question. For instance, to query data for Ziff-Davis' stock, type ZD, then click OK. Wait a moment while Excel connects to the Internet and grabs the statistics for that stock. Once it's finished, you'll see formatted price information appear in your worksheet. Try experimenting with the other queries in the Run Query dialog box. To learn how to create your own Web Query (.iqy) files, refer to the Microsoft Knowledge Base article at:

http://support.microsoft.com/support/kb/articles/q157/4/82.asp

To learn more about queries, refer to the information at the Microsoft site:

http://www.microsoft.com/excel/webquery/

The Cobb Group's Inside Microsoft Excel 97 journal

If you routinely need to work with the same set of Excel files, use a saved workspace for quick access to them. When you open a saved workspace, all the Excel files grouped in it are opened. To create your workspace, open all the Excel files you want included in it. Then, select File/Save Workspace. When the Save Workspace dialog box appears, enter a name in the File Name text box and click Save. Excel creates an XLW file with the name you specified. At this point you can close all your files. To open them back up as a group, simply select File/Open and select the XLW file.

ZD Journal

If you're constantly switching between sheets in an Excel workbook, try these keyboard shortcuts instead of using your mouse:

[Ctrl][Page Down] moves to the next spreadsheet

[Ctrl][Page Up] moves to the previous spreadsheet

[Ctrl][Shift][Page Up] or [Ctrl][Shift][Page Down] moves and selects spreadsheets.

The ZD Journals' Inside Microsoft Excel journal

You may already know that comments are a great way to leave user instructions that pertain to a specific cell. To do this, click in the desired cell and choose Insert/Comment. Then, type your message and click outside of the comment box. Excel indicates that a cell contains a comment by inserting a small red triangle in the upper- right corner of the cell. To view the comment, you ordinarily have to hover the mouse pointer over the triangle. However, you can configure Excel to always display comments. Select Tools/Options from the menu bar and click on the View tab. Then, choose the Comment & Indicator option in the Comments panel and click OK. If you find the comment boxes get in the way of a particular bit of data, just drag them to a new position -- an arrow will continue to anchor the comment to its originating cell.

ZD Tips

Have you ever printed a worksheet that had very little data on it, but that produced lots of blank pages? Or tried to print a large worksheet and only received output for a small range? Chances are somebody changed the print area for the sheet. Normally, Excel prints the range that accommodates all your data; nothing more and nothing less. Selecting a print area overrides this. To reset Excel to print all your data, select File/Print Area/Clear Print Area.

If you want to set up a print area so you can consistently print just a specific part of a worksheet, first select the area you want to print. Then, choose File/Print Area/Set Print Area. From now on, only the selected range will be printed.

ZD Journal's Inside Microsoft Excel journal

If you like to hide the cells that contain formulas in your workbooks, you know that hiding rows and columns is usually a tedious process, forcing you to drill through multiple menus. Use these shortcuts to quickly hide rows and columns:

Hide rows       [Ctrl][9]
Hide columns    [Ctrl][0]

To redisplay the hidden data, select the surrounding rows or columns and use these keyboard shortcuts:

Unhide rows     [Ctrl][Shift][9]
Unhide columns  [Ctrl][Shift][0]

ZD Tips

If you're like a lot of people, you're constantly fiddling with your worksheet's column widths to suit your immediate needs. Unfortunately, this sometimes can cause distracting readability problems. For instance, suppose you want to decrease the width of a column just slightly. You may find that when you do, the last few letters of the column label are no longer visible. If you use the Wrap Text feature, Excel may break the label in odd places. You could solve the problem by changing the font size for the affected cell, but that gets tedious if you frequently resize the columns. A more effective method of resizing the cell contents is to use the Shrink To Fit feature. This feature, as you've probably guessed, shrinks the cell contents to whatever size is necessary to display everything in the cell. As you shrink or widen the column, the cell's contents size adjusts accordingly. When you later widen the column, the cell contents returns to the appropriate font size. To apply this Text Control, select the appropriate range and choose Format/Cells from the Excel menu. Then, click on the Alignment tab, select the Shrink To Fit check box, and click OK. Note that this feature is unavailable if you have the Wrap Text option activated or if you're using Justify or Fill alignment.

ZD Journal

Sometimes it's helpful to view different parts of a worksheet at the same time. You can easily split the view of your worksheet into scrollable panes. To create a horizontal split, drag the split box (the small rectangle that's directly above the vertical scroll bar) to the position on the worksheet where you want the split made. To create a vertical split, drag the split box that's to the right of the horizontal scroll bar. To remove a split, just double-click on the split line.

Theodore B. Davis III

If you've ever wanted to double underline the contents of a cell, you probably selected Format/Cells from the menu bar, chose Double from the Underline dropdown list, and clicked OK. That's quite a bit of work for such a minor formatting change. You can achieve the same results simply by pressing the [Shift] key and then clicking the Underline button on the toolbar.

Priscilla Lawler

If you want to rearrange a column of Excel data into a row or vice versa, you can use one of two Transpose features: The Paste Special... command or the Transpose() function. To use the Paste Special... command, first copy a columnar range of data, such as cells A1:A10. Then, select the leftmost cell in a row where you want the results to be displayed, such as B14. Issue the Edit/Paste Special... command, select the Transpose check box, and click OK. Excel will create a copy of the entries only laid out across cells B14:K14.

To use the Transpose() function, first you select the range B14:K14 and enter the function =Transpose(A1:A10) as an array. To do this, hold down the key combination [Ctrl][Shift] as you press [Enter]. The resulting rowwise array will display the data in cells A1:A10. The advantage of using the Transpose() function rather than the Paste Special... command is that the formulas remain linked to the original cells.

The Cobb Group's Inside Microsoft Excel 97 journal

If you use Excel to solve complex mathematics, science, and statistics problems, you should check out the Website:

http://sunsite.univie.ac.at/Spreadsite/

This page features a rich collection of resources and links to sites containing information about using spreadsheets with an emphasis on mathematics and statistics education.

The Cobb Group's Inside Microsoft Excel 97 journal

If you have ever tried to fit a really long word or a couple of words into a cell and still try to make them legible, and not microscopic you'll want to check out the Shrink to Fit Command in Excel 97. First, begin by selecting the cell or cells that you want to shrink. Next, choose Format \ Cells from the toolbar. Then, when the Format Cells dialog box appears, click on the Alignment tab and check the box labeled Shrink to Fit. After you click OK, the selected text will fit into the cells.

The Cobb Group's Inside Microsoft Excel 97 journal

You may know that you can quickly move your cell selector to the last or first cell in a filled column by pressing [Ctrl] and the appropriate directional arrow. This also works for moving to the last or first cell in a filled row ([Ctrl] and the left or right arrow key). However, a lesser-known feature of Excel is the ability to do this with just your mouse. All you need to do is double-click on the border of the cell selector. Excel will move the selector to the last filled cell in the direction corresponding to the edge you clicked on.

Kees Brantjes