Split one long column into multiple smaller columns

It happens quite often that you have a very long table and want to split it into multiple column in order to have a better view or to be able to handle it better.

For example some formulas do not accept more than 255 entries and so that is what you have to do. Please look at the example here to find how to display more than 255 curves in a graph.

In this example we start in a sheet we will call "rawdata" that looks like this.

split-one-long-column-into-multiple-columns



As per the example mentioned earlier, the formula to transform this long column into multiple smaller columns look like this.

=INDIRECT(CONCATENATE("'rawdata'!";$A$4;(ROW()+$A$5-$E$2-7)+$E$2*(INT(COLUMN()-1))))

(beware depending where you live, you might have to change the semi column ; into , or the opposite)

It uses the INDIRECT function that lets you create formulas with formulas.

You will get the following result in the result sheet

split-one-long-column-into-multiple-columns



 

Congratulation, you made it until the end of this Example.

You can download the example here.