Lists in Excel often need to be reshaped depending on the task at hand.

Sometimes you’ll have a flat list, where each value appears in its own row without any grouping or hierarchy. If a value occurs multiple times, it will simply be repeated X number of times.

On the other hand, an itemised list contains one instance of a unique value per row — but has one or more additional columns to house associated details.

How do you convert one to another?

Watch the video

Flat list → Itemised list

In the example, several cafe drinks appear in A3:A25 multiple times: “Cappuccino”, “Espresso”, “Hot chocolate”, “Latte”, and “Mocha”.

Thanks to Excel’s GROUPBY function, it’s pretty easy these days to convert them from a flat list to an itemised list.

The formula in C3 is:

=
GROUPBY(
A3:A25,
A3:A25,
COUNTA,
,
0
)

This categorises each drink type and uses the built-in COUNTA function to count how many there are in each group. The presence of 0 in [total_depth] also prevents the ‘Total’ row from appearing in the result.

Itemised list → Flat list

The example begins with the itemised list of drinks, which is composed of static values this time.

The formula in D3 is:

=
TEXTSPLIT(
CONCAT(
REPT(A3:A7&"|",B3:B7)
),
,"|",
TRUE
)

REPT is used to repeat each drink in A3:A7 according to the quantity in B3:B7, with the vertical bar (|) acting as the separator. Combined with CONCAT, this forms one long string that TEXTSPLIT then divides into separate rows.

TRUE removes the empty cell that would otherwise appear at the end. The result is a flat list with each item shown the correct number of times.