Microsoft

Pro tip: Accommodate an expanding range in Excel

Design is best, but when you're stuck with an existing data structure, it helps to know how to work around trouble. Susan Harkins explains.

Excel sheets grow — sometimes by adding new functionality, but most often, by simply adding new data. A good design will accommodate most growth and changes, but often, you find yourself working with a data set that doesn't conform well to what you need to do. Fortunately, with a little know-how, you can get what you want. When you can't, you might have to consider restricting the data. In this example, we'll look at three ways to do the same thing — get dependent functions to update automatically when you expand a data range. Specifically, we'll count string occurrences across several columns rather than one. You can work with a simple data set of your own or download the .xlsx demonstration file.

Counting is easy

Figure A shows a simple data set of several columns and rows. Because the names occur across columns and rows, we'll reference the range. To that end, I've used a simple COUNTIF() that evaluates the entire range instead of a single row or column, as you might be more accustomed to seeing.

Figure A

Figure A

COUNTIF() returns the number of times each name occurs in the range.

This function uses the syntax:

=COUNTIF(range, criteria)

After entering the first function, =COUNTIF($B$2:$E$9,$H2), into cell I2, I copied it to the remaining list of unique names. Notice that the range reference is absolute, but only the column is absolute in the criteria reference. The absolute reference is necessary to get an accurate count when evaluating a range instead of a row or column.

Adding data complicates the count

If you're working with a stable range where the dimensions don't increase, this solution works fine. However, as soon as you add a new row or column, you run into trouble because the COUNTIF() functions won't update automatically to include the new data. As you can see in Figure B, the counts remain the same even after adding a new row and column.

Figure B

Figure B

The COUNTIF() functions don't update when you add new data.

The absolute reference in the range reference is necessary in this case. Because the reference is absolute, the function won't update automatically to include new data added each day.

An easy fix

You probably know that you can insert a row or column into a range to take advantage of automatic referencing. Figure C shows the result of adding a new row. As you can see, the functions update, but the new row introduces new problems:

  • The data set is no longer in date order. Usually this won't matter because you can sort your data.
  • The newly inserted row creates a blank in the unique name list. If you can move the unique list and the column of COUNTIF() functions to another sheet, this problem disappears.

Figure C

Figure C

Inserting a row or column is a simple solution if it doesn't introduce new problems.

Although I didn't show it using the example data set, the same troublesome potential exists when inserting a column. However, these small problems are easily solved. Don't store anything else on the sheet with your data set, and inserting suddenly becomes an easy solution. You could even create a macro that inserts new columns and rows for your users.

Table rescue

Depending on the expertise level of your users, you might consider converting the data set to a Table object (supported by Excel 2007 and later). When you insert columns and rows, the references will update automatically.

To convert a data set into a Table, do the following:

  1. Click inside the data set.
  2. On the Insert tab, click Table in the Tables group.
  3. Specify whether the table has headers or not. In this case, the data set has headers (Figure D).
    Figure D
    Figure D
  4. Click OK.

With the new Table object in place, you can insert a new row quickly and easily. Simply select the first blank cell under the table and start entering data. Excel will automatically extend the Table object to include the new row and update the reference in your COUNTIF() functions, as shown in Figure E.

Figure E

Figure E

Excel's Table object is flexible enough to include new data and update dependent functions.

To make the most of the Table arrangement, use a structured reference in the COUNTIF() functions. Specifically, replace the absolute range reference with the name of the new Table, as shown in Figure F. Then, when you insert a new column to the right, the COUNTIF() functions will update accordingly.

Figure F

Figure F

After converting a data set to a Table, update the dependent references.

When you create a Table object, Excel names it — use that name or give it a more meaningful name yourself. Click anywhere inside the Table, and then click the Design contextual Tab. The Table's name will appear in the Table Name control on the far left, as shown in Figure G. To rename the table, simply click inside and type a new name.

Figure G

Figure G

Use Excel's default name or rename it.

Restructuring

The Table solution is my favorite, but it might not be practical for you. Excel's Table object, while powerful, does have a few limitations. For that reason, I'd like to introduce one more possibility — one that's more difficult to implement after the fact.

As is, the data range structure is a bit ambiguous. A more stable structure is shown in Figure H. Adding the names into a long column — rather than spreading them across several columns — makes implementing other Excel features a bit easier (because you can easily sort, adding new records for past dates isn't a problem).

Figure H

Figure H

Instead of going across, go down.

The column reference shown in Figure I handles new rows as-is. The sheet must be clear of other data below the data range, because the column reference evaluates the entire column (including the header cell).

Figure I

Figure I

The structured column reference evaluates new rows.

Restructuring is awkward, especially if you have a lot of data. If you can implement this structure initially, you can avoid troubles as your workbook grows.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox