Share via

How to Delete Thousands of Blank Rows at the bottom of a data set?

Anonymous
2022-05-16T20:41:27+00:00

Hi Everyone,

I have a data set of around 5723 rows of data.

If I go to the bottom of my excel sheet, it goes down all the way to almost 11K rows. That's about 5K rows of nothing. This is causing my file to be slower and heavier.

I know 1 solution to this, simply copy the data set to a new sheet and delete the pre-existing sheet.

But is there any way that I don't have to copy/paste the data onto another sheet and simply remove those blank rows at the bottom of my data set?

I feel like I tried everything... Highlighted the blank rows > delete. Doesn't work, they still exist.

If anyone can answer this, it would seriously save me a ton of time and effort.

Thanks!

Tyler

Microsoft 365 and Office | Excel | For home | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2022-05-17T12:15:49+00:00

    As suggested, go to the first blank row, press CTRL-SHIFT-DOWN ARROW, right-click and choose DELETE.
    Save the file, size should go down.

    If it is still large then go to the first blank column, press CTRL-SHIFT-RIGHT ARROW, right-click and choose DELETE. Save the file.

    Sometimes I have to use SAVE AS and overwrite the file. Suggest you save with another name and see.

    You will still see blank rows and columns but they are just displayed by Excel and do not take any space in the file.

    200+ people found this answer helpful.
    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-06-24T16:57:22+00:00

    I tried the solutions presented here in many variations and short of copy and paste into a new sheet, I can't get this problem resolved on my existing sheet... Isn't there some parameter that can be set for Excel that would limit the number of empty rows at the bottom. Hiding empty rows might get around the problem but let's face it, this is not a solution.

    The fundamental issue is that scrolling is impractical with a sheet that has thousands of empty rows displayed.. This seems like a real bug in Excel and it's been there for years over the many versions of Excel that I've dealt with. Yes, I've seen that the fixes presented here can fix this problem but not in my current situation.

    50+ people found this answer helpful.
    0 comments
  2. Anonymous
    2022-05-17T02:27:56+00:00

    You could always select the top-most row you want to get rid of, press Ctrl + Shift + Arrow-Key Down (selects all of the visible rows) and then right-click to Hide them all. I don't 100% know if that solves the problem (out of sight out of mind) or if your file would still be referencing that hidden space and taking a toll on being slower/heavier.

    I've personally never had issues with Excel bogging down as long as those blank cells are actually blank and don't contain any data or anything, so it's strange that you would be seeing something like that in my opinion.

    10+ people found this answer helpful.
    0 comments
  3. Anonymous
    2022-05-16T21:47:56+00:00

    Re: "I feel like I tried everything... Highlighted the blank rows > delete. Doesn't work, they still exist."

    After highlighting the rows, right-click the rows header and choose delete from the popup menu. You may have to save the workbook before seeing the results.

    '---

    Nothing Left to Lose

    https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

    The free "Professional_Compare" workbook provides a 'Clean Data' utility.

    (it includes deleting the unused rows/columns outside of the data area)

    10+ people found this answer helpful.
    0 comments
  4. Anonymous
    2022-05-17T12:44:23+00:00

    omg yes this did it!

    Steps, highlight rows that are blank at the bottom taking up space > Delete > Save As New File > Close File > Reopen File > Fixed !

    9 people found this answer helpful.
    0 comments