Compress Excel Sheet

hi
i have excell sheet contain 30 sheets , + 3 sheets with multi graphics charts , size 28 MB
i removed the 3 graphics sheets , keeping the normal sheets which contain less than 1000 rows each BY 30 COLUMNS , Contain only letters & numbers
but the size still as it is : 28 mb
HOW DO I COPMRESS THE FILE TO THE MINIMUM SIZE
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

helpfinderIT ConsultantCommented:
do you have it in .xls or .xlsx file format?
.xlsx (MS Excel 2007 and newer) uses compresion so the same content in .xlsx makes the file smaller than in .xls.
So if you have it saved as .xls try .xlsx (if your MS Office allows you to do this) and if you are not able than you have to compress by WinRAR or freeware 7zip

To compare:
I have excelsheet with 700 rows, columns till FR and I copied that sheet 9 more times - so 10 sheets in one excel workbook with 700 rows and till FR column (174 columns should be) and my .xlsx file has 6,87MB.

If your excel does not contain anything else just characters as you wrote and is in .xlsx file format than I do not see a reason why it should be 28MB

Maybe there is something hidden? some another sheets/columns?
You can try to delete sheet by sheet, after every sheet deletion save the file and check the files size if it won´t jumps from 20+ MB to less than 10MB and you find sheet making troubles
0
NiceMan331Author Commented:
ok
i will trace it one by one
0
helpfinderIT ConsultantCommented:
Do it please and let us know about your findings
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

byundtMechanical EngineerCommented:
Please try the following macro to see if it reduces your file size. It deletes the blank rows and columns that lie beyond the bottom and right edges of your data. I wrote the macro a long time ago, but it is cited in this article by alainbryden Beginner's Guide to Improving the Speed and reducing the Size of Excel files.
Sub ExcelDiet()
    Dim ws As Worksheet
    Dim LastRow As Long, LastCol As Long
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            Range(.Cells(1, LastCol + 1), .Cells(ws.Rows.Count, ws.Columns.Count)).Delete
            Range(.Cells(LastRow + 1, 1), .Cells(ws.Rows.Count, ws.Columns.Count)).Delete
            LastRow = .UsedRange.Rows.Count 'Force excel to re-evaluate used range
        End With
    Next ws
End Sub

Open in new window

0
NiceMan331Author Commented:
buynder
thanx
i try it , run the macro , but the size still as it is
as there any requirement for that macro ?
0
byundtMechanical EngineerCommented:
There are no particular requirements for that macro. Note that it won't work on protected worksheets, however.

Did you save the file after running the macro? The file size reduction may not show up until you save the file, close it, then reopen it.

File format makes a tremendous difference in file size. Try saving it with .xlsb file extension (Excel binary workbook, *.xlsb).

If that still isn't helping, could you post the file so we can identify the root cause of large file size?
0
helpfinderIT ConsultantCommented:
NiceMan, did you try to trace which sheet is so big (deleting sheet by sheet)?
0
NiceMan331Author Commented:
please check the attached
it is one sheet
800 kb
sample.xls
0
byundtMechanical EngineerCommented:
Making no changes other than file extensions, I got 324 kb for .xlsx and 176 kb for .xlsb

When reviewing file content, it looks like you have a small amount of data in rows 248, 276 & 283 in columns BZ:CH. I relocated this data to the bottom of columns A:BR, but .xls file size did not change.

If the sample worksheet is typical of your workbook, you should consider saving the file as .xlsb Excel binary workbook for almost 4/5 reduction in file size.
sampleQ28252093.xlsx
sampleQ28252093.xlsb
0
NiceMan331Author Commented:
it talks hard to open the xlsb file
it send me many error message , then it open but as read only
then : could you please tell me how to convert to those extensions ?
0
byundtMechanical EngineerCommented:
Which version of Excel are you using?
0
NiceMan331Author Commented:
i'm using 2007
0
byundtMechanical EngineerCommented:
To save a file with .xlsb extension In Excel 2007:
1.  Click the Office "meatball" in upper left corner of window
2.  In the resulting dialog, choose Save As on the left and then click Excel Binary Workbook on the right
3.  Choose the desired name and location for the file. It will have a .xlsb file extension.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
it is ok
the 28 mb as xls is 6.4 mb as xlsb
ok , i accept the solution
but still the thechnical question  is : it normal that the file i sent you consume 800 kb
with that few columns & rows ?
0
byundtMechanical EngineerCommented:
In the workbook that you posted, the used range of cells was 754 rows by 70 columns (52780 cells). Of those cells, 23076 were populated. The .xlsb file was 176 kb, or 7.6 byte per cell containing a value (3.3 byte per cell in used range).

Those ratios don't seem overly large to me, considering that you have to store values, formulas, formats and other properties of each cell.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.