Fixing Bloated Excel Files
Often times, excel files grow to a size much larger than is necessary considering the quantity of data stored in them. Sometimes this is due to an excess of pictures, formatting, or drawing objects within the workbook, or hidden sheets that contain a lot of data, but 9 times out of 10, the problem is with dirty cells
. By dirty cells, I am referring to cells that once contained data, but have since been cleared. Excel needs them to be cleared in a specific way before it will free up the associated memory.
If your files seem bloated, a very common cause is Excel thinking that some data is present in some place quite distant from the actual end of data. If your scrollbar goes way past your data (lots of scrollbar travel left, even though you are at the bottom of the data), then you need to clear the blank rows and columns beyond the range of your data.
Go to the bottom row of data, then select the entire next row by clicking on the row number. Then press the keys “CTRL” + “Shift” + “Down Arrow” to select all the remaining rows in the worksheet. Right click within your selection, and choose “Delete”. Now when you save the workbook, the scroll bar should instantly expand, showing that there is less scrolling range. You can do the same if your horizontal scroll bar is going too far. Select the first blank column, “CTRL” + “Shift”+ “Right Arrow” will take you to the last column, and then delete the selection. If you can identify several sheets with excess scrolling space such as this, and delete the unused rows and columns, you should find the file size greatly reduced.
: You cannot simply select these cells and hit the "Delete" key on your keyboard. This key actually tells Excel to "ClearContents" of the selected cells. The cells may still remain dirty because of individual formatting settings, especially if the original content was copy-pasted in.
If you are familiar with Macros, you can create a macro that will automatically clear excess space in all the sheets in a workbook. All you have to do is open the VBA project manager (Alt+F11) and create a new module, then paste in the following code:
'Credit: byundt in Q_22889991
Dim ws As Worksheet
Dim LastRow As Long, LastCol As Long
Application.ScreenUpdating = False
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Range(.Cells(1, LastCol + 1), .Cells(ws.Rows.Count, ws.Columns.Count)).Delete
Range(.Cells(LastRow + 1, 1), .Cells(ws.Rows.Count, ws.Columns.Count)).Delete
LastRow = .UsedRange.Rows.Count 'Force excel to re-evaluate used range
Application.ScreenUpdating = True
Running this code and then saving the file should instantly shrink an excessively large file.
If your workbook is large because it has many rows of a repeated or iterated formula, you may want to consider conceiving of a way to delete all but a couple rows of the repeated computation when you go to save the workbook, and restore the large number or repeated calculations only when they are needed upon running the workbook.
If your workbook is large because you are storing hundreds of thousands of rows of data (i.e. using Excel as a database), you should consider at some point switching to a more appropriate infrastructure, such as using SQL Server or Microsoft Access to maintain your database.
Making Excel Run Faster
There are many, many reasons why excel might be slow to update. Most times, it is due to having a huge array of complicated formulae running each time an update is made. If you just want to speed up normal operation of your spreadsheet, you should consider the following:
You can disable automatic updating of all calculated values until you have made all your changes. In either the Office Ribbon or in Excel Options, go to the “Formulas” tab and look for the “Calculation” section. Here you can change calculation options from “Automatic” to “Manual”. Remember to turn it back to Automatic later, because this setting will not reset automatically and applies to all Excel files, not the current one.
Certain types of formulas are processor hogs, sometimes you can find much faster alternate functions, but you should consult an excel expert for this. Using a lot of VLOOKUP, MATCH, and D-Functions are among the worst culprits for slowing down your application. Sometimes there is no other solution, but you should aim to replace them or optimize them. Make sure your lookup ranges aren't bigger than they have to be, and if possible, sort the lookup range by the value you're looking up. Also check for array forumulas that operate on very large ranges. You should also try to avoid using a lot of custom (VBA) functions within the spreadsheet, as these are usually slow to run as well and do not take advantage of parallel computing.
Unload other applications and worksheets from memory while running calculating this file. Because excel files all open in the same instance by default, a slow workbook will perform much better if there aren’t other workbooks open at the same time, taking a share of its resources.
If you are using VBA functions and/or macros it may be possible to write more efficient versions (see below)
If you have physical memory restrictions that don't allow an entire workbook to be held in memory all at once, make sure you at least have free hard disk space for paging. Writing to disk is much slower than working with a workbook in memory, so keep in mind that the a larger workbook will be directly related to the speed at which it operates.
Making Excel Macros Run Faster
You can turn off screen updating with Application.ScreenUpdating = False. This speeds up code by orders of magnitude by removing the need for excel to update the user interface with each new action that is automatically performed. This also prevents the unsightly flickering that recorded macros suffer when run. Make sure to re-enable screen updating at the end of the code.
You should disable automatic calculation for the duration of the code, and only force computation on the sheets that require it when your code needs excel to produce a result. Often times, you can disable calculation for the entire macro and just re-enable it again at the end. To do this, use Application.Calculation = xlCalculationManual. Restore it by setting to xlCalculationAutomatic. You can force calculation when it’s needed using Application.Calculate for the whole workbook, or Sheet1.Calculate if you only need to recalculate a certain sheet.
Use VBA functions instead of Excel.WorksheetFunctions. There are almost always VBA equivalents to basic excel worksheet functions and you should use them in code, as they are usually faster.
Do not use “Select” or “Selection” in your code. Always refer to objects directly. Instead of using “Range(“A1”).Select” followed by “Selection.Value = 12”, use “Range(“A1”).Value = 12”. The former is what code looks like when it’s first recorded, but it should always be adjusted. This not only improves the speed, but the reliability and readability of the macro.
This has all been general advice that applies to pretty much every Macro in excel, but there are many more specific tips available if you take the time to research them. You should search online for ways to speed up specific actions you are trying to perform, such as fast string concatenation, more efficient log-normal distributions, or fast data importing from external sources.
If, in spite of your efforts, your file is still suspiciously large or your code is running too slowly to be practical - Hey! That's what experts-exchange is for. Post a question and I'm sure we have tons of suggestions for further improvement.
Byundt on Shrinking Excel files
ShaneDevenshire on speeding up Excel calculation
Sample VBA speed-up articles:
Optimize string handling in VB6
Microsoft Q72622: Optimizing Worksheets for Fastest Calculation
MSDN: Improving Performance in Excel 2007
Using Regular Expressions in Visual Basic for Applications
Fast Data Push to Excel
How to Split a String with Multiple Delimiters in VBA
A VBA Progress Bar for Excel