# Excel how to insert below cell value and skip every other row?

I have a excel spreadsheet like:

What I would like:

Thus, what I am trying to achieve is for the current cell to take the value of the cell directly below value and repeat, skipping each of the directly below cells.

For example, cell `B2` will take the value 10.62 (i.e. cell `B3`); then cell `B3` will be skipped; then, cell `B4` will take value of 13.26 (i.e. cell `B5`), and following suite...

I have tried this formula:

``````=INDEX(B2:\$B\$4518,ROWS(B2:B2)*2)
``````

Then, selecting that row and the row below, which doesn't have any formulae, and dragging down. But, sadly unless I only drag down one then reselect it doesn't work correctly. It seems to take the first selected cells value and then adds 1.

• please post also screenshot of current- and desired outputs, your question is unclear now. – Máté Juhász Oct 3 '16 at 11:52
• I have updated the question – Vaishal Patel Oct 3 '16 at 12:13

So, if I'm understanding correctly, in row 2 you want the value from row 3, in row 3 you want to keep its own value, in row 4 you want the value from row 5...etc?

If that's correct, there's a fairly straightforward process you can use - in C2, enter the formula `=\$B3`. In C3, enter the same formula, `=\$B3`. That should put the same value into both cells. Then highlight both cells and use the fill handle to copy them into the other cells. This should get you the repeating pattern you want.

• I need the values in the B column though. – Vaishal Patel Oct 3 '16 at 13:57
• You would use the C column to create the values, then you can use Copy | Paste Special (Values) to place the results over the top of the existing values in B. – Werrf Oct 3 '16 at 14:00

because you will need the B value for 2 cells, copy the B column to the C column otherwise will eventually get a reference issue

then in the B column, replace with the following formula

• =IF(EVEN(ROW()),C3,C2)

The if checks to see if needs to go down 1 or just go across.

Row() returns the row this is on. In the case of B2 it is 2 Even() checks if something is even. For a 2 this is true.

IF (test,True_Action,False_Action) . The if works by making a test then choosing either a true or a false action. Since Even is true for b2, the true_action points to C3.

Note. This has the side effect of expanding your data range. A copy /paste special could be used over the b row to replace with the new values. Then afterwards, the C column could be deleted.

• Very insightful, I went with the option below which seems to have done the trick fantastically. – Vaishal Patel Oct 4 '16 at 11:26