Transposing Excel Ranges
Often, you’ll receive Excel files from other individuals and their sense of data layout isn’t quite along the same lines as yours. More often than not, data is laid out improperly, and a little “tweaking” needs to be done in order to get everything in tiptop shape.
One of the frequent mistakes include the never-ending left to right row of values. Names, numbers, or dates starting in A1 and stretching across this massive sheet all the way to cell HX1 or some such far-away, never-before-heard-of column. Using the Paste Special dialog box, there is an easy and simple way to turn that huge row into a more appropriate column of data. We’ll use a pared down example.
In this example, we have Joe in A1, Sally in B1, and Bob in C1. The objective is to have these values all in column A, instead of stretching across columns.
This is a quick and easy change! First, highlight A1 through C1 and click on Edit, and then Copy. Next, click on cell A2 (since A1 currently has a value, we’ll paste our values starting in cell A2 instead).
Now click on Edit, and then Paste Special. You’ll notice a nifty little checkbox at the bottom of this Paste Special dialog box called, “Transpose” (see Figure 2).
“Transpose” does just that. After clicking OK to get back to your spreadsheet, you’ll have a nice neat column of values: Joe in cell A2, Sally in A3, and Bob in A4. Now you can simply delete row 1 and you’re layout has most definitely changed for the better!
The Transpose feature also works the opposite way (transposing from columns to rows) and works on larger blocks of data (multiple rows and columns). It’s a great, fast way to shape up your data in order to use it more efficiently!
©2010 Blue Moose Technology, LLC
Article Publishing and Re-Print Guidelines
- The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
- When re-published online, all links must be live hyperlinks.
- These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
- These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!
You can easily link to this article!
If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:
On your page, the code will appear like this: