Slow Excel Spreadsheet? Try this...

There are a number of things that can cause an Excel spreadsheet to slow down. Sometimes, the spreadsheet can become unusable, requiring up to a minute for saving or even the most basic of changes. Here’s a brief checklist of the most frequent causes of Excel slowdowns.

(Have a spreadsheet slow-down that I haven’t described below? Let me know in the comments!)

§  Network problems. If the spreadsheet is accessed over the network (local or remote), network issues can delay saving.

§  Macros/VBA code. If you are using macros (or Visual Basic for Applications), make sure you don’t have unnecessary macros running, or macros that perform unnecessary calculations.

§  Burdensome animation. Try disabling animation. 2003: Tools > Options > Edit > Turn off “Provide feedback with animation”. 2007: Excel Button > Excel Options > Advanced > General > Provide feedback with animation (uncheck)

§  Complicated calculations. If you’re doing lots of calculations, you can disable auto-updating of these calculations. When you do so, your spreadsheet will not update until you explicitly desire (e.g. when you hit the F9 key). To disable auto-updating in 2007, go to the Excel Button > Excel Options > Formulas > Workbook Calculation, and select “Manual”.

§  Duplicate Conditional Formatting Rules. Conditional formatting can be a processor-intensive task if your rules aren’t set up right. Manage your conditional formatting rules (2007) via the ribbon at Home > Conditional Formatting > Manage Rules… Try to consolidate similar rules into one rule (e.g. a rule for an entire column instead of a rule for each cell in the column)

§  Funky Formatting. Formatting can really slow down a spreadsheet, especially on slower machines. Try removing all formatting: Removing All Formatting.

§  Unnecessary junk. Sometimes, an Excel file can become backlogged with a bunch of unnecessary junk. Try saving as a CSV (comma-separated) file, which will strip out everything except for the data. (Make sure to not save over your original file in case this workaround does not, well, work!)

§  Unknown, but it seems to help sometimes. Install the Lookup Wizard add-in.

§  Other issues. Do you know of another issue that causes Excel to slow down? Let me know.

Cell Referencing Method - MS Excel

In Excel there are three types of cell references, these are Relative, Absolute and Mixed.
There are two types in Mixed reference, Absolute Row Relative Column Reference and Relative Row Absolute Column Reference.

Detailed usage of cell references are listed below.

Serial No	Method	What is referenced?	Type	Usage
1	A1	Single cell	Relative	A is column identifier, 1 is row identifier
2	A1:B3	Range	Relative	This references 6 cells
3	5:05	Range	Relative	All cells in row 5
4	B:B	Range	Relative	All cells in column B
5	5:07	Range	Relative	All cells in rows from 5 to 7
6	B:E	Range	Relative	All cells in columns from B to E
7	$A$1	Cell	Absolute	The cell A1. Cut-pasting formula to another location will not change the reference.
8	A$1	Cell	Mixed	The $ makes the column reference absolute, leaving the row reference relative
9	R1C1	Cell	Absolute	R1 is row 1, C1 is column 1
10	R1C1:R3C2	Range	Absolute	Same as A1:B3 as shown above
11	R	Range	Absolute	Reference to current row
12	C	Range	Absolute	Reference to current column
13	R[-2]C	Cell	Relative	Cell two rows up and in the same column
14	R[-2]C1	Cell	Mixed	Row reference is relative, column reference is absolute
15	R[2]C[3]	Cell	Relative	Cell two rows down of current row and three rows to right of current column
16	R[-1]	Range	Relative	Cells in the row above the current row
17	(defined name)	Range or Cell	Absolute	Named cells are always absolute
18	Sheet1!(reference)	Either	Either	Prefixing the sheet number and exclamation mark before any of the above references indicates the source worksheet.
19	=‘C:\[sample.xls]Sheet1’!$A$1	Cell	Absolute	Refers to an external file - a worksheet in the file and an absolute position within that worksheet.
20	=‘C:\[sample.xls]Sheet1’!octroi	Cell	Absolute	Same as above. Refers to a defined name “octroi”
21	Sheet1:Sheet3!A1:B3	Cell	Relative	Refers to all cells of A1 to B3 in all worksheets from Sheet 1 to sheet 3. “Sheet1” and “sheet3” are actual worksheet names.
22	Getpivotdata function	Range	Absolute	This is a special function which is used to refer to a range of rows/columns in a pivot table.