Tip # 11 Skip through your worksheets with a shortcut

When I'm working on many programs at once (yes, of course I'm a multitasker — what did you expect?), I use the keyboard shortcut ALT+TAB to move between open programs. (If you haven't tried this, do so now — it's a great Microsoft Windows® trick.)

However, this doesn't work when you're moving between worksheets in the same workbook. Don't despair; there are a couple of neat little shortcuts that handle the job quite nicely.

To move one worksheet to the right

Press CTRL+PAGE DOWN.

To move one worksheet to the left

Press CTRL+PAGE UP.

Now you can skip through those worksheets with lightning speed and amaze your friends and coworkers (if they're the type to be amazed by that sort of thing, of course).

Tip # 10 Current date and time

'Ctrl+;' will insert today's date into a cell

AND

'Ctrl+Shift+;' inserts the current time into a cell.

Tip # 9 Conditional Formatting to avoid error

I have two handy ways I use conditional formats:

1. In a user form, you can highlight a field that require input by setting up a condition to turn it yellow if the cell is blank. Then, when the user enters some data, the cells turns white again.

2. You can also use conditional formatting to hide those ugly error messages (#NA, #NAME?, #DIV etc). This is perfect when such errors are unavoidable, but you don't want them to clutter up your worksheet. Just set up a format to change the text color to white whenever the cell formula is "=ISERROR(A4)", where A4 is the cell reference.

Tip # 8 Conditional Formatting

I love conditional formatting. Two advanced user tips:

1) Watch out for fixed cell references:
When you choose another cell (say a4) to compare your cell to (say b4), Excel puts dollar signs in so that if you copy this format to another cell (say a5) it compares to $a$4, rather than the cell that is in the same relative position as the first cell (which would be b5 in this example.) This may be exactly what you want, but if it is not, you will have to edit the $s out of the condititional formatting before copying.

2) If you want your conditional formatting to have nothing to do with the value in the cell, but instead to be based on the value in another cell (a4), set up an intermediate cell (b4) that uses a formula like this:

=if(a4={condition},{a number that is guaranteed to be higher than the number in the formatted cell}, {a number that is guaranteed to be lower than the number in the formatted cell})

Then have your conditional formatting do a greater than or less than comparison to the intermediate cell.

Tip # 7 Two workbooks side by side

If you just want to have the two workbooks side-by-side, use the Window-->Arrange command and select Vertical. If you want to see them stacked, select Horizontal. Of course, if you have more than one workbook open, the Arrange command will arrange them all, but afterwards, you can resize them however you want. To go back to full screen mode, just click the maximize button on the title bar of one of the workbooks.

Tip # 6 Named Range

The use of good spreadsheet techniques is critical to an efficient spreadsheet. Nothing kills analysis, scenario checking or error checking faster than "hard-coded" data and the lack of cell naming. Avoid spending significant time reviewing each and every formula in your sheet by:

1. Set up the variables assumed in your spreadsheet in their own cells (even on their own sheet - I like to utilize a 'Data' sheet in all my spreadsheets).

2. Name pertinent cells or ranges (click in the "Name Box" to the left of the formula bar and enter a name).

3. Use the Name that you created to build your formulas.

The result is that you can change the calculated values of multiple formulas easily with the change of one cell containing your variable.

Tip # 5 Auto fill down

Let's say you have column A, B and C with 1027 numbers in each of them. To add an average column, you just go to the top of column D, type in =AVERAGE(A1:C1) and hit enter, then double-click on the lower right corner of that cell. It fills that value down to the bottom of the table. So much easier than scrolling down to the bottom and doing a fill down.

Also, when a group of cells is selected, to put the same value/formula into each of them, simply type the value or formula and hit Ctrl+Enter.

Tip # 4 Comparing two sheets

To compare two sheets from the same workbook, choose "Window->New Window" and then the side-by-side comparison should work.

Also use Window --> Arrange -->Select(Horizontal,Vertical,Tiled,Cascade) to compare more easily.

Tip # 3 Format Painter

The little "format painter" is the most useful button in Excel. It looks like a paintbrush and is grouped with the cut,copy,paste buttons. Basically highlight a cell with the formating that you want, click the button, and then highlight cell(s) that you want to just paste the format. HUGE time saver!

Tip # 2 Format all the worksheets in a workbook

If you need to format all the worksheets in a workbook the same way, you can hold down the Shift or Control button and select the worksheets. Now that you have the worksheets selected, you can change things like cell or page properties that will apply to all the worksheets selected. After you're done, click on an inactive worksheet (if you've selected all the worksheets) or click on an unselected worksheet (if you did not select all the worksheets) to release the hold.

Tip # 1 The Great F4

The 'F4' is easily the most useful shortcut key in Excel. Basically it repeats your last command.
So say you changed the color of a font in a cell. Now click in a new cell and hit 'F4' and it will change the font in the new cell to the same color.Wonderful, right? Well what's great about this is that it works for nearly everything else in Excel too.Highlight a row, right-click and choose 'Delete'. Now highlight another row and hit 'F4'. It deletes that row.Whatever you did last, Excel will attempt to repeat the same command until you do something else. This works great for things that require you to go to the menus or right-click and make choices or click buttons that bring up more dialogs. It can be huge time saver once you get used to using it. As the help says, it doesn't work in every situation but most times 'F4' works great.