Excel Tips 'n Tricks
(good for either 97 or 2000)
By Jan Altman

Presented at SPAUG General Meeting, May 2001

Moving around

• Make a run for the border: Double-click a cell edge in the direction you want to go.
  Wait until the mouse cursor is a white arrow.
  
• Right-click the tab scrolling buttons to jump to a worksheet.
• Find and unhide a hidden column by finding the right spot in the column headings.
  Slightly to the right of the column boundary, middle bar of pointer opens up and width displays "0.00".

Editing and formatting

• When zooming to 39% or less, named ranges are bordered and labelled in blue.
• View Page Break Preview is completely editable. Visually drag page breaks and margins, and the information is automatically scaled to fit the page.
• Special built-in number formats allow easy formatting of telephone numbers, zip codes, social security numbers, and fractions.
  Enter fractions with a leading zero, so Excel won't mistake it for a date.
• Drag a range to another sheet in the workbook by holding down Alt.
• Move, copy, or autofill a range with the right mouse button to get a shortcut menu with several options.
  Fill dates.
  Copy/fill formatting only.
  Create absolute links.
  
• Enter cell ranges in dialog boxes by collapsing the box to see the worksheet behind it.
  Box collapses when special button or background worksheet is clicked. Title bar and empty field remain for entry.
• You can design custom lists to tailor the AutoFill button and the Data Sort command to your own needs.
  You can create a list from existing items on worksheet, or you can type one from scratch.
• AutoComplete compares text you are typing with existing text in the same column, and completes the entry for you.
  Use Alt/down arrow to choose from a list. (Does not include custom lists.) Can also right-click and choose Pick From List.
  Completes only entries with text and/or numbers. Numbers only, dates, and times are ignored.
  Turn off via Tools Options, Edit.
• AutoFilter displays quick subsets of a database based upon built-in or customized criteria.
  Blank, non-blank, top, bottom.
  Filtered rows and arrows become blue.
  Good for summing or changing certain fields.

Entering formulas

• Parentheses used in formulas can be matched visually.
  Sets of parentheses are displayed in the formula bar in matched-color pairs.
• The Formula Palette hand-holds you through using functions by describing each argument, and displaying interim results.
  Appears below formula bar after clicking "=" or choosing function from Paste Function box.
  From keyboard, enter function name and press Ctrl-A to display Formula Palette, or Ctrl-Shift-A to display arguments.
  Formula AutoCorrect table.
• The Range Finder gives visual cues as you work with a formula. It uses different colors to display all the cells and ranges that the formula refers to (its "precedents").
  "Precedents" are not what we elect every November, nor even what we receive for our birthday.
  Drag colored borders to change references.
  Charts have Chart Range Finder.
• Conditional formatting dynamically applies specific formatting to cells falling inside or outside preset limits.

Going for a ride…

• Easter in Excel 2000:
  1. Open a new file in Excel 2000.
  2. Select File and Save As, and in the Save As Type menu, select Web Page.
  3. Click the Selection:Sheet radio button, and checkmark Add Interactivity.
  4. Click Publish. In the Publish As dialog box, click Publish again.
  5. Switch over to Internet Explorer (IE). Open the HTML page you've just saved in Excel. You'll see an Excel worksheet in the middle of the IE page.
  6. Press the Page Down key until you get to row 2000, then tab over to column WC.
  7. Hold down Shift/Ctrl/Alt while you click the Office logo at the upper left.
  8. 
     You've now dropped into a full-screen game called Dev Hunter. Use the arrow keys to drive. You'll see developers' names painted on the road as you go.
     
     If you're an aggressive driver, hit the spacebar to fire and the letter O to drop oil slicks. When it gets dark, use H to turn on your headlights.
     
     (Note: Your system must be equipped with DirectX in order for this egg to work.)
     
     
• In Excel 97:
  1. Open a new worksheet in Excel 97.
  2. Press F5.
  3. Type X97:L97 and press Enter.
  4. Press Tab.
  5. Hold down Ctrl and Shift while you click the Chart Wizard button.
  6. 
     A 3D flight simulator appears. The right mouse button moves you forward, and the left mouse button puts you in reverse.