SS1 THIRD TOPIC

COMBINING DATA IN MICROSOFT EXCEL

It's a very common situation. You have a worksheet with different pieces of data in different columns or rows that you need to put together into a single cell. Perhaps you have a list of people whose first, middle and last names are all sitting in different columns. Maybe you have a set of addresses where the street, city and postal code are in adjacent columns but you need to put them together for generating a mailing list. 

Fortunately, Excel 2016 (and all prior versions) make it very easy to grab data from many different cells and put it into one. Just don't confuse this "combining" with "merging," another Excel procedure which causes one cell to span across multiple columns, but only uses the content from that single cell. To combine cells in Excel:

1. Select a location where you want the new, combined cell(s) to appear. Most likely, you'll want to pick a new column that's to the right of your other cells, but you don't have to.

2. Type =B2&C2 into the formula bar where B2 and C2 are the addresses of the cells whose data you want to combine (it could be any two cells). You can use an unlimited number of ampersands to add an unlimited number of cells.

3. Include spaces between cells by adding &" " to the formula. Anything within the quotes will be rendered so it could be a space, a comma or any text you want.

4. Hit Enter to see how it renders

Copy and paste your formula to reproduce the results in other rows.

MERGING DATA IN EXCEL

Ever created an Excel worksheet and wish that one or more of your cells could span across two or more columns? Excel 2016 and previous recent versions have a "merge" feature which lets you span a cell across multiple columns. So, for example, if you have a table and you want to have a centered header row that cuts across the entire space, you can do that with the merge feature.

However, it's important to note that the merge cells feature only causes a single cell to span across multiple columns; it does not combine the content from adjacent cells. So when you merge a series of cells, make sure that all but the leftmost are empty, because the content in any other selected cells will disappear (hitting CTRL + Z to undo will get it back). Here's how to merge cells in Excel 2016; these steps should also work in previous versions such as Excel 2013 and Excel 2010.

1. Select the cells that you want to merge. You select multiple cells in Excel by holding down the mouse button and dragging the cursor across columns or rows. 

2. Navigate to the Home tab in the office ribbon, if you aren't already there.

3. Select Merge & Center or Merge Across (if you want the text left aligned) from the Merge & Center menu.

Your single cell will now take up two, three or more. If you change your mind,  you can hit CTRL + Z to undo right away or select Unmerge cells from the Merge menu later.