Pivots - Excel 2007

Posted on 2012-08-13
Medium Priority
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
  • 2
  • 2
LVL 50
ID: 38287100

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 Contextures.com http://www.contextures.com/xlPivot01.html

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

ID: 38287247
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

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38287295
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

ID: 38287690
Perfect, thanks teylyn!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

862 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