Why is this 'phantom' formatting contributing 9MB to file size!?

[d0rian]

I run a web query that imports ~44,000 rows of plain text data into columns A:Q, then runs some basic sorting on it (and copies a portion of it to other sheets, but that's not important.)
I couldn't figure out why this file had grown to 18MB in size. 44,000 rows of data is a lot, but even when I deleted all the contents of columns A:Q, the file was still ~12MB in size.

Some trial and error uncovered this really weird artifact: when I cleared the formatting from those empty A:Q columns (Highlight A:Q > Clear > Clear Formats), my file size dropped to ~3MB. There's no weird conditional formatting or anything. As far as I can tell, the ONLY formatting that had ever even been applied to those columns was some Left/Right/Center alignment or Number/2-decimal or the like.

How can I drill down to discover what formatting my file thinks is being applied to those columns that could possibly consume 9MB in file size?

 

[d0rian]

Bizarre follow-up to my main Q above: I highlighted cols A:Q and selected "No Fill" (since I had highlighted a single header row in blue), then saved the file, wondering if removing color from a single row would change the file size, and that simple step added 400kb to the file size(!!)

How does that make any sense???

1. Open 18,968 KB file
2. Remove any color fill from cols A:Q
3. Save.
4. Now a 19,340 KB file (!!!???)

(and yes, I closed / re-opened the file a few times, since I know sometimes that cures some file size bugs)

 

[NoSparks]

don't know if it would work or not but maybe have a look at the LoseThatWeight macro posted here by MARK858 back in 2016.

 

[Michael M]

Try running this code after your import

Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    ActiveSheet.UsedRange
Next ws
End Sub

 

[Saba Sabaratnam]

or after importing, you could save the file as CSV and then open it with excel and save as Excel file. It will remove all unwanted formatting.


Kind regards

Saba

 

[d0rian]

Thanks for replies. My issue isn't so much getting rid of the phantom formatting stuff and reducing the file size (which Clear > Clear Formatting seems to do just fine), as it is drilling down to understand how it happened in the first place because I have many other quite large files with dozens of worksheets, and I want to know what process led to this file nearly doubling in size all due to some basic formatting stuff, since I want to make sure I don't re-introduce the problem later or in different workbooks.

It's more than just standard formatting, since after I cut the file size down by removing the formatting, I reintroduced the alignment / number formatting stuff that had once been applied to those columns, and the file size barely changed. So...there's something about how Excel sorts formatting cells that seems to give each cell a 'memory' for what kind of formatting it may once have contained, which creates bloat that's tricky to identify and eliminate.

 

[RoryA]

Formatting entire rows/columns is more efficient than formatting blocks/individual cells.

 

[d0rian]

Formatting entire rows/columns is more efficient than formatting blocks/individual cells.

Yes, and I always make it a habit to format entire rows, and not blocks, as you say.
(Although I suspect that there are a few functions -- Delete Duplicates is one, and basic 'sorting' is another -- that do a wonky job of retaining formatting of various cells; there are other threads online suggesting the same, though I didn't really think it was all that applicable here, so I didn't get into it.)

In any case, it doesn't seem as though, beyond my own trial/error troubleshooting, there's an easy way to pull up and examine just WHAT (whether contents, or formatting) is responsible for an oddly large file size...(?)

 

[eduzs]

... sorry

 

[Michael M]

I'm still not sure it's caused by formatting issues.
I'd suggest that when you import only 44,000 rows of data, you may actually be unknowingly "filling" a million rows of data !!
Once you have done the import, press CTRL + End and see which cell is highlighted.
If it's WAAAAY down the page somewhere beyond your import, that would be the issue.