In Excel, you can sum a number of cells using a variable range with the INDIRECT function. The INDIRECT function automatically updates the range of cells that you have referenced, without manually editing the formula itself. You can use the INDIRECT function with any number of Excel functions, but the most common (and useful) is when using the SUM function.

You can include the INDIRECT function as an argument in the SUM function to create a variable range of cell references that the SUM function can add. The INDIRECT function does this by referring to the range of cells indirectly, through an intervening cell reference.

The format of both functions looks like this:

This locks the range to start at D1, and allows D4 to change if you insert or delete cells in column D.