Formula Friday - Create A Dynamic Pivot Table Data Source Using OFFSET and COUNTA Functions

Happy Friday Excellers…..

Pivot Tables are great for taking hundreds or even thousands of rows of data and be able to summarise and analyse your data set with a few clicks of your mouse, if you want to read more about creating your first Pivot Table the you can check out my blog post here or my YouTube Video here.

Using A Dynamic Data Source

So, if you continually add extra rows to your Pivot Table data source, or indeed remove them it would be really useful to have Excel automatically adjust the data source to accommodate these changes. There are a few ways to create a dynamic data source, but today let’s look at using a dynamic formula to define a Named Range as the data source. Let’s go ahead and create it.

The Syntax of OFFSET Formula is-

reference – this argument is required it is the reference from where you want to base the OFFSET. This must refer to a cell or a range of adjacent cells.

rows – this argument is required it is the number of rows either up or down that you want the left uppermost cell to refer to.

columns – this argument is required it is the number of columns, left or right that you want the upeermost cell to refer to.

height – this argument is optional and is the height in the number of rows that you want the returned reference to be – it must be a positive number.

width – this argument is optional and is the width in the number of columns that you want the returned reference to be -it must be a positive number.

This example uses Excel 2013.

Formulas Tab – Defined Names Group- Define Name
In the New Name dialog box, call the named range something, I have called this range “my_data_range”.
In the Refers To section is where we enter the OFFSET Formula, which returns a reference to a range specified by number of rows and columns.

I have my sample data set below which is four columns in width- so I need to design a dynamic range which is four columns wide.

offset1

The arguments in this OFFSET formula are-

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

The data is contained Sheet1

Reference Cell -Sheet1!$A$1 – as this is a Pivot Table, we need the Column Names so we start at reference A1
Rows to offset -0
Columns to offset – 0
Number of rows –COUNTA(Sheet1!$A:$A)
Number of columns -4

The COUNTA part of the formula counts the number of values in the range that are not empty. We use this in the ROWS argument. When you add a value to the range i.e a new row of data, COUNTA($A:$A) increases. As a result, the named range expands. (Or reduces as rows are deleted) Cool huh?!.

offset2