Change Single Column List to 2 Column Table

Change Single Column List to 2 Column Table

On the weekend, I copied a list of blog names and URLs into Excel. On the website that I copied it from, the list was in 2 columns, but it pasted into a single column in Excel. Having everything mixed together in one column wasn’t going to be very helpful. Here’s what I did to change single column list to 2 column table.

Add Headings and IDs

This screen shot shows the list, in a single column.

sortalternaterows09

I wanted the site names in one column, and the URLs in another. If I labeled all the site names as “a”, and the URLs as “b”, I’d be able to sort them into two groups.

  • First, I put a heading at the top of the list, and an “ID” heading for the column to the left.
  • Then, I typed “a” and “b” as the IDs for the first two rows.

NOTE: if your data is in groups of three or more rows, give a unique letter ID to each item in the first group.

sortalternaterows01

Fill the ID Column

To label the remaining rows, fill the IDs down:

  • Select the first two IDs
  • Point to the Fill Handle – the small black square at the bottom left of the selected range
  • When the pointer changes to a black plus sign, double-click
  • The IDs will fill down, stopping above the first blank row.

sortalternaterows02

Sort by ID

  • Next, right-click a cell in the ID column
  • Click Sort, then Sort A to Z

This will group the data by type – all the site names are listed first, followed by all the URLs.

sortalternaterows03

Move the URLs

To put the data side-by-side:

  • Select all the URL cells
  • Drag them up to the column to the right of the site names. Be sure to drop them beside the first row of data.

sortalternaterows04

Create a Formatted Table

Now that all the data is in the correct place, the final steps will change the two column list into a formatted table.

  • Delete the ID column, and add a heading to each column

sortalternaterows05

  • Select a cell in the table, and on the Home tab of the Ribbon, click Format as Table.
  • Click on a Table style, to create the table.

sortalternaterows06

When the Format as Table dialog box appears, check the data range, and correct it, if necessary

Add a check mark to “My table has headers”, and click OK

sortalternaterows07

Now the table is nicely formatted, and you can add new items to the bottom of the list, and the table will expand automatically to include them. You can read more about Excel tables on my website.

sortalternaterows08

Other Ways to Fix the List

If you have a different solution, please let me know in the comments. Maybe there’s an even easier way to do this.
__________________