Creating your own formatting style

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.

Is there a way to get stock information imported into my spreadsheet?

Yes, you can import updated stock information from the Internet into Microsoft Excel 97. For example, to get Dow Jones stock quotes:

1. From the Data menu, choose Get External Data and then Run Web Query.
2. Highlight one of the available query files, for example "Dow Jones Stocks by PC Quote, Inc", and click Get Data.
3. In the dialog box, select an option and a cell location where you want the quote to appear, and click OK.

Easily modify a range reference in a formula

If you've ever tried to edit a range reference in a formula, you know that Excel 97 color-codes the reference and puts an outline that corresponds with the color in the formula around the cells in the range. The easy way to modify this range is to begin by selecting the cell with the formula and pressing [F2]. Now, locate the color-highlighted border that is around the cells in the range. While holding the mouse button down, drag the fill handle until you've enclosed the new range and press [Enter]. Excel will now change the cell reference in the formula to the new range that you specified when you moved the color-coded border.

Create a quick reminder by adding a comment to a cell

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.

Make your cells any size by merging them

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.

Change the orientation of your text

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.

Convert a 1-D Object into a 3-D object quickly

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.

Transposing data in Excel

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.

Importing Microsoft Money 98 data into Excel

If you use Money 98 to manage your finances, you might be interested to know that you can bring your data into an Excel workbook to analyze,
manipulate, and chart your data in ways possible only in Excel.
To import your data into Excel, you must download and install Money Link to Excel,
which imports data from Money 98 Financial Suite and Money 98 Financial Suite Trial Edition as well. Once you install Money Link to Excel, you'll
see new commands in Excel that you can use to start the Money Link to Excel Wizard and synchronize data that you may have already imported. And if you make changes in Money 98, you can easily update the workbook data by clicking a button. To download or learn more about Money Link to Excel, go to www.microsoft.com/money/excel/readme.htm.

Use the Calendar control on your VBA forms

One way to avoid date validation routines on your VBA forms is to use the Calendar control, which looks just like a calendar. A user can choose a date by simply clicking on that date on the Calendar control. To add the Calendar control to a user form, you first have to add the calendar control to your Control toolbox. To do this, right-click on the Control Toolbox, choose Additional Controls..., select the Calendar Control 8.0 check box, and click OK. The Calendar control should now appear in your Control Toolbox. The last step is to program the control. For instance, to make a calendar control--named Calendar1--display the current date, place the following code in the user form's initialize event procedure:

Calendar1.Today

By using the calendar control's Click event, you can capture the user's selection, having it automatically entered in a text box or in a cell in an Excel worksheet. For instance, the code

TextBox1.Value = Calendar1.Value

places the selected calendar control date in a text box named TextBox1. Or you could use the code

Selection.Value = TextBox1.Value

to enter the chosen calendar control date in the selected cell.

Exporting Excel charts as graphic files

In order to display an Excel chart on a Web page, you have to capture its image and save it as a graphic file, such as a GIF or JPG. Although you could use a screen-shot capturing program to create graphic files of your charts, you'll find that it's much quicker to use this VBA procedure:

Sub ChartToGIF()
Dim YourChart As Chart
Const ChartPath = "C:\Windows\Desktop\"

Set YourChart = ActiveChart
GifName = InputBox("Enter a name for the graphic file:", _
"Export Chart", ActiveChart.Name)

If GifName <> "" Then
YourChart.Export FileName:=ChartPath & GifName & _
".GIF", FilterName:="GIF"
End If
End Sub

To use the procedure, first select a chart object or a chart sheet displaying the chart you want to export in a GIF file. Then, run the procedure. The procedure will display a Export Chart dialog box, which prompts you to specify a name for the graphic file. The text box automatically displays the name of the active chart. However, you can type a new name if you want. Finally, click OK and the procedure will create a GIF file named Schedule.GIF on your Windows 95 Desktop.

You can make the procedure create the GIF file in any folder on your PC. Just change the second statement so that it assigns the desired path to the
constant named ChartPath. You can save charts using the JPG graphic format, as well as the GIF format. Just replace the two GIF references in the Export statement with JPG references.

Autility for finding unwanted formula links in Excel

Sometimes a workbook will contain mysterious links that are difficult to find and delete. Fortunately, Bill Manville's excellent utility-FINDLINK.XLA-enables you to find and remove links from any Excel 5.0 or later workbook. Just install the add-in and a Find Links option will appear on your Tools menu. You can download the free utility from the Accounting Advisors home page at www.accountingadvisors.com. Once you get there, click the Excel Tips button and scroll down to the Downloads section.

Deleting cell comments from an Excel worksheet

Importing a Lotus 1-2-3 spreadsheet into Excel can leave you with dozens or hundreds of comments where formulas didn't convert. An easy way to clear
all of these comments at once is to use the following VBA procedure:

Sub ClearComments()
For Each c In ActiveSheet.Comments
c.Delete
Next
End Sub

Using dynamic range names

Arguably, one of the most powerful features in Excel is the ability to create dynamic ranges, which expand or contract as you add or delete data.
You can create a dynamic range that consists of a single row by choosing Insert | Name | Define, then enter MYRANGE in the Names In Workbook field,
enter the following formula

=OFFSET(Sheet1!$B$5,0,0,1,COUNTA(Sheet1!$5:$5))

in the Refers To field. Finally click Add to save the reference. It's even possible to take this method a step further and make both the rows AND
columns dynamic, with the following syntax

=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B),COUNTA(Sheet1!$5:$5))

This way the formula automatically counts how many cell entries are in column B, and how many cell entries are in row 5, then expands or contracts
the range for as many cells.

Entering a formula with argument placeholders in Excel

When entering a formula, press Ctrl+Shift+A to view the arguments for the function. For instance, typing =DSUM into a cell, then pressing Ctrl+Shift+A will return =dsum(database,field,criteria).

Printing multiple files at one time

Although the method you use isn't intuitive, it's easy to print more than one Excel file with a single Print command. Here's the trick: Choose the Open command from the application's File menu. Navigate to the directory containing the files you want to print, then select the appropriate files.

To select contiguous files, click the first file's name, hold down the [Shift] key, and click the last file's name. To select noncontiguous files, hold down the [Ctrl] key and click each filename. Once you've selected all the files you want to print, click the Commands and Settings button (near the top-right corner of the dialog box) and choose Print from the dropdown menu. Windows will then print the documents in the order they appear in the filename list, using the default settings in the Print dialog box.

Launching Excel without a blank workbook

When you launch Excel by choosing its executable file or a shortcut to its executable file, the program opens a new, blank workbook named Book1. If
you want Excel to start without a blank document, you can do so by using the /e startup switch. The best way to do this is to right-click the Microsoft Excel shortcut in the Office folder, choose Properties, click the Shortcut tab, and type a space followed by /e at the end of the command in the Target text box.

Preview Excel workbooks in the Open dialog box

Excel's Open dialog box features a preview window that allows you to see an image of the selected workbook. To display the preview window, click the
Preview button near the top-right corner of the Open dialog box. (Hold your mouse over the buttons to reveal their names.)

If the preview window displays the message Preview not Available instead of an image of the selected workbook, first open the workbook you want to make previewable. Then, issue the File/Properties command, select the Save Preview Picture check box, click OK, and save the workbook. Before changing this setting for all your workbooks, you should know that it makes the file larger--in some cases, a lot larger.

Calculate portions of an Excel formula

The next time you need to debug a complex formula in Excel or simply want to know what value a cell reference in that formula is referring to, try this simple technique:
Select the cell containing the formula and press [F2] (or simply double-click that cell).
Then, highlight the equation or cell reference in question.
Press [F9] and Excel will replace the highlighted portion with the calculated result.
Press [Ctrl]Z to undo the conversion if you wish.
You can continue in the same manner to calculate other parts of the formula until you find the equation that returns the erroneous result.
(To convert the entire formula to its result, press [F9] immediately after you press [F2], or drag across the entire formula and press [F9].)
For instance, suppose you've selected the cell containing the formula =(12*12)/(1+Rate)^A20.
Press [F2], highlight the equation 12*12, and press [F9]. Excel will convert that part of the formula to 144.
You'll then see the formula displayed as =(144)/(1+Rate)^A20.
Similarly, highlight the cell reference A20 and press [F9], and Excel will replace it with the value in that cell of your worksheet.
Once you've finished calculating portions of the formula, press [Esc] if you want to preserve the original formula.
However, if you want to replace the original formula with the calculated result, press [Enter] instead.
You can press [Ctrl]Z to undo the entry if you wish.

VBA Equivalents for Lotus 1-2-3 Macro Commands

Converting a 1-2-3 macro to an Excel VBA procedure can be quite tedious.
Before taking on this task, you should download a helpful Microsoft Knowledge Base article titled Visual Basic for Applications Equivalents for
Lotus 1-2-3 Macro Commands. As its name implies, this article lists equivalent VBA macro code for most Lotus 1-2-3 macro functions. For
instance, it says you can replicate 1-2-3's {APPENDBELOW} command function (which copies the contents of source-location to the rows immediately below a target-location) by using the VBA code location.

startRow = Range("A1:A5").Row + Range("A1:A5").Rows.Count
startCol = Range("A1:A5").Column
Selection.Copy destination:=Cells(startRow, startCol)

Enhancing Excel's Help system with links to Microsoft Knowledge Base articles

Want to annotate Excel's Help system with hyperlinks to Microsoft Knowledge Base articles? You can do so easily by downloading and installing
Microsoft's Excel 97 Weblinks Help File (Xl9701hlp.exe). To download the 229KB file, go to www.microsoft.com/office/download/xl9701hlp.exe. It's
easy to install--just follow the directions at the download site.

Another Recalculation Bug in Excel

On March 20, 1998, Microsoft released an Auto-Recalculation Patch to fix a problem that prevents certain cells within a worksheet from automatically
recalculating. However, they've discovered the patch doesn't fix the problem for all circumstances. There should be an updated patch available at www.microsoft.com/Excel/enhancements/XL8P1.asp in early April. In the meantime, you can ensure your formulas are fully recalculated by pressing
[Ctrl][Alt][F9].

Creating a splash screen for your custom Excel applications

Once you've created an application for someone, you might want to incorporate a splash screen, which can make your work look more professional. You might program the splash screen to display your company name and logo and the name of the custom application for two or three seconds upon starting. To learn how to create a splash screen in an Excel workbook, refer to John Walkenbach's article "Creating a Splash Screen for an Excel 97 Workbook" (www.j-walk.com/ss/excel/tips/tip39.htm) and download and open Larry Reimann's splash.xls workbook (www.accountingadvisors.com/download/splash.xls).

Sorting Excel sheets alphabetically

Excel doesn't provide a feature that sorts a workbook's sheets alphabetically by sheet name. However, you can use this simple bubble-sort procedure:


Sub AlphaSheet()
   Application.ScreenUpdating = False
     Count = Sheets.Count
        For i = 1 To Count - 1
              For j = i + 1 To Count
                    If Sheets(j).Name < Sheets(i).Name Then
                       Sheets(j).Move Before:=Sheets(i)
                    End If
             Next
      Next
End Sub

To create the procedure, press [Alt][F8] to display Excel's Macro dialog box. Type AlphaSheet in the Macro Name text box and then click the Create
button. Excel will activate the Visual Basic Editor and open a new module sheet already containing the beginning and ending statements in the procedure. Fill in the rest of the code, then switch back to Excel by pressing [Alt][F11]. To run the procedure, press [Alt][F8], choose AlphaSheet from the list of macro names, and click OK. When you do, it will arrange the sheets alphabetically.

Retrieving data from a Web site and displaying it in an Excel 97 workbook

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.

A Shortcut for Closing Multiple Workbooks

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.

Using the Shrink to Fit command to format text within a cell

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.

Moving an Embedded Chart to a different Worksheet

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.

Quickly change formula cell references

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

Avoid needlessly opening files

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.

Control how many recently used files are displayed

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.

Save your workspace (and time)

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.

Shrink the contents of a cell

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.

Worksheet navigation tips

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.

Make your formulas more readable with labels

Many of you probably use named ranges in your formulas to improve readability. However, you may not be aware that you can often use column and row labels in your formulas to refer to data in ranges that aren't named. By default, Excel allows you to substitute row or column labels in most functions. To see how this works with column headings, enter FNAME in cell A1 and LNAME in cell B1. In cell A2, enter the first name Brian and then enter the last name Desso in cell B2. In cell C2, enter the formula =FNAME&" "&LNAME to produce the result Brian Desso. As mentioned, this also works with row labels. Enter the label SALES in cell A11 and the label PERCENT in A12. In cell B11, enter $78,999. Then enter 15% in cell B12.
Finally, in cell B13, enter the formula =SALES*PERCENT.

Quickly create weekly column headings in Excel

Here's a quick way to make a weekly listing of column headings in adjacent columns. Enter the first date of the series in a cell (we'll say A1). In the cell next to it, enter the formula +A1+7.
Then, place the mouse pointer on the lower-right corner of the second cell until the fill handle appears. Finally, click on the fill handle and drag to produce your row of headings.

Use hyperlinks to navigate through large workbooks

If you have an Excel workbook that contains a large number of worksheets, chances are you can't see all of the worksheet tabs at once on the worksheet area. This is especially a problem if your
worksheet tabs have long titles. If you have this problem, consider creating a worksheet that acts as a table of contents and use hyperlinks to navigate to the appropriate worksheet. First, create
a blank worksheet in your workbook. Now, we'll walk you through creating your first hyperlink. In the cell that will contain your hyperlink, enter a description of the sheet you want to jump to.
Then, press [Ctrl]K. If you're prompted to save your file, do so.
Next, click the Browse button next to the Named Location In File (Optional) text box. Select the sheet you want to tie the hyperlink to and click OK on all open dialog boxes. That's all there is to
it! You may also want to place hyperlinks on each individual worksheet that link back to your table of contents page.

Legal Disclaimer
THE INFORMATION IN THIS PUBLICATION IS PROVIDED "AS IS". WE EXPRESSLY DISCLAIMS ALL REPRESENTATIONS AND WARRANTIES OF ANY KIND REGARDING THE CONTENTS OR USE OF THE INFORMATION INCLUDING, BUT NOT LIMITED TO, EXPRESS AND IMPLIED WARRANTIES OF ACCURACY, COMPLETENESS, MERCHANTABILITY, FITNESS FOR A PARTICULAR USE, OR NON-INFRINGEMENT. IN NO EVENT SHALL WE BE LIABLE FOR ANY DIRECT, INDIRECT, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, LOST BUSINESS OR LOST DATA, RESULTING FROM THE USE OR RELIANCE UPON THE INFORMATION, EVEN IF WE HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO THE ABOVE EXCLUSION MAY NOT APPLY TO YOU.