How to Create a Dot Plot

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.

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):

=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 DOWN, you can use ROUNDUP or ROUNDDOWN instead, or the CEILING or FLOOR functions:

=ROUNDUP(number/mult,0)*mult
=CEILING(number,mult)

=ROUNDDOWN(number/mult,0)*mult
=FLOOR(number,mult)

The ROUND, ROUNDUP, and ROUNDDOWN functions will round negative numbers as if you first multiplied the value by -1, did the rounding, and then multipled by -1 again. So -23.6 rounded UP would be -24. When using CEILING or FLOOR, the multiple needs to be the same sign as the number.

Round Price to Nearest Nickel

=ROUND(price/0.05,0)*0.05
=MROUND(price,0.05)

=CEILING(price,0.05)    (rounds up)

Round Minutes to the Nearest Second

=ROUND(minutes/(1/60),0)*(1/60)
=MROUND(minutes,(1/60))

Round Feet to the Nearest Inch

=ROUND(feet/(1/12),0)*(1/12)
=MROUND(feet,(1/12))

Round to the Nearest Even Number

=ROUND(number/2,0)*2
=EVEN(number)    (rounds up)

Round to the Nearest Integer

=ROUND(number,0)

=INT(number)    (rounds down)

Round to the Nearest 15-Minute Interval

=ROUND(minutes/15,0)*15

=CEILING(minutes,15)   (rounds up)

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.

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 in your list. This change in Excel 2007 was actually an upgrade.

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. 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+1 to increment the number. If I were to delete row 6 I'd get a #REF! error and I'd have to fix the formula. To avoid getting the #REF! error, you can use the following formula where this_cell is the cell containing the formula:

=OFFSET(this_cell,-1,0,1,1)+1

In this example, the OFFSET function is referencing the cell immediately above this_cell. So if this_cell is B7, the formula is essentially the same as =B6+1. But now if you delete row 6, you don't get a #REF! error.

This same concept works for the running balance in column J. The formula for the balance in cell J7 would normally be =J6+I7-H7. However, just as in the previous example, if you were to delete row 6, you'd get a #REF! error. So instead you can use the OFFSET function:

=OFFSET(J7,-1,0,1,1)+I7-H7

I've used this technique in a number of my templates because it makes the spreadsheet easier to use. The problem with this approach is that it makes the formulas more difficult to debug, because it looks like the formula is referencing itself.

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.

=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 that uses MATCH. The reason for the 255 repeated z's for the lookup value is to ensure that the lookup value is larger than any other text value in the range. "Larger" means that if you were to sort the values in ascending order, the largest value would be listed last. In the above formulas, if there are no text values within the lookup range greater than or equal to the lookup value, the MATCH function will return the position of the LAST text value.

The problem with these formulas is that there ARE characters that are larger than "z". If you look up "Default Sort Order" in the Excel help, they don't mention all special characters. Here is an excerpt from the Excel 2003 help documentation:

Text and text that includes numbers are sorted 
in the following order:
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , 
. / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D 
E F G H I J K L M N O P Q R S T U V W X Y Z

It turns out that all the Greek characters (alpha through Ω) come after "z" in the sort order. I mean the Greek characters in the Unicode data set, not characters just displayed using the Greek font.

I didn't test ALL the Unicode characters, but I did find that Cyrillic, Hebrew, and some Arabic characters also come after "z" in the sort order. Most of the special characters like ¢, £, ©, ®, °, ¶, •, …, €, and ™ come BEFORE "z" in the sort order, so if you never use any of the Greek, Cyrillic, Hebrew, or Arabic characters in your spreadsheet, using REPT("z",255) for the lookup value may be just fine.

If you like to use Greek letters (like I do), using the Capital Letter Omega in the lookup value may be appropriate:

=INDEX(A:A,MATCH(REPT("Ω",255),A:A))

To enter the special character Ω in Excel you can use your Character Map (Start > Accessories > System Tools), or you can use the shortcut keyboard sequence Alt+234 (Press and hold the Alt key, then use the numeric keypad to enter 234 and then let go of Alt).

References:
- ANSI Character Set
- Collation
- Enter Unicode Characters in Windows
- Greek Capital Omega
- Entering Special Characters in Excel

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.

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
=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

Both of these formulas work very well and are still able to return the last numeric value even if the range contains blanks, error values (like #N/A), and text values.

The value 9.99999999999999E+307 is the largest number you can enter in a cell. The range doesn't have to be a full column. You could use B5:B100 instead of A:A.

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.

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 to the same reference format. For example, if the first reference in the selection is $A$1, then all of the references will change to A$1.

To change just a single reference within a formula I've found that the fastest way for me is to use the mouse to click on the reference within the Formula bar and then press F4 (rather than double-clicking on the cell, then clicking on the right reference).

For me, the time when it is NOT faster to use F4 is when I want to toggle a reference to (or from) A$1 or $A1. I can never remember which one comes first and I inevitably press F4 too many times and end up in an eternal loop trying to get to the right format. Going from $A$1 to A1 is just a quick 3 presses of F4, which I can remember.

By the way, when you are not editing a formula, F4 is also the alternative shortcut to Ctrl+y ('repeat' or 'redo').

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. 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 entire range of cells.

This is basically an alternative approach to entering a value in a single cell and then copying that cell and pasting the value or the formula. Using Ctrl+Enter does not copy formatting.

If you are using a relative reference in your formula (like =A1), then the formula in the other filles cells will be the same as if you entered the formula in just the one cell and then copied that cell to the other cells in the range. If you want the formula reference to not change when you use Ctrl+Enter, you will need to use an absolute reference (like =$A$1).

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