join

How to merge multiple columns into a single column using Microsoft Excel



Sharing buttons:

hello and welcome to strictly virtual

today's tutorial is merging data from

multiple columns into a single column in

Microsoft Excel now Microsoft Excel

comes with a merge cells feature but the

problem with this is it only retains the

data in the leftmost cell that you merge

so if you're merging two or more cells

with data in them then you'll lose the

data from all of the cells apart from

the leftmost cell so it's not much use

if you want to retain that data so we're

going to be using the concatenate

function today so we've got two columns

of data column a with first names and

column B with surnames and we're going

to merge these fields into column C so

first thing to do is to highlight a cell

c2 c2 because we don't want to merge the

header row so as I said highlight cell

c2 and then click up in your formula bar

type in equals and then concatenate you

then need an opening bracket and then

we'll select our first set of data which

is a2 so you can either type in a2 or

just click on it then you want comma and

then click on b2 and a closing bracket

and hit enter so you can see now that

our data is merged into column C however

as it's a name we need a space in

between the first name and the surname

so hit back back on up to your formula

bar and click in just after the comma

and type in quotation space quotation

comma and hit Enter

and now you can see our data is nicely

formatted with a space in between so if

you want to apply that to all of your

rows then just hover over the bottom

right corner of the cell until you get

the plus sign and left click and drag it

down so that's pretty much how you do

that but what about if you've got data

in more than two columns so let's just

get rid of this here and we'll add

another column and we'll call it age and

we'll just type in some ages here so

we're going to now merge this data into

column D but we'd like quite nice

formatting so we're going to add in some

spacing and a hyphen between the name

and the age so head back 1 up to your

formula bar and again type in the equal

sign and concatenate opening bracket and

select your first cell again a2 and

comma and then we want to add our

spacing straightaway so quotation space

quotation comma and then b2 comma now

for our age we're going to add a space

before the - so it's quotation space

quotation comma and then quotation -

quotation comma and then we're going to

stick a space afterwards as well so

quotation space quotation comma and then

we just need our age column so click on

cell c2 to add that in closing bracket

and hit enter

so now we've got outdated with the aged

nicely formatted and again we can just

hover over the bottom right corner and

drag this down to get all of our rows so

that's pretty much it just one word of

warning that this data now is dependent

on the first three columns so if you

change anything in these columns then

your data is going to change too so the

way to get around that if you want to

keep that dejan it not be dependent on

those columns is a simple copy and paste

so just select your cells hit copy and

decide where you want to paste them can

be anywhere same worksheet different

worksheet or just the next column and

then hit paste and paste values so now

we've got our data there but it's no

longer dependent on the previous column

so if we change that age to 98 you'll

see that the first column of data column

D will change because it's dependent on

the first cells column e will not change

because it's standalone so that's

basically it hope you enjoyed the

tutorial for more tips tricks and how-to

head over to the blog on our website at

www.kppc.org/keeps