WEBVTT

1
00:00:00.000 --> 00:00:04.931
[MUSIC]

2
00:00:04.931 --> 00:00:08.687
As you start with your spreadsheet,
it's important to keep formatting and

3
00:00:08.687 --> 00:00:10.460
organization in mind.

4
00:00:10.460 --> 00:00:12.310
How much time you spend organizing and

5
00:00:12.310 --> 00:00:16.870
formatting the spreadsheet depends
on the answers to a few questions.

6
00:00:16.870 --> 00:00:21.180
The first question you should ask
yourself is, what's the meaning of life?

7
00:00:21.180 --> 00:00:23.860
I am just kidding,
that a fun question to ponder but for

8
00:00:23.860 --> 00:00:28.660
what we are doing now is actually,
how is this spreadsheet going to be used?

9
00:00:28.660 --> 00:00:30.520
Is it a one off piece of work?

10
00:00:30.520 --> 00:00:33.825
Or this is something that I expect
to use in an ongoing manner?

11
00:00:33.825 --> 00:00:35.355
For example,

12
00:00:35.355 --> 00:00:40.675
doing calculations on how much I have to
save to buy a car might be a one off use.

13
00:00:40.675 --> 00:00:41.955
Tracking a monthly budget or

14
00:00:41.955 --> 00:00:46.315
rolling forecast are examples
of repeated use of spreadsheets.

15
00:00:46.315 --> 00:00:49.395
For spreadsheets you will use
frequently and for a long time,

16
00:00:49.395 --> 00:00:54.050
you should put more thought into
how you organize and format it.

17
00:00:54.050 --> 00:00:55.067
It's an important spreadsheet.

18
00:00:55.067 --> 00:00:56.540
So put the time in up front.

19
00:00:57.840 --> 00:00:59.910
Another important question to ask is,

20
00:00:59.910 --> 00:01:03.010
am I going to be sharing this
spreadsheet with others?

21
00:01:03.010 --> 00:01:07.020
Formatting becomes much more important
when multiple people will be working on

22
00:01:07.020 --> 00:01:08.320
this spreadsheet.

23
00:01:08.320 --> 00:01:11.220
You'll want the spreadsheet to be clear,
easily read and

24
00:01:11.220 --> 00:01:14.260
understandable to everyone
you share it with.

25
00:01:14.260 --> 00:01:16.310
We'll talk about some best practices for

26
00:01:16.310 --> 00:01:18.340
formatting spreadsheets in
this part of the course.

27
00:01:19.390 --> 00:01:21.470
Let’s start with adding
color to spreadsheets.

28
00:01:22.510 --> 00:01:25.360
Lots of newcomers to spreadsheets
will start to use color

29
00:01:25.360 --> 00:01:27.960
as an organizational formatting tool.

30
00:01:27.960 --> 00:01:32.490
I can change the background of
cells by clicking on a cell and

31
00:01:32.490 --> 00:01:34.910
then choosing the color that
I want to change it to.

32
00:01:37.100 --> 00:01:39.090
So here I've selected a bunch of cells.

33
00:01:39.090 --> 00:01:41.590
I'm going to make them all a light gray.

34
00:01:41.590 --> 00:01:45.296
You can also change
the color of text in cells.

35
00:01:48.287 --> 00:01:52.218
I wanna make this text green.

36
00:01:52.218 --> 00:01:53.660
That's scary.

37
00:01:55.060 --> 00:01:58.400
So I'm gonna undo all
the color changes I just made.

38
00:01:58.400 --> 00:01:59.540
If you're collaborating,

39
00:01:59.540 --> 00:02:02.860
make sure you are aware if any of
your collaborators are color-blind.

40
00:02:02.860 --> 00:02:05.360
Be sensitive to their needs and
make adjustments accordingly.

41
00:02:06.360 --> 00:02:08.940
Share your spreadsheet early to
get feedback from others you work

42
00:02:08.940 --> 00:02:13.100
with to make sure the colors make your
spreadsheet easier to read and understand.

43
00:02:13.100 --> 00:02:17.910
In my opinion, the two most powerful uses
of colors in a spreadsheet are, one,

44
00:02:17.910 --> 00:02:22.960
to signal hard coded or soft coded cells,
and two, to highlight important data.

45
00:02:24.530 --> 00:02:29.190
Here I've opened a spreadsheet
example that has good formatting.

46
00:02:29.190 --> 00:02:32.890
In the first case of using colors
powerfully in spreadsheets,

47
00:02:32.890 --> 00:02:37.930
that is to signal hard coded or soft coded
cells, best practice is to use blue for

48
00:02:37.930 --> 00:02:40.750
numbers that are manually inputted or
hard coded and

49
00:02:40.750 --> 00:02:44.980
black for cells that are referencing other
cells with no manual input in the formula.

50
00:02:45.990 --> 00:02:50.575
Here we have colored text that
is manually inputted blue

51
00:02:50.575 --> 00:02:55.740
and cells that are referencing
others through formulas as black.

52
00:02:55.740 --> 00:02:59.420
Most financial analysts will be familiar
with this formatting practice and

53
00:02:59.420 --> 00:03:00.790
sometimes expand on it.

54
00:03:00.790 --> 00:03:03.170
So for example,
if they're using an assumption and

55
00:03:03.170 --> 00:03:07.570
they're not sure about it,
they might color it red, instead of blue.

56
00:03:07.570 --> 00:03:10.760
For highlighting important data,
don't go crazy.

57
00:03:10.760 --> 00:03:12.664
Simply highlight a row or a column or

58
00:03:12.664 --> 00:03:16.610
a collection of cells showing the most
important items on a spreadsheet.

59
00:03:16.610 --> 00:03:21.872
So here we have highlighted this
as the key takeaway, it's yellow,

60
00:03:21.872 --> 00:03:26.930
there's not a bunch of color,
it's just this one row.

61
00:03:26.930 --> 00:03:30.736
Keep in mind when working with colors
that too many colors can quickly become

62
00:03:30.736 --> 00:03:32.821
distracting and difficult to maintain.

63
00:03:32.821 --> 00:03:35.924
Let's look at example.

64
00:03:35.924 --> 00:03:40.119
In this spreadsheet,
there is lot's of different colors and

65
00:03:40.119 --> 00:03:45.042
it's kinda hard to tell what they mean.
What's the important thing that

66
00:03:45.042 --> 00:03:49.723
I'm supposed to looking at here?
Sometimes people will use colors for

67
00:03:49.723 --> 00:03:53.285
rows, they'll use colors for columns.

68
00:03:53.285 --> 00:03:55.365
You're gonna have to use
your judgment a bit here.

69
00:03:55.365 --> 00:03:58.795
But just be cautious about getting
too crazy with using too many colors.