# Excel formula to populate every 3 rows in a column from values in every 3rd cell from in an adjacent column

I'm looking for a way to populate every 3 rows in a column from values in every 3rd cell from in an adjacent column.

So I am trying to go from this:- To This:- I have thousands of rows, so I am trying to find a way to make this as painless as possible.

Thanks

Try:

``````=IF(D2<>"", D2, IF(D3<>"", D3, D4))
``````

Best regards, Lukas

• It's good one +10 ☺ Sep 17, 2019 at 9:24
• @RajeshS thx... :-) Hope, there is not a gap greater than 2 rows... :-)
– Muji
Sep 17, 2019 at 9:25
• That is perfect!! Thanks Muji Sep 17, 2019 at 9:29
– Muji
Sep 17, 2019 at 9:31
• If cells contains spaces or any another "invisible" symbols (not empty value) the formula will fail. Sep 17, 2019 at 12:20

If values in column D are numbers, then:

``````E2=MAX(D2:D4)
``````

Drag this formula down.

If values in column D are strings which must be treated as numbers, then:

``````E2=MAXA(VALUE(D2:D4))
``````

Formula must be entered as array formula (by pressing Ctrl+Shift+Enter). Then drag it down. If some cell contains a value which cannot be converted to number type by VALUE() function then you'll obtain #VALUE error for according cells.

Manually set the cells in the first three rows to all be equal to the first calculated value.

Then, select those 3 cells.

If there's anything in the column to the right of those cells, you may be able to double click on the square "◾" icon in the bottom right corner of the selection's outline to get it to auto-populate the remaining cells.

Otherwise, drag the square "◾" icon in the bottom right corner of the selection's outline to the last cell you want populated.

This should result in every cell having a formula with a simple reference to the desired "calculated" cell, without any indirect formulas, which should be faster for Excel to recalculate than the formulas from any of the other methods.

• If there's anything in the column to the right of those cells ... This works until first empty cells pair (adjacent to left and to right) - i.e. maybe not whole column will be filled. Sep 17, 2019 at 12:38
• Otherwise, drag the square "◾" icon The same may be performed using copy-paste (drag down can be not safe when there is too many lines to fill). Sep 17, 2019 at 12:40
• @Akina, Yes, it would stop at the first empty cell to the right. If there many cells then starting with the bottom 3 cells and dragging up would be easier. Copy+Paste is also quick, and you could use `CTRL`+`SHIFT`+`Down`/`Up` to speed up selecting the target cells. Sep 17, 2019 at 12:55
• it would stop at the first empty cell to the right. To the right AND left. Any one of them is enough to continue copying down. Sep 17, 2019 at 12:59
• Copy+Paste is also quick It allows select 1st cell, then freely scroll down, then Shift-click last cell - all intermediate cells wil be selected too. Then Ctrl-V. Sep 17, 2019 at 13:00

Put this in E2 and fill down to cover all the values in column D.

``````=INDEX(D:D, MATCH(1E+99, D\$1:D4))
``````