*mult*is the multiple to round to):

# Excel Tips

## Monday, May 25, 2009

### 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

### 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.

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.

## Friday, May 01, 2009

### 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.## Wednesday, April 29, 2009

### 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.## Tuesday, April 28, 2009

### 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.

## Monday, April 20, 2009

### 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**.## Thursday, April 16, 2009

### 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.## Thursday, April 09, 2009

### 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).

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

Bottom line:

**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**## Tuesday, March 17, 2009

### 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.

## Thursday, March 12, 2009

### 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**.## Monday, November 03, 2008

### 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.

## Thursday, September 29, 2005

### 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:## Monday, August 29, 2005

### 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**.## Monday, August 15, 2005

### 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:## Wednesday, August 10, 2005

### 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**:## Wednesday, December 08, 2004

### 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**.
Subscribe to:
Posts
(
Atom
)