Excel files larger than they should

Data base programs often export data as CSV files. Also if you want to transport Excel data into a database program, often the CSV file type works. The file type is designed to work on both Excel and other applications, e.

Text and data is usually saved well though. But formatting and many other features for example charts, tables, conditional formatting might run into problems. So what is the difference between those two? Basically, the two file types use the same structure.

But :. The Strict variant has less support for backwards compatibility when converting documents from older formats. Additionally: Opening in other applications than Excel might work better. Some backward compatibility problems might occur.

PDF stands for portable document format and has the reputation of not easily being manipulated. I recommend using it when you present your final results in the following cases:. That could be the case if somebody wants to check your results on an iPad or a similar device. But: In many cases it would be professional and nice to also send at least parts of your Excel file. That way, your recipient could at least take a look at the calculation process.

XLAM is the file extension for Excel add-ins. They can contain VBA macros, worksheets as well as forms, images and individual ribbons. If you double click on such file, it will open in Excel and you can use the funcationality of the add-in. As the XLAM file type is not really an alternative for all the other file extensions above, we skip the advantages and disadvantages.

So if we put all the advantages, disadvantages and facts from above together, we come to the following comparison:. You just have to answer two questions: Do you need VBA macros and do you work with a large amount of data? Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports. Wonderful information on Excel. You pulled a lot on information together in a succinct presentation.

Thanks Much!!! Hi Foster, Thanks for the reply. Best regards, Henrik. Thanks for the feedback, Mark. Does this always happen to you working in the xlsb file format? Thanks for the feedback. Please provide any proof for your accusations. What do you mean with misleading?

And concerning the second part: What is wrong about it? Please try it yourself: Save an Excel file as. What is the maximum row number there? What is the maximum column? So please provide any kind of evidence or proof supporting your comments. I have sevral functions that do lookups on multple arrays of data.

I added the array into Named Ranges on a worksheet, thing this would make it easy to update the arrays when needed. Once I got everything working, I saved as. When I open the. What is the best file type for this? Or should I just put my lookup data into static arrays in the code?

I am suitably embarrassed since it said to do that on the referenced website but I didn't think "some older versions" applied to Office XP, although I guess it is an older version.

Further good news is my file is now much smaller than it was. It still thinks the document has been altered even if I just open and try to immediately close it, however see other thread. Re: Excel thinks my spreadsheet is much bigger than it is See another reply in that other thread.

Bookmarks Bookmarks Digg del. All times are GMT The time now is AM. But the 'used range' in a new worksheet is saved as cell A1 to the last cell with data in it. Willl 4 4 silver badges 14 14 bronze badges. Nigel Heffernan Nigel Heffernan 4, 35 35 silver badges 38 38 bronze badges. Hi Nile, Thanks for your suggestion.

It is not possible to ask my users to do the save as option each and every time. I got almost the solution not a solution exactly from Microsoft. It seems it is a bug from Microsfot. Please find the link for the same: support. Saving as HTML may strip out important information such as filters. Better to delete the cruft data. Greg - Deleting the 'cruft' data is a good suggestion; but it's a long job on workbooks with several sheets, and you will run into cases where the file bloat is binary 'bitmap' cruft usually associated with pivot caches and deleted images which is inaccessible to the User Interface.

At that point, file 'washer' tools and saving in and out of other file formats is all you've got. I dev an AddIns with auto install in french to include in context menu 3 new buttons: Optimize Optimize and Save Toggle Case This is based on KB of Microsoft office with personals improvement : add compression of images fix bug for Columns feat compatibility with excel - Alban Alban 2, 5 5 gold badges 26 26 silver badges 43 43 bronze badges. UsedRange End Sub You could also tie this macro to an Excel keyboard shortcut for quicker access using the Application.

Zack Graber Zack Graber 1, 8 8 silver badges 7 7 bronze badges. Gayathri Visvanathan Gayathri Visvanathan 11 1 1 bronze badge. The Overflow Blog. Stack Gives Back Safety in numbers: crowdsourcing data on nefarious IP addresses.