8 ways to Optimize Microsoft Excel files

Excel can work unreasonably slow or the Excel file size may exponentially increase. Here are some ways I use almost everyday to make the file smaller and work faster.

1. Save in .xlsx format, avoid saving in .xls format. (Potentially save 10-50% of file size)

2. Try saving in .xlsb Excel Binary format. (Potentially save 10-20% of file size)

3. Delete all names in the name manager. Sometimes there are hidden names, look up google for the Macro to unhide the hidden names, then delete them. The reason the names exists maybe because of poorly written Macros which created alot of the names but these poorly written Macros never deletes them after use. (Potentially save 10-30% of file size, prevents name conflict error message when you copy an Excel worksheet)

4. Delete all objects (shapes, comments, text boxes, pictures). Press F5-->Special-->Objects. Then press delete. (May speed up the file loading time by 90%, Excel will feel smoother when scrolling through the sheet)

5. Delete empty rows and column. If the file size was unreasonably large at about 10-90MB, the file size may be reduced to only 1MB. (Potentially saving 10-99% of the file size)

6. Unmerge all cells. If Excel freezes while you are trying to unmerge, try unmerging each column one at a time. (Excel will feel smoother when scrolling through the sheet)

7. If you're dealing with huge amounts of data, about 300 thousand to 1 million rows of data, try encapsulating the entire data into a pivot table, delete the original data, and save it. Of course, do keep a backup of the original data. (Saves 80% of the file size, lets you filter analyse the data much faster, files takes 80% faster to load)

8. Open the excel file with WinZip/WinRAR and look for the component which is causing the huge file size. Sometime it could be a huge picture inside the file, or single sheet that is causing the file to be huge. Delete said culprit. (Potential saving vary depending on the circumstances)

Excel may be useful tool, but can really get out of hand if left uncontrolled, hope these methods can help you make the Excel file smaller and work faster.

Feel free to share other tips with me, I only learnt some of these ideas above a few months ago. There are always alot of information out there to share.

View Facebook tagged photos, bypass private setting

Sometimes, certain people may make their tagged photos hidden. Here is a way to bypass that and view their tagged photos.

Search the person on facebook, click on the triangle and then click photos.



Alternatively, if there is no triangle to click on, then do the following:

1. Get the person's profile ID by going to the person's profile, right click anywhere and view source. Then Ctrl+F to find "profile_id".

2. Copy the profile ID and paste into this URL xxxxxxxxx

https://www.facebook.com/search/xxxxxxxxxx/photos-of

3. Done

4. Optional step: You can also filter by year example below.

https://www.facebook.com/search/xxxx/photos-of/2018/date/photos/intersect