Excel Transpose Methods Then and Now

Microsoft Excel programmers have long used the PasteSpecial Transpose method to Transpose (Pivot) their data.  While very useful, it is a manual process and such it does take more effort than desired. But that was then, this is now, and transposing Excel data just got much easier with the Transpose Function.

 

=TRANSPOSE(SORT(UNIQUE(tblEployeeData3[Region]),1,1))

 

Microsoft Upgrades Excel Office 365 Engine:  Recently Microsoft released a series of new Dynamic Array Functions in Office 365, aka Dynamic Array Spill Functions.  For those not in the know, Excel Spill Functions are amazingly powerful, yet easy to program.

Now More like Queries than Functions:  One cell, one function.  These newer Spill Functions are more like Access Select Queries than they are Excel formulas,  forever changing how Excel consultants program Microsoft Excel.

The Transpose function is one of the new Dynamic Array Functions, these Spill Functions simplify Excel programming and are available in Office 365.

 

 

As Professional Excel Consultants We Welcome the Change

These “query-like” functions are changing the way Excel consultants program Microsoft Excel.  Our firm Excel and Access, LLC has changed the way that we approach Excel programming, due to the Spill Array Functions’ power and ease of use.

 

These Spill Functions now take center stage in our design process.

 

As a result of the updates to Office 365, Transposing data in Excel just got easier to do, and with new powerful possibilities.  For example, you can now use the Transpose Function with other functions such as Sort, Unique or Filter.  Amazingly powerful; Amazingly easy-to-use.

 

Transposing Excel Data Then and Now - this makes Excel easy to use.


If Microsoft Excel is not easy to use, you are doing it wrong.

 

In our opinion: the way developers used to transpose data in Microsoft Excel is no longer necessary, or even adequate, in most situations.  Using the “PasteSpecial Transpose” Method is largely a thing of the past unless you need to Transpose merged-cells or non-rectangular ranges.   But in most cases, the Transpose Function Method is going to be the preferred method for most experienced Excel experts.

 

Excel Expert’s Note:  Our team of Excel consultants have changed the way that they program our clients’ Excel files due to the Spill Functions being released. We are big into Automation and these new Spill Array Functions are a huge game changer, if you ask our opinion.  More like writing a database-query than an Excel formula.

 

 

 

Don’t Be Afraid of Spill Array Functions

Typically, people have avoided the typical Array Functions because they worked differently than standard Excel formulas, like the VLookup.  But these new Dynamic Array Spill Functions are not like that; they are more like Excel formulas, so don’t be intimidated, you can do this.

 

Dynamic Arrays just means that the output result of the Function will grow and shrink as needed (Spill). So no fear, you can do this.

 

Experts Note on Arrays:  You no longer need to: type an opening brace ({) and a closing brace (}) around the array, and press Ctrl + Shift + Enter to indicate that this is an array formula. Instead, type =Transpose(yourdatarange) and hit enter.

 

Transposing Excel Data Then and Now - Here is the arguments for the Unique Array Function.


While they call it an Array, you do not program it like a typical Array. This you program like a one-parameter function, like Sum. So do not be intimated, these are super easy to program.

Forget about Ctrl + Shift + Enter.

 

 

 

Why do Programmers Transpose Data in Excel

The need to transpose data in Excel is very common.  Sometimes that data you have organized in rows, needs to be in columns, or visa versa.  So in these situations, the Excel programmer will usually use the PasteSpecial Transpose method to get the data how they want it.

It is so common that Microsoft created a new Spill Array function that allows you to dynamically transpose data, making it even easier.

 

What exactly is Transposing:   Transposing Excel data allows you to quickly take rows of data, flip it onto its side, basically pivoting the data, and thus creating columns of data.   This allows you to change the shape of your dataset. Which in turn allows you to use specific functions, like the XLookup or SumIfs Functions.

Note:  The Excel Transpose Function is very similar to an Access Crosstab Query.

 

Transposing Excel Data Then and Now - Here we combine three Spill Functions, Transpose, Sort, and Unique.


By combining two or more Spill functions, you can get more out of Excel, with fewer formulas.

 

Experts Note: Transposing data is much like using a Crosstab Query in Microsoft Access.  As we have been saying since the release of the Spill Array Functions, Excel is becoming more Database-like.

 

 

 

How do Programmers Transpose Data in Excel

Take your pick:  There are 5 or more common ways to Transpose data in Microsoft Excel.  Here we briefly mention a few of them.   Which method you use depends on your needs.  In most situations, several methods should work, as in all things Excel.

We recommend that you at least know Method 1, 2 and 5.  In older versions of Excel, for backwards compatibility, the new Transpose Function will not work; so you will need to know the Paste Special Method.  Just as you still need to know the VLookup, even though the XLookup replaced it.

  1. Historically, when you transposed data in Excel, you would PasteSpecial Transpose Method, often as Values.
  2. Now with the release of the Transpose Spill Function Method you write a single function, in a single cell, and presto, the data has been pivoted.
  3. Alternatively, you could write a series of formulas to pivot the data.
  4. You could use direct cell references.
  5. You can use VBA to Transpose your data.
  6. You can manually transpose your data.

 

 

 

 

How do Programmers PasteSpecial Transpose in Excel

Excel Transpose Methods Then and Now:

PasteSpecial Transpose is the classic goto, and Transpose Spill is new school programming.  But there are basically 5 common ways to Transpose data in Excel, each one easier than the others, and the Spill function is the easiest way to do it.

For the longest time when someone wanted to transpose their Excel data, they did it with PasteSpecial Function.  It was a manual method, it was quick, powerful, many of options, and it worked.  You could also include it in your vba coding.

But you could not place the results into another formula.  So it had its limitations, as this is a major disadvantage when compared with the Spill function method.

 

Here is how you copy a column of data and turn it into a row of data using Paste Special Tranpose:

  1. Select the range to copy.
  2. Copy the Range.
  3. Select the destination cell.
  4. Paste Special Transpose via mouse or keyboard (Keyboard Shortcuts below).
    1. Copy/Cut.
    2. Alt
    3. E
    4. S
    5. E
    6. Enter/Ok.
  • You can do this in a macro, you can paste values or formulas or links.  PasteSpecial has many options.

 

Paste Special Transpose is an older method to pivot your Excel data.


This is how people Transposed Data in Excel for the longest time, using the Paste Special Transpose method, possibly in vba.
This method works very well; but you cannot use this method within Excel Functions.

 

 

 

 

How do Programmers Transpose Spill Function in Excel

Using the Transpose Function takes a few seconds to write.  You can combine it with other functions, such as Sort or Filter. It is literally as easy to use as the Sum Function.  Simply select the data to be Transposed inside the simple formula.  Done.

=Transpose(SalesData)

 

How Easy is it to Pivot Data using Transpose Array Function.

  1. Simply write the function, in one cell.
  2. If needed, use with other Spill Functions such as Sort or Unique.

 

Transposing Excel Data Then and Now - much has changed. It got easier.


Apply Unique with Transpose against an Excel Table, to get a list of Regions. You can use that list in a Validation Control as a Dynamic Source.

 

 

 

Differences Between Transpose Methods in Excel

Just as with all things Excel, there are many ways to skin the cat.  Transposing Excel data is no different.  But up to now, when it comes to transposing data, the PasteSpecial approach has been the most used.

If Microsoft did not release the Transpose Function, there would be no need to change methods, but wow, this Spill Function is way better in our opinion.

 

Not the Same:  Unlike using SumIf or DSum, which get the same end results, and which are similar to use, the two Transpose methods are not similar, not even close.  One is a Dynamic Array Function, the other is a series of Keystrokes (Copy/Cut, PasteSpecial Transpose).

Other Differences:  One can be used with other formulas and functions.  One works with non-rectangular data.

Our Advice:  While we strongly recommend that Excel programmers use the Transpose Function Method, there are situations when the PasteSpecial Transpose Method will be the optimal approach.  So make sure to know how to do both, the best practices of each.  And when the time comes to use the time proven PasteSpecial method, you will know.

 

Experts Note:  Now you can use a super simple to use formula to pivot your Excel data.

 

 

 

 

 

How do Programmers use the Transpose Function in a Business Setting

Super simple example:  Here is an example of why businesses need to transpose data.  Let’s say you have an Excel file, and you have 12 months of data, vertically in an Excel Sales Table.  In one of the columns of the Excel Table you have the name of the month.  What if you want to use those 12 labels (January, February, … December) as headers for a 12-month trending report?

Sure, you can type them, or you can Transpose them. The Transpose Function returns a Dynamic Data Set.

When you need to Pivot your Excel data, you can use the Transpose Spill Function to quickly pivot the cells into the desired format.

Excel Programmers Advice:  In this example, you have multiple years in the file, so you will have repeated months.   So in this case you would combine the Unique Function with the Transpose Function, to give you a clean unique list that you can use. You would not want to use Sort or Filter with this.

This is a perfect example of how powerful and useful these new Spill Array functions are. You can see why our Excel consultants use them daily.

 

Transposing Excel Data Then and Now - The New Excel Spill Array Function is worth knowing.


Here we combined the Transpose Function with the Unique Function. If this was to get the Unique Region, you would add the Sort Function. All written in seconds, in one cell, fully dynamic.

Look at just how simple this is, anyone can write this: =TRANSPOSE(UNIQUE(tblEployeeData3[Column1]))

 

 

 

 

Transpose Function versus PasteSpecial

The PasteSpecial Transpose Method works great, it has its benefits, it has been used for years by thousands of Excel programmers across the globe.  And for good reason, it works well, and it is quick and easy to do.  You should know how to do this.

That said, it is not as powerful as the new Transpose Spill Function Method. So in our opinion, you should use the Spill Function when working in Office 365.  For older versions of Excel, use the Paste Special Transpose method.

Pivoting data in Excel just got much much easier.  And fine with us, because Excel should be easy to use.

 

Benefits of using Transpose Spill Function:

  1. When your data changes, grows, or shrinks, the Dynamic Array Transpose Function accounts for that.
    1. Hence, these are Dynamic Array Functions. Your data is live, and dynamic.
      1. Just leave plenty of room, to avoid getting that spill error.
  2. The Transpose Function can be combined with other Excel functions. This is where the real power comes in.
    1. Such as Sort, Unique, Filter, ChooseCols, VStack, etc.
  3. Saves time and reduces errors.
    1. With the Transpose Spill Function, you don’t need to manually copy and paste the transposed data.

=TRANSPOSE(UNIQUE(tblSalesData[NetSales]))

 

 

 

 

Conclusion – Excel Transpose Methods Then and Now

Microsoft has been making significant changes to Microsoft Excel in Office 365 in recent years.  They rebuilt the calculation engine, and that really altered what Excel can do.  Since then they released a series of Dynamic Array Functions, aka Spill Functions, and we talked about a few of them here.

The Dynamic Array Transpose Spill Function is a game changer, and it changes how Excel consultants’ program in Excel.  Long gone are the days of having to use the manual Paste Special Transpose Method.  Now one dynamic array function will do the trick.

Of course, if needed, the old standby method will do the trick, for non-rectangular data and such.  Else, use the new Transpose Spill Array Function.

 

Final Comparison of the Transpose Methods

There are at least 5 different ways to transpose data in Excel.  The Transpose Array Function and the Paste Special Transpose Method are two of them, each with its advantages and disadvantages.

The Transpose Function can be used to transpose a range of cells, either to convert rows into columns or columns into rows. It is a built-in Dynamic Array Function in Excel that offers a simple and quick way to transpose small ranges of data.

Although the Transpose function cannot transpose non-rectangular ranges of cells or merged cells, it can still be used in formulas and calculations just like any other function.  This is a major advantage.

Alternatively, the Paste Special Transpose Method will help you transpose a range of cells that have been copied or cut to the clipboard. It can transpose rectangular ranges of data, and unlike the Transpose function, it can be used with merged cells and non-rectangular ranges.

The downside is that it can’t be used in formulas and calculations, plus you must manually copy and paste the data.

 

Our Expert Advice:  If you need to transpose a small range of cells and use the transposed data in formulas and calculations, use the Transpose Array Function. If you need a larger range of cells with non-rectangular ranges or merged cells, use the Paste Special Transpose method.

Just remember, the Paste Special Transpose won’t be able to be used in formulas and calculations, a major drawback.

 

Get Help Transposing Excel Data 877-392-3539

 

If you need help with this function, or anything Excel, please contact us to discuss.

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

 

 

 

 

Other Posts on how to use the new Excel Spill Array Functions for Business.

 


Check out our blog, we actually write our own content.

Microsoft Excel Spill Array Functions for Business – what to do with them.

  1. Related Post:  Using ChooseCols Function in Excel Reports – Excel Reporting Just Got Easier.

2. Related Post:  Using VStack Function to Consolidate Excel Data

3. Related Post:  Using Unique Function in Validation Controls – Users Love Drop Down Lists

4. Related Post:

 

 

 

 

 

 

 

 

 

 

 

 

 

Additional Resource on Transposing Data in Excel

Trump Excel has a GREAT video on this, on YouTube.  We recommend that you check it out.  Check out his XLookup video too, that is a really good one, where he covers aspects of using the XLookup, that competitor videos do not.  Love this guy.