How to fill in missing data with a simple formula

This isn’t a problem for humans because we can see and understand the overall structure of the data.

Let’s try it with this data.

To begin with, grab the data.

Next, select only the empty cells.

The key is starting with the right reference.

To do that, verify the active cell in the selection is one of the empty cells.

The active cell will be the basis for all formulas we enter.

Next, enter a formula that sets the active cell to the address of the cell above it.

Control + Return enters the same formula into all selected cells at once.

Because the formula contains a relative reference, Excel will update the address at each location.

This creates a chain of formulas that all “look up” to retrieve values.

With all formulas selected, copy to the clipboard, then usePaste Special, and select values.

This replaces all formulas with actual values.

Now we have a complete set of data that we can easily analyze.