Abnormal file size caused by converting Excel tables to data areas

Copper Contributor

Abnormal.xlsx file size caused by converting Excel tables to data areas (empty areas with no data but occupied by styles). Is there a way to clear styles without opening the file

 

9 Replies
There's a way but it's more of last resort. Re-name the file to end with the extension .zip. Open the zip using File Explorer. Open the 'xl' folder and delete styles.xml. Close out of the zip package and re-name the file to be an .xlsx again. When the file is opened, you'll receive a message indicating problems with content were found (similar to an open and repair) and styles will be wiped.

@Patrick2788 

Thank you very much for your answer. After experiments, the size of the file is not reduced. Maybe my description of the problem is not accurate enough

@Patrick2788 

I recorded the steps, and what I want to do is fix the action that caused the file size explosion

Step-1Step-1Step-2Step-2Step-3Step-3Step-4Step-4Step-5Step-5Step-6Step-6Step-7Step-7

@Wxy-Excel 

With permission of all, I would offer an additional solution.

 

This is how you could reduce the volume of your Excel file again:

To be on the safe side, make a backup of the file first.

Try to delete the superfluous cells by marking the rows or columns,

right-clicking with the mouse and selecting Delete rows.

 

Quickly delete multiple cells/columns:

select/click cell/column,

CTRL+left shift key+ right or down arrow

depending on the cell or column, press .

 

Save the file. Close and open again. If you look at the file size in Explorer, it should now be significantly reduced and the table should be usable again.

     If the size doesn't really change afterwards, try copying the spreadsheet to a new file and saving it accordingly. At least that should help.

 

Happy new year and much success with Excel.

@Wxy-Excel 

I see. Essentially, you need to re-size the table, but the workbook won't open to where you can fix it.

 

You can still update the table dimensions using xml.

 

1. Make a copy of the workbook

2. Re-name the workbook to end with .zip instead of .xlsx

3. Open the .zip using File Explorer

4. Go to 'xl' folder then open 'tables' folder.  In the Tables folder you'll have an xml file for each table in the workbook (table1, table2, table3 - it always shows the default generic name here).

5. Drag table1.xml outside of the zip package to another folder and then open it with Notepad.

6. Within the xml file you have to update the dimensions in two places as seen in capture here:

Patrick2788_0-1672163385192.png

7.  After correcting the table dimensions, save the xml file and close it.  Drag and drop the xml file back into the zip package in the tables folder - opt to 'Copy and Replace' when prompted.

8. Back out of the zip package entirely.  Re-name the .zip to be .xlsx once again.  Open .xlsx.

9. Enjoy

Very good solution. The disadvantage is that. The xml is too large (usually 500-600MB), and there is no editor for editing

@Wxy-Excel 

If on Pro: Inquire -> Clean Excess Cell Formatting

If on 365: open in Excel for Web -> Check Performance -> Optimize

Or on any version select unused area, Home -> Clear -> Clear All

500mb?

my suggestion is to use database rather than spreadsheet.
It is an empty table, and actually has no content, but the style occupies 50-60MB. The xml memory usage after decompression by zip will increase by about 10 times