Sunday, June 25, 2017

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.

Sunday, March 19, 2017

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

3. Done

Saturday, October 31, 2015

Excel General Ledger Part 3: Dropdown boxes

Utilising the data validation tool in excel, we can create dropdown boxes for our ledger. This can save time keying manually and increases accuracy.

Create a new sheet, name it "chartOfAccounts"

Populate the sheet with account names

Go back to your ledger sheet.

And select the accounts column.

Click on Data > Data Tools > Data Validation

Change the fields as follows:
Allow: List
Source: =chartOfAccounts!A:A

You might want to disable error alert by clicking on Error Alert and unchecking "Show error alert after invalid data is entered"

Now you can select any cell in the account column and a dropdown box will appear.

You can also do something similar for the date column to save time entering today's date.

Simply by creating a new sheet with the formula "=now()" and using data validation steps above.