How do I prevent Excel from adding a million blank rows?
October 30, 2020 1:38 PM Subscribe
Sometimes I realize Excel has inserted hundreds of thousands of rows below my last edited cell.
Is there a way to prevent it from adding blank rows? Is there a kind of edit I should avoid doing that triggers it? Is there a way to more easily rectify it once I notice?
I usually realize this because some operations ranging from saving to minor edits start taking excessive amounts of time. I used to think they were "virtual rows" (annoying in that they foil usefully scrolling, but not otherwise consequential) until I noticed the slowdowns and that the file gets gigantic.
My workaround is to select and delete the rows in 7 or 8 big chunks by the thousands — but not too big, because it can crash or hang if I try to select them all — saving in between each couple of steps just in case. I select a whole row a few rows underneath my data each time, dragging the right-hand scroll bar down as far as I dare, shift-click another row, use the right-click menu to delete the chunk, and repeat.
(I'm on a Mac if that matters.)
Is there a way to prevent it from adding blank rows? Is there a kind of edit I should avoid doing that triggers it? Is there a way to more easily rectify it once I notice?
I usually realize this because some operations ranging from saving to minor edits start taking excessive amounts of time. I used to think they were "virtual rows" (annoying in that they foil usefully scrolling, but not otherwise consequential) until I noticed the slowdowns and that the file gets gigantic.
My workaround is to select and delete the rows in 7 or 8 big chunks by the thousands — but not too big, because it can crash or hang if I try to select them all — saving in between each couple of steps just in case. I select a whole row a few rows underneath my data each time, dragging the right-hand scroll bar down as far as I dare, shift-click another row, use the right-click menu to delete the chunk, and repeat.
(I'm on a Mac if that matters.)
In Ye Olden Times, you would move the mouse to cell A1 and save, and that would clean up all the extra stuff.
Also: highlight your first empty row at the bottom, then hold down Shift, and press the End key, then press the down arrow. That will highlight all the rows from the one you've highlighted to the end. (End then a direction goes as far as it can in that direction until it either hits something or goes to the end of the spreadsheet. There's a little thing at the bottom that says "End Mode" while it's active. You don't hold down on End, just push it once.)
posted by Huffy Puffy at 3:00 PM on October 30, 2020 [2 favorites]
Also: highlight your first empty row at the bottom, then hold down Shift, and press the End key, then press the down arrow. That will highlight all the rows from the one you've highlighted to the end. (End then a direction goes as far as it can in that direction until it either hits something or goes to the end of the spreadsheet. There's a little thing at the bottom that says "End Mode" while it's active. You don't hold down on End, just push it once.)
posted by Huffy Puffy at 3:00 PM on October 30, 2020 [2 favorites]
A lousy workaround, but if you're literally getting hundreds of thousands of extra rows you might save a little time by doing a first save to the old .xls (not .xlsx) file format, which (iirc) would only save up to the old maximum of 65,536 rows.
If you do this, though, make sure to save back to .xlsx to avoid losing any new features that the old format doesn't support. Probably a good idea to erase the temporary old format file just so you don't later open it by mistake instead of the right one.
Depending on how complex your worksheets are you could also try copying the real rows or cells, pasting to a new worksheet, and deleting the original.
posted by trig at 3:22 PM on October 30, 2020
If you do this, though, make sure to save back to .xlsx to avoid losing any new features that the old format doesn't support. Probably a good idea to erase the temporary old format file just so you don't later open it by mistake instead of the right one.
Depending on how complex your worksheets are you could also try copying the real rows or cells, pasting to a new worksheet, and deleting the original.
posted by trig at 3:22 PM on October 30, 2020
Best answer: Also, according to some sites this might be caused by random formatting that somehow got applied to those rows. So it's probably worth paying attention to how you tend to apply formatting, in case there's some relevant habit you can learn to avoid.
And you can use the approach described in this post to try to figure out what the problem is for any specific file: an .xlsx file is actually a .zip archive of mostly XML files, so the post describes exploring the contents, looking for a really huge XML file, and checking out the later rows to figure out what formatting Excel has associated with them. Then you can either use that information to remove the formatting in Excel, or (probably?) just delete all those rows straight from the XML file with a handy text editor that will let you delete huge numbers of rows at once.
ETA: Actually I'd first try this - Microsoft's instructions for how to get rid of extraneous formatting.
posted by trig at 3:31 PM on October 30, 2020 [3 favorites]
And you can use the approach described in this post to try to figure out what the problem is for any specific file: an .xlsx file is actually a .zip archive of mostly XML files, so the post describes exploring the contents, looking for a really huge XML file, and checking out the later rows to figure out what formatting Excel has associated with them. Then you can either use that information to remove the formatting in Excel, or (probably?) just delete all those rows straight from the XML file with a handy text editor that will let you delete huge numbers of rows at once.
ETA: Actually I'd first try this - Microsoft's instructions for how to get rid of extraneous formatting.
posted by trig at 3:31 PM on October 30, 2020 [3 favorites]
I haven't used it recently, but when I was in Excel a lot, I used a tool called ASAP Utilities that had a lot of Excel functions, one of which can strip all unused formatting out of each workbook. Its not free, and may not be worth it for just that feature, but if you use Excel a lot it has a bunch of other handy tools as well.
posted by nalyd at 7:27 AM on October 31, 2020
posted by nalyd at 7:27 AM on October 31, 2020
Best answer: Highlighting an entire row or column and applying a format to it can cause this.
posted by soelo at 2:28 PM on October 31, 2020 [1 favorite]
posted by soelo at 2:28 PM on October 31, 2020 [1 favorite]
This thread is closed to new comments.
posted by Lyn Never at 1:52 PM on October 30, 2020