Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Pivot Table error message 2
- Thread starter kclose
- Start date
-
2
- #2
Hiya,
Basically, it means that because of the number of fields that you have specified in your pivot table, with all the sub-totals in place, the pivot table will exceed the maximum number of rows that Excel can handle.
The easiest way to overcome this is add 4 fields to the data area, create your pivot table, click on each field and take the sub-totals out, the go back to wizard and add more fields to the data area.
You need to repeat this step until you have all your fields on your pivot table.
HTH
Tim
Basically, it means that because of the number of fields that you have specified in your pivot table, with all the sub-totals in place, the pivot table will exceed the maximum number of rows that Excel can handle.
The easiest way to overcome this is add 4 fields to the data area, create your pivot table, click on each field and take the sub-totals out, the go back to wizard and add more fields to the data area.
You need to repeat this step until you have all your fields on your pivot table.
HTH
Tim
- Thread starter
- #3
Tim,
Thanks for the help, but I guess I don't understand what you're saying. I tried different variations of what I thought you meant, but nothing seems to work. Basically I want to place 401 rows in a pivot table with subtotals on one field of data. I can't believe that I'm exceeding the maximum number of rows that Excel can handle????
Thanks for the help, but I guess I don't understand what you're saying. I tried different variations of what I thought you meant, but nothing seems to work. Basically I want to place 401 rows in a pivot table with subtotals on one field of data. I can't believe that I'm exceeding the maximum number of rows that Excel can handle????
Hiya,
You are exceeding the number of rows, because Excel will automatically try to sub-total on every field that you add to your spreadsheet.
What you need to do is put four fields onto your pivot table. Let Excel create the pivot table, then click on each field in turn and take the sub-total away for that field. Repeat for each field.
Then add the next batch of four fields to the pivot table and repeat, until you have all your fields that you require on your pivot table.
Finally, select the field that you do want sub-totals on, and add the sub-totals
You are exceeding the number of rows, because Excel will automatically try to sub-total on every field that you add to your spreadsheet.
What you need to do is put four fields onto your pivot table. Let Excel create the pivot table, then click on each field in turn and take the sub-total away for that field. Repeat for each field.
Then add the next batch of four fields to the pivot table and repeat, until you have all your fields that you require on your pivot table.
Finally, select the field that you do want sub-totals on, and add the sub-totals
Doesn't necessarily mean that it's going over 65536 rows - Pivot tables take up a LOT of excel's memory and this is quite common (especially if you have more than a coupla PTs in 1 workbook)
What it usually means is that Excel is out of available memory. Generally, the bigger the raw dataset that the pivot table is based on, the less row items you can have. If you have other workbooks open, try closing them and try building the pivot table again. Also, try copying over formulae with values (if you can).
HTH
Geoff
What it usually means is that Excel is out of available memory. Generally, the bigger the raw dataset that the pivot table is based on, the less row items you can have. If you have other workbooks open, try closing them and try building the pivot table again. Also, try copying over formulae with values (if you can).
HTH
Geoff
Doesn't sound like it should be too big - having said that, I would rarely create a pivot table with > 4 row fields - can you put any of the row fields as page fields ??
Thinking about it, Tim1 may be right but not in the way he thinks - PTs create veryhidden sheets with all the calcs and data on (in 97 anyway, I believe they use OLAP cubes in 2000 and higher) Ther more row fields you have, the more lines of calcs are needed on this veryhidden sheet and it could be this that is going over 65536 rows rather than your actual PT sheet.
What you might want to do is, as they are row fields and therefore not calcs, set up the PT with 4 row fields and then vlookup the other information into cells next to them
eg
col col col
vlookup vlookup row row row row data data data
vlookup vlookup row row row row data data data
etc etc
HTH
Geoff
Thinking about it, Tim1 may be right but not in the way he thinks - PTs create veryhidden sheets with all the calcs and data on (in 97 anyway, I believe they use OLAP cubes in 2000 and higher) Ther more row fields you have, the more lines of calcs are needed on this veryhidden sheet and it could be this that is going over 65536 rows rather than your actual PT sheet.
What you might want to do is, as they are row fields and therefore not calcs, set up the PT with 4 row fields and then vlookup the other information into cells next to them
eg
col col col
vlookup vlookup row row row row data data data
vlookup vlookup row row row row data data data
etc etc
HTH
Geoff
- Status
Similar threads
- Locked
- Question