1
00:00:01,030 --> 00:00:08,680
You might come across cases where you need to combine the values of two or more cells into one cell.

2
00:00:08,790 --> 00:00:14,510
A common way of doing this is to use the "ampersand" in between your cell references.

3
00:00:14,590 --> 00:00:18,220
In this example we have the item name and we have to type.

4
00:00:18,360 --> 00:00:25,210
So if it's men or women and let's say we want to combine this two information in one single cell.

5
00:00:25,210 --> 00:00:32,380
Start off with the equal sign, let's reference A5. To connect the result of this cell with this cell

6
00:00:32,380 --> 00:00:40,520
we need to use the "ampersand". And now click on the other cell reference, press enter and we get "ScarfM".

7
00:00:41,020 --> 00:00:42,800
Let's send this down.

8
00:00:42,970 --> 00:00:46,020
Now what if we wanted to add a space in between.

9
00:00:46,030 --> 00:00:48,640
So we don't want the M to stick to scarf.

10
00:00:48,670 --> 00:00:51,970
You want to have a single space in between these.

11
00:00:51,970 --> 00:00:56,040
So here again we're going gonna use the formula reference, "ampersand".

12
00:00:56,200 --> 00:01:03,730
Now we want to add a space and a space is a text character that we're typing in the formula.

13
00:01:03,730 --> 00:01:06,750
So we have to put it in quotation marks.

14
00:01:07,030 --> 00:01:15,310
Here's another Excel essential, whenever you combine text with cell references you need to put the text

15
00:01:15,430 --> 00:01:16,750
in quotation marks

16
00:01:17,880 --> 00:01:19,850
and you need a double quotation mark.

17
00:01:19,880 --> 00:01:25,540
So I'm going to put the quotation mark again and I want a space in between these and space is text.

18
00:01:25,840 --> 00:01:29,070
So I'm just going to add a space character here.

19
00:01:29,140 --> 00:01:38,020
Now after this, I want to combine this result with this cell. I need to use the "ampersand" again and reference

20
00:01:38,320 --> 00:01:40,300
that cell and press enter.

21
00:01:40,500 --> 00:01:45,700
We get "scarf M" followed by space character followed by our M.

22
00:01:46,480 --> 00:01:48,110
Let's send this down.

23
00:01:48,310 --> 00:01:53,890
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

24
00:01:54,300 --> 00:01:55,320
I'm going to do the same.

25
00:01:55,320 --> 00:01:56,900
So we do scarf.

26
00:01:56,900 --> 00:02:00,330
Now can I just put in the dash sign like this.

27
00:02:00,340 --> 00:02:04,500
No, because it's text, right? I need to start with quotation marks.

28
00:02:04,510 --> 00:02:10,090
Anytime you input text in your formula box you need to add a quotation mark in there.

29
00:02:10,090 --> 00:02:18,140
So let's add dash, quotation mark and I reference this cell, press enter and send this down.

30
00:02:18,240 --> 00:02:19,920
So here's another challenge.

31
00:02:20,020 --> 00:02:26,140
We have the ability to select year from here and we want to create new titles for these.

32
00:02:26,170 --> 00:02:32,020
We want to have price dash and the year that's selected, volume dash and the year that's selected.

33
00:02:32,050 --> 00:02:34,280
So let's see how we can write this formula.

34
00:02:34,290 --> 00:02:36,370
First is prize, that's this one.

35
00:02:36,460 --> 00:02:38,770
Now I'm planning to pull this to the right.

36
00:02:38,770 --> 00:02:41,740
Do I need to fix anything here?

37
00:02:41,740 --> 00:02:46,130
I don't need to fix the cell reference because I want it to be relative.

38
00:02:46,130 --> 00:02:47,880
Now I'm going to add the "&"

39
00:02:48,010 --> 00:02:50,200
Let's say we want to follow this with a dash.

40
00:02:50,200 --> 00:02:52,390
We don't want the year to stick to the price.

41
00:02:52,480 --> 00:02:56,660
So I'm going to add quotation marks dash in other quotation mark.

42
00:02:56,860 --> 00:02:57,560
What do I need now?

43
00:02:57,880 --> 00:03:00,410
Can I just click on this cell.

44
00:03:00,490 --> 00:03:00,910
No.

45
00:03:00,910 --> 00:03:01,110
Right.

46
00:03:01,120 --> 00:03:03,220
Because I'm going to leave that formula.

47
00:03:03,220 --> 00:03:06,800
I need the "&" before I reference that cell.

48
00:03:06,830 --> 00:03:12,340
Now I'm going to click on this cell and before I press enter how should I fix this?

49
00:03:12,340 --> 00:03:17,890
I need to fully fix this because when I pull my formula across I don't want that reference to

50
00:03:17,890 --> 00:03:20,070
shift. Press enter.

51
00:03:20,240 --> 00:03:24,440
Now pull this, everything updates properly.

52
00:03:24,460 --> 00:03:28,610
Let's change the year, the results update accordingly.

53
00:03:28,640 --> 00:03:28,850
Okay.

54
00:03:28,870 --> 00:03:31,180
So we learned two important concepts here.

55
00:03:31,270 --> 00:03:39,220
We can use the "ampersand" to connect cell references and we can also add text in there as long as we

56
00:03:39,220 --> 00:03:41,710
add the text in quotation marks.