There are multiple ways to achieve dynamic ranges:
COUNTA in a named range
If you use the
=COUNTA($A:$A), you'll receive all items in column A. Make sure that you pick a column that is always filled, e.g. the ID column. If your data table starts somewhere further below, you must also account for that:
Now, you can use the formula
=OFFSET($A$1,0,0,COUNTA($A:$A),4) to determine a range starting in A1 that is 4 columns wide and contains all rows.
The trick to use this defined range is to enter it as a named range! To do this, go to the Names Manager (in the Formula tab) and insert a new name (e.g. data). Here, instead of providing the reference to a fixed range, copy in the above formula. (Note: if you want to edit the formula, don't forget to press F2 before using the arrow keys, else the formula will be messed up by cell references). Make sure that you use the
$ for all cell references.
You can now use the name data as a source for your pivot. If you want to verify that the full range is used, just enter the name in the Name box left of the formula bar (you need to actually type it there) - or just click on the small icon next to the formula in the Name manager.
The small disadvantage of
OFFSET is that it's volatile, i.e. that it is calculated with every calculation run, even if the referred data is not changed at all. This might slow your model down in case you use the dynamic name frequently in formulas.
To avoid this, replace
INDEX in the following way:
This will do the same job, but is non-volatile. In addition, if you insert a new column before column D, this formula will adjust while in the former option you'd have to replace the 4 with a 5 (or use
Use Excel table
A much more elegant way than named ranges are Excel tables. If you import data, Excel usually stores the imported data already in a table (you can recognize it by its alternating row color and the Table tab that is available in the ribbon upon selection).
Alternatively, you can insert a Table manually (Insert tab - Insert Table).
Provide the table with a speaking name (in the name field in the Table tab) - and then simply use this name as the source of your pivot! If the data changes, the table and therefore the associated range for the pivot will adjust automatically.
However, please be aware that you'll still need to refresh your pivot table(s) after any update!