Tips and Tricks

Excel 97

Give Text a Break

You can break a long text entry on a spreadsheet into separate lines by positioning the cursor in thecell entry where you want the new line to start and pressing Alt+Enter. Excel automatically wraps the text in the cell, according to the cell's column width and the position of the line break.

Swift Series

Use AutoFill to automatically enter serial information in cells. Type the first item in the series (for example, a month or day of the week) and then click on the lower right corner of the cell. The cursor turns into a cross; drag it right or down to highlight the cells to fill. When you release, the series will be extended throughout the new cells.

More Familiar Formulas

You can use cell headings, instead of cell addresses, to reference cells in a formula. For example, if your column headings are months, you can get a total for those contents by adding the column names (January+February+March).

Add Texture to Charts

You can put texturized backgrounds in Excel charts by right-clicking on the chart and choosing Format Plot Area from the menu. Click on the Fill Effects button and select the Texture tab. Choose a texture and click on OK twice.

Quick Database Count

You can get a quick count of how many items you have in a spreadsheet database using the COUNT IF function. Use the following formula: COUNTIF(Range,Criteria). For example, if you want to find out how many times "CA" appears in column C, type COUNTIF(C1:C2200,"CA").

Create Personalized Defaults

When you create a new Excel file, you get a set of defaults that might not fit your needs. But you can create personalized defaults easily. Open a new workbook (or page) and set the desired formatting. Then save the file as a template named BOOK.XLT for a workbook or SHEET.XLT for a single sheet in the Excel XLStart folder. Excel will then use this template as the default model.

Put On the Breaks Before Printing

You can manually set page breaks for a spreadsheet. Select File/Print Preview and click on Page Break Preview; drag the blue lines to scale the data to fit the page size.

Copy Conditions

You can copy conditional formats to other cells. Select the cells with the conditional format, click on Format Painter (the button with a paintbrush icon), then select the cells in which you want to copy the condition.

Size Cells for Text

The Merge Cells tool lets you smoothly display blocks of text in a spreadsheet. Select adjacent cells where you want the text and right-click. Choose Format Cells from the shortcut menu and select the Alignment tab. Click on the Merge Cells and Wrap Text options. The text will fill the merged cells without spilling into adjacent cells.

Speedy Sum

Quick sum a range of cells by highlighting them and pressing Alt+= (equal sign).

Keeping Tabs on Tabs

In a multiple-sheet workbook, all the tabs may not be visible. Right-click on any of the arrow buttons in the lower left corner, and a list of sheet names will appear. To jump to a sheet, click on its name.

Force a Fit

If, you don't want Excel to shrink or grow a cell to accommodate an entry, tell Excel to automatically resize the data itself. Select the cell(s), choose Format/Cells/Alignment and check the Shrink To Fit box.

Expeditious Duplication

You can enter a formula or data into several cells at once. Select the range of cells, type the information and press Ctrl+Enter.

To select multiple cells
Click on the first cell that you wish to select and with the Control key depressed click on the other cells that you wish to select

Dates can be added or subtracted
You can add or subtract dates, as in the example shown
="2/4/98"+"4/6/98"

To enter a 'carriage return' within a cell
If you want to for instance enter a 5 line address within a single cell, you can do this by entering Alt+Enter, at the end of each line

To select the entire worksheet
Click on the intersection of the rows and columns

To insert a comment in a cell
Right click on the cell and from the pop-up menu displayed, select the Insert Comment command.

To insert a row or column
Select a row or column, then right click on the row or column to display a pop-up menu.
From the pop-up menu, select either Insert Row or Insert Column.

To hide a row or column
Select a row or column, then right click on the row or column to display a pop-up menu.
From the pop-up menu, select Hide.

To display a Close All command on the File drop down menu
Depress the Shift key, when you click on the File drop down menu. A special Close All option with be displayed.

To word wrap text within a cell
Right click on the cell(s) and from the pop-up menu displayed, select Format Cells.
Select the Alignment tab.
Click on the Wrap text check box.

To angle text within a cell
Right click on the cell(s) and from the pop-up menu displayed, select Format Cells.
Select the Alignment tab.
In the Orientation section, enter the angle required.

To rename a sheet tab
Double click on the tab that you wish to rename.
Enter the new name and press the Enter key.

To insert a new worksheet within a workbook.
Right click on the workbook tab that you wish to insert a new worksheet before.
From the pop-up menu displayed select the Insert command.

To delete a worksheet within a workbook.
Right click on the workbook tab that you wish to insert a new worksheet before.
From the pop-up menu displayed select the Delete command.

To reorder worksheets within a workbook
Drag and drop the tabs to the left or to the right, as required.

To find a file containing a particular piece of text
From the File menu, choose Open to display the Open dialog box.
To search the current folder for a file containing a particular piece of text, enter this text into the Text or Property box and then click on the Find Now button.

To display other toolbars
� Right-click on any toolbar to display a pop-up menu from which you can select the toolbar that you wish to display (or hide).

To customize Excel toolbars
Right click on any toolbar to display a pop-up menu.
Select the Customize command, which displays the Customize dialog box. 
Select the Options tab, and then select from the options displayed.

To apply a series using drag and drop techniques
Enter a value, such as MONDAY and press Enter.
Move the mouse cursor to the bottom right-hand corner of the selected cell, until the cursor changes to the shape of a fine cross-hair.
While depressing the left-hand mouse button, drag down the screen.
When you release the mouse button, the selected cells will be filled with the series.

To create a style based on existing formatting within a cell
Select the cell which contains the format you want to use repeatedly.
From the Format menu, choose Style to display the Style dialog box.
Type a name into the Style Name drop-down list box and click on the Add button
Close the dialog box by clicking on the OK button.

To apply a style to selected cells
Select the cell(s) that you wish to apply a style too
From the Format drop down menu select the Style command to display the Style dialog box.
Click on the down arrow in the Style name part of the Style dialog box and select the required style.
Select OK.

To display the Format Cells dialog box
Press CTRL+1 to display the Format Cells dialog box.

To turn off the gridlines on-screen
From the Tools menu, choose Options to display the Options dialog box.
Select the View tab.
Uncheck the Gridlines check box in the Window Options area.
Select OK. The gridlines will not appear on the screen.

To turn on the gridlines so that they will be printed
From the File menu, choose Page Setup to display the Page Setup dialog box.
Select the Sheet tab.
Check the Gridlines check box in the Print area.
Select OK.

To enter the current date into a spreadsheet
To get the sheet to always show the current date use the TODAY() function which will return the serial number for now. When formatted as a date this will show the current date.

To entering data into a cell it is the same as in the cell above it
Position the active cell just below a cell containing data.
Press Ctrl +' (apostrophe) and the contents of the cell above are copied to the cell.

To format AutoShape objects
Double click on the AutoShape that you wish to format. This will display the Format AutoShape dialog box, which contains four tabs, Colors and Lines, Size, Protection and Properties.
Select the required options and then click on the OK button to close the dialog box and apply your selections.

To create a template
Create the workbook you wish to save as a template. 
From the File menu, select Save As to display the Save As dialog box.
Type the name for the template in the File Name text box.
From the Save as Type drop down list box, select Template.
You should find that the folder automatically changes from the default of My Documents to the Templates folder.
Select Save. The extension .XLT is added to the file name and the template is saved in the Templates folder.

To create a button and assign a macro to it
Select the Create Button icon from the Forms toolbar.
To create an option button, click and drag on the worksheet. 
When you release the mouse button, the Assign Macro dialog box will be displayed. 
Click on the Record button, and the Record Macro dialog box will be displayed.
Enter a name for the macro, in the Macro Name section.
Click on the OK button.
Record your macro in the normal way, and click on the Stop Macro button when you have finished.
To run the macro simply click on the button.

To set the font that will be used in all new workbooks
Click on the Tools drop down menu, and select the Options command.
Select the General tab.
In the Standard Font section, select the new default font that is required.

To set the default file location of Excel
Click on the Tools drop down menu, and select the Options command.
Select the General tab.
In the Default File Location section, enter the required folder location default.

To increase the number of recently used files displayed in the File drop down menu.
Click on the Tools drop down menu, and select the Options command.
Select the General tab.
In the recently used file list section, enter the required number of files that to wish to see displayed at the bottom of the file drop down menu.