-2

I have an empty excel file, which is 5.5mb large. If I open it - the process is very laggy, even on fast PC (intel i7 processor). It opens ~30 sec.

When it opens, it shows that the document has 1048576 rows. I tried to delete them - but unsuccessfully. If I remove the G column, the file size gets decreased by half (2.5mb). If I remove the Entire Sheet, adding new empty one, the file size gets 8kb.

The question is not about how to solve the problem, but what does cause the problem, why this is happening and how do I remove unused rows? I tried to delete them in different ways. saved the document-reopened - no success.

Here is the document, if you need: https://files.fm/u/erfr4weq

Sergej
  • 2,030
  • 1
  • 18
  • 28
  • Please, comment the reason of down-vote – Sergej Dec 10 '18 at 19:13
  • Define "empty": Is there any formatting or conditional formatting applied to the cells? Are there formulas with a result of `""` (which *looks* like an empty cell)? – cybernetic.nomad Dec 10 '18 at 19:18
  • Yes, there are some formattings, but it is not the cause. because I already tried to fully clean entire sheet from formatting, formulas, data etc... the table was entirely empty. the file size was 2.5mb. The only reasy I see - that there are 1+ million rows – Sergej Dec 10 '18 at 19:19
  • 1
    @Sergej - This question doesn't appear to be about programming. It's strictly around why an Excel file has data that's not visible to your naked eye. I feel this is better suited for [Superuser](http://superuser.com). – gravity Dec 10 '18 at 19:19
  • Excel is very powerfool, and there is programming part (Visual Basic) if you do not know. And weird file size is an advanced question. If you think you know the answer - you are welcome to share you answer – Sergej Dec 10 '18 at 19:21
  • Just because Excel has some aspects to programming, doesn't make **this question** functionally useful on a programming Q&A website. Please consider migrating to the alternative site. – gravity Dec 10 '18 at 19:23
  • You cannot judge the question is related to programming or not, if you do not know the answer. May be I have to use some programming specific tools or appoach to resolve this issue. I know that excel is not a programming language itself, but that doesn't mean that it is not wrriten and cannot have a programming specific issue. – Sergej Dec 10 '18 at 19:26
  • @Sergej I've looked at your file. This has **nothing to do with programming** - it is 100% a simple matter of formatting and styling on over 1 million rows. I mass-selected all columns, rows, and deleted en masse and re-saved for it to shrink to 32k. If you're still containing 1 million rows, you're not using Excel correctly. Furthermore, posting an Excel file (as you pointed out - that could contain malicious code or scripting) isn't recommended for this site either. – gravity Dec 10 '18 at 19:33
  • can you tell me please, what operations do I need to do? I have excel 2007. if you post correct answer, I'll accept it. but it seems more like that you are correct for the reason. – Sergej Dec 10 '18 at 19:34
  • Create a new sheet. Delete the sheet with Cyrillic characters. Save. Done. – gravity Dec 10 '18 at 19:36
  • thank you for you answer, but in real file there are data, that i will have to copy, I just were wondering of fixing current sheet, but not creating new one. – Sergej Dec 10 '18 at 19:43
  • why dont you just copy the data to another excel file and abondon this one? – Derviş Kayımbaşıoğlu Dec 10 '18 at 19:55
  • I think this is the only way that I can do. But I was wandering by fixing the current sheet. I posted a comment to you answer below. If I remove all the styles, the XML keeps only rows without anything, which I cannot remove from the sheet. I am googling for solution, but with no succes at the moment. – Sergej Dec 10 '18 at 19:57
  • 1
    For starters, as I believe this is the root cause (and answer) to your question: [Avoid formatting entire rows or columns at once, and instead only format your used areas](https://support.office.com/en-us/article/clean-excess-cell-formatting-on-a-worksheet-e744c248-6925-4e77-9d49-4874f7474738) – gravity Dec 10 '18 at 19:59
  • Yes, I know how to use Excel, but this is not mine :) workbook. Just a person has asked for a help. I already tald her, that she should avoid styling the entire document. Now I'm working just how to fix the sheet, when it's already styled. – Sergej Dec 10 '18 at 20:08

3 Answers3

1

Save the excel file with the open xml format, unrar and open it with the editor to see what is going on in it.

Please note that this approach is only valid for xlsx files (office 2007 and onwards)

Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72
  • There is no data, so it is not possible to save the file as XML. – Sergej Dec 10 '18 at 19:17
  • Then how do you know that it is 5.5MB? – Derviş Kayımbaşıoğlu Dec 10 '18 at 19:18
  • If you have not tried @Simonare instructions, please do. Just because you can't see it, does not mean this isn't useful in isolating where the data is hiding - whether it be in formulas, formatting, etc. You **have data** in this file. – gravity Dec 10 '18 at 19:20
  • I tried. When I try to save the file as XML, it says that there is no data. 5.5mb i see in explorer, file properties. – Sergej Dec 10 '18 at 19:23
  • 3
    Saving the file as `XML` is not the same as what @Simonare is asking you to do. See [this](https://support.office.com/en-us/article/Open-XML-Formats-and-file-name-extensions-5200D93C-3449-4380-8E11-31EF14555B18) to find out about Open XML and [this](https://stackoverflow.com/questions/8984254/how-to-view-the-xml-form-of-an-excel-file) to see what's going on with your file – cybernetic.nomad Dec 10 '18 at 19:28
  • Ok. I've got it. There is inside XML file ~80mb. Now I'm trying to view it, but my notepad++ is lagging. I think it tries to load the entire file in memory for editing. May be I have to used another tool – Sergej Dec 10 '18 at 19:32
  • It is full of this code: – Sergej Dec 10 '18 at 19:36
  • I removed all styles and XML now looks like: ... I do not get how to entirely remove the empty rows. Delete function do not help. – Sergej Dec 10 '18 at 19:48
  • 1
    Answer for - how to remove the empty rows is not found and it's already late, so I will just move the data from one sheet to new one, delete the old sheet, and avoid styling the entire worksheet in future. Thank you for your help. – Sergej Dec 10 '18 at 20:17
0

It's most likely not actually empty, there will be some hidden data somewhere. Try the clear function on excel instead of just the delete button.

user8261831
  • 464
  • 1
  • 4
  • 20
0

I had the same thing happen. I had 1200 data records that took up 5MB, which is odd. I looked at the right scroll bar and saw that it was small. Excel had added 10,000 more rows, which I found out. I got rid of the extra rows, so it is now 152kb. Check to see if the number of rows on your scroll bar matches the number of rows you're using. Then get rid of them. Even though it looks like you're not deleting anything, you are.

Michelle
  • 1