Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer
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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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