Golden rules of inserting columns and rows

Excel has numerous shortcuts and tricks, particularly for creating formulas and functions, and for managing data lists. These tricks won't work if you have blank columns or rows within the body of your data.

You also won't be able to navigate easily with the END key, create subtotals or AutoOutlines, etc.

So rule #1 about inserting columns and rows in Excel is

Don't insert a blank column or row for "white space" in your worksheet. Instead, make existing columns or rows bigger to provide the white space.

As you know, functions, names, and many other things in Excel refer to ranges, for example the function =SUM(C3:C8) refers to the range from cell C3 to cell C8. If you insert in the middle of a range, references to the range will expand accordingly. For example, if we added a row at row 6, the function would automatically expand its reference and we would see =SUM(C3:C9).

But, if you insert a row before or after the end of a range, references to the range will not adjust. You will then have to hunt down and modify each instance of the range. That can be difficult, time-consuming, and potentially prone to error.

Therefore, rule #2 about inserting columns and rows in Excel is

Never insert new items at the end of ranges when doing so will throw off your formulas, functions, and names.