They say bigger is better but if you are asking why is my Excel spreadsheet so large that is probably not the case! If you have been busy working on your Excel file when all of a sudden it jumps in size, maybe from KB to MB, or MB to GB, then this post is for you.
There are a number of reasons an Excel file can jump in size, the positive is you can usually resolve the problem in less than 10minutes.
Get rid of the junk
Many times a sudden increase in Excel spreadsheet size is a result of applying formatting, especially conditional formatting to areas of the spreadsheet you probably are not using.
Most people don’t use the whole Excel spreadsheet, scrolling around too much makes for a cumbersome file so the norm is to stick to a smaller work area that fits to most screens. There is usually no need to have formatting outside of this work area as it will only take up space so ensuring all of this space is 100% clear is critical to keeping an efficient Excel file.
It is very easy to accidentally apply a format, or even worse a conditional format, to an entire row or column as we often drag cells and formulas around the spreadsheet without paying much attention. This means the first thing to do in order to reduce the Excel file size is to clear all the junk outside your main work area.
Clear those Columns and Rows
Remember: Always save your file and make any changes to a copy, just in case you remove something unintended.
Open your file and find the end of your work area, you will repeat this process for each worksheet you are using so start with the first if you have multiple sheets. In the example below there is nothing after column M so that can be highlighted by clicking on the column letter.
With the starting column highlighted select all the remaining columns in the worksheet. Do this by dragging the mouse across and highlighting them all or better yet use the keyboard shortcut of pressing the CTRL key along with the Shift Key and the Right arrow on your keyboard. This will highlight every column from M to XFD.
With the entire range highlighted click on Clear and then Clear All from the Editing section of the Excel ribbon. This will remove any data, formatting, conditional formats and comments that have managed to find their way into that range. Remember even if a cell looks blank it sometimes isn’t so go ahead and clear it all.
Now the process is repeated for the rows outside of the work area.
Highlight your starting row by clicking on the row number, in this example there is nothing past row 20 of the Excel worksheet.
With the starting row highlighted select all the rows below either by dragging the mouse or press the CTRL key + Shift + Down Arrow on your keyboard, this will highlight all rows from your starting point.
And as with before click on Clear and then Clear All from the Editing section of the ribbon.
If you are using multiple worksheets in the Excel file repeat this process for every worksheet and then save your file, it should be significantly smaller now as you have cleared any rogue formatting and data from the file.
My Excel spreadsheet is still too large!
If you have tried the above method and your Excel file is still huge there are a few other considerations.
Data
Data takes up a lot of memory in any Excel spreadsheet so you should try and limit the amount that you bring into the file. Recent versions of Excel can handle an ever-increasing amount of data efficiently but there are always limitations so if you have a truly huge data-set to work with consider doing the grunt work with database software like MS Access.
The problem doesn’t always stem from the actual data itself but comes when you try to work with it in Excel. For example adding formulas, lookups or conditional formats to a large data-set will always increase your file size so the key is to limit the amount of data you import into Excel, and the amount of manipulation you do to that data.
Formatting and Pictures
Excel is very efficient when you choose to pimp the spreadsheet up which is great news if you are not a fan of boring spreadsheets. Removing existing formatting to try and decrease the file size is often a wasted effort. As long as your formatting doesn’t expand across the entire worksheet you should be fine so look for other issues that could be causing the problem.
One thing to watch out for is importing images or external files into your Excel workbook, for example a company logo or a picture from the web. Sometimes these files can be huge without your knowledge so try removing them if you are having problems. If you really want to use an external image in your Excel file that is a bit big try compressing the image first using a free compression website like Compress JPEG.
Formulas
This really ties in with Data as generally formulas won’t be a problem in your Excel file, after-all it’s a key feature of Excel!
The main problem is when you are using complex formulas on medium to large data-sets. Formulas like nested IF statements, lookups and especially Array formulas are very memory intensive and can easily cause an Excel file to shoot up to an abnormal size. As with before if this applies to you then the best option is to do the manipulation in database software first. Alternatively try breaking down complex formulas into multiple steps. This method may use more rows/columns and look less efficient but if it avoids using Arrays or nested IF statements you will likely find it reduces file size and enhances the files performance.
Summary
Hopefully you have now answered the question Why is my Excel spreadsheet so large! After applying the techniques above you will be able to go back to working with regular Excel file sizes and next time your Excel spreadsheet suddenly becomes 2GB there is no need to panic.
Keep Excelling,
Now you’ve explored ‘Why is My Excel Spreadsheet So Large?’, it’s time to delve into another common Excel issue: unresponsive formulas. Discover why your ‘Excel Formula Does Not Calculate‘ and how to fix it for a smoother, error-free Excel experience.
