Unpivot data in Excel using Power Query

Sometimes data is perfectly shaped and structured — other times, it’s not.

If you’ve used pivot tables, you’ll be familiar with the cross-tabular format, which summarises data based on two or more categorical variables.

While this layout is great for presentational purposes, it isn’t conducive to in-depth analysis.

However, unpivoting the data converts it into tabular format, where multiple column headers sit in a single column, each captured as a different row.

In the example, a grid displays the fixture dates and results of Premier League football matches, with the home teams displayed vertically and the away ones horizontally. Each home side has 19 games — plus the extra non-existent one against themselves.

How do we unpivot this? Follow these steps:

1. On the Data tab, select From Web.
2. Input the URL https://www.footballwebpages.co.uk/premier-league/match-grid and select OK.
3. Select Table 0 and then Transform Data.
4. Right-click on the first column’s header containing the home teams and select Unpivot Other Columns.
5. On the Home tab, select Close & Load to dump the data into a new worksheet.

Done! Now, there are three columns. Each home team appears in 20 consecutive rows within the first, alongside the away teams in the second and the fixture dates and results in the third. The total number of rows is 401 (including the headers).
 
Adapt these steps for your cross-tabular data. No need to faff about dragging cell ranges around or writing long windy formulas. All you need is trusty Power Query.