How to transpose values to column headers, including extra blank columns

• Introduction
• Basic Transposition
• Adding Extra Columns
• Why the ✏?

How to transpose values to become headers, adding extra blank columns in between the values as well.

Introduction

When analysing data in a spreadsheet format, you will often need to pivot a set of values (e.g. cells orientated in a single column) into a set of headings (cells orientated in a single row). This is normally very easy, but you might also want to do slightly more complicated things, such as add extra blank columns between each value to use sub-headings. Here is how.

Basic Transposition

Imagine we were gathering information about fruit preferences (who likes which fruits) in a spreadsheet. We have a list of fruits (such as the one below) which we want to become our column headings (from the B column onwards). The first column will contain the name of each person for whom we are recording data.

The following formula, using the transpose function, will change the orientation of our values (assuming the values are in Column A, from rows 1 to 15 inclusive).

TRANSPOSE(A1:A15)

We can also manipulate our list a little before we transpose it. In the following formula, we are applying a sort to the values first, meaning our column headers will be alphabetically sorted.

TRANSPOSE(SORT(A1:A15))

We could also take this further, using using query, importrange or filter functions to refine the list of values we want in our headers.

Adding Extra Columns

Occasionally, we may need to use sub-headings to further record data. In this case, we might want to record a taste preference (likes, does not like) and an allergy notification for each fruit. We could combine this into a single cell (‘Likes, Allergic’) but this makes filtering much more difficult and is error-prone.

Instead, we can use the following formula to create our headers with two columns per fruit (e.g. Apricot, Blank Column, Avocado, Blank Column, …).

SPLIT(ARRAYFORMULA(JOIN(REPT(" ✏", 2), SORT(A1:A15))), "✏")

Simply adjust the 2 value in the formula above to give the number columns you would like for each value. The key part of the formula is the blank space, as this will become the blank column, so make sure you don’t accidentally remove it when you use the formula above!

SPLIT(REPT(JOIN("✏", {"Preference","Allergy"})&"✏", COUNTIF(A1:A15, "<>")),"✏")

To create our sub-headings (a repeated set of ‘Preference’ and ‘Allergy’), we can use the formula above. Using the countif function, it will create these column pairs the same number of times as our fruits.

We will then end up with a set of headings that look something like this:

Why the ✏?

No reason at all! You just need to use any character or symbol that will not appear in your values at all! The pencil unicode symbol is just a memorable one chosen to meet this requirement.