Pivots - Excel 2007

Posted on 2012-08-13
Last Modified: 2012-08-13

I have a pivot which analyses a data range that changes, some days 100 rows, others, 103., others 96.

How can i set the pivot so it always set to end at the last row.

Question by:Seamus2626
    LVL 50

    Expert Comment


    You can set up a dynamic range name for the source table, then let the pivot table source refer to that range name instead of a direct cell reference.

    Here is a step by step on

    In Excel 2007 and later, you can select the source data and click Insert > Table. Excel will give this table an internal name and it will grow and shrink with the data. Then use that table name as the pivot source. Details here:

    cheers, teylyn

    Author Comment

    Hey teylyn,

    Im using 2007.

    My data set is a data call from access

    When i click into the data set to insert a table, that option is blanked out.

    Is there any reason for this?

    LVL 50

    Accepted Solution

    If the data is from a data connection, then you cannot also make it an Excel table. But if it's from an external data connection, you could insert the pivot table using an external data source.

    In an empty sheet, click Insert > Pivot Table. Tick "Use an external data source" and connect to your Access data that way.

    cheers, teylyn

    Author Closing Comment

    Perfect, thanks teylyn!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now