This can be useful for presentation purposes or to make your table easier to read, for example if your dataset has grown to a large size and the number of columns means it’s very wide, or very long. TRANSPOSE is a handy alternative to copy-pasting, allowing you to switch rows and columns in Google Sheets instantly without errors.<\/p>\n
This guide shows how to turn rows into columns, or columns into rows, with the TRANSPOSE function. It also covers how to paste data transposed with Paste special, and how to combine TRANSPOSE with UNIQUE and SPLIT.<\/p>\n
TRANSPOSE syntax<\/h2>\n
TRANSPOSE(array_or_range)<\/strong><\/p>\n array_or_range<\/strong>\u00a0\u2013 the array or the reference to the range of columns and rows you want to swap or transpose.<\/p>\n <\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This is one of the simplest formulas in Google Sheets. Take a look at the following examples:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/TRANSPOSE-google-sheets-1.png” alt=”TRANSPOSE Google Sheets \u2014 sample data with EMP ID #, Salesperson and Sales in A1 to C7″ title_text=”Sample data with EMP ID #, Salesperson and Sales in A1 to C7″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n My sample data fills cells A1 to C7. At present, my list of Employee ID numbers (Emp ID#) runs vertically along the left hand side of spreadsheet, in a column. Instead, I want to see my Employee ID list horizontally, along the top of my spreadsheet. <\/p>\n To do this, I enter a TRANSPOSE formula into cell D1:\u00a0<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/TRANSPOSE-google-sheets-2.png” alt=”TRANSPOSE Google Sheets \u2014 =TRANSPOSE(A1:C7) in D1 turns the three columns into rows” title_text=”=TRANSPOSE(A1:C7) in D1 turns the three columns into rows” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You can see that my original data range has been inverted. The rows and columns from the input\u00a0array_or_range\u00a0<\/strong>have been swapped and the new (flipped) dataset starts in cell D1.\u00a0<\/p>\n Remember to leave enough space in your spreadsheet for the new table of data. Otherwise, the formula returns\u00a0#REF! error:<\/a><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/TRANSPOSE-google-sheets-3.png” alt=”TRANSPOSE Google Sheets \u2014 #REF! error in D1 because the result would overwrite a value in D2″ title_text=”#REF! error in D1 because the result would overwrite a value in D2″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Another option is to enter an array<\/strong> instead. Here’s an example:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/TRANSPOSE-google-sheets-4.png” alt=”TRANSPOSE Google Sheets \u2014 array of four names and numbers transposed into rows 1 and 2″ title_text=”Array of four names and numbers transposed into rows 1 and 2″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n When entering the array, use semi-colons to separate each individual row value in the input range. For individual column values, use commas. As before, you need to ensure that there is enough empty space in your spreadsheet for the new, transposed table of data.<\/p>\n If you want to delete the original table of data and replace it with the transposed data<\/p>\n You might notice that your formatting is lost when you transpose the data in Google Sheets. In this case, use the copy-paste method:<\/p>\n [\/et_pb_text][et_pb_text module_id=”how-to-paste-transposed-data-in-google-s” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The TRANSPOSE formula keeps the flipped table linked to the original, so it updates whenever the source data changes. When you only need a one-off copy, paste the data transposed instead. The pasted table is static: it keeps the original formatting, and you can edit or sort it like any other range.<\/p>\n Follow the instructions below to transpose your data when pasting into a new cell.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/paste-transpose-google-sheets-select-range.webp” alt=”Paste transpose in Google Sheets \u2014 vendor price table selected from A1 to N9″ title_text=”Vendor price table selected from A1 to N9″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/paste-transpose-google-sheets-copy.webp” alt=”Paste transpose in Google Sheets \u2014 right-click menu with Copy on the selected range” title_text=”Right-click menu with Copy on the selected range” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Select the cell where you want the transposed data. Go to Edit > Paste special > Transposed, or right-click the cell and choose Paste special > Transposed.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/paste-transpose-google-sheets-paste-special-transposed.webp” alt=”Paste transpose in Google Sheets \u2014 right-click Paste special menu with the Transposed option” title_text=”Right-click Paste special menu with the Transposed option” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 4.<\/strong> That\u2019s it. The rows and columns in the table have been transposed.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/paste-transpose-google-sheets-result.webp” alt=”Google Sheets swap rows and columns \u2014 pasted table from A11 with months down column A and vendors across the top” title_text=”Pasted table from A11 with months down column A and vendors across the top” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n There is no specific keyboard shortcut for pasting transposed data. However, if you have enabled compatible shortcuts<\/a>, you can use a shortcut to select this option quickly. After copying the cells you want, click on an empty cell and use the following shortcut to transpose and paste: Alt + e, s, e (Windows) or Ctrl + Option + e, s, e (Mac).<\/p>\n [\/et_pb_text][et_pb_text module_id=”use-transpose-with-other-functions” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n While the function is very useful on its own, you can manipulate your data with other functions before using it. By nesting functions, you can transpose the results of your formulas. Below, you have examples of how to combine TRANSPOSE with two other functions: UNIQUE<\/a> and SPLIT<\/a>.<\/p>\n Imagine you have a table with sales for different teams. You want to create a table with summary data for each team, with the team name as the column header. By using TRANSPOSE and UNIQUE on the sales team column, you can get the unique team names in a row.<\/p>\n 1.<\/strong> Select the cell where you want the data, ensuring there are enough empty cells below and to the right.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/transpose-google-sheets-unique-select-cell.webp” alt=”Transpose Google Sheets with UNIQUE \u2014 sales table with a sales_team column and cell G1 selected” title_text=”Sales table with a sales_team column and cell G1 selected” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> Type the TRANSPOSE function, then the UNIQUE function, and select the range of cells you want. In this case, select the data in the \u2018sales_team\u2019 column.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/transpose-google-sheets-unique-formula.webp” alt=”Transpose Google Sheets with UNIQUE \u2014 =TRANSPOSE(UNIQUE(D2:D585 typed in cell G1″ title_text=”=TRANSPOSE(UNIQUE(D2:D585 typed in cell G1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Close both parentheses and press \u2018Enter\u2019 to see the result.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/transpose-google-sheets-unique-result.webp” alt=”Transpose Google Sheets with UNIQUE \u2014 Southteam, Northteam, Eastteam and Westteam returned across G1 to J1″ title_text=”Southteam, Northteam, Eastteam and Westteam returned across G1 to J1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Imagine you have a cell containing a list of items separated by semicolons. Follow the steps below to split these items into separate rows instead of columns.<\/p>\n 1.<\/strong> Type the TRANSPOSE function in an empty cell, followed by the SPLIT function.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/transpose-google-sheets-split-formula.webp” alt=”Transpose Google Sheets with SPLIT \u2014 =TRANSPOSE(SPLIT( typed in A3 below a semicolon-separated list in A1″ title_text=”=TRANSPOSE(SPLIT( typed in A3 below a semicolon-separated list in A1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> Select the cell or cells containing the data you want to split and transpose. After a comma, type the delimiter between quotation marks.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/transpose-google-sheets-split-delimiter.webp” alt=”Transpose Google Sheets with SPLIT \u2014 SPLIT formula with A1 and a semicolon delimiter typed in A3″ title_text=”SPLIT formula with A1 and a semicolon delimiter typed in A3″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Close both parentheses and press \u2018Enter\u2019 to see the result.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/transpose-google-sheets-split-result.webp” alt=”Transpose Google Sheets with SPLIT \u2014 Item 1 to Item 9 listed down column A from A3″ title_text=”Item 1 to Item 9 listed down column A from A3″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n That’s how to quickly switch your data from vertical to horizontal, or rows to columns. Looking for more time-saving tips and tricks in Google Sheets? Learn how to filter Google Sheets without affecting other users<\/a> or discover how to connect Google Sheets automatically<\/a>.<\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" In Google Sheets, if you ever need to flip the columns and rows of an array or a data range,\u00a0TRANSPOSE\u00a0Google Sheets function is the one to go with. It swaps\u00a0\u2014 or transposes \u2014 the rows and columns, changing the orientation of your spreadsheet data from horizontal to vertical or vice versa. This can be useful […]<\/p>\n","protected":false},"author":40,"featured_media":4024,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"In Google Sheets, if we ever need to flip (or transpose) the columns and rows of an array or a data range, TRANSPOSE<\/a> Google Sheets formula is the one to go with.\nHow to use TRANSPOSE Google Sheets function<\/h2>\n
How to replace the old table<\/h3>\n
\n
Transpose and keep formatting<\/h3>\n
\n
How to paste transposed data in Google Sheets<\/h2>\n
Use paste special to transpose<\/h3>\n
\n
\n
Use a keyboard shortcut to paste transposed data<\/h3>\n
Use TRANSPOSE with other functions<\/h2>\n
UNIQUE & TRANSPOSE<\/h3>\n
SPLIT & TRANSPOSE<\/h3>\n
Work more efficiently in Google Sheets<\/h2>\n
Syntax<\/h3>\nTRANSPOSE(array_or_range)<\/strong><\/span>\n\narray_or_range<\/strong><\/span> - the array or the address reference to the range whose columns and rows we need to be swapped or transposed.\n
Usage: TRANSPOSE Google Sheets formula<\/h3>\nThis is one of the simplest formulas we encounter while using the Google Sheets spreadsheet application. Let us explore its working with help of a few examples, and we will use the following sample data.\n\n
![\"TRANSPOSE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-27.png\")
\n\nThe test data spans from cells A1 through to C7. So, we will key in the formula in the cell D1 as illustrated in the snapshot below, and try to understand its behavior.\n\n![\"TRANSPOSE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-28.png\")
\n\nWe\u2019ll see the returned data flows from D1 towards right and further down.\u00a0The number of columns and rows returned by this formula is same as the rows and columns of the input array_or_range<\/strong><\/span> respectively. So, it is very important that we keep the cells, where we expect the data to flow, clear of any values. Otherwise, the formula returns #REF! error. This is demonstrated in the screenshot below.\n\n![\"TRANSPOSE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-29.png\")
\n\nGoing back to the second image, we will notice that the rows in the source range changes to columns and vice versa.\n\nWe have witnessed the working of the TRANSPOSE formula when we keyed in the address reference as its input parameter. Let us now try to input an array instead, as illustrated in the image below.\n\n![\"TRANSPOSE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-30.png\")
\n\nThe semi-colons separate each individual row values in the input range. Whereas commas separate each individual column values. Such an input is now transposed as shown in the image above. Just like in the previous case, the expected real estate for this formula should be free from any other values. Otherwise, it returns an #REF! error.\n\nImage by Freepik<\/a><\/small>","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[55,39,28],"class_list":["post-3860","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-ben-collins","tag-connections-t","tag-spreadsheets"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/3860","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=3860"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/3860\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/4024"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=3860"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=3860"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=3860"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}