You might come across cases where you need to combine the values of two or more cells into one cell.
A common way of doing this is to use the "ampersand" in between your cell references.
In this example we have the item name and we have to type.
So if it's men or women and let's say we want to combine this two information in one single cell.
Start off with the equal sign, let's reference A5. To connect the result of this cell with this cell
we need to use the "ampersand". And now click on the other cell reference, press enter and we get "ScarfM".
Let's send this down.
Now what if we wanted to add a space in between.
So we don't want the M to stick to scarf.
You want to have a single space in between these.
So here again we're going gonna use the formula reference, "ampersand".
Now we want to add a space and a space is a text character that we're typing in the formula.
So we have to put it in quotation marks.
Here's another Excel essential, whenever you combine text with cell references you need to put the text
in quotation marks
and you need a double quotation mark.
So I'm going to put the quotation mark again and I want a space in between these and space is text.
So I'm just going to add a space character here.
Now after this, I want to combine this result with this cell. I need to use the "ampersand" again and reference
that cell and press enter.
We get "scarf M" followed by space character followed by our M.
Let's send this down.
Now what if I wanted to add a symbol in there so let's say I just want it at the dash sign in between
I'm going to do the same.
So we do scarf.
Now can I just put in the dash sign like this.
No, because it's text, right? I need to start with quotation marks.
Anytime you input text in your formula box you need to add a quotation mark in there.
So let's add dash, quotation mark and I reference this cell, press enter and send this down.
So here's another challenge.
We have the ability to select year from here and we want to create new titles for these.
We want to have price dash and the year that's selected, volume dash and the year that's selected.
So let's see how we can write this formula.
First is prize, that's this one.
Now I'm planning to pull this to the right.
Do I need to fix anything here?
I don't need to fix the cell reference because I want it to be relative.
Now I'm going to add the "&"
Let's say we want to follow this with a dash.
We don't want the year to stick to the price.
So I'm going to add quotation marks dash in other quotation mark.
What do I need now?
Can I just click on this cell.
No.
Right.
Because I'm going to leave that formula.
I need the "&" before I reference that cell.
Now I'm going to click on this cell and before I press enter how should I fix this?
I need to fully fix this because when I pull my formula across I don't want that reference to
shift. Press enter.
Now pull this, everything updates properly.
Let's change the year, the results update accordingly.
Okay.
So we learned two important concepts here.
We can use the "ampersand" to connect cell references and we can also add text in there as long as we
add the text in quotation marks.