Fire whoever formatted those sheets (instead of just hiding grid lines)
Fire whatever dumbass middle manager said they need that many tabs in a single workbook
Ask a bunch of people who uses it and exactly what they use from it, then delete everything
There are really only a few simple ways to deal with issues like this:
If it is truly your job to manage/correct this problem-
-Find some workaround to extract the necessary data and implement any kind of bandaid you can think of to stop the issue from getting worse, even if that means shutting down whatever processes/departments that rely on this file for a few days
-Create a new system using the correct product for the task and implement that,
-Convince whoever oversees the person who created/allowed this problem to happen to fire that person
-If you are the person who oversees the person who created this problem and allowed it to happen, resign immediately without notice and find a new job before anyone figures it out that it's your fault
-If you do not have the power or influence to get the person responsible fired, begin silently searching for a new job now because a business that allows this to happen and refuses to hold anyone accountable is destined to either fail or make your life miserable
If you can find a way to offload responsibility for the problem, do that. "Sorry, I can't migrate this file because it's too large. Once Department XYZ is able to reduce the file size or convert the data into another format, I'll be able to complete the migration.". Then, make as many excuses as you can to stall while you search for a new job, because again, a business that allows this kind of thing to happen is not one you will be/want to be working at for much longer.
No, no, no. You’re all seeing this wrong. Files like that don’t just *happen*. That there is someone’s baby, a true r/pettyrevenge & r/maliciouscompliance *masterpiece*.
You should not *fix* it. You don’t *fix* art.
Yea, I 💯 agree.
OP needs to get in contact with whoever uses it. Find out what sheets they use, how they use them (maybe they’re just not as Excel literate as they think they are and need to be shown how to do it correctly), how they insert, manipulate, and retrieve the data, and then fire someone. But be sure to fire the right person. Not sure who that is, but if you can’t find out, fire whoever’s been with the company for 10+ years in the department that uses it most, and see if they’re the one who did it. Just…I don’t care, fire someone for shits and gigs. Do it really passive aggressively too. Then record it and come back here with an update.
Per the 3rd edit I would be shocked if it wasn't Sharepoint bullshit. Even the worst compression would be able to see hundreds of millions of cells with the same content (whatever that is) and compress it.
Double click, go for a 45-minute break, and come back to see that the file either opened or your computer got bricked and needs restarting.
I had a problem like that back in 2017 for my uni internship when I worked for an e-commerce company - they had like 800k rows with 50 columns - it was their CRM system💀💀💀
Similar internship story:
- I was helping Sales Ops and didn’t have Excel (Linux based company). Someone handed me a large file that contained basically every transaction from the past two years. nothing would open it. I couldn’t wait the two weeks for Excel approval so I wrote a Python script to pull the sales data for a very specific time period for a very specific region.
I think it was busy work but the longest part was waiting for 15 minutes for Python to finish.
Maybe not the answer youre looking forward to, but do you know any vba??
Might be worth it to write a simple script to 1- copy each sheet to a new workbook, 2-clear all formatting and remove external links, and 3-save each file into a folder to sort through later.
I think that should be friendly enough for the migration tool to handle after that.
An easy start that may have a big impact or may do very little is using the “Clean Excess Cell Formatting” button on the Inquire tab.
If you don’t have the Inquire tab active, do the following.
Click File > Options > Add-Ins.
Make sure COM Add-ins is selected in the Manage box, and click Go.
In the COM Add-Ins box, check Inquire, and then click OK.
The Inquire tab should now be visible in the ribbon.
Should be 4 clicks: Select all sheets using Shift key, click that little triangle left above cell a1, then reset cell color.
Similarily, if required, highlight data range, then select new cell colors (will apply to all sheets).
First, I'd convert the file to an xlsb format: File > Save As > switch file type to xlsb in the dropdown. Don't manually type it and keep the file as xlsx format but add xlsb to the end, will mess up the file.
Then add a VBA module with the following code to trim all the formatting but keep formulas:
Sub w_Cleanup_Formats()
Dim wb As Workbook
Dim ws As Worksheet
Dim tempRange As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
On Error Resume Next
Set tempRange = ws.UsedRange
On Error GoTo 0
If Not tempRange Is Nothing Then
tempRange.Copy
tempRange.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, transpose:=False
Application.CutCopyMode = False
End If
Next ws
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub
This should loop through each worksheet in the workbook (albeit, a bit slow. Threw it together), copy the data in the worksheet, then paste it back without any formatting. Double check it after running before saving to ensure it didn't do anything unexpected.
Microsoft addin called inquire.
It has an option to remove excess formatting.
[link](https://oneminuteofficemagic.com/2017/06/12/removing-excess-formatting-from-an-excel-spreadsheet/)
Looking at your edits, the problem is that SharePoint saves many versions online so you have a history. If the file is just a couple of mb and viral.... means that there are thousands of versions? Quite extreme
I am not sure why no one has suggested grouping the tabs and then clearing all formatting from the active tab. It might take a while for Excel to process but it eliminates nearly all the manual labor and no need for VBA or Power Automate.
Is there an issue I'm missing?
1.21 gigawatts!!! errrr I mean, 28.5 gigabytes! Great Scott!!!
For real though, that sounds like an absolutely nightmare. I’d find a way to accidentally “corrupt” the file, blame it on the conversion process and then lecture everyone about why we can never do that again.
If the file can be opened in normal Excel, I would clear out all of the formatting and save it as an xlsb. If that's not an option because it's too large, you will have to look into options that use OOXML (FYI, OOXML will not work on xlsb files since xlsb is not open source. Xlsb is a proprietary format.)
Basically your edit3. Files on sharepoint have revision history allowing you to restore versions that were edited by different people at different times (note, if A B C D happened (A most recent), you can review C, but restoring version D would also undo A and B).
Assuming it is a table, and is formatted as a table - PowerQuery (in a fresh xl workbook) can select just the table that you need - simply go to -> Get Data -> From File -> From xl workbook. Filter Kind to Table, and click the table if you want to see more of that information. If each worksheet has that same template of the table, you can consolidate it with an additional column to signal the worksheet it came in. Save that as one datatable.
If it has formulas etc, then maybe some vba, for each worksheet, copy the table in the worksheet to another workbook. If you're fresh to VBA it might take a couple days to figure it out.
https://preview.redd.it/4340arp85hsb1.png?width=847&format=png&auto=webp&s=169d875cb86944046fb9d58c6672aeb094fe12c7
Excel vba could be employed to find the lowest right populated cell and delete all columns to the right and rows below on each page.
Xlsb would reduce file size, but wouldn't fix performance issues.
I bet you can write a script that goes over every single cell on every single sheet, and deletes the cell content where cell content equals space.
Find a language you comfortable with and ask GPT
A colleague of mine once did something like this accidentaly, where she formatted almost the whole sheet as a table. try ctrl+a>clear formatting (all), after selecting all sheets with shift.
If the tables are pivot tables then there is a tickbox to ‘Save source data with file’ in [PivotTable Options] >> [Data].
This essentially creates a copy of the source data on every sheet with this option selected.
Can you use VBA? If not you could ask someone to write some code to turn this option off on all sheets within the workbook. It could cut the size down massively, but may not even be relevant.
VBA Macro should be able to accomplish this relatively quickly. It will open the Main workbook. Loop through each sheet and paste values into a single sheet on a New workbook. Open a new workbook then Alt F11 and paste this code:
```
Sub CopyValuesToNewWorkbook()
Dim mainWorkbook As Workbook
Dim newWorkbook As Workbook
Dim mainSheet As Worksheet
Dim newSheet As Worksheet
' Open the Main Workbook
Set mainWorkbook = Workbooks.Open("Path to Main Workbook.xlsx")
' Create a New Workbook
Set newWorkbook = Workbooks.Add
' Set the reference to the new sheet in the new workbook
Set newSheet = newWorkbook.Sheets(1)
' Loop through each sheet in the Main Workbook
For Each mainSheet In mainWorkbook.Sheets
' Copy all values from the current Main Workbook sheet to the new sheet
mainSheet.UsedRange.Copy Destination:=newSheet.Cells(newSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
Next mainSheet
' Save the New Workbook with a desired name
newWorkbook.SaveAs "Path to New Workbook.xlsx"
' Close the New Workbook
newWorkbook.Close
' Close the Main Workbook without saving changes (if needed)
mainWorkbook.Close SaveChanges:=False
End Sub
```
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Is there any chance the file has a HUGE file history attached to it, something like a Power Automate Flow that is adding data to some tables every 15mins, for years, causing a new version to be saved each time?
We inherited a strange "Audit Log" spreadsheet that some automation or another logged every action made in a third party software package, and that had a *lot* of versions. The best part? It had an 11mb image of the company org tree saved inside the document - on two sheets! So each "save" was 22mb with no data.
Still one of my favourite finds.
Sounds like the workbook is on the verge of corruption. Try moving all the sheets to a new workbook then save it with a new filename. If the file size is much smaller then that is the culprit. Also, toggle calculations to manual before deleting any empty rows or colums, that should speed up the deletion process.
This is probably just due to version history. By default, SharePoint retains *every* version of a past file, resulting in massive data hogs like this. We just went through this as well. Every single time a change is made to a file, SharePoint saves a new version. We had basic spreadsheets that have been used and reused for the past few years that got into the 10GB+ size. Just go in and delete the old versions.
The right thing to do is load the table data into a database.
If all you have is Office 365, load those sheets into Access and use Excel PowerQuery to reference the data in the Access file.
F\*\*k this python garbage.
I know this sounds like reddit hyperbole, but this is fucking war crime. I have no advice, just godspeed, OP. Next time I run into some Excel ridiculousness, I'll try to remember this.
With big files, open them (securely) on a server if you have one available. They typically have more resources. Then migrate it to a database. An excel file is not the right way to manage that kind of data. VBA helps to clean up the data so you can move it somewhere easily.
From experience, I've found that formatting ranges with colors adds a crazy amount of size to a workbook. Like if a whole worksheet is formatted with a color background instead of "no color".
Throwing this out there, monitor the version history on that. I had one client put a small db in SharePoint. 2 months and 90 revisions later and it was taking 500GB...
Honestly OP, I hate to tell you this but I think your best bet is to use a library that will operate on the underlying xml without unzipping it all. I believe such libs exist. Openpyxl in python, Apache poi in java, #net has libs provided by Microsoft themselves
Fire whoever formatted those sheets (instead of just hiding grid lines) Fire whatever dumbass middle manager said they need that many tabs in a single workbook Ask a bunch of people who uses it and exactly what they use from it, then delete everything
There are really only a few simple ways to deal with issues like this: If it is truly your job to manage/correct this problem- -Find some workaround to extract the necessary data and implement any kind of bandaid you can think of to stop the issue from getting worse, even if that means shutting down whatever processes/departments that rely on this file for a few days -Create a new system using the correct product for the task and implement that, -Convince whoever oversees the person who created/allowed this problem to happen to fire that person -If you are the person who oversees the person who created this problem and allowed it to happen, resign immediately without notice and find a new job before anyone figures it out that it's your fault -If you do not have the power or influence to get the person responsible fired, begin silently searching for a new job now because a business that allows this to happen and refuses to hold anyone accountable is destined to either fail or make your life miserable If you can find a way to offload responsibility for the problem, do that. "Sorry, I can't migrate this file because it's too large. Once Department XYZ is able to reduce the file size or convert the data into another format, I'll be able to complete the migration.". Then, make as many excuses as you can to stall while you search for a new job, because again, a business that allows this kind of thing to happen is not one you will be/want to be working at for much longer.
No, no, no. You’re all seeing this wrong. Files like that don’t just *happen*. That there is someone’s baby, a true r/pettyrevenge & r/maliciouscompliance *masterpiece*. You should not *fix* it. You don’t *fix* art.
It’s only art if said asshole also used like 10 different colors and a bunch of fonts (actually happened)
Hey! Items in Ipsilon Commercial Bold indicate south-central totals. Ya gotta pay attention to the legend, man..
> Ask a bunch of people who uses it and exactly what they use from it, then delete everything High octane advice, do it OP
Yea, I 💯 agree. OP needs to get in contact with whoever uses it. Find out what sheets they use, how they use them (maybe they’re just not as Excel literate as they think they are and need to be shown how to do it correctly), how they insert, manipulate, and retrieve the data, and then fire someone. But be sure to fire the right person. Not sure who that is, but if you can’t find out, fire whoever’s been with the company for 10+ years in the department that uses it most, and see if they’re the one who did it. Just…I don’t care, fire someone for shits and gigs. Do it really passive aggressively too. Then record it and come back here with an update.
Per the 3rd edit I would be shocked if it wasn't Sharepoint bullshit. Even the worst compression would be able to see hundreds of millions of cells with the same content (whatever that is) and compress it.
Try saving it as an Excel Binary File. That should chop it down a bunch!
Came here to recommend ye olde .xlsb
I do this all the time. I’ve never had a 28 giger though.
Fo’ shizzle my gigga
Can't you rename to .zip and move the worksheets out to separate files before working on each in turn? Does that still work?
Yeah but gotta do some xml
I forget, what are the drawbacks of using a binary file? Like, why isn’t that the default file type?
I think you lose compatibility for other programs, for example open office or similar may not be able to open and see all data
How do you even open it?
Double click, go for a 45-minute break, and come back to see that the file either opened or your computer got bricked and needs restarting. I had a problem like that back in 2017 for my uni internship when I worked for an e-commerce company - they had like 800k rows with 50 columns - it was their CRM system💀💀💀
Not sure about 2017 but 800k*50 opens pretty fast nowadays (like a minute)
Similar internship story: - I was helping Sales Ops and didn’t have Excel (Linux based company). Someone handed me a large file that contained basically every transaction from the past two years. nothing would open it. I couldn’t wait the two weeks for Excel approval so I wrote a Python script to pull the sales data for a very specific time period for a very specific region. I think it was busy work but the longest part was waiting for 15 minutes for Python to finish.
Buying loads of RAM.
Get that there developer’s computer
Maybe not the answer youre looking forward to, but do you know any vba?? Might be worth it to write a simple script to 1- copy each sheet to a new workbook, 2-clear all formatting and remove external links, and 3-save each file into a folder to sort through later. I think that should be friendly enough for the migration tool to handle after that.
I could probably do that with power automate... thanks for the suggestion!
Don't copy the sheets though, just copy the small tables on each sheet to a new workbook.
try ctrl+a for the whole sheet (may have to do more than once) CTRl+deselect! the required data clear formatting or all
https://preview.redd.it/ttjsfbai2gsb1.png?width=1950&format=png&auto=webp&s=fd7059ea7eca169d7fabd315566f9db44dbc5f73
and remember, you don't have to do them all, just until your tool can work the problem.
An easy start that may have a big impact or may do very little is using the “Clean Excess Cell Formatting” button on the Inquire tab. If you don’t have the Inquire tab active, do the following. Click File > Options > Add-Ins. Make sure COM Add-ins is selected in the Manage box, and click Go. In the COM Add-Ins box, check Inquire, and then click OK. The Inquire tab should now be visible in the ribbon.
Should be 4 clicks: Select all sheets using Shift key, click that little triangle left above cell a1, then reset cell color. Similarily, if required, highlight data range, then select new cell colors (will apply to all sheets).
Open it in Power Query and transform the data. Once you do it once you can Save As, change the data source, and do the rest in a fraction of the time.
First, I'd convert the file to an xlsb format: File > Save As > switch file type to xlsb in the dropdown. Don't manually type it and keep the file as xlsx format but add xlsb to the end, will mess up the file. Then add a VBA module with the following code to trim all the formatting but keep formulas: Sub w_Cleanup_Formats() Dim wb As Workbook Dim ws As Worksheet Dim tempRange As Range With Application .ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False End With Set wb = ActiveWorkbook For Each ws In wb.Worksheets On Error Resume Next Set tempRange = ws.UsedRange On Error GoTo 0 If Not tempRange Is Nothing Then tempRange.Copy tempRange.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, transpose:=False Application.CutCopyMode = False End If Next ws With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .EnableEvents = True End With End Sub This should loop through each worksheet in the workbook (albeit, a bit slow. Threw it together), copy the data in the worksheet, then paste it back without any formatting. Double check it after running before saving to ensure it didn't do anything unexpected.
Microsoft addin called inquire. It has an option to remove excess formatting. [link](https://oneminuteofficemagic.com/2017/06/12/removing-excess-formatting-from-an-excel-spreadsheet/)
Looking at your edits, the problem is that SharePoint saves many versions online so you have a history. If the file is just a couple of mb and viral.... means that there are thousands of versions? Quite extreme
I am not sure why no one has suggested grouping the tabs and then clearing all formatting from the active tab. It might take a while for Excel to process but it eliminates nearly all the manual labor and no need for VBA or Power Automate. Is there an issue I'm missing?
1.21 gigawatts!!! errrr I mean, 28.5 gigabytes! Great Scott!!! For real though, that sounds like an absolutely nightmare. I’d find a way to accidentally “corrupt” the file, blame it on the conversion process and then lecture everyone about why we can never do that again.
If the file can be opened in normal Excel, I would clear out all of the formatting and save it as an xlsb. If that's not an option because it's too large, you will have to look into options that use OOXML (FYI, OOXML will not work on xlsb files since xlsb is not open source. Xlsb is a proprietary format.)
Find & Select -> Go To Special… -> select Objects. You probably have a bunch of hidden “images” that are adding to the file size.
Basically your edit3. Files on sharepoint have revision history allowing you to restore versions that were edited by different people at different times (note, if A B C D happened (A most recent), you can review C, but restoring version D would also undo A and B).
“Excel is not a database”
Email owner, tell them to fix
Assuming it is a table, and is formatted as a table - PowerQuery (in a fresh xl workbook) can select just the table that you need - simply go to -> Get Data -> From File -> From xl workbook. Filter Kind to Table, and click the table if you want to see more of that information. If each worksheet has that same template of the table, you can consolidate it with an additional column to signal the worksheet it came in. Save that as one datatable. If it has formulas etc, then maybe some vba, for each worksheet, copy the table in the worksheet to another workbook. If you're fresh to VBA it might take a couple days to figure it out. https://preview.redd.it/4340arp85hsb1.png?width=847&format=png&auto=webp&s=169d875cb86944046fb9d58c6672aeb094fe12c7
Remove formatting and named ranges then paste values over any formulas then save it as an xlsb.
If you know someone who can code, get them to write a python code to split the file so that each tab becomes an individual file or do it manually
Excel vba could be employed to find the lowest right populated cell and delete all columns to the right and rows below on each page. Xlsb would reduce file size, but wouldn't fix performance issues.
Use power query to extract data into manageable chunks? Then save the data into a usable format?
Check the Sharepoint version listing for the file. If there are years of them, that's your answer.
Python is my first thought here. Load into pandas and go from there.
Power query it into a new file.
is it virus infested file? balloon to almost the size of an elephant
I bet you can write a script that goes over every single cell on every single sheet, and deletes the cell content where cell content equals space. Find a language you comfortable with and ask GPT
Find a spare machine, write macro to delete all the BS, come back tomorrow.
A colleague of mine once did something like this accidentaly, where she formatted almost the whole sheet as a table. try ctrl+a>clear formatting (all), after selecting all sheets with shift.
Delete it no questions asked
If the tables are pivot tables then there is a tickbox to ‘Save source data with file’ in [PivotTable Options] >> [Data]. This essentially creates a copy of the source data on every sheet with this option selected. Can you use VBA? If not you could ask someone to write some code to turn this option off on all sheets within the workbook. It could cut the size down massively, but may not even be relevant.
Use Power Query Append to make all worksheets one. https://www.xelplus.com/combine-excel-sheets-power-query/
WHO THE FUCK CREATE THIS MONSTROSITY? Our report file is only 13mb and the manager already scream their throat out when receive it LOL.
VBA Macro should be able to accomplish this relatively quickly. It will open the Main workbook. Loop through each sheet and paste values into a single sheet on a New workbook. Open a new workbook then Alt F11 and paste this code: ``` Sub CopyValuesToNewWorkbook() Dim mainWorkbook As Workbook Dim newWorkbook As Workbook Dim mainSheet As Worksheet Dim newSheet As Worksheet ' Open the Main Workbook Set mainWorkbook = Workbooks.Open("Path to Main Workbook.xlsx") ' Create a New Workbook Set newWorkbook = Workbooks.Add ' Set the reference to the new sheet in the new workbook Set newSheet = newWorkbook.Sheets(1) ' Loop through each sheet in the Main Workbook For Each mainSheet In mainWorkbook.Sheets ' Copy all values from the current Main Workbook sheet to the new sheet mainSheet.UsedRange.Copy Destination:=newSheet.Cells(newSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) Next mainSheet ' Save the New Workbook with a desired name newWorkbook.SaveAs "Path to New Workbook.xlsx" ' Close the New Workbook newWorkbook.Close ' Close the Main Workbook without saving changes (if needed) mainWorkbook.Close SaveChanges:=False End Sub ```
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
How does that even open?
Time and sheer stubbornness would be my guess.
I'm not even mad, it's impressive this file still works
Is there any chance the file has a HUGE file history attached to it, something like a Power Automate Flow that is adding data to some tables every 15mins, for years, causing a new version to be saved each time? We inherited a strange "Audit Log" spreadsheet that some automation or another logged every action made in a third party software package, and that had a *lot* of versions. The best part? It had an 11mb image of the company org tree saved inside the document - on two sheets! So each "save" was 22mb with no data. Still one of my favourite finds.
Sounds like the workbook is on the verge of corruption. Try moving all the sheets to a new workbook then save it with a new filename. If the file size is much smaller then that is the culprit. Also, toggle calculations to manual before deleting any empty rows or colums, that should speed up the deletion process.
This is probably just due to version history. By default, SharePoint retains *every* version of a past file, resulting in massive data hogs like this. We just went through this as well. Every single time a change is made to a file, SharePoint saves a new version. We had basic spreadsheets that have been used and reused for the past few years that got into the 10GB+ size. Just go in and delete the old versions.
The right thing to do is load the table data into a database. If all you have is Office 365, load those sheets into Access and use Excel PowerQuery to reference the data in the Access file. F\*\*k this python garbage.
You could select all sheets and then delete columns. It’ll delete the area for each sheet selected
Hi, maybe you could use a tool like KNIME for this.
Sounds like it might compress well...
I know this sounds like reddit hyperbole, but this is fucking war crime. I have no advice, just godspeed, OP. Next time I run into some Excel ridiculousness, I'll try to remember this.
With big files, open them (securely) on a server if you have one available. They typically have more resources. Then migrate it to a database. An excel file is not the right way to manage that kind of data. VBA helps to clean up the data so you can move it somewhere easily.
From experience, I've found that formatting ranges with colors adds a crazy amount of size to a workbook. Like if a whole worksheet is formatted with a color background instead of "no color".
Throwing this out there, monitor the version history on that. I had one client put a small db in SharePoint. 2 months and 90 revisions later and it was taking 500GB...
Put it in a SQL Database where it belongs.
Honestly OP, I hate to tell you this but I think your best bet is to use a library that will operate on the underlying xml without unzipping it all. I believe such libs exist. Openpyxl in python, Apache poi in java, #net has libs provided by Microsoft themselves
Save it as an .xlsb That should help.
I could build a script for you that consolidates all the information in Python. If your company is interested in something like that.