In this lesson, we’re going to move away from the container arrival data and invoice data that we’ve been working on in the last few lessons. In a different set of data we’re going to look at the useful transpose function. This function is very similar to the TRANSPOSE function that we have in standard Excel which will move data from a column format into a row format and vice versa.
Let us imagine that we need to change some data so that the headers and first column have swapped place…something like the below:
In the above example, we want to swap round the department names and month names so that the list of departments is on the top of the table, while the month names are down the side of the table.
To do this we can use the Transpose function.
How to use the Transpose function
To do a simple transposition, the first thing to do is get rid of any rows above, or columns before, the data we wish to transpose. In our example above, this would be the row with the value ‘Headcount’ and the first column with the word ‘Department’ as those will make a mess of our transposition. It is possible to include them in a transposition, but that’s a little more advanced, so we’re going to leave it for the moment.
To remove the top row:
a) select Remove Rows
b) then remove top rows and put in 1 in the quantity box.
To remove the first column:
a) highlighting the column,
b) right click the header and select Remove Column.
Right, now we can move on to transposition.
So making sure that there isn’t any of our data in the headers (so we want the headers to just be called Column1, Column2 and so on), go to the Transform tab and select Transpose.
And that’s it! We’ve now got the axes moved around.
Nothing else to add here – next lesson please!