{"id":3860,"date":"2017-04-13T17:24:15","date_gmt":"2017-04-13T15:24:15","guid":{"rendered":"https:\/\/www.sheetgo.com\/?p=3860"},"modified":"2017-04-13T17:24:15","modified_gmt":"2017-04-13T15:24:15","slug":"formule-de-transposition-google-sheets","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/fr\/blog\/google-sheets-formulas\/transpose-formula-google-sheets\/","title":{"rendered":"Comment utiliser la formule TRANSPOSE de Google Sheets ?"},"content":{"rendered":"\n[et_pb_section fb_built=”1″ admin_label=”section” module_class=”sheetgo-post” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]

In Google Sheets, if you ever need to flip the columns and rows of an array or a data range,\u00a0<\/span>TRANSPOSE<\/a>\u00a0<\/span>Google 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.<\/p>\n

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

Syntax<\/h3>\n

TRANSPOSE(array_or_range)<\/strong><\/span><\/p>\n

array_or_range<\/strong><\/span>\u00a0<\/span>\u2013 the array or the reference to the range of columns and rows you want to swap or transpose.<\/p>\n

 <\/p>[\/et_pb_text][et_pb_text admin_label=”Connections T – Automate beyond importrange – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”50462″ theme_builder_area=”post_content”]\"\"\n\n\"39\"\n\nAutomate data transfers beyond Importrange<\/span>\n\nFind out how<\/a>[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]

How to use TRANSPOSE Google Sheets function<\/h3>\n

This is one of the simplest formulas in Google Sheets. Take a look at the following examples:<\/p>[\/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 1″ title_text=”TRANSPOSE google sheets 1″ _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”]

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. <\/span><\/p>\n

To do this, I enter a TRANSPOSE formula into cell D1:\u00a0<\/span><\/p>[\/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 2″ title_text=”TRANSPOSE google sheets 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”]

You can see that my original data range has been inverted. The rows and colums from the input\u00a0array_or_range\u00a0<\/strong>have been swapped and the new (flipped) dataset starts in cell D1.\u00a0<\/span><\/p>\n

Remember to leave enough space in your spreadsheet for the new table of data. Otherwise, the formula returns\u00a0<\/span>#REF! error:<\/a><\/p>[\/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 3″ title_text=”TRANSPOSE google sheets 3″ _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”]

Another option is to enter an array<\/strong> instead. Here’s an example:<\/p>[\/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 4″ title_text=”TRANSPOSE google sheets 4″ _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”]

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

How to replace the old table<\/h4>\n

If you want to delete the original table of data and replace it with the transposed data<\/p>\n

    \n
  1. Copy the new table (the data you have transposed).<\/li>\n
  2. Go to Edit<\/strong> > Paste Special > Paste values only. <\/strong><\/li>\n
  3. Paste directly over the original table.<\/li>\n<\/ol>\n

    Transpose and keep formatting<\/h4>\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

      \n
    1. Select the cells you want to transpose.<\/li>\n
    2. Copy the cells.<\/li>\n
    3. Click inside the cell where you want the new table of inverted data to start.<\/li>\n
    4. Go to Edit > Paste special > Paste transposed.<\/li>\n<\/ol>\n

      Work more efficiently in Google Sheets<\/h4>\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 disover how to connect Google Sheets automatically<\/a>.<\/p>[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text admin_label=”Connections T – One tool – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” global_colors_info=”{}” global_module=”50458″ theme_builder_area=”post_content”]\"\"\n\n\"39\"\n\nOne tool to merge, split, and filter all your spreadsheet data<\/span>\n\nFind out how<\/a>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]\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":"open","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.\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\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\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\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\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":[],"_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}]}}