1
00:00:00,810 --> 00:00:07,130
In this lecture let's take a look at some number formatting options that we haven't looked at yet.

2
00:00:07,140 --> 00:00:12,490
Let's say you received this dataset from your HR department and you want to make it easier to read.

3
00:00:12,510 --> 00:00:14,470
So we have a list of employee names.

4
00:00:14,550 --> 00:00:17,050
Here we have their phone numbers.

5
00:00:17,100 --> 00:00:20,490
Let's make this phone numbers look more like a phone number.

6
00:00:20,670 --> 00:00:23,940
Let's highlight this and bring up number formatting.

7
00:00:23,940 --> 00:00:26,890
The shortcut key was? Control+1.

8
00:00:26,910 --> 00:00:35,100
Down here you see special, you have the ability to make these numbers look more like zip codes or more

9
00:00:35,100 --> 00:00:36,450
like phone numbers.

10
00:00:36,450 --> 00:00:38,610
So here you can see a preview.

11
00:00:38,640 --> 00:00:41,900
Now this looks so much easier to read than the number there.

12
00:00:41,980 --> 00:00:43,200
Click on OK

13
00:00:43,410 --> 00:00:50,160
and all of these are formatted. But take a look at the formula bar. The number in the cell hasn't changed,

14
00:00:50,520 --> 00:00:59,850
the way it looks has. So number formatting doesn't change the actual value, it just adds makeup to your cell.

15
00:00:59,850 --> 00:01:02,510
Now let's also format yearly salary.

16
00:01:02,610 --> 00:01:04,500
So for this we have different options.

17
00:01:04,500 --> 00:01:08,310
We can directly click on the accounting number format here.

18
00:01:08,490 --> 00:01:13,210
If you want it in a different currency, you can select the currency from here.

19
00:01:13,230 --> 00:01:19,940
If you don't see it here, you can go to more accounting formats and select a symbol from this dropdown

20
00:01:19,960 --> 00:01:21,170
arrow here.

21
00:01:21,220 --> 00:01:25,350
Another option you have is to format this in the currency format.

22
00:01:25,500 --> 00:01:28,830
So let's see what the difference between accounting and currency are.

23
00:01:28,830 --> 00:01:30,050
That's the currency format.

24
00:01:30,060 --> 00:01:37,040
So notice the dollar sign is stuck to the number whereas with accounting, the dollar sign is left aligned.

25
00:01:37,050 --> 00:01:40,020
The other difference is also how zeros are represented.

26
00:01:40,020 --> 00:01:44,390
So if I put a zero in this cell, this is how it looks in the currency format.

27
00:01:44,490 --> 00:01:48,810
If we switch to accounting, that's how it looks with accounting.

28
00:01:48,810 --> 00:01:51,960
So I'm just gonna press Control+z to go back.

29
00:01:52,020 --> 00:01:59,850
Now you can also always influence the decimals by decreasing the decimal or increasing the decimal directly

30
00:01:59,880 --> 00:02:00,800
from here.

31
00:02:00,870 --> 00:02:07,530
If you don't want to show the currency symbol here, you can use a comma style type of number format and

32
00:02:07,530 --> 00:02:09,449
then take away the decimals.

33
00:02:09,539 --> 00:02:15,150
Another common style that I use is the number format directly from here.

34
00:02:15,150 --> 00:02:18,150
I put a checkmark for use a thousand separator.

35
00:02:18,270 --> 00:02:20,000
I changed the decimal places

36
00:02:20,040 --> 00:02:22,750
if I don't want any, I put it to zero.

37
00:02:22,770 --> 00:02:27,060
You can also select how you want negative numbers to be represented.

38
00:02:27,060 --> 00:02:33,330
If you just wanted like the usual, you can select the first option which is generally the default when

39
00:02:33,330 --> 00:02:34,800
you go to the number format.

40
00:02:34,830 --> 00:02:36,960
So if we do these as well.

41
00:02:37,030 --> 00:02:41,060
So if I highlight this, press Control+1, directly go to number.

42
00:02:41,100 --> 00:02:46,590
All I have to do is use a thousand separator. If I don't want to have any decimals,

43
00:02:46,650 --> 00:02:48,890
I'm going to put this to zero and then click on Ok.

44
00:02:48,900 --> 00:02:51,080
Now for the difference.

45
00:02:51,240 --> 00:02:58,890
Let's actually show the negative values in red. So there was an option for this, under number. So thousand

46
00:02:58,890 --> 00:02:59,840
separator.

47
00:02:59,850 --> 00:03:06,110
Let's not show any decimal and here I'm going to pick negative numbers to be shown as red

48
00:03:06,270 --> 00:03:08,160
and in brackets.

49
00:03:08,160 --> 00:03:08,460
OK.

50
00:03:08,460 --> 00:03:10,880
And all of this is obviously dynamic.

51
00:03:11,220 --> 00:03:18,150
So if our number increases here for previous year, our difference becomes negative. It's automatically

52
00:03:18,510 --> 00:03:21,090
reflected as red. Now for the bonus,

53
00:03:21,090 --> 00:03:22,340
this one is simple.

54
00:03:22,410 --> 00:03:26,850
We're going to highlight this and we're going to change this to a percentage.

55
00:03:26,850 --> 00:03:29,250
We can show a decimal, in this case anyhow

56
00:03:29,310 --> 00:03:30,390
they're all zeros.

57
00:03:30,510 --> 00:03:32,970
So I'm just going to collapse the decimal.

58
00:03:33,000 --> 00:03:38,640
Okay, so these are the different number formatting options you can apply to your own data sets.