+ Reply to Thread
Results 1 to 4 of 4

Data maximum for pivot tables?

  1. #1
    Registered User
    Join Date
    02-03-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    67

    Question Data maximum for pivot tables?

    I'm creating a pivot table for a very large amount of data (46,000 rows and about 20 columns). I'm using 6-8 of the columns as row filters, and then below in the data table there will be 8-10 columns of data. It will be the entire 46,000 lines row length.

    I tried to create the pivot table, dragging the fields I wanted in the pivot table field list "Report filter" section and "Row labels" section. I took away subtotals. As I tried to drag all the fields I needed into the report filter and row labels sections, I got the following error message:

    "A field in your source data has more unique items than can be used in a PivotTable report. Microsoft Excel may not be able to create the report, or may create the report without the data from this field."

    I wasn't able to even drag in several of the fields I needed, and only 27,000 of my 46,000 rows of data are displayed.

    Is there truly a maximum on amount of data you can include in a pivot table, or is there a way to make this work?

    Thanks for your help

  2. #2
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Data maximum for pivot tables?

    The error does mean what it says - there are too many unique items within one of your fields. A pivot table is used to summarise data and therefore a row field with thousands of items is not truly appropriate. I thought you were using a regular table for displaying data however and the pivot table only for filtering - or is this another project?
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  3. #3
    Registered User
    Join Date
    02-03-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Data maximum for pivot tables?

    This is part of the same project. I'm working on a contracting job where for several months I'm doing various things with this data. The original data is indeed the 46,000 rows.

    There are regular tables for displaying data- several versions with all 46,000 lines, and six regional divisions that each have multple tables to display region-specific data.

    I will also be doing individual pivot tables by region (so the 46,000 lines will instead be roughly 46,000/6), but they also want one for all regions. I have table versions with filters, and then my boss now also wants me to make a pivot table with slicers where I can sort by 6-8 categories of types of managers and sales agents (the categories I used the fake names of "national manager," "regional manager," "district manager," etc on my fake data spreadsheet from yesterday).

    Basically we want, say, John Doe, National manager, to be able to click on his name and see all his regional managers, then click on them and see local managers (these are all fake titles but hopefully it helps you undertand it's basically a hierarchy of one guy for each of the six regions, then many guys under him). When you have, say, John Doe, national Manager plus Jane Smith, Regional Manager plus Anne Thomas Local Manager all selected in the slicers, you see all the contracts that are overseen by the combination of these three people, with about 8-10 lines of pertinent data about the plans.

    Does that make sense?

    I can probably get away with making six different pivot tables, one for each region, but I know my boss would really love one with all the regions (all 46.000 plans) because the top guys DO have access to everybody's plans, and want to be able to quickly see this data.

  4. #4
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Data maximum for pivot tables?

    In Excel 2010 you may have 32500 unique items per field. If you have more than this I think you do not want a pivot table.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Validating Data Between 2 Pivot Tables (Pivot Tables Don't Line Up)
    By JohnGC84 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-18-2013, 05:07 PM
  2. Complex Request with Pivot Tables and copying pivot data to new page with formulas
    By Obsessed in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-07-2013, 09:16 AM
  3. Copying pivot tables as data tables with formatting
    By wolis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2012, 12:57 AM
  4. Replies: 3
    Last Post: 02-27-2012, 08:03 PM
  5. Creating data tables from a database without using pivot tables
    By gareth.campbell in forum Excel General
    Replies: 4
    Last Post: 11-24-2010, 12:23 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1