Selecting a range of cells in an Excel worksheet, is such a common thing to do that many take it for granted. However, there are a few shortcuts that are worth reviewing, and a very useful formula technique called dynamic named ranges, that in my opinion, is underutilized by many Excel analysts.
You are probably aware, that if a cell within a group of cells containing data is selected, you can use the control key to move within the data, For example, <ctrl> with the down arrow will move the selected cell down to the last cell containing data before an empty cell. The up, left, and right arrows behave similarly.
We can combine this feature with holding down the <shift> key to select a range. If we hold down the <shift> key and hit <ctrl> down arrow we will highlight all the cells from the present position to the bottom of a column of data. If we continue to hold down the <shift> key and hit <ctrl> right arrow, we will select a rectangular range.
Less widely known, is that using of the shift key to select a range can be combined with the cell location field in the Excel toolbar. If we are on cell B2, we can enter another cell, perhaps G7, in the cell location field. Before hitting enter, however, hold down the <shift> key. Now when we hit <enter>, the entire range from B2 to G7 will be selected.
This technique can be helpful in situations where it might be annoying to select a column of cells in the conventional fashion. For example, in an earlier blog we saw how to add sparklines to some economic data. To accomplish this, we selected just over two hundred rows in a column newly created for the sparklines. An easy way to select the necessary rows in the “B” column, we note that the top row in the range to be selected is B3. We then move to column C and hit <ctrl> and the down arrow. This takes us to the last row of data. After moving one column to the left, we enter B3 in the cell locator and hit <shift><enter>. This selects the required range, from the end of the data back to B3.
While not strictly speaking about range selection technique, dynamic named ranges solve similar problems and thus should be mentioned here.
If your worksheet data has been converted to an Excel table, data newly added at the bottom will be incorporated into the table automatically. The same is not true of a named range. We can, however, achieve a similar result with a dynamic named range.
We start by going to the formula tab on the Excel ribbon and bringing up the Name Manager. We create a new name; I’ve chosen “Barney” for this example. While names are most commonly used to define ranges, it is possible to use names to define formulas. We will now combine those two techniques and give a name to a formula that defines a range.
We will do this using the OFFSET function. Let’s imagine we wish to take the sum of a list of cells, but we would like new data added to the bottom to be automatically included in the sum.
The first argument of the OFFSET function is the cell at the top of our list. The second and third arguments are the row and column offsets. These are the arguments that are generally important when using the OFFSET function. In this case, however, the offsets are both zero because we want our starting point in this example to be $A$3, the referenced cell at the top of the list. For a dynamic named range, the real work is done by the fourth and fifth arguments, indicating the number of rows and columns we want the returned range to be. In this example, we will always want to return a single column. It is the number of rows we wish to make dynamic. We achieve this using the COUNTA function with the argument $A$A. This will give us the count of rows from the starting point to the end of the data, exactly the number we need.
Once the name is defined, we can use it in a formula like any other named range. Dynamic named ranges do not appear in the drop-down list.
Here we are summing the first nine prime numbers. If we add a tenth to the bottom of the list in column A, the count and sum automatically recalculate when the dynamic named range changes.
The <ctrl> key in association with the arrow keys moves to the edge of an area containing data. Holding down the <shift> key at the same time selects the range from the starting cell to the cell at the limit of the data range.
The formula for dynamic named ranges may look daunting at first glance, but in fact it is quick and easy and provides a practical means to create columns of data that are responsive to the addition of new data without the need for tables.