create a pivot table

Posted on 2012-09-09
Last Modified: 2012-09-10
I would like to create pivot that will include all the rows and columns on a given sheet, even if the rows increase/decrease.

On the sample I have included a manual pivot with the needed filter.
However, on refresh, the source data may have changed, so I need it to choose all the data on the source sheet first.
Question by:Euro5
    LVL 8

    Expert Comment

    by:Elton Pascua
    You are using a table as your data source so it should already dynamically adjust rows and columns as you add them (just refresh the Pivot Table).

    Just in case that is not the case, you can create a dynamic range (Formulas > Name Manager) and make it the source of your pivot table.


    Open in new window


    Author Comment

    techfanatic - The pivot table has a determined range - so it won't add the row or column even on refresh. I really cant find anything formulas>Name Manager that creates a dynamic a loss here.
    LVL 92

    Assisted Solution

    by:Patrick Matthews
    Please have a look at my article here:

    It describes techniques no only for automatically updating the source range for a PT, but also for automatically refreshing your PTs.

    The named range approach shown above is a viable technique (although I prefer a different formula, as shown in the article).  If you are using Excel 2007 or later, I recommend creating a Table as the data source.
    LVL 8

    Accepted Solution

    Hi Euro5, check the attached file and go to Formulas > Name Manager. You will see a named range called "mypivotdata". Check the refers to box which contains the offset formula that refers to the entire table.

    Author Closing Comment

    Excellent information, thanks very much!!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now