1
00:00:00,090 --> 00:00:06,050
Copying and pasting data seems like a simple task, right? But there is a lot of hidden features

2
00:00:06,210 --> 00:00:12,090
when it comes to pasting data in Excel. Here I have some sample data on profit.

3
00:00:12,090 --> 00:00:14,090
These are all input fields.

4
00:00:14,100 --> 00:00:19,400
The last one here is calculated so that's the sum of all the months here.

5
00:00:19,400 --> 00:00:24,960
Now if I just copy these press control+c. Go down here,

6
00:00:24,960 --> 00:00:29,730
press Control+V, it paste all the formatting, it will paste all the formulas

7
00:00:29,730 --> 00:00:31,570
if they have formulas in them.

8
00:00:31,710 --> 00:00:33,240
So that's the default

9
00:00:33,240 --> 00:00:34,480
Copy and paste.

10
00:00:34,500 --> 00:00:41,130
But we have a lot more options if we go to paste special here. Under paste special

11
00:00:41,130 --> 00:00:47,220
we can decide exactly what we want to paste. If we just wanted to paste the formulas not the formatting.

12
00:00:47,490 --> 00:00:53,150
Paste values so the numbers the result and not the formula behind this number.

13
00:00:53,490 --> 00:01:00,510
If we just wanted to paste the formats and not the content of the cell we pick formats.

14
00:01:00,510 --> 00:01:03,470
Paste the comments that are in the cell or the data validation. The column widths

15
00:01:03,500 --> 00:01:10,590
and we also have the option of pasting just the formulas and Number Formats which means

16
00:01:10,620 --> 00:01:15,210
this doesn't paste any other type of formatting that you have in that cell.

17
00:01:15,210 --> 00:01:22,050
So for example, if you have a yellow background color that wouldn't come if you select formulas and number

18
00:01:22,050 --> 00:01:23,130
formats.

19
00:01:23,130 --> 00:01:25,200
You can also paste the values.

20
00:01:25,230 --> 00:01:30,780
So this means not the formulas just the results and the number formats.

21
00:01:30,780 --> 00:01:35,580
So take some time and take a look at the options you have here because that's going to save you a lot

22
00:01:35,580 --> 00:01:36,110
of time

23
00:01:36,120 --> 00:01:42,450
if you already select the right option when you're pasting your data. Let's go and do a test here.

24
00:01:42,480 --> 00:01:49,040
In this case I want to copy these and they have formulas behind but I don't want to paste the formula.

25
00:01:49,170 --> 00:01:50,140
Copy this,

26
00:01:50,220 --> 00:01:53,220
come here and paste it as values.

27
00:01:53,220 --> 00:01:59,910
So I'm going to right mouse click and paste as values is automatically available here but we can also

28
00:01:59,910 --> 00:02:03,170
go to paste special, click on values, and click on Ok.

29
00:02:03,190 --> 00:02:09,870
So notice paste as values only brings the number without the formatting.

30
00:02:10,020 --> 00:02:17,010
I can right mouse click, go back to paste special, if I also wanted the formatting I would select values

31
00:02:17,130 --> 00:02:21,260
and number formats. That brings just the number formats.

32
00:02:21,330 --> 00:02:29,220
If I also wanted to bring in the Cell Formatting I could have selected values and then formats because

33
00:02:29,220 --> 00:02:34,360
this brings in everything. The number formats as well as the cell formats.

34
00:02:34,470 --> 00:02:39,930
And notice that you can continue to go back to paste special and add on more things.

35
00:02:39,930 --> 00:02:42,740
It still keeps it in memory.

36
00:02:42,740 --> 00:02:50,730
Now the downside of pasting as values is that your numbers are not dynamic anymore. If something changes

37
00:02:50,730 --> 00:02:54,900
here it's going to pull through here but it's not going to pull through here.

38
00:02:54,900 --> 00:03:02,640
If you want it to be dynamic so if you want it to reference back to this cell you can use another option.

39
00:03:02,640 --> 00:03:10,470
So after you copy go to the cell you want your results in, go to a paste special and this time select

40
00:03:10,470 --> 00:03:13,340
paste link from down here.

41
00:03:13,410 --> 00:03:18,080
What this does is it links directly to your results.

42
00:03:18,090 --> 00:03:23,130
It doesn't take over the Cell Formatting. The Cell Formatting comes from the existing cells

43
00:03:23,130 --> 00:03:24,800
these numbers are sitting on.

44
00:03:24,870 --> 00:03:30,990
So if we want to bring over the cell formatting we can go to paste special and we can select formats as

45
00:03:30,990 --> 00:03:36,430
well and click on ok. So these keeps the links and adds the formats.

46
00:03:36,480 --> 00:03:42,090
Now you can do the same thing for the labels as well so I'm going to copy these, right mouse click here,

47
00:03:42,390 --> 00:03:44,990
go to paste special, and paste links.

48
00:03:45,180 --> 00:03:48,880
So these are now directly linked to these values.

49
00:03:48,880 --> 00:03:52,770
Now if something changes here, if this number changes to 10.

50
00:03:52,770 --> 00:03:58,420
It's going to pull through here and is going to pull through here. Where as in the previous example.

51
00:03:58,440 --> 00:04:05,030
It's not going to pull through. Another really handy paste special option is to paste as transpose.

52
00:04:05,070 --> 00:04:13,620
So if you wanted to have our data set the other way around we just have to select it, control+C, go down

53
00:04:13,620 --> 00:04:20,610
here, right mouse click, go to paste special, put a check mark beside transpose, and click on Ok.

54
00:04:20,640 --> 00:04:25,890
Now we get our data set the other way round

55
00:04:26,050 --> 00:04:32,600
but this also got transposed properly so our values add up correctly.

56
00:04:32,600 --> 00:04:32,820
Okay.

57
00:04:32,840 --> 00:04:36,530
So what the transpose option does is it turns around

58
00:04:36,530 --> 00:04:44,660
whatever was in the columns it puts it on rows and whatever was under rows it puts it on the columns.

59
00:04:44,660 --> 00:04:47,690
Now here's another hidden feature for paste special.

60
00:04:47,840 --> 00:04:54,590
So I have these January numbers here and let's say I received some additional numbers that I need to

61
00:04:54,650 --> 00:04:57,350
add on to my January numbers.

62
00:04:57,350 --> 00:05:03,920
So this number should get added on to "WenCaL" hundred should get added on to the second number.

63
00:05:03,950 --> 00:05:09,860
I could write a formula somewhere so I could say equals this plus this and pull this down

64
00:05:09,980 --> 00:05:16,960
then copy and paste as values and put this back here. Or I can use paste special.

65
00:05:16,970 --> 00:05:21,200
First thing I need to do is to copy the values I want to add to these.

66
00:05:21,220 --> 00:05:23,860
So highlight, press control+c

67
00:05:24,170 --> 00:05:29,540
Now go to January, right mouse click, go to paste special.

68
00:05:29,540 --> 00:05:32,560
Keep your eye on the original numbers.

69
00:05:32,930 --> 00:05:35,000
Click on Add, click on ok.

70
00:05:36,300 --> 00:05:36,310
.

71
00:05:36,980 --> 00:05:40,370
These numbers got added to these values.

72
00:05:40,400 --> 00:05:44,960
I didn't have to write a formula, I didn't have to take additional steps.

73
00:05:44,990 --> 00:05:51,640
I did it directly in the cells. Taking some time to become familiar with the paste special options can

74
00:05:51,650 --> 00:05:54,680
come in really handy and it can save you a lot of time.