Pivot Table, Limit 255 rows, Invert rows and column

  • Thread starter Thread starter xav
  • Start date Start date
X

xav

I create a pivot table from a worksheet where there is row data like:

Date - Product - Quantity

The pivot table shows the product in the rows and the date in the
columns and finally display the sum of the quantity in the middle. (I
hide the column for all the previous date and only keep the current
date, but unfortunately I can only hide the other date as their
quantities are used in the calculation)
My problem is if I've got more than 255 dates (I want to display the
information on one year) the last data are shrinked from the pivot
table and I lose the information. I can still reverse rows and column
(there are less than 255 products) but it hard to read the pivot table.
Is there a way to copy only the displayed cells (not hidden) and paste
them in the a different sheet. And moreover is it possible to invert
the rows and columns?
 
Possibly select the table, then Edit=>Goto=>Special and select Visible
cells.

then do your copy and paste to a new sheet.

Depending on the size of the data, you can then copy this data, and on
another sheet, do paste => Special and select transpose.
 
That seems to work fine. But is possible to create a sheet with 255
rows and 65000 columns instead of the opposite.
 
the row and column dimensions are fixed. It has been announced that the
next version of excel will have a million rows and A to ZZZ columns (as I
recall).
 
Back
Top