It’s fairly easy to do, just select the range and then type the name in the Name Box. You can use the Name Box to select the named range as well.
The OFFSET function returns a range based on a given starting point with a specified height and width (no of cells).
It’s a really useful formula for setting up dynamic ranges as you can vary the height and width on the result of another formula, in our example above this other formula is COUNTA which sets the height.
The OFFSET function has the following syntax: All of the inputs above can be number values (except the reference cell) or can refer to cell locations.
This Excel tutorial shows you how to create pivot tables based on a dynamic named range that will expand as you add additional rows of data.
This can be a huge time saver and helps to protect against inadvertent errors that result when pivot tables draw from only part of the data source.
Typically, when you build a pivot table, you select any cell in your data range and choose INSERT Pivot Table.This generates the following dialogue box with a fixed Table/Range defined by an Absolute formula. $A:$D This is fine until you come to add more data.Here is the same table with an additional week’s worth of course attendance data added.Refreshing the pivot table will not pull in the extra days data as the data range is still fixed.You can update this by clicking on the Pivot Table and then choosing Options Change Data Source, but it’s an additional task to remember and if you have multiple pivot tables pulling from the same data range it is quite time consuming.Naming a range is relatively easy and when you use the name rather than the reference in a formula it really aids the understanding of the formula.