I have two 'tabs' in my Excel file and I would like to merge the two tabs into one sheet (in the same file).

The two files look somthing like:

artnr      language       description      price
artnr      language       description      price
artnr      language       description      price

What I would like to do is merge those two files in the following manner:

first row of first sheet
first row of second sheet
second row of first sheet
second row of second sheet

I tried to use the following formula:


This works, however when I try to expand the formula down (to the other rows) I get:


In stead of:


Any help is appreciated!


If you do not require the referencing to the original cells, you could do this another way.

First, in sheet 1 row 1; enter odd numbers on the cell to the right starting with 1 and drag the pattern to the last row

first row | 1
second row | 3
third row | 5

...and the same for sheet 2; except you use even numbers starting with 2.

Finish off by copying sheet 1 rows (including the numbering) below sheet 2 rows and do a sort by numbering column.

  • Now that's a simple and working solution! Thanks! – PeeHaa Dec 28 '10 at 13:47
  • Glad I could help. – Reuben L. Dec 28 '10 at 13:59

Put the following formula in A1 of your third sheet:


and then copy&paste in the rows below.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.