1
00:00:02,820 --> 00:00:05,600
Imagine you receive a workbook from your colleague.

2
00:00:05,610 --> 00:00:12,360
It has a sheet that includes a lot of data and you want to quickly recognize which of these cells

3
00:00:12,360 --> 00:00:15,030
have formulas and which of them are input.

4
00:00:15,030 --> 00:00:22,530
You can use "go to special". "Go to special" is another really helpful but hidden feature that's not known

5
00:00:22,530 --> 00:00:24,320
by a lot of Excel users.

6
00:00:24,330 --> 00:00:29,310
So here we just have a small dataset and I'm going to demonstrate what go to special can do.

7
00:00:29,310 --> 00:00:30,220
Just by looking at this.

8
00:00:30,240 --> 00:00:34,170
I can't tell what are the formula cells in here.

9
00:00:34,170 --> 00:00:42,020
I'm going to go to "go to special". Go to home, on the right hand side here where you see find and select.

10
00:00:42,030 --> 00:00:46,810
There is go to "go to special" and then you will see other features here.

11
00:00:46,830 --> 00:00:49,890
These actually belong to "go to special".

12
00:00:49,890 --> 00:00:56,850
So if I just want to highlight the formula cells on this sheet, I just have to select formulas and they're

13
00:00:56,850 --> 00:00:59,710
already highlighted, now the totals are fine.

14
00:00:59,760 --> 00:01:02,070
These look like they have formulas too

15
00:01:02,240 --> 00:01:06,100
and this number also has a formula behind it.

16
00:01:06,120 --> 00:01:12,360
The good thing is because they're all selected I can go and I can add a color to this.

17
00:01:12,360 --> 00:01:18,360
I can go and make them green and just take a look at the formula cells. And then go back to this and see

18
00:01:18,360 --> 00:01:21,460
what type of formula is behind this.

19
00:01:21,480 --> 00:01:28,180
This one looks like it's an input and these have formulas so it's referencing this cell minus one, this

20
00:01:28,190 --> 00:01:29,890
cell minus two.

21
00:01:29,910 --> 00:01:32,950
So I'm just gonna press control+z to go back.

22
00:01:32,970 --> 00:01:37,620
Now what if I wanted to see all the constants on this sheet.

23
00:01:37,620 --> 00:01:38,640
I can do that too.

24
00:01:38,640 --> 00:01:46,020
I can go to find and select and select all the constants. And I immediately noticed that this cell is

25
00:01:46,020 --> 00:01:47,920
not an Input cell.

26
00:01:48,000 --> 00:01:51,060
You can also select specific areas.

27
00:01:51,120 --> 00:01:58,770
So if I just highlight this here and I say I want to see the constants only in this selection, I just

28
00:01:58,770 --> 00:02:03,450
have to do the highlighting before I go and select constants.

29
00:02:03,450 --> 00:02:08,630
Now let's take a look at more options directly under go to special.

30
00:02:08,850 --> 00:02:13,170
This takes us to this view where we have even more options.

31
00:02:13,170 --> 00:02:19,800
So we see the constant and the formulas option we saw before but here we can actually decide what type

32
00:02:19,800 --> 00:02:21,950
of constants we want to get.

33
00:02:21,960 --> 00:02:24,910
So let's say I don't want to have any text constants.

34
00:02:24,930 --> 00:02:30,660
I'm going to take the tick mark away and the selection here should disappear and I click on OK then

35
00:02:30,660 --> 00:02:38,640
it's only these numbers that are selected. The shortcut for go to special is Control+g

36
00:02:38,640 --> 00:02:43,800
and then you can press Alt+s to go to special or just select special.

37
00:02:43,800 --> 00:02:47,070
Now this time again let's select a constant.

38
00:02:47,070 --> 00:02:51,170
Let's just have the numbers and no text and click on OK.

39
00:02:51,330 --> 00:02:54,410
And I can highlight the input cells differently.

40
00:02:54,420 --> 00:02:58,440
So let's go and highlight them in this light yellow color.

41
00:02:58,440 --> 00:03:03,550
Now for this one that should actually be a constant and not a formula.

42
00:03:03,690 --> 00:03:06,240
I want to remove the formula behind this.

43
00:03:06,240 --> 00:03:07,710
Just keep the number.

44
00:03:07,800 --> 00:03:14,610
So I'm going to right mouse click this, select copy or just press Control+c and then right mouse click

45
00:03:14,680 --> 00:03:16,650
and under paste special.

46
00:03:16,650 --> 00:03:23,700
The second option here is to paste as values which means just keep the number but take away the formula

47
00:03:23,730 --> 00:03:24,990
behind it.

48
00:03:24,990 --> 00:03:26,550
So now let's just try that again.

49
00:03:26,550 --> 00:03:28,660
I'm going to highlight this area.

50
00:03:28,800 --> 00:03:30,920
I'm going to go to find and select.

51
00:03:30,990 --> 00:03:36,870
I don't have any text here so I'm just going to select the constants directly from here and then use

52
00:03:37,110 --> 00:03:38,980
the color here.

53
00:03:39,010 --> 00:03:46,200
OK so that's how easy it is to recognize formula and input cells in your worksheet.

54
00:03:46,200 --> 00:03:52,080
Now we are going to take a deeper look at the other options in go to special especially when we take

55
00:03:52,080 --> 00:03:53,670
a look at cleaning data.

56
00:03:53,760 --> 00:03:55,460
So lots of fun stuff coming out.

57
00:03:55,470 --> 00:03:59,100
Make sure you do the exercises so you don't forget everything you're learning.