filling intermittent blank cells with last value

  • Thread starter Thread starter john
  • Start date Start date
J

john

I have a number of columns of data.

All cells in a column are filled intermittently.

How do you fill cells with the last value shown.
 
Hi John -

Assuming your data goes from row 2 to at most row 1000, insert this
formula in cell A1; don't type the curly brackets, just the part within
them, then hold down Ctrl+Shift while pressing enter. If you do this
properly, it creates an array formula, which Excel indicates with the
brackets.

{=INDEX(A2:A1000,MAX(ROW(A2:A1000)*NOT(ISBLANK(A2:A1000)))-1)}

- Jon
 
John -

A second interpretation. Do you want to put the last value in any
subsequent empty cells?

Select the entire range, then press Ctrl+G, click on Special, and choose
the Blanks option button. This selects just all the blank cells in the
range you had selected. Press the Equals key, then the up arrow, then
Ctrl+Enter. This puts a formula in each cell which takes the value from
the cell above (which in turn, may take it from the one above that, etc.).

- Jon
 
Back
Top