Excel : Hangs while inserting rows/columns

Many a times I faced this issue where Excel hangs while inserting rows/columns (and during many other types of edits)
Solutions you can try:

1. Check if the sheet has external links.
2. Press Ctrl+End and delete rows/columns which are beyond the cells actually having data. If there is even a space or a cell which was accidently edited but far from the actual last cell of the sheet then Excel has to move more data.
3. This is most invisible issue: Check conditional formatting applied for cells (Alt+O+D). On the Conditional Formatting Rules Manager (Excel 2010), in Show formatting rules for select "This Worksheet". You should now be able to view all rules applicable for the worksheet. If there are too many rules supposed to do the same formatting then this is your issue. Solution is to delete all similar looking rules and keep single rule applicable to multiple cells/ranges and keep doing the same whenever such cellls are copied to this sheet. Read this for details on how to manage conditional formatting for better performance.

Excel : Managing Conditional Formatting

If not managed property Conditional formatting can lead to performance issues in you worksheet. One of the simplest solutions is to keep a watch on what rules are in there.

Check conditional formatting applied for cells (Alt+O+D). On the Conditional Formatting Rules Manager (Excel 2010), in Show formatting rules for select "This Worksheet". You should now be able to view all rules applicable for the worksheet.

- Identify duplicate rules (quite possible if you copy data from another worksheet having conditional formats). Duplicates rules are those which have same/similar condition but has same/different ranges in Applies To column.
- Delete the duplicates
- Define only one rule and select all cells (under Applies To column) for which the rule is applicable.

If you frequently copy and paste from other sheets to this sheet, Excel is pretty notorious. If you just do a Ctrl+C and Ctrl+V Excel will keep the conditional formatting of the newly copied cells. As a result, even if you follow above steps to define single rule, it will break the ranges to exclude the newly copied cells. Over a period of times there will be additional conditional formatting rules equal to the number of times you paste into the sheet. If the Stop if true column is unchecked in the original sheet then each rule will be executed for each cell in ranges mentioned Applies To column.

Solution is to use Paste Special (Alt+E+S) and select All merging conditional formats. This will not break existing ranges in the rule as above. But it still adds another rule. This is easier to manage as you can identify such rules easily for deletion.