Microsoft Excel Users

Microsoft Excel Users

65,010 members
  • Join

    When you join a group, other members will be able to see your profile and message you. The group logo will be visible on your profile unless you change that setting.

  • Information and settings

Have something to say? Join LinkedIn for free to participate in the conversation. When you join, you can comment and post your own discussions.

Gordon

I am trying to add a dynamic range to a pivot table report instead of modifying the reange every time I use the report. I am using excel 2010 Any recommendations?

Director of Finance & HR at 3-D Business Accelerator

  • Comment (14)
  • August 11, 2012
  • Close viewer

Comments

  • Melanie W.

    Melanie

    Melanie W.

    Experienced accountant

    To create a Name (say, Data) Formulas -> Define Name. In the Refers To Cell, please input

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

    Note : The above formula has been prepared on the assumption that your data file has been stored from cell A1 of Sheet 1. Pls amend the formula based on your data set.

    After defining the above Name, you can input the said Name in the Data Source of your pivot table. After updating your data source, you have just refresh your pivot table only instead of redefining the Data Source from time to time.

  • Khalid T.

    Khalid

    Khalid T.

    Head of Information Systems Center at Al-Faris State Co.

    Convert range to Table. By selecting your range and from "Insert" Tab on ribbon press "Table" to create table; you will get a dynamic range.

  • Petros C.

    Petros

    Petros C.

    Financial Modeller / VBA Developer - RibbonCommander.com

    I agree with Khalid, let Excel maintain the range Name for you.
    Melanie's range formula may be not be accurate if your data set has blanks in column A

    More pivot tips:

    http://www.spreadsheet1.com/pivot-problems.html

  • Guillermo C.

    Guillermo

    Guillermo C.

    Reservoir Management Information Manager at Chevron

    The offset formula is explained in :

    http://greatofficetips.wordpress.com/2012/06/24/excel-how-to-create-a-dynamic-pull-down-list-part-2/

  • Edwin S.

    Edwin

    Edwin S.

    Mobile Application Developer at Bromance Labs

    I take another approach: Make your range a table (Home Tab | Format as Table). You can use the table as your pivot table source data, it becomes a named range, AND it automatically updates when you add more rows/columns.

    Fun fact: this is also handy for charts that have changing data.

  • Winston S.

    Winston

    Winston S.

    Controller at Sodexo B&I

    @All,

    Use INDEX() in your dynamic name range formulas instead of OFFSET(). OFFSET() is a volatile function and will slow down your workbooks.

    Daniel Ferry at Excel Hero gives us more:
    http://www.excelhero.com/blog/2011/03/the-imposing-index.html

    Best,
    w

  • José María M.

    José María

    José María M.

    Owner, Aula Divulgacion Informatica

    The use of OFFSET formula only meaningful for users with Excel prior to 2003, from Excel 2003-2007-2010-2013 should be used Insert-Table (or Format as Table with the database)

Have something to say? Join LinkedIn for free to participate in the conversation. When you join, you can comment and post your own discussions.

Feedback