•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Over 1 million rows - how to delete blanks?
Our take
Dealing with over a million rows in Excel can feel overwhelming, especially when over 99% of them are blank. If you've exported data from Smartsheet and are left with a sluggish file, you're not alone. Fortunately, there’s an efficient way to delete those blank rows without starting from scratch or losing your carefully set-up formulas and conditional formatting. Keep reading to discover a simple method that will streamline your spreadsheet and enhance performance, making your data management smoother and faster.
We have an Excel file that was exported from Smartsheet. Unfortunately when exported it brought over 1 millions rows - less than 300 rows have data. How do we delete all of the blank rows?
I know we could copy the data we need to a new spreadsheet, but we have already setup formulas, conditional formatting, etc. The file is kind of slow (shared via OneDrive) and we are working to reduce some of the conditional formatting, etc but I think the formulas checking a million rows has to be slowing it down some too.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Trying to remove invisible characters (go to special -> blanks not finding all blank cells)Hi everyone, I’m at my wits end with this one. I’ve copy and pasted data a large chunk of data from one excel sheet to another that has a blank line or two between meaningful data. I want to remove all those blank cells, but when I use go to special -> blanks it says there isn’t any. I’ve tried using CLEAN, tried using TRIM, tried using both, tried using CODE to detect what character is in the cell, it comes back as #VALUE. The way the data was pulled was with the formula IFERROR(TEXTBEFORE(A1, “ - “,1)””). It isn’t treating “” as blank for whatever reason. Maybe an extra important detail: the original file is a .xls; new book is .xlsx. Using Office 365. Any help is appreciated! submitted by /u/newblood310 [link] [comments]
- Any way to automate removal of older rows of data?I have a spreadsheet with 4 columns: first name, last name, score and date. I have people who are duplicated, some with the same date which I can easily remove with "remove duplicates" but I have examples where there are people with multiple rows from where they have taken a test a few years later, and i am trying to find a way to optimise my chopping up of this spreadsheet to only have a single row per user, and showing only their most recent score for the test. The date column is dd/mm/yyyy and then a 24 hour format time stamp and I can't think of a good way to optimise that as it covers multiple years. Theres no good consistency over the old date and the most recent date I imagine excel has some way of pruning older data. Atleast I hope so or ill have to check 50000+ rows manually to remove old results 😭 submitted by /u/Skellyhell2 [link] [comments]
- Excel in page layout - How to remove pages without any content?My spreadsheet behaves somewhat strangely. The pages without content aren't all greyed out. At the same time the cell grid on those pages is shown cropped. Printing the sheet results in two pages. One with all my content and one that's blank. How do I remove this blank page? Selecting the columns AD to AS and deleting them doesn't solve the issue. On the contrary, it just makes it worse. Now there would be five blank pages when printed. https://preview.redd.it/toybdete030h1.png?width=2473&format=png&auto=webp&s=ec611af7dccf21ccf8806340c93e2efd86392014 Win 11 with Office 2024 submitted by /u/Wild-Sir8868 [link] [comments]
- How to remove blank space from pivot tableI'm doing an assignment where we have to put our data into a table but I cant seem to get rid of the blank column, is there a function to get rid of it? I'm new to excel and have already tried some methods that did not work so I'm unsure if I might've done them the wrong way? Also I think I'm using the most recent version of excel https://preview.redd.it/45higvez8wqg1.png?width=610&format=png&auto=webp&s=12273a9ed89b8151f3d1d4c8368a78829e6b73fa submitted by /u/Lone_rerednow [link] [comments]