Working with Excel 2007

What are ranges? What do they do? Basically, they save you time and effort. Which do you think will be more beneficial and time-saving?

= B1 + B2 + B3 + B4 + B5 + B6 + B7 + B8 + B8 + B9 + B10 + B11

Or

= SUM(B1:B11)

The second one is clearly the less complicated of the two. Just think if you had a spreadsheet full of data and need to calculate the sum of every row from B to Z. Would you want to type in every cell reference one-by-one? I don't think so.

This is where ranges come in. Ranges offer a simple solution to long and drawn-out formulas. You will select a cell ranges in three different instances; when required from a dialog field, in a function argument, and before using a command that uses a range input. There are also three common ways in which you can select a range using a dialog box field or function argument.

Ways to Select a Range

The simplest technique to select a range is to just type it in. In a dialog box or a function, type in the anchor cell (the upper-left cell) followed by a colon and then the lower-right cell. For example, say you need the average of the all the numbers in cell C5 to E12. The way you would type the function, and thus the range, is like this: =AVERAGE(C5:E12). The part that is bolded is the cell range.

Sometimes, you don't know or can't see the reference cell range while entering the function. If it is blocked by a dialog box or you are referencing cells from another sheet or workbook (3D range), it is too much of a hassle to continuously going to look to see what your cell reference is. In this instance, it is better to either use the mouse or keyboard to select the range.

With the mouse, click the anchor cell and drag down to the lower-right cell. There are a couple of tricks that I can show you while using this selection technique:

If you need to see the active cell after selection a large range, use the scroll bars to scroll back up or press Ctrl+backspace on your keyboard

Sometimes you will need to use a noncontiguous range; where the cells are scattered across the sheet and not in a contiguous, rectangular section. To do this, just hold down you Ctrl key while using your mouse to select the cells.

Excel has an Extend mode for when you want to select a contiguous, rectangular section. Click on the anchor cell, press F8, then click on the lower-right cell. Excel will select the entire range. Press F8 to turn off Extend mode.

While dragging the mouse, if you let it go and select too small or too large of a range, there is a quick fix for that. Just hold down Shift and click the correct lower-right cell. Excel automatically adjusts the range.

If you're like me, you don't want to be bothered going from keyboard to mouse continuously. If I am already typing, I would rather stick with shortcut keys. Just like Word, Excel has a few tricks up its sleeves when it comes to keyboard shortcuts:

For contiguous range, navigate to the anchor cell (top-left cell) and press Ctrl+Shift+right arrow to move down the column. Then press Ctrl+Shift+down arrow to move across the row until you reached the lower-right cell.

To scroll through the cells without deselecting them, turn on the Scroll Lock on your keyboard.3-D Ranges

I only touched on briefly about 3-D ranges earlier in this article. A 3-D range, or 3-D reference, is a range that spans across multiple worksheets. This is a power tool in Excel; and a tool I use quite often.

With my business, I often set up workbooks with multiple worksheets. For instance, to keep up with my bills, I set up a workbook that have 13 different worksheets; one for each month of the year and one for the running totals. To be able to keep my 'Running Total' sheet updated, I reference cells in each of the other worksheets in my functions.

There could be two main reasons why you will need to use a 3-D range. One could be to format all of your sheets at one time. For instance, if all of your worksheets share the same type of title at the top of the page, you can select all the worksheets by clicking the first one, holding the Shift key, and then click the last one. Now select the cell you want to format, apply your formatting, and watch all the same cell in all the worksheets be changed.

Another reason to use the 3-D range would be like the example above with the 'Running Total' worksheet. To do this, start your formula in the relevant cell. Select all the sheets by selecting the first sheet, holding down the Shift key, and clicking the last sheet. Now, select your cell range by either the typing, mouse, or keyboard technique. Press Enter to complete your formula. Here is an example of a formula I used for my 'Running Total' worksheet:

=SUM(Jan:Dec!B4:B11)

What I am saying with this formula is that I want the sum of all the cells 'B4' to 'B11' in the worksheets 'Jan' to 'Dec'. This is an example of a 3-D range.