Splitting an excel cell vertically into several cells

I need to turn an excel sheet like this:

Into a sheet like this:

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.

1 Answer

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

1. Right Click on the number 7

• (left side, first screenshot below row 6)

2. select Insert Row
3. repeat,

• Now you have two blank rows (7 & 8) as in second screenshot.

4. merge cell,