«

»

Mar 25

Dynamic Ranges

Arguably, one of the most powerful features in Excel is the ability to create dynamic ranges, which expand or contract as you add or delete data. You can create a dynamic range that consists of a single row by choosing Insert | Name | Define, then enter MYRANGE in the Names In Workbook field, then enter the following formula

=OFFSET(Sheet1!$B$5,0,0,1,COUNTA(Sheet1!$5:$5))

in the Refers To field, then finally click Add to save the reference. in addition, it's even possible to take this method a step further and make both the rows AND columns dynamic, with the following syntax

=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B),COUNTA(Sheet1!$5:$5))

where the range counts how many cell entries are in column B, and how many cell entries are in row 5, then expands or contracts the range automatically.