UseExcel: Excel Tips and Tricks

Some of these Tips and Tricks can be seen at Keyboard Shortcuts

The Excel Application

Some settings or options affect Excel and all workbooks that Excel opens. Some of these are:
Always show full menus and Show Standard and Formating toolbars on two rows. These two are described in detail at Getting Started
There are dozens of options that affect the appearance and behavior of all Excel worbooks and worksheets under Tools, Options.

Workbook Navigation

New Excel workbooks usually start with three worksheets. (You can change this option under Tools, Options, General tab, Sheets in new workbook spin box.) As you develop your new workbook, you might insert more worksheets (Insert, Worksheet). To navigate from worksheet to worksheet using your keyboard, press Ctrl-Page Up to navigate to the left, and Ctrl-Page Down to navigate to the right.

Worksheet Navigation

Navigating within a selected range:

1. Select a range, say A1:D4.
2. Holding down the Ctrl key and pressing the period key will cycle around the four corners of the selction: A1, D1, D4, A4.

This shortcut is really handy if you select an entire column. Then Ctrl-. will tell you if row 65,536 is empty with just two keystrokes.

Cell Selection

Select the entire row of the active cell:
Select any cell. Then do Shift-Space Bar.

Related to the last shortcut - Select the entire column of the active cell:
Select any cell. Then do Ctrl-Space Bar.

Select all cells in a worksheet
Handy for clearing an entire worksheet at once. Click on the square above row number 1 and to the left of column A.


Select non-contiguous cells
Select a cell, say A1. Hold the Ctrl key down and select another cell, say C1. A1 will have a shaded look and C1 will be the active cell. Keep holding the Ctrl key down and select another cell, say B5. Cells A1 and C1 will have a shaded look and B5 will be the active cell.

Cell Formating

Shrink to fit Format, Cells, Alignment tab, Shrink to fit will fit the cell content within the given width and height of the cell. The figure below demonstrates this in cell A1. Cell A2 does not have this format.

Orientation Remaining in Format, Cells, Alignment tab, on the right hand side of the tab, text orientation will produce the effect that you see in the figure below. F5 will bring up the Go To dialog box. Eqivalent to Edit, Go To, or Ctrl-G. Go To can remember where you have been. Try going to Z100 from A1. Then press F5 once more. Go To will remember that you came from cell A1.

Make cell content look invisible This is a very low tech way to hide content. Make the cell font color the same color as the fill color. If there is no fill color, make the font color white. The figure below demonstrates this in cell D1.

Graph paper This is a really neat effect. To make your worksheet look like graph paper, that is, so that the cells look like they are squares, set the Row Height to 20 and the Cell Width to 3. The result is shown in the figure below, for columns A to F.

Formulas

Autosum formula:
If you want to add up a column of numbers, why do Insert, Function, Math, Sum? Instead just do Alt-= (equals sign).

What if you don't want to go the bother of entering a SUM function in a cell? Just select all of the cells with numbers in them and glance down at the lower right hand side of the status bar. You should see a status that says Sum=23342 (for the example in the image below). (The status bar is below the last row in the visible window. You should see Ready on the left hand side.)
Concatenation
Say you have someone's first name in cell A2, their last name in cell B2 and you want to display their full name (first name and last name) in cell C2.
In column C, do =A2&" "&B2

The F keys

Help
F1 will run Help - the first place you should look for answers to your questions!

Edit cell contents
F2 will allow you to edit the contents of the active cell. If you have Tools, Options, Edit tab, Edit directly in cell checked (default behavior), then you should see the blinking editing cursor inside the active cell. Remember to press Esc (the Escape key) when you have finished editting.

Here's another tip - while you are editing a cell, to select an entire word, instead of sweeping over it with your mouse from left to right, double-click the word.

Or, if you want to be more careful in selecting text in a cell, place your editing cursor (the one that blinks) just to the left of what you want to select and then hold the Shift key down and press or hold the right arrow key down for as much as you want.

If you want to display your cell content in one or more lines, say, instead of

Geof Wyght, 123 Main St.

you want to display

Geof Wyght
123 Main St.

then where you want your line break to be, within the cell, enter Alt-Enter.

Excel will ignore leading zeroes. To demonstrate this to yourself, select a cell and then enter the number 01234 and press the Enter key. The leading zero will disappear and the cell will contain the value 1234. To dispay leading zeroes, say for east coast US ZIP codes, instead of entering 01234, enter '01234 - that is a single quote followed by 01234.

One more tips with a single quote. If you enter =A2+B4+V60 in a cell and press Enter, you will see the result of the sum of the values in cell A2, B4, and V60, assuming that all three cells contained numbers. But if for the sake of illustration you actually wanted to show the cell formula then enter '=A2+B4+V60

F4 Toggle among absolute references and relative references.
While editing a formula, F4 will toggle among the three variations of absolute references and relative references. Please see this page for more on this tip.

Repeat format cells Say you have just changed the fill color of a cell to yellow. You can apply the same format to other cells by pressing F4. Of course, you have to navigate to the other cells first. This is the same as Edit, Repeat format cells, or Ctrl-Y.

F5 Go To: will bring up the Go To dialog box. Equivalent to Edit, Go To, or Ctrl-G. Go To can remember where you have been. Try going to Z100 from A1. Then press F5 once more. Go To will remember that you came from cell A1. F7 Spell checking
F7 will run Tools, Spelling.

F11 will create an instant bar chart. To demonstrate this, create a column of dates in a 10/1/2007 type of format. Beside that column, creat a column of temperatures representing the high for that day. Select both columns of data and press F11.

Text Functions

One of Excel's strong points is its vast array of functions. Insert, Function will reveal a long, categorized list of functions. Here are some handy text category functions:
Left() function, =LEFT("Excel",2) returns "Ex"
Right() function, =RIGHT("Excel",3) returns "cel"
Mid() function, =MID("Excel",2,2) returns "xc"
Lower() function, =LOWER("EXCEL") returns "excel"
Upper() function, =UPPER("excel") returns "EXCEL"
Upper() function, =PROPER("microsoft excel") returns "Microsoft Excel"
These last three functions are very handy for cleaning up data.
Find a character within a text string, =FIND("c","Excel",1) returns 3