There is a formula=average(c1:e5) in cell a1, if I...

Excel does “lose” the original C cells — but it can still keep the formula valid by shifting and resizing the referenced range.

What happens in your case

You have:

• A1: =AVERAGE(C1:E5) (that’s 3 columns: C, D, E)

When you delete column C, Excel doesn’t leave a hole. It removes that column and shifts everything to the right one column left:

• what used to be D1:D5 becomes C1:C5
• what used to be E1:E5 becomes D1:D5

So Excel rewrites your formula to reference the remaining cells:

• =AVERAGE(C1:D5) (which corresponds to the old D1:E5)

No #REF! is needed because the reference can be adjusted to a still-existing range.

When you would get #REF!

You typically get #REF! when the formula references something that gets deleted and there’s nothing left to adjust to, for example:

• =AVERAGE(C1) and you delete column C → becomes =AVERAGE(#REF!)
• =AVERAGE(C1:C5) and you delete column C → becomes =AVERAGE(#REF!)
• =AVERAGE(C1:E5) and you delete columns C through E (entire referenced area) → #REF!

So: deleting part of a multi-cell reference usually causes Excel to shrink/shift the range, not error out.

If you delete column E (the right edge of the range), Excel can still keep the reference valid by shrinking the range.

Starting formula:

• =AVERAGE(C1:E5)

Delete column E → column E disappears, and columns to the right shift left. Excel adjusts the range to the remaining columns:

• becomes =AVERAGE(C1:D5)

So again, no #REF!, because the range can be “repaired” by resizing.

You’d get #REF! only if the deletion wipes out the entire referenced range (e.g., delete columns C through E), or if the formula refers to a single cell/entire column that gets deleted (like =AVERAGE(E:E) or =E1).