tag:blogger.com,1999:blog-93128772012-02-01T13:16:04.608-07:00A blog for publishing Excel Tips and Tricks.Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.comBlogger221100tag:blogger.com,1999:blog-9312877.post-21845608990049414252009-07-16T12:53:00.002-07:002009-07-16T12:58:38.913-07:00

This article explains how to use the REPT() function in Excel to create a dot plot like the one shown to the left. It really is very simple. In some cases, dot plots may be better than bar charts, particularly when representing tallies or small numbers of items grouped into categories. By their nature, bar charts imply that values are continuous, while dot charts are used for discrete counts.

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com8tag:blogger.com,1999:blog-9312877.post-3742449647040188512009-05-25T09:24:00.011-07:002009-07-23T18:52:08.457-07:00

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):=ROUND(number/mult,0)*multor just=MROUND(number,mult)(Thanks to mmmoj's comment for the MROUND function)If you want to round UP or DOWN, you

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com6tag:blogger.com,1999:blog-9312877.post-23326026620399960552009-05-25T07:28:00.002-07:002009-05-25T07:31:45.423-07:00

[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.com1tag:blogger.com,1999:blog-9312877.post-81652720145772980052009-05-25T07:22:00.004-07:002009-05-25T07:27:38.924-07:00

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.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 they remain

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-76983326721052490072009-05-01T10:34:00.006-07:002009-05-25T07:33:04.754-07:00

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. 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 =B6+

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com2tag:blogger.com,1999:blog-9312877.post-39450119744875547162009-04-29T08:45:00.011-07:002009-04-30T08:38:29.296-07:00

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.=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 the formula

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-58425224147580428022009-04-28T07:01:00.004-07:002009-04-29T10:50:41.679-07:00

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.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) or=

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com4tag:blogger.com,1999:blog-9312877.post-79304540668632283502009-04-20T07:23:00.003-07:002009-04-20T19:26:43.042-07:00

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

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-29099239838813330282009-04-16T08:36:00.002-07:002009-04-16T08:49:39.495-07:00

Use 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 cells2. Enter the value or formula3. Instead of pressing Enter afterwards, press Ctrl+Enter to fill the entire

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com11tag:blogger.com,1999:blog-9312877.post-24759370801290253322009-04-09T09:57:00.005-07:002009-04-09T10:21:34.507-07:00

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.com1tag:blogger.com,1999:blog-9312877.post-66728587445588124302009-04-07T09:37:00.003-07:002009-04-09T10:22:13.339-07:00

One of the tricks you can use when creating a nice dashboard or Excel template is to add a drop-down list to a cell using Data Validation. You can reference a list from a different worksheet as long as you define the list as a Named Range. In my new article, "Drop Down List using Data Validation", I explain how to do it.

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-4735702847727026912009-03-17T10:58:00.005-07:002009-04-09T10:24:44.230-07:00

There is one AutoCorrect option (Tools > AutoCorrect Options...) 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".Instead of turning off this option, you can add exceptions via the Exceptions button

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-91908511609083366262009-03-12T18:53:00.004-07:002009-04-09T10:23:32.723-07:00

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

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312877.post-11279986072151059942008-11-03T15:05:00.001-07:002009-04-09T10:24:21.087-07:00

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.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.com2tag:blogger.com,1999:blog-9312877.post-1152808218897532402006-07-13T09:25:00.001-07:002009-04-09T10:25:28.030-07:00

Charley Kyd just wrote a great article, titled "How to Create Bullet Graphs To Replace Gauges in Excel". Although I wish there was an easier way to create these types of bullet graphs, this is a great article, and a nice example of how to manipulate Excel charts to make them look highly professional.Synopsis: "Bullet graphs present the same information that dashboard gauges do, but they're

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com1tag:blogger.com,1999:blog-9312877.post-1128061568624536052005-09-29T23:01:00.001-07:002009-04-09T10:25:55.847-07:00

I've come to really like a new 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+F3. Type the first few letters of the font4. Press EnterThis same basic procedure can be used to select the Font Size, by

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-1125376627752512472005-08-29T21:04:00.001-07:002009-04-09T10:26:19.094-07:00

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.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.com7tag:blogger.com,1999:blog-9312877.post-1124167964287481552005-08-15T21:38:00.001-07:002009-04-09T10:26:40.705-07:00

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:Hold down the Shift key as you select the Edit menu. This will make the "Copy as Picture..." option available.This will work for nearly any

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-1123721786672748882005-08-10T17:41:00.001-07:002009-04-09T10:27:05.836-07:00

Lately, I have been trying to use as many Excel keyboard shortcuts as I can (not only for productivity, but for ergonomic reasons). I haven't found a quick keyboard shortcut to insert a new row or column, but there is a two-step process that is quite convenient:Shift+SpaceBar = Select the current rowCtrl+Shift+PlusSign = Insert row(s) To insert a new column, in step 1 use Ctrl+SpaceBar to select

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com41tag:blogger.com,1999:blog-9312877.post-1110426132397012692005-03-09T20:42:00.000-07:002005-03-09T20:42:12.396-07:00

Allen Wyatt's Excel Tips site has a new look! And it looks great! ExcelTips.com has moved to exceltips.vitalnews.com and the new site has a wonderful selection of organized tips available online. And of course, you can still sign up for the ExcelTips newsletter. Check it out.

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0tag:blogger.com,1999:blog-9312877.post-1102526097319534292004-12-08T09:49:00.002-07:002010-02-09T15:04:02.385-07:00

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

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com4tag:blogger.com,1999:blog-9312877.post-1101339329559565772004-11-24T16:28:00.000-07:002004-11-26T15:22:52.796-07:00

The purpose of this web log is to provide RSS newsfeeds to other sites related to Microsoft Excel that wish to have quality dynamic content. If you are an Excel consultant, this log can provide a way to spread your knowledge, make press releases, etc. This log will serve as a convenient method for announcing new Excel tips added to various websites (or tips recently discovered). Please contact

Jon Wittwerhttp://www.blogger.com/profile/04461947852006005549noreply@blogger.com0