Dynamic Named Ranges in MS Excel
Static and dynamic named ranges in Excel help accessing data and perform calculations quicker and easier and are more intuitive.
How do we access Excel worksheet cells? By selecting them. If we wish to access Excel cells from another worksheet in the workbook then we need to provide a ‘complete path’ or ‘address’ to the worksheet and the cells. Similarly if you wish to add different cell ranges or different areas in a worksheet the situation becomes even more complicated. An easy solution to this problem is to use named ranges. You can name a single cell or many contiguous cells or non-contiguous cells. Accessing such named cells or ranges becomes easy. For example, if you wish to add the data in the cells you use the formula ‘=sum(name of the cell range)’. Let’s say you had quantities of sales items in cells B3 to B14. You highlight or select the cells ‘B3:B14’ and name the range ‘Quantity’. Now if you use the formula ‘=sum(Quantity)’ the total is immediately displayed. You could access the named ranges in another cell also using the same formula. Now imagine you had an ‘expense’ named range in sheet1, another ‘income’ named range in sheet2 and a range called ‘depreciation’ in sheet3. You could easily calculate your ‘profit’ in sheet4 by using the simple formula ‘=sum(income-depreciation-expense)’! The Excel worksheet cells that you select and name become static named ranges, i. e. if you add more data below the named range the data is not included in the named range. How can we ensure that if more data is added to the range the data becomes a part of the named range automatically and we can perform calculations which includes the new data? By using dynamic named ranges! Assume for a moment your data is in the A column. Using the following formula you could easily create a dynamic range ‘=OFFSET(Sheet3!$A$2,0,0,COUNTA(Sheet3!$A:$A),COUNTA(Sheet3!$1:$1))’ You can see the different types of named ranges in action in the Excel training video below.