Transpose or Swap Rows and Columns

Sometimes you download good data, but your visualization tool requires you to transpose, or swap the rows and the columns, in order to create the chart or map you desire. This problem often comes up when working with longitudinal or historical data. For example, you often find the data organized with years placed horizontally as column headers, as shown in Figure 5.3.

We often find data with years placed in horizontal column headers.

Figure 5.3: We often find data with years placed in horizontal column headers.

But if you wish to create a line chart, which you’ll learn in Chapter 6: Chart Your Data, you need to transpose the data above, so that the years run down the first vertical column, as shown in Figure 5.4.

But we need to transpose the data to place years in the first vertical column.

Figure 5.4: But we need to transpose the data to place years in the first vertical column.

Let’s transpose some sample data:

  1. Open the Transpose sample data file in Google Sheets, sign in with your account, and go to File > Make a Copy to create a version you can edit in your Google Drive.

  2. Select all of the rows and columns you wish to transpose, and go to Edit > Copy.

  3. Scroll further down the spreadsheet and click on a cell, or open a new spreadsheet tab, and go to Edit > Paste Special > Paste Transposed, as shown in Figure 5.5.

Go to Edit - Paste Special - Paste Transposed to swap rows and columns.

Figure 5.5: Go to Edit - Paste Special - Paste Transposed to swap rows and columns.

Now that you know how to clean up data by transposing rows and columns, in the next section you’ll learn how to split data into separate columns.