Creating a dynamic range for Excel pivot charts and tables

If you need to create pivot tables based on a dynamic named range that will expand as you add additional rows of data you. If just selecting complete columns blank values will show up in the pivot filters. Basically an easy formula can be used therefore:

=OFFSET(Sheet1!$A$1;0;0;COUNTA(Sheet1!$A:$A);4)

Further information for the offset formula can be found on the excelpivots.com blog.

Leave a comment

Your email address will not be published. Required fields are marked *