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 Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories