1
00:00:01,270 --> 00:00:03,910
Now that we take a look at inputting and editing data.

2
00:00:04,210 --> 00:00:07,740
Let's take a look at adding basic formulas. Right here,

3
00:00:07,840 --> 00:00:13,630
I'm going to add a total column where I want to get the total sales value for each product

4
00:00:13,630 --> 00:00:18,130
for both of the months. And here let's add percentage change.

5
00:00:18,140 --> 00:00:23,470
And notice Excel already tries to figure out the type of formatting I want by taking a look at these

6
00:00:23,470 --> 00:00:24,310
cells.

7
00:00:24,340 --> 00:00:31,020
It didn't apply the border to it so I'm just going to do it quickly by using the Format Painter.

8
00:00:31,270 --> 00:00:34,780
I'm also going to do the same thing for these numbers.

9
00:00:34,780 --> 00:00:38,370
Let's just apply the same formatting to these two columns as well.

10
00:00:38,410 --> 00:00:43,830
So click on Format Painter and just draw it out here. To calculate the total

11
00:00:43,840 --> 00:00:45,700
I have different options.

12
00:00:45,700 --> 00:00:48,930
First off, I have to start with an equal sign.

13
00:00:48,940 --> 00:00:53,530
This means I'm going to be doing a mathematical operation in this cell.

14
00:00:53,530 --> 00:01:00,700
Now I need to use my cell references. So I have to go and click on the cell I want to add. I can either

15
00:01:00,700 --> 00:01:07,540
use my mouse and go and click here or I can work with the arrow key. If I click on the left arrow key

16
00:01:07,540 --> 00:01:16,510
I can already see my selection has shifted to B6. I can use the mathematical operation plus and then

17
00:01:16,660 --> 00:01:20,680
use my arrow key again and select a value in C6.

18
00:01:20,680 --> 00:01:27,650
Once I'm happy with my formula I can press enter or control+enter if I want to stay in the same cell.

19
00:01:27,670 --> 00:01:34,470
What this has done is it's taken the value in this cell and added it to the value in C6.

20
00:01:34,490 --> 00:01:40,540
Now check what happens when I go on the fill handle here and I drag this down.

21
00:01:40,570 --> 00:01:42,290
What do you notice here?

22
00:01:42,310 --> 00:01:44,410
It didn't copy 210 down.

23
00:01:44,470 --> 00:01:47,360
Take a look at the formula behind this.

24
00:01:47,380 --> 00:01:56,650
It also didn't directly copy the exact same formulas I had because this cell is not adding B6 and C6.

25
00:01:57,070 --> 00:02:03,400
It looks still the same in the formula bar but that's because my first selection is still the first

26
00:02:03,400 --> 00:02:03,930
cell.

27
00:02:03,970 --> 00:02:09,199
If I click on the second cell notice we see B7 plus C7.

28
00:02:09,310 --> 00:02:13,170
That's the advantage of working with cell references in Excel.

29
00:02:13,300 --> 00:02:17,630
Because when you drag down formulas they adjust accordingly.

30
00:02:17,640 --> 00:02:20,860
Now notice something else happened. When I drag this down

31
00:02:20,860 --> 00:02:22,870
the formatting came with it.

32
00:02:23,350 --> 00:02:30,520
I have an option to reverse that by clicking here and selecting fill without formatting. But when I pull

33
00:02:30,520 --> 00:02:34,990
this down here again we see it update to B8 and C8.

34
00:02:35,000 --> 00:02:41,220
Now another way of writing this, I'm just gonna remove these, is to use the SUM function.

35
00:02:41,320 --> 00:02:47,040
Excel has inbuilt functions that we're going to take a look at in more detail in another section.

36
00:02:47,080 --> 00:02:52,160
One of the simplest and most used functions in Excel is the SUM function.

37
00:02:52,210 --> 00:02:55,950
The advantage of it is that you can use a range.

38
00:02:56,050 --> 00:03:03,760
So instead of clicking on each cell, I can select an entire range. That range can be many cells so in

39
00:03:03,760 --> 00:03:05,340
this case I have these two.

40
00:03:05,350 --> 00:03:11,530
It could also be something like this. So if I just close the bracket when I'm done writing my function

41
00:03:12,000 --> 00:03:14,880
and I press control+enter.

42
00:03:14,890 --> 00:03:18,090
I see, that's the sum of all these values.

43
00:03:18,100 --> 00:03:24,580
There is another way of inputting the SUM function and that's to use AutoSum.

44
00:03:24,610 --> 00:03:25,870
You can see AutoSum.

45
00:03:25,870 --> 00:03:27,330
It's on the Home tab.

46
00:03:27,340 --> 00:03:28,480
It's sitting right here.

47
00:03:28,480 --> 00:03:34,780
So sitting on the most expensive real state because it's a feature that most people need when they

48
00:03:34,780 --> 00:03:36,120
work with Excel.

49
00:03:36,280 --> 00:03:43,300
If you just click directly on AutoSum it tries to create entire formula for you by trying to guess

50
00:03:43,390 --> 00:03:46,550
what numbers you probably need to sum.

51
00:03:46,600 --> 00:03:49,210
And in this case it's guessed correctly.

52
00:03:49,210 --> 00:03:52,880
So I'm fine to click control+enter or enter.

53
00:03:53,510 --> 00:03:57,250
Now did you notice the shortcut is Alt and the equals sign.

54
00:03:57,430 --> 00:04:01,840
So that's another way you can input AutoSum. Go to your cell,

55
00:04:01,840 --> 00:04:02,340
hold down Alt

56
00:04:02,370 --> 00:04:10,330
and type in the equals sign. And then press control+enter to stay on the same cell. Now, to apply this

57
00:04:10,330 --> 00:04:18,010
to the other cells you can either drag this down or you can also double click. What the double click

58
00:04:18,010 --> 00:04:24,220
does is it applies the formula to all the cells that it thinks need applying.

59
00:04:24,220 --> 00:04:31,990
So my values go down to row 9 and it applies the formula all the way down to D9. Again to fill this

60
00:04:31,990 --> 00:04:37,870
without formatting I can select fill without formatting. There is a shortcut that you can use for this.

61
00:04:37,870 --> 00:04:44,110
First you need to highlight the area that you want to copy your formulas to so let's say I didn't

62
00:04:44,110 --> 00:04:49,400
have these values, I'm just going to remove them. I'm going to highlight this area press F2.

63
00:04:49,490 --> 00:04:57,530
So go to edit mode and then press control+enter, that copies your formula without the formatting.

64
00:04:57,580 --> 00:05:03,580
So this just saves you a step so that you don't have to remove the formatting after you've copied down.

65
00:05:03,630 --> 00:05:04,850
Now let's also add

66
00:05:04,860 --> 00:05:09,810
Total here so that we can see the total value by month.

67
00:05:09,840 --> 00:05:14,460
I'm just gonna copy the formatting and apply it to this one.

68
00:05:14,550 --> 00:05:17,550
Let's see the auto sum part in one go.

69
00:05:17,580 --> 00:05:22,990
What was the shortcut key for AutoSum? Alt and the equal sign and that's it.

70
00:05:22,990 --> 00:05:25,880
And it tries to figure everything out by itself.

71
00:05:25,920 --> 00:05:27,430
I'm just going to double check.

72
00:05:27,430 --> 00:05:34,260
It's done it correctly. So it's taking this range to add these values. For this one, it's taking the correct

73
00:05:34,260 --> 00:05:35,140
range.

74
00:05:35,220 --> 00:05:37,550
Let's calculate the percentage change.

75
00:05:37,560 --> 00:05:39,580
Start off with the equals sign.

76
00:05:39,600 --> 00:05:41,790
Now to calculate a percentage change.

77
00:05:41,790 --> 00:05:51,480
I need to get my February value minus the January value and I need to divide this result with my January

78
00:05:51,540 --> 00:05:59,580
value. Because I want the answer of this first before the division I have to keep this part in brackets

79
00:05:59,930 --> 00:06:03,490
and then divided by my January value.

80
00:06:03,510 --> 00:06:09,720
This is not a percent yet so my change is 0.1 but I'm going to turn it into a percentage

81
00:06:09,750 --> 00:06:11,100
in a second.

82
00:06:11,100 --> 00:06:11,490
Next,

83
00:06:11,490 --> 00:06:16,500
I just want to drag this down and let's go and fill without formatting.

84
00:06:16,740 --> 00:06:20,010
I'm going to add a thick border to the bottom.

85
00:06:20,010 --> 00:06:22,420
Now let's take care of the formatting.

86
00:06:22,470 --> 00:06:27,360
So first off I want to have these numbers with a thousand separator.

87
00:06:27,480 --> 00:06:31,490
I can use the number formatting options that are provided here.

88
00:06:31,580 --> 00:06:34,910
So if I click on this, I get the dollar icon.

89
00:06:35,010 --> 00:06:40,090
I get two decimal places and I get the thousand separator there.

90
00:06:40,110 --> 00:06:46,580
If I click this down arrow I can change my currency to other currencies.

91
00:06:46,710 --> 00:06:52,930
If want to remove the decimals, select decrease decimal and increase decimal

92
00:06:52,950 --> 00:07:00,270
If I want to get my decimals back. Another option is to use this comma style number format.

93
00:07:00,270 --> 00:07:04,380
So when I click on it that adds the thousand separator.

94
00:07:04,380 --> 00:07:08,810
It has two decimal places but it doesn't have the currency.

95
00:07:08,910 --> 00:07:13,990
In this case let's say I don't need the currency in there so I'm gonna go with this option and I'm going

96
00:07:13,990 --> 00:07:18,470
to take away the decimal places. From this dropdown here,

97
00:07:18,480 --> 00:07:25,260
You have some more options that you can select from and you can go to more number formats and select

98
00:07:25,320 --> 00:07:27,540
a number format from here.

99
00:07:27,540 --> 00:07:35,370
So what I usually do is I come here to format cells to number formatting and I add a thousand separator

100
00:07:35,730 --> 00:07:38,510
and I take away the decimal places.

101
00:07:38,580 --> 00:07:42,960
You can also select how you want your negative numbers to be shown.

102
00:07:43,050 --> 00:07:49,770
If you want them in brackets, if you want them red in brackets, just red without the minus, or to have

103
00:07:49,770 --> 00:07:54,360
them as just minus and the number and then click on OK.

104
00:07:54,480 --> 00:08:01,620
Now for the percentage, what do I need to select? The percentage number format and that's it.

105
00:08:01,650 --> 00:08:04,620
So you don't have to multiply it by a hundred.

106
00:08:04,620 --> 00:08:08,000
You can just select the percentage number format.

107
00:08:08,010 --> 00:08:11,400
Let's just remove that border we have here.

108
00:08:11,400 --> 00:08:12,890
Just the bottom border.

109
00:08:12,990 --> 00:08:14,690
How do we do it?

110
00:08:14,700 --> 00:08:19,530
I can go back to borders, go to more borders, from here

111
00:08:19,530 --> 00:08:21,200
Just remove the bottom borders.

112
00:08:21,210 --> 00:08:28,050
So either click on this directly here or click on this toggle to take away the border completely and

113
00:08:28,050 --> 00:08:31,130
then click on OK and that borders gone.

114
00:08:31,590 --> 00:08:36,510
Before we wrap up this lecture let's also make the total and percentage change standout.

115
00:08:36,510 --> 00:08:38,230
I'm gonna highlight this.

116
00:08:38,309 --> 00:08:40,179
I also want to make these standout.

117
00:08:40,200 --> 00:08:44,230
I'm gonna hold down to control key and highlight these as well.

118
00:08:44,310 --> 00:08:49,180
I'm gonna switch these to bold. In addition, let's change the alignment of these.

119
00:08:49,290 --> 00:08:51,640
I'm just gonna push them to the right.

120
00:08:51,720 --> 00:08:54,780
So they stand on top of the numbers here.

121
00:08:54,780 --> 00:09:00,990
I can also make these a bit smaller so if I want them to auto fit I just have to double click on the side

122
00:09:01,320 --> 00:09:02,970
to get them auto fit.

123
00:09:02,970 --> 00:09:08,940
I can also expand them a little bit further. And as long as I have all three highlighted it's going to

124
00:09:08,940 --> 00:09:11,880
apply that to the selected columns.

125
00:09:11,880 --> 00:09:19,710
Okay so that's the basics of creating simple basic formulas in Excel and doing basic formatting.

126
00:09:19,710 --> 00:09:21,000
In the next lecture

127
00:09:21,000 --> 00:09:26,250
I'm going to take you through a hidden tip that not so many people know of in Excel.