Solved – the problem with Excel Tables and Transpose


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

There are several ways for an Excel table to be transposed (rows and columns) but none of the online suggestions work very well with tables.  We’ve found a solution which transposes one table into a ‘swapped’ table i.e headers become column A and column A becomes the headers.

Along the way we’ll explain a clever use of Power Query, how to run VBA code when certain cells are changed and how to refresh all or a single query from VBA.  If you’ve not used PowerQuery, this is a simple, useful and self-contained starter.

This is what we’re trying to do. At top is a list of exam results from the town of Bedrock and a page right out of history.

solved the problem with excel tables and transpose microsoft office 32919 - Solved – the problem with Excel Tables and Transpose

The bottom table is transposed so we can filter / sort by students instead of subjects.

What’s needed is a way to take a source table and make a transposed version which is also a table AND updates whenever the source changes. There are many times this kind of transposed table is useful yet it’s not truly and properly supported even in modern Excel.

The transpose solutions that aren’t good enough

Microsoft among many offers two Transpose methods, neither are truly what’s needed.

Paste | Paste Special Transpose

is OK but is a ‘once-only’ action not suitable for changing source data. And it doesn’t work for whole tables.

solved the problem with excel tables and transpose microsoft office 32920 - Solved – the problem with Excel Tables and Transpose

Transpose()

The Transpose() function would seem ideal because it works from a source Excel table.

BUT, the result isn’t an Excel table and can’t be converted into a table (because Transpose() returns a dynamic array).

Transpose an Excel table into another Excel table

The trick is to use Power Query which has its own Transpose function which, with a little tweak, will do what we need AND drop a full Excel table back into the worksheet.

Sadly the updating from changed source data isn’t automatic but there’s a VBA fix for that if you need real-time updating.

Select the source table then Data | Get & Transform Data | from Table

solved the problem with excel tables and transpose microsoft office 32921 - Solved – the problem with Excel Tables and Transpose

That imports the current table into the Power Query editor.

solved the problem with excel tables and transpose microsoft office 32922 - Solved – the problem with Excel Tables and Transpose

Power Query has a Transform | Transpose option but it will remove the existing headers (not move them to rows).  Before Transposing, convert the headers into a normal table row (this is the ‘little tweak’ we mentioned.

Go to Transform | Use First Row as Headers | Use Headers as First Row

solved the problem with excel tables and transpose microsoft office 32923 - Solved – the problem with Excel Tables and Transpose

Now the table headings are in row 1.

solved the problem with excel tables and transpose microsoft office 32924 - Solved – the problem with Excel Tables and Transpose

Choose Transform | Transpose to swap the rows and columns.

solved the problem with excel tables and transpose microsoft office 32925 - Solved – the problem with Excel Tables and Transpose

Finally use Transform | Use First Row as Headers to make the new top row into headers.

solved the problem with excel tables and transpose microsoft office 32926 - Solved – the problem with Excel Tables and Transpose

One of the many great things about Power Query is that it’s all recorded as a series of steps that are repeated each time the data is refreshed.

solved the problem with excel tables and transpose microsoft office 32927 - Solved – the problem with Excel Tables and Transpose

Close and Load the query into your worksheet.  By default, the new transposed table is dropped into a new sheet.  We moved it to the same sheet as the original table.

solved the problem with excel tables and transpose microsoft office 32928 - Solved – the problem with Excel Tables and Transpose

Two tables, the original (top) and the transposed (bottom) each with its own table filters and other Excel table goodies.

Make sure you only edit the original/source table.  Any changes to the second table will be overwritten when the data is next refreshed.

Delinking the tables for fixed data

If your source data is fixed (like exam results) then you can just delete the query. Its transpose trickery won’t be needed again.  Right-click on the query in the Queries and Connections pane then Delete.

solved the problem with excel tables and transpose microsoft office 32929 - Solved – the problem with Excel Tables and Transpose

The second transposed table will remain but delinked from the original table.

Updating the query and table

Let’s suppose the original table has values that will change over time. Maybe Fred & Barney will complain that their wives did better?

There are a few options for updating the transposed table after the original table changes.  Which one you choose depends on the situation.

Each query has some update options, right-click on a query then Properties | Usage.

solved the problem with excel tables and transpose microsoft office 32930 - Solved – the problem with Excel Tables and Transpose

Refresh every … minutes might be enough but it still means the two tables could be out of sync.

Refresh data when opening the file should be ON.

Refresh this connection on Refresh All should be ON.

None of these are good options because there will always be a time delay between changing the original table and the transposed table updated.

The real-time updating fix

The better fix is to force a query update whenever a cell in the source table is changed.  For that we turn to VBA and one of its event handlers.

Excel VBA has an automatic trigger or event when there’s a change in a worksheet: Worksheet_Change(ByVal Target As Range) in that function you can add code to only work when certain cells in the worksheet are changed.   The basic function looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim CellsChanged As Range

' CellsChanged is the cells that will trigger an action if changed.
‘ change to whatever range applies to the source table
    Set CellsChanged = Range("A1:E5")

If Not Application.Intersect(CellsChanged, Range(Target.Address)) Is Nothing Then

   ‘ do whatever you want here

End If  ‘ change in one of the CellsChanged range

End Sub

What we want to do is refresh the query which is simply this line:

ActiveWorkbook.RefreshAll

That’s the VBA equivalent of pressing Data | Refresh All.

Update a single query in VBA

Most workbooks will stick with RefreshAll but if there are a lot of queries in the workbook you might not want to refresh them all, to update a single query use:

ActiveWorkbook.Connections("Name_just_the_query").Refresh

NOTE: the query name is NOT the name that appears in PowerQuery and the Power Query editor, for VBA it must be prefixed with ‘Query – ‘.  For example this query ‘ExamResults-transposed’

solved the problem with excel tables and transpose microsoft office 32931 - Solved – the problem with Excel Tables and Transpose

In VBA is called ‘Query – ExamResults-transposed’ as in:

ActiveWorkbook.Connections("Query - ExamResults-transposed").Refresh

No, we don’t understand it either, but that’s what works!

Full VBA update query on change code

The Worksheet_change function goes in the VBA for the sheet with the original / source table.

solved the problem with excel tables and transpose microsoft office 32932 - Solved – the problem with Excel Tables and Transpose

As a final little tweak the Range() is now the name of the source table, not a fixed cell reference.  That lets the code work if the table is resized.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim CellsChanged As Range

' CellsChanged is the cells that will trigger an action if changed.
' change to name of the source table

    Set CellsChanged = Range("ExamResults")

If Not Application.Intersect(CellsChanged, Range(Target.Address)) Is Nothing Then

   ActiveWorkbook.RefreshAll
 
   ' if a single query update is preferred, change this line
   ' ActiveWorkbook.Connections("Query - ExamResults-transposed").Refresh
   ' remember to prefix query name with 'Query - '

End If  ' change in one of the CellsChanged range

End Sub

subs profile e1563205311409 - Solved – the problem with Excel Tables and Transpose
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address