≡ Menu

How to Create a Pivot Table with Expanding Data Ranges

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.

In the example illustration, that is: Sheet1!$A$1:$D$11

Dynamic pivot table ranges

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.

Dynamic data ranges for Excel

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.

Using Named Ranges

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.

It’s fairly easy to do, just select the range and then type the name in the Name Box. Press enter and the name is defined. You can use the Name Box to select the named range as well.

Naming Ranges in Excel

Making the Named Range Dynamic with the Offset Formula

If we want to make our named range dynamic we can no longer work with the Name Box so we have to shift to the Name Manager. Formulas > Name Manager

As I’ve already set up a Named Range and imaginatively called it DATA, I can just amend the formula for this range. You could just as easily create a new name for your data range and then use the formula below:

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

This is based on the powerful OFFSET function.

Dynamic pivot table ranges

How does the OFFSET Function Work?

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:

= OFFSET(Starting or Reference Cell, Rows Down, Columns Right, Height, Width)

All of the inputs above can be number values (except the reference cell) or can refer to cell locations.

Here is a visual example to illustrate how the OFFSET function works

How the OFFSET function works

So the new dynamic range formula is:

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

Which will start in the top left hand cell (A1), and then reference a range which is COUNTA(Sheet1!$A:$A) rows high and 4 columns wide. Which is just what we wanted.

Points to bear in mind:

  • It is good practice to start your source data with headers in cell A1, if you don’t you will have to amend your formula to reflect any extraneous rows before the data starts
  • COUNTA counts all the non empty cells so you should also confirm that Column A doesn’t have empty cells, you could however refer to a different column if that held complete data

Having set up the Dynamic range it is now time to adjust your pivot table so that it is now based on the new named range:

Select a cell in the pivot table

  • Options > Change Source Data
  • Overwrite the Range: with the name of your range, in my case DATA
  • Click Ok and rest safe in the knowledge that your pivot will always look at the full range of data.
Doctor Moxie

An Article By -

NHS Accountant with geeky tendencies - serial blogger on subjects varying from Excel, Raspberry Pi, productivity, allotment gardening and running. The ExcelPivots blog is reserved for Excel topics.

Comments on this entry are closed.

  • Pingback: Angela Wolff()

  • gigi

    Hey, first off thanks for the awesome tutorial.
    I’ve ran into a problem with the data source though. If I try to open the file on a different computer it is looking for the source data on the previous computer (e.g. c:\program files\etc) thus it’s not working. I’ve tried checking “save source data with file” and “Refresh on file open” but I encounter the same issue.

    Do you have any ideeas how I could fix this problem? Thank you.

    • Deric

      As you checked “Refresh on file open”, Excel will try to refresh the source data, i.e. data not found finally.

      • Aakash

        I have the same problem,Whenever I copy the file to different location and try to open, it is looking for the source data on previous file location.Please help me out on this.

        • ChrisCarroll

          Is your source data stored with the same spreadsheet or is it pulled from a separate file?

          If it is the latter you should save the source file in a central location (i.e. a shared drive as opposed to a specific C drive). you can then change the name range to point at that and regardless of what machine you run it on it should work.

          If the source data is in the same spreadsheet then just rewrite your name range to only contain the source sheet name and cells, not the full location (‘C:userschrisetc.)

          =OFFSET(general_report!$E$1,0,0,COUNTA(general_report!$E:$E),28)

          If you click into ‘Formulas’ –> ‘Name Manager’ you should be able to see the ‘Refers to’ section and fix from there.

          hope that helps in some way

          • Aakash

            Hey,thanks ChrisCarroll for your explanation.

            I am using the same way as you are suggesting me.

            In my case, source data is in the same spreadsheet, and i am rewriting the name range with sheet name not the full location like(‘C:usersaakashetc.).

            source sheet name is ‘RowData’ and i am using following name range formula with name as ‘DATA’ :

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

            and then i set source data of pivot table as ‘DATA’.

            But when I copy and open the file then source data refers to previous absolute location like
            (‘vardatareport_downloadTestFile.xlsx!DATA’)

            Please may you suggest me any other solution.

          • ChrisCarroll

            Hi Aakash,

            are you copying the file from location A to B and then opening from B? or are you copying data from the file in A into a new file in B?

            I have copied my file from various locations and cannot replicate your issue. The formulas stay with the spreadsheet.

            If you are copying the data from 1 spreadsheet to another would you not try making a copy of the 1st working file, then opening the copy and editing it as needed?

            It might not help because it sounds like you have your’s set up the same as me, but just in case it helps here’s what I have;

            when i go into Formulas –> Name Manager, I have: ALL_OPEN_LOGS

            under Value it has defaulted to: {…}

            under refers to i have: =OFFSET(full_report!$E$1,0,0,COUNTA(full_report!$E:$E),28)

            NOTE: full_report is the name of the sheet where all my raw data is stored

            under scope i have: Workbook

            Then when i click on my pivot table on a separate sheet called ‘Tables’ then select from the top bar Options –> Change Data Source, I can see the table/Range is set to: ALL_OPEN_LOGS

            As i said earlier I can copy the file to multiple locations and it is still working fine, so I’m not sure what your issue is if all of the above is the same, apologies I cannot be of more help.

          • http://whatapalaver.co.uk Dr Moxie

            I was going to suggest having a look at Name Manager as well. I wondered if you had more than one named range “Data”. You should expect it this range to reference the open spreadsheet.

  • TM

    Pivot Table does not recognize the name range (unfortunately). So, although the formula worked (which is great) using it in a Pivot table doesn’t work and that was the reason I was looking at your site. I need to be able to use a Named Range to capture dynamic, and changing rows of data. Error Received for Reference: “The PivotTable Field name is not valid. To create a Pivot table report you must…. blah blah blah” I have headers captured in my PivotTable so this error is a misfire. I am using Excel 2010. Thanks.

    • http://www.doctormoxie.com Doctor Moxie

      It should work with Excel 2010, that’s what I’m using. It sounds from your description as though you might have a problem with the headers in the source data. Check that you don’t have a blank column heading (perhaps a hidden blank column).
      If in doubt select the data manually and see if you get the same error message when trying to insert a pivot table.
      And my final thought is to check the dynamic range. If you type the name in the address bar, does it highlight the expected block of data and doesn’t accidentally include an extra blank column?

      • ColeSlawBecky

        I had this same problem, and I found a fix. The issue was that the COUNTA formula was counting the original cell (A1 in this example) as part of the number of cells it needed to offset by. So it was offsetting by one too many rows and that row did not have a header, thus causing the issue. I fixed it by adding a (-1) to the COUNTA.
        E.g. =OFFSET(Sheet1!$A$1,0,0,(COUNTA(Sheet1!$A:$A)-1),4)

    • Elia

      Hi, this problem appears when your offset formula refers to a range from another book. So if you move a sheet to make every sheet in one book then it should work.

  • ADP

    Hi,

    Very useful tutorial.
    If I have 626 coloumns and 357 rows with A1 as a starting cell, what would be the new new dynamic range formula?

    =OFFSET(Sheet1!$A$1,0,0,357,626)

    Thanks

    • Greg

      Hi ADP,

      I don’t believe this would be dynamic because you are setting the row count and column count. You would want to replace the 357 in your offset formula with the COUNTA function like this:

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

  • SteveB

    Worked for me
    =OFFSET(LaborHours!$A$1,0,0,COUNTA(LaborHours!$A:$A),7)

  • ChrisCarroll

    I have an issue with this, don’t now if anyone can think of a workaround for me?
    I extract data every day and the number of rows varies each day – usually just over 2,000.
    I input this raw data onto say SHEET1.
    SHEET2 & SHEET3 then extract all the relevant data from SHEET1 that they need.

    In order to populate SHEET2 & SHEET3 i have formulas in to say if Cell x on SHEET1 = blank then you are BLANK otherwise fill yourself with contents of cell x
    (e.g. =IF(SHEET1!A2134=””,””,SHEET1!A2134)

    The issue I have is that the formula below is looking at all cells that have data displayed plus all the cells that have nothing displayed but contain formulas within them;
    =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),4)

    At present I have 2,000 lines with formula in them.
    on average we’ll say about 1,000 lines show up on SHEET2 & SHEET3 as being populated and the remaining 1,000 lines are blank with formula only.
    When I created the dynamic range and used it in a pivot table it shows all the different results plus 1 extra that is blank and throwing my total way off – example below (in reality I’ve hundreds of rows with small quanities).
    Is there any way to tell it to only count cells that contain text??
    any help is appreciated.

    PRODUCTA 150
    PRODUCTB 250
    PRODUCTC 100
    PRODUCTD 500
    1,000
    ______________________
    TOTAL 2,000

    • http://www.doctormoxie.com Doctor Moxie

      I might not have completely understood what you are doing but I wondered if you could add in an extra formula column in your sheets that test whether the relevant column includes a text item. This would hold either a true or false and this field could be dragged into the pivot as a report filter which you could then use to select TRUE only.

      You might find a neater solution by using sheet 1 as a data source and creating a query from it using Microsoft Query.

      • ChrisCarroll

        Thanks for the quick reply!!
        I created a hidden TRUE/FALSE column and filtered the tables accordingly.
        It worked like a charm.
        Filter doesn’t look great above each table but if it save me manually updating each of the 25 table each day I am happy to live with it!

        For anyone reading this in future with same issue and the above dynamic range not excluding blank rows that you have due to the blank rows containing formula do the following as Dr Moxie suggested, do this;

        Insert new column A with a heading of say ‘DATA’
        Then in cell A2 under the ‘DATA’ heading type; =if(B1=””,”EMPTY”,”TEXT”)
        Then drag down as far as where your formula stop, e.g. cell A2000
        Now use the dynamic range on your pivot table
        From the PivotTable Field List add ‘DATA’ into the ‘Report Filter’ section and then filter by ‘TEXT’
        All your totals will match up based only on the fields you are looking for.

        Thanks again Doctor Moxie.

        • http://www.doctormoxie.com Doctor Moxie

          I’m glad it worked.
          If you don’t like the filters you could hide those rows so that you don’t see them.

          • ChrisCarroll

            Thanks Moxie,
            this would work normally but I’ve a mixture of small tables and large tables on the same sheets for printouts that get distributed around the business so hiding a row for the filter of a smaller table that is halfway down the page will also hide a row of data from the middle of the larger table.
            Thanks though

  • Nikhilesh

    Worked for me thanx!!

  • Sanchari

    Its is not working for me. I named the data range and after I wrote the offset formula when I am trying to set that data range in my pivot it simply says data source reference is not valid. Please suggest something.

    • Jaco

      check if excel accepts your field seperator in the defined formula:
      in stead of comma (,) use semi colon (;)
      it worked for me

      • Dale Flint

        came across this and all looks well but I too have the “source reference is not valid” issue. The semicolon didn’t help either. Here is my formula – =OFFSET(‘2016Sales’!$A$1,0,0,COUNTA(‘2016Sales’!$A:$A),20). No issues when I enter the formula in the Name Manager, only when I change the Source Data.

    • TcheQ

      Probably a bit late, but I bet it’s because your source data has a blank header somewhere (mine did). I used CountA(row) on the headers (in the example it is 4) to solve it (since extra columns may be added at a later stage)

  • Craig Marshall

    Thanks

    have managed to add the dynamic data range in but can you tell me whether the pivot table should automatically update or whether you need to click ‘refresh’ in order for the newly added rows of data to be incorporated into the pivot table?

    Cheers

    • http://www.doctormoxie.com Doctor Moxie

      Hi Craig
      It won’t automatically update so you would need to click the refresh button.

    • ChrisCarroll

      just in addition to Moxie’s answer;
      I just want to point out that if you have a load of different pivot tables using the same dynamic data range then you will only need to refresh 1 for them all to update.
      I have about 20 different pivot tables in the one spreadsheet that look toward 2 different dynamic data ranges, instead of having to refresh all 20, I just need to refresh 2 (1 of each)

  • http://centafinance.com Rajagopal

    Can you make a single pivot table from multiple dynamic ranges on different worksheets in the same work book?
    Thanks

  • Jmfva

    Thanks, works well.

  • Pingback: Pivot table on a active worksheet()

  • pdross

    I’m doing something obvious wrong but I can’t find it. I tried to reference the dynamic named range called “DATA” from my pivot table when asked for “Data or Range”. When I enter the range called “DATA” I get an error saying “The command requires at least 2 rows of source data”. When I manually enter the named range in the source table it does select all the rows and columns I expect. Thanks for the help…

    • isolino ferreira

      Maybe so late. But to solve this problem you need a selected area with two lines at least.

  • Dale Anderson

    A quick alternative is to select all columns for your data and then filter out blanks using one of your fields…

  • SCARNG

    Can I make rolling 12 month chart using Pivot Table? Can’t seem to get offset to working with Pivot Table.

    • ChrisCarroll

      Hi Scaring,

      I don’t think you need an offset for this.
      you can highlight your 12 rows.
      row 1 could be;

      =TEXT(TODAY()+M1,”MMM”)

      then the next 11 could be;
      =TEXT((DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))),”MMM”)
      =TEXT((DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY()))),”MMM”)
      =TEXT((DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))),”MMM”)
      =TEXT((DATE(YEAR(TODAY()),MONTH(TODAY())-4,DAY(TODAY()))),”MMM”)
      =TEXT((DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY()))),”MMM”)
      …….etc.

      this way the 12 select fields will change for you, eliminating the need for the offset. your data columns next to each month could then vlookup a data sheet based on the month displayed in rows detailed above.
      It would be easier to set up and I think it would also load the data quicker.

  • isolino ferreira

    Great article. I’m inserting data using POI and this offset function help me so much

%d bloggers like this: