Forum Discussion
Wxy-Excel
Dec 27, 2022Copper Contributor
Abnormal file size caused by converting Excel tables to data areas
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
Wxy-Excel
Dec 27, 2022Copper Contributor
I recorded the steps, and what I want to do is fix the action that caused the file size explosion
Step-1Step-2Step-3Step-4Step-5Step-6Step-7
Patrick2788
Dec 27, 2022Silver Contributor
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:
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
- Wxy-ExcelDec 29, 2022Copper ContributorVery good solution. The disadvantage is that. The xml is too large (usually 500-600MB), and there is no editor for editing
- peiyezhuDec 30, 2022Bronze Contributor500mb?
my suggestion is to use database rather than spreadsheet.- Wxy-ExcelDec 30, 2022Copper ContributorIt 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