statrefs home‎ > ‎Main‎ > ‎Software‎ > ‎Microsoft Excel‎ > ‎

Excel Tricks


 




How to change an Excel table from ‘wide’ format to ‘long’ format without needing VBA or the use of formulas ...

From:  http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26549515.html


- Click anywhere within the data table
- hit the key combination Alt - d - p to bring up the Pivot Table Wizard (which is not on the ribbon)
- select "Multiple consolidation ranges" and click Next
- select "I will create the page fields" and click Next
- select your data table (in the example it's A1:E11)  and click Next
- select the location and click Next

Now you will see a pivot table that looks just like your data table, but we're not finished.
- click the Pivot table and in the field list uncheck Row and Column (and uncheck any Page Field, if present)
- now select the cell with the single remaining total number and double click it. 

The result is a new sheet with your data laid out one item per row.



 





 





Comments