tag:blogger.com,1999:blog-93128772024-03-24T23:09:46.027-07:00Excel TipsJon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.comBlogger17125tag:blogger.com,1999:blog-9312877.post-3742449647040188512009-05-25T09:24:00.011-07:002013-07-13T13:35:04.971-07:00Round to the Nearest Multiple Other Than 10If 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):
=ROUND(number/mult,0)*mult
or just
=MROUND(number,mult)
(Thanks to mmmoj's comment for the MROUND function)
If you want to round UP or Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com9tag:blogger.com,1999:blog-9312877.post-23326026620399960552009-05-25T07:28:00.002-07:002009-05-25T07:31:45.423-07:00Changing 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.Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com3tag:blogger.com,1999:blog-9312877.post-81652720145772980052009-05-25T07:22:00.004-07:002013-07-13T13:34:29.551-07:00Changing the Number of Recently Used Files in ExcelIn 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.
In Excel 2007, the new recently used file list is probably one my most favorite updates. Instead of just listing the files, you can also "pin" the files that you use all the time so that Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-76983326721052490072009-05-01T10:34:00.006-07:002013-07-13T13:37:47.408-07:00Use the OFFSET Function for a Running BalanceIf 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.
To see how this works, take a look at the image below which is a screenshot from a modified version of my Checkbook register template.
To start, let's talk about the formula in column B. In cell B7 let's say I used the formula Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com4tag:blogger.com,1999:blog-9312877.post-39450119744875547162009-04-29T08:45:00.011-07:002013-07-13T13:38:11.490-07:00Return the Last TEXT Value in a ColumnOn 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.
=VLOOKUP(REPT("z",255),A:A,1)
or
=INDEX(A:A,MATCH(REPT("z",255),A:A))
(For the formula that returns the last NUMERIC value in a column, see my previous post)
I prefer theJon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com2tag:blogger.com,1999:blog-9312877.post-58425224147580428022009-04-28T07:01:00.004-07:002013-07-13T13:35:14.496-07:00Return the Last Numeric Value in a ColumnReturning 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.
After trying a variety of different formulas for returning the last numeric value in a column, I settled on the following two.
=VLOOKUP(9.99999999999999E+307,A:A,1)
Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com21tag:blogger.com,1999:blog-9312877.post-79304540668632283502009-04-20T07:23:00.003-07:002013-07-13T13:39:27.590-07:00Toggle Between Relative and Absolute ReferencesUse 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.
If you want to toggle all the references in the formula at the same time, just select the entire formula. The first time you press F4, all of the references currently selected will Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-29099239838813330282009-04-16T08:36:00.002-07:002013-07-13T13:39:55.671-07:00Quickly Fill Multiple Cells with a Value or FormulaUse the keyboard shortcut Ctrl+Enter to quickly fill multiple cells with a value or formula.
For example, you may want to enter a bunch of 0's (zeros) in a range of cells. Or, you want to apply the same formula to a range of cells without changing the cell formats.
1. Select a range of cells
2. Enter the value or formula
3. Instead of pressing Enter afterwards, press Ctrl+Enter to fill the Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com27tag:blogger.com,1999:blog-9312877.post-24759370801290253322009-04-09T09:57:00.005-07:002009-04-09T10:21:34.507-07:00Use 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,Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com2tag:blogger.com,1999:blog-9312877.post-4735702847727026912009-03-17T10:58:00.005-07:002013-07-13T13:33:56.491-07:00Adding AutoCorrect ExceptionsThere is one AutoCorrect option that I like to always turn OFF in Excel: It is the "Correct TWo INitial CApitals" option.
As an engineer, I frequently work with units like MPa, where you DO want to use two initial capitals. It is very annoying to have "MPa" keep switching to "Mpa".
To edit the AutoCorrect options:
Excel 2003: Tools > AutoCorrect Options
Excel 2010: File > Options > Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-91908511609083366262009-03-12T18:53:00.004-07:002013-07-13T13:35:43.447-07:00Switching Between Different Tabs / WorksheetsOne 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.
If you are an avid user of Tabbed Browsing, you may be more familiar with using Ctrl+Tab / Ctrl+Shift+Tab to switch between Tabs. That is the shortcut used by Internet Explorer, FireFox, and Safari. That probably makes more sense than Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com2tag:blogger.com,1999:blog-9312877.post-11279986072151059942008-11-03T15:05:00.001-07:002013-07-13T13:37:03.363-07:00Dynamic Print Area in ExcelI 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.
The key was to use a dynamic print area - a print area that uses a formula to define the range of cells to Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com10tag:blogger.com,1999:blog-9312877.post-1128061568624536052005-09-29T23:01:00.001-07:002013-07-13T13:36:52.372-07:00Keyboard Shortcut - Choosing a FontI'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:
1. Select the text (or cell)
2. Press Ctrl+Shift+F
3. Type the first few letters of the font
4. Press Enter
This same basic procedure can be used to Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-1125376627752512472005-08-29T21:04:00.001-07:002013-07-13T13:36:43.907-07:00Keyboard Shortcuts to Insert Special Symbols in ExcelThe 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.
You can actually create your own keyboard shortcuts for inserting other special symbols in Excel such as the micro symbol (Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com13tag:blogger.com,1999:blog-9312877.post-1124167964287481552005-08-15T21:38:00.001-07:002013-07-13T13:36:33.842-07:00Copying/Pasting Excel Charts Into Other ProgramsOne 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:
Hold down the Shift key as you select the Edit menu. This will make the "Copy as Picture..." option available.
This will work for Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-1123721786672748882005-08-10T17:41:00.001-07:002013-07-13T13:36:24.004-07:00Insert a New Row or Column - Excel Keyboard ShortcutLately, 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:
Select the current row: Shift+SpaceBar
Insert a row (above the currently selected row): Ctrl+"+"
To insert a new column, in Step 1 use Ctrl+SpaceBar to Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com60tag:blogger.com,1999:blog-9312877.post-1102526097319534292004-12-08T09:49:00.002-07:002013-07-13T13:35:53.312-07:00Square Cells in ExcelIf you have done much drawing using Excel's drawing tools, you may have figured out that you can use the cells as a grid.
For example, if you press the Alt key as you draw, your shapes will snap to the corners of the cells. (You can also turn on the "Snap to Grid" option, but using Alt is more convenient) Holding the Shift key will scale shapes proportionally.
Instead of explaining the steps toJon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com7