Excel: Get & Transform Data - Unpivot Columns

Posted on:  09/21/2019
Excel: Get & Transform Data - Unpivot Columns

In Excel, use the Power Query Editor (Get and Transform data) to unpivot columns and transform your data. If your data is set up cross tabular, you can't sort, filter, subtotal, or create PivotTables. To transform your data, use Power Query Editor. It changes your columns into rows. It is located in Excel 2016 with the Get & Transform Data group on the Data tab. It is also available in Excel 2019, Excel 2013, and Excel 2010. The Microsoft support articles and a video are below.

Screenshot 1 - Crosstab data - this is not correct

With the data below, I can't sort or filter. I can't make a PivotTable. I see data in this format all the time. I used Get & Transform to change it quickly, so the data is correctly displayed as three columns.

Screenshot 2 - Data after it has been transformed

Now that I've used Get & Transform to unpivot the columns, I can sort, filter, and create a PivotTable. This data is known as tabular data.

Example: In screenshot 1 above, in row 1, starting in cell B1 and running horizontally across are months, but this could be employee's names, countries, or states. In A1 is a category that is correctly set up. This is called cross tab data.

The correct method would be to have products in column A. The data starting in B1 needs to be labeled and then run down column B, not across. Column C would have the values. This shows in screenshot 2 above.

Other Excel articles

Steps to Unpivot columns with Get &Transform

  1. Click inside your data.
  2. Click the Data tab.
  3. In the Get & Transform data group, click From Table/Range.
  4. Click Yes to transform into a table and the Power Query Editor automatically opens.
  5. Right-click and Unpivot other columns.
  6. Change the data types, and rename columns if necessary. 
  7. Click Close & Load, then click Close & Load To...
  8. Make a table in a new workhsheet.
  9. Click OK.

YouTube video of Get and Transform - Unpivot columns

Microsoft Support Articles

  1. Unpivot columns - https://support.office.com/en-us/article/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098
  2. Getting started with Power Query - https://support.office.com/en-us/article/getting-started-with-power-query-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a

Latest YouTube videos

  1. XLOOKUP - Excel's new awesome function https://youtu.be/1sSje27jnTA
  2. OneNote - delete a OneNote notebook https://youtu.be/iCSybD_XKjY
  3. Dots in your Gmail address don't matter https://youtu.be/2FolZ1Q9bYw
  4. SUMIFS Function in Excel https://youtu.be/qrYim5WZhqk
  5. Word's new Rewrite feature https://youtu.be/UQ2yul_4GAI
Chris Menard

Chris Menard is a Senior Training Specialist at SurePoint Technologies. Chris is certified in Excel, Word, PowerPoint, and Outlook. Menard has a YouTube channel with other 600 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 7 million viewers have very appreciated. Because of Chris's certification and expertise with Microsoft, Chris is a proud member of Microsoft's Creator Team. Being a member of Microsoft's Creator Teams means many of his videos are available on Microsoft 365 YouTube channel and Microsoft support websites.

Categories