Isn’t it irritating when you keep having to go back into wizards, dialog boxes and formulae to manually change data ranges?
You can often get round this problem by using column references or tables.
There are times when you want a range to expand itself based on the size of the data, and here’s a way to do it.
Because of the way this works, it’s best to have your data on its own sheet, without anything else with it. You also need to make sure there’s always data in the first row and first column. Put some data in and select the data sheet before you start the next bit.
First, you need to know about Names. In Excel 2003, you’ll need to go to ‘Insert > Name > Define’ to open the Name Manager. In 2007, you’ll need the ‘Formulas’ tab, the ‘Defined Name’ group, ‘Name Manager’ button.
Create a new meaningful name using the dialog box, for example ‘tbl_mydata’ – try to stay clear of names that might be in common usage, such as ‘data’, ‘table’, etc. You can’t use spaces, but can use underscore, as in my example name.
Now, usually names refer to ranges. In this case, the name will refer to a formula, so make the name refer to:
=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
What? To explain…
The OFFSET() function defines a range.
- The first parameter shows a ‘start point’, and in this case I’ve chosen the top-left cell, which I’m assuming is the top-left cell of your data.
- the 0,0 are the number of rows and columns to move from the start point before definining the range
- the last two are the height and width of the range, using the COUNTA() function, which counts all non-blank cells – the first counting everything in column A to give the height, the second counting everything in row 1 to count the width.
Having named this formula, you can now use the name in other formulas and dialog boxes. So, for example, =COUNTA(tbl_mydata) will count the number of non-blank cells in the range. This is quite a good one to use to test your name definition, because it’ll change as you add data on.
You can also test it with Go To
(2003: Edit>Go To ;
2007: Home tab, Editing group, Find & Select, Go to; for both the shortcut is Ctrl-G or F5)
– type the name into the ‘go to’ box and hit OK. Add some more data into the first row and/or column, try it again, and you’ll see it get bigger.