Occasionally you might need to convert the headings from an Microsoft Excel spreadsheet (which are horizontal) into a list (which is vertical) so you can email it to someone or include the list in documentation.
You don’t need to retype the list or even cut and paste each heading, cell by cell. My method takes a few steps, but I think it’s a lot faster and certainly more accurate. It also works with other applications than Excel. (You can also use the same method to copy a horizontal row of cell values into a vertical row (convert a row into a column), but more on that later.)
Update: Actually, I found a MUCH easier way to do it–convert rows into columns and vice versa. See these simple instructions at http://www.howtogeek.com/howto/12366/convert-a-row-to-a-column-in-excel-the-easy-way/
For example, assume you have an Excel report with these headings:
and you wanted to convert it into a list like this:
- Server
- Application
- Database
- Location
- IP address
- Critical?
Here’s how I convert the headings into a list:
- Copy the row containing the headings from your Excel spreadsheet.
- Paste them into Notepad, which resides on every Windows OS.
This step removes the cells and leaves you with just text that is tab delimited. - Copy the result of step 2 from Notepad into Microsoft Word.
- Turn on all formatting marks in Word by clicking the Show/Hide formatting marks button (
).
You dont’ have to do this step, it just helps you see how the next 2 steps work. - Open Word’s Replace function and type the info shown below ( ^t for tab and ^p for paragraph) to replace the tabs with paragraph marks.
To type the upper carat symbol (^), hold SHIFT and press 6.
- Click the Replace All button.
That’s it. You might want to change the paragraph spacing, but now you can copy your list to email or wherever you need it. With smaller files, retyping may be easier than doing this, but when you have a spreadsheet with many columns/headings in it, it’s easier to copy and paste (with less chance of error).
I’ll explain how to replace other special characters besides the tab and paragraph in a future post.
Copy Rows into Columns
You can use the same method above to copy/convert rows into columns. For example, assume you want to change the spreadsheet shown above to what’s shown below:
You can copy all the rows and columns and paste the entire block of data into Notepad, then Word, and then replace tabs with paragraph marks, or you can do it row by row. Either way, you still need to copy and paste each row of data from Word (now in vertical “list” format) one at a time into your column in Excel. But it beats retyping!
P.S. Remember to check the results. I noticed I lost the “?” after Critical in cell A11.
ITauditSecurity 