I need to turn an excel sheet like this:

enter image description here

Into a sheet like this:

enter image description here

So basically that one cell should be split into several, such that new rows are generated and the other cells that used to be in that row such as Spam and Eggs are merged.

Doing this manually is time-consuming as this happens often, and in reality there are many more columns than in this simplified example.

If this is not a feature which is already in Excel, don't bother writing macros/scripts because I'm pretty sure we wouldn't end up using those.

What I tried

Browsed through the first page of results on Google and watched some YouTube tutorials, but they are all either about some different feature of Excel called "tables", or about splitting the data itself into multiple rows, but neither seems to be what we want.

  • Create two new rows below Spam, mark the cell Spam plus the two cells below and use the feature to join cells. Same for Eggs
    – Robert
    Sep 5, 2021 at 4:08
  • Yes, but is there no feature to just split that one cell so that all this will happen without having to do it manually? In reality there are way more columns than in this example.
    – Bemmu
    Sep 5, 2021 at 4:26

1 Answer 1


This is how i would do it: (assuming on pc)

  1. Right Click on the number 7
  • (left side, first screenshot below row 6)
  1. select Insert Row
  2. repeat,
  • Now you have two blank rows (7 & 8) as in second screenshot.
  1. merge cell,

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .