When we are processing data, sometimes we need to change one line of content into multiple lines of content. Most people will use the copy and paste method to complete, but if you encounter a lot of content, it will be very troublesome and waste time.

So is there a way to achieve this effect quickly? Let’s talk about how to use the formula in Excel to quickly realize the operation of changing one row to multiple rows. Let’s take the following figure as an example.

There is a row of data here. Suppose we want to turn this row of content into three rows.

Type this 【=INDEX($A$1:$H$1,1,(ROW(A1)-1)*3+COLUMN(A1))】in any cell, and press Enter.

$A$1:$H$1: This represents from cell A1 to H1, so after we select this area with the mouse, we need to press F4 once.

*3: This means that I want to change the content into a row with only three cells.

So, now we use handle fill to fill this content.

There are three cells in a row, and then fill it down. After there is no content, the cell will display #REF!.

Well, that’s all for today’s tutorial. Bookmark this post so that you don’t need to memorize the formula. See you next time.