0

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?

1
  • 1
    What I've done in cases like this is select and copy the used cells and then paste into a new spreadsheet. – essjae Mar 10 '20 at 22:06
0

Clear all might work

Select the row where the rows of blank rows begin and press CTRL + Shift + Down to highlight all of the empty rows.

Home tab > Editing > Clear drop down > clear all

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.