Round to the Nearest Multiple Other Than 10

If you want to round a price to nearest nickel (multiple of 0.05), or a length to the nearest inch (multiple of 1/12), or a number of minutes to the nearest second (multiple of 1/60), you can use the following formula (where mult is the multiple to round to):

Changing the Default Number of Sheets in Excel

[For Excel 2000/2002/2003]

To change the default number of sheets in a new Excel workbook, go to Tools > Options > General tab and change "Sheets in new workbook". The default in Excel 2003 is 3 sheets, but I find that extremely annoying and instead of always deleting the other 2 sheets, I just set the default to 1.

Changing the Number of Recently Used Files in Excel

In Excel 2003, you change the number of recently used files listed in the File menu by going to Tools > Options > General tab and editing the number in the "Recently used file list:". The maximum if 9.

Use the OFFSET Function for a Running Balance

If you have a spreadsheet that uses a running balance, the OFFSET function is a great way to allow you to easily insert and delete rows, without messing up the balance.

Return the Last TEXT Value in a Column

On a number of forums, I found the following suggested formulas for returning the last TEXT value in a column. These are pretty good and they allow the range to include blanks, error values (like #N/A), and numeric values.

Return the Last Numeric Value in a Column

Returning the final value in a column is particularly useful for spreadsheets that involve running balances, where you want to reference the final balance regardless of whether it is the smallest or largest value in the column.

Toggle Between Relative and Absolute References

Use the function key F4 to toggle a reference between relative and absolute references while editing a formula. Repeatedly pressing F4 will cycle through the 4 different options in this order: A1, $A$1, A$1, $A1.

Quickly Fill Multiple Cells with a Value or Formula

Use the keyboard shortcut Ctrl+Enter to quickly fill multiple cells with a value or formula.

Use Correct Syntax with INDEX()

I am guilty of shortcutting when using the INDEX() function. The proper syntax is INDEX(array,row_num,column_num), but if your data is in a row, the following shortcut will also work: INDEX(array,column_num). DON'T DO THAT! It may work just fine in your Excel document, but I discovered recently that when you try to open the document with OpenOffice, it will interpret your formula as INDEX(array,row_num), and most likely result in a bunch of errors.

If your array is a single row, use INDEX(array,1,column_num)

Bottom line: Use Standard Syntax

Adding AutoCorrect Exceptions

There is one AutoCorrect option that I like to always turn OFF in Excel: It is the "Correct TWo INitial CApitals" option.

Switching Between Different Tabs / Worksheets

One of the keyboard shortcuts I use all the time in Excel is the one for switching between tabs or worksheets within a workbook: Ctrl+PageUp or Ctrl+PageDown.

Dynamic Print Area in Excel

I was creating a new version of my amortization schedule spreadsheet today and wanted to set the print area so that it didn't print a bunch of blank pages. If a person chooses a 15-year loan with monthly payments, I wanted the print area to show only those payments - not the entire table.

Keyboard Shortcut - Choosing a Font

I've come to really like a new excel keyboard shortcut - one that allows you to easily choose a font for a cell. This shortcut even works for portions of text within a cell. It also works in Microsoft Word. The process for editing the font is:

Keyboard Shortcuts to Insert Special Symbols in Excel

The copyright ©, trademark ™, and registered trademark ®, symbols can be inserted quickly in Office applications using the Autocorrect feature. Typing (c), (tm), or (r) will accomplish this. The other way to enter special symbols in Excel is by going to Insert > Symbol.

Copying/Pasting Excel Charts Into Other Programs

One of the valuable "hidden" tricks in Excel is how to copy a chart as an image so that when pasting into another program, it will paste as an image rather than an Excel object (which generally saves the entire workbook along with the chart). Here's the trick:

Insert a New Row or Column - Excel Keyboard Shortcut

Lately, I have been trying to use as many Excel keyboard shortcuts as I can (not only for productivity, but for ergonomic reasons). There is a quick two-step process that is very convenient for inserting or deleting a row or column:

Square Cells in Excel

If you have done much drawing using Excel's drawing tools, you may have figured out that you can use the cells as a grid.