Written by Allen Wyatt (last updated October 7, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
You may already know that you can transpose information by using the Paste Special abilities of Excel. The information that is pasted is not dynamic, however. In some instances it might be nice to have the transposed information always reflect what is in the original, un-transposed information.
Here's a cool way to transpose information and have it linked to the original information:
That's it. You've now got a dynamic transposed table, and any changes you make in the original are automatically reflected in the transposed version.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12614) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Transposing and Linking.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Circular references occur when a formula includes a reference to the cell in which the formula appears. Here's how you ...
Discover MoreWhen you need to pull information from a lot of different worksheets into a single worksheet, it can be baffling to ...
Discover MoreWhen processing some text data, you may need to perform some esoteric function, such as adding dashes between letters. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-10-08 10:21:57
J. Woolley
If you have Excel 2021 or newer (Excel 2021+) with support for dynamic arrays, you probably already know it is unnecessary to pre-select blank cells as described in step 2 or use Ctrl+Shift+Enter (CSE) as described in step 4.
My Excel Toolbox's SpillArray function (described in UseSpillArray.pdf) simulates a dynamic array in older versions of Excel.
See https://sites.google.com/view/MyExcelToolbox
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments