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.
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.
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.
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.
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.
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
- 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.
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
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.
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.