Convert Report Headings into List

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:

Convert headings to list

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:

  1.  Copy the row containing the headings from your Excel spreadsheet.
  2. 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.
  3. Copy the result of step 2 from Notepad into Microsoft Word.
  4. 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.
  5. 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.Find, Replace special characters in Microsoft Word
  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:

Copy horizontal to verticalYou 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.

Leave a comment

Filed under Excel, How to...

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s