We work with data — LOTS of it. But all too often before we can start the analysis, these data must first find their way into Microsoft Excel to be cleaned up, rearranged or reformatted.
As such I wanted to pass along a few time-saving tips I use to manipulate data in Excel and get it ready for analysis. This will be part one of a three part series on working with your data in Excel.
TRANSPOSE: How to switch data from rows to columns in two easy steps.
After importing new data into Excel, you may be annoyed to find the information that you expected to be in columns is instead spread out over several rows. Indeed, many times throughout our process I find myself needing to re-orient spreadsheet data from a horizontal to a vertical axis. While copy and paste works well for small transfers, a much more elegant solution is to use Excel’s ‘Transpose’ feature. (click on any of the pictures to enlarge)
1) First, select the data you wish to re-orient and copy it to your clipboard.
2) Now, select a cell where you want to put the data. Be sure the cell is empty and there is no overlap with your previous selection. Select Paste Special (right-click), and be sure to check the Transpose box before clicking OK.
Alternatively, you can click the Paste icon from the Home menu and select Transpose from the dropdown menu.
And presto! Your columns have now become rows (or vice-versa).
Be on the lookout for the second part of this series, where we’ll be looking at several ways to manipulate your cell data to structure it just how you need it for analysis. If you have any questions on this or any other text analytics topic, drop me a line at Luis@ideaworks.com.
