There's an important spreadsheet that I use in work with around 5000 rows of data, and is updated daily.
Someone has formatted the entire columns to have full borders, thus extending the spreadsheet all the way down to its maximum; 65536 (old Excel).
Now, this has caused the file to get very large and has also meant that whenever I try and use PowerQuery with this worksheet, transformations take ages to complete as PQ is checking all 65536 rows.
Now, I've searched extensively for a solution to this. My attempts:
(1) Use the Inquire add in and select "Clear Excess Formatting". This simply does nothing to my spreadsheet. Although, I have a similar spreadsheet on a different sheet (same workbook), and it clears all of the excess formatting (simply blank rows but with full borders around each cell) on this sheet, but not my original sheet?
(2) Selected the row where the rows of blank rows begin and press CTRL + Shift + Down to highlight all of the empty rows. Right clicked and deleted these rows. Saved the file. Re-opened, and absolutely nothing has changed.
These are the two methods suggested, but neither of them work for me?