Transpose 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 arises when working with time-series or historical data, because tables and charts approach them in opposite directions. When designing a table, the proper method is to place dates horizontally as column headers, so that we read them from left-to-right, like this:15

Year 2000 2010 2020
Series1
Series2

But when designing a line chart in Google Sheets and similar tools, which you’ll learn in Chapter 7: Chart Your Data, we need to transpose the data so that dates run vertically down the first column, in order for the software to read them as labels for a data series, like this:

Year Series1 Series2
2000
2010
2020

Learn how to transpose rows and columns in our 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.3.

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

Figure 5.3: 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.


  1. Stephen Few, Show Me the Numbers: Designing Tables and Graphs to Enlighten, Second edition (Burlingame, CA: Analytics Press, 2012), p. 166↩︎