Put your Excel files on a diet!

In Office 365, Excel Online has a strict limit on file size. If the file is bigger than 10MB, the file will give you an error and not display.

Frequently, your file may be larger than you realize because there are blank cells holding formatting information.

The way to detect this is to press CTRL+End when you are in a cell on each worksheet in your workbook. If the last cell in the sheet is far beyond the data you actually have, you probably have formatting blank cells that you don’t want.

Case in point: A user gave me a workbook with 7 worksheets, as follows:

Tab Name Last Cell
Sheet 1 AA471
Sheet 2 AXL1048560 – yes that is 1,048,560 – over a MILLION ROWS!
Sheet 3 S8
Sheet 4 AVH332
Sheet 5 DL284
Sheet 6 A1
Sheet 7 H90

The file was 5.6MB.

Sheet 2 only had 18 columns and 38 rows of data. But the remaining columns, from S to AXL, and from 39 to 1,048,560, had invisible formatting data.

I created a new worksheet in the workbook, copied just A1 to R38 to the new sheet, and deleted the original sheet. When I saved the resulting workbook, it was now 254KB. It was now about 4% of its previous size! It shrunk 96%!

The file now also uploads and downloads and opens faster!

–Michael

Leave a Reply

Your email address will not be published. Required fields are marked *

*