1
00:00:04,530 --> 00:00:09,320
Here are some do's and dont's when it comes to creating your next Excel workbook.

2
00:00:09,360 --> 00:00:13,110
There are two main aspects when you design an Excel spreadsheet.

3
00:00:13,110 --> 00:00:15,150
First, the structure of the workbook.

4
00:00:15,150 --> 00:00:21,840
Second, the visual design of the worksheets. Let's cover structure first. Keep raw data separate to the

5
00:00:21,840 --> 00:00:28,770
analysis. By separate I mean in separate tabs. I know in the examples we cover in the course, the raw data

6
00:00:28,770 --> 00:00:31,730
is sitting right there when we do the analysis.

7
00:00:31,770 --> 00:00:37,800
It's simpler to teach this way without having to switch sheets all the time. But once you work with your

8
00:00:37,860 --> 00:00:46,020
own data, keep your raw data in its own tab and use a new tab for the analysis. Each sheet should have

9
00:00:46,020 --> 00:00:46,650
a purpose

10
00:00:46,650 --> 00:00:53,880
you can easily explain. For example, in this report the Data tab has the raw data, Dashboard has the final

11
00:00:53,880 --> 00:00:54,560
report.

12
00:00:55,140 --> 00:01:01,500
All calculations are done in the calculation tab. And the control tab has a summary of the changes made

13
00:01:01,830 --> 00:01:04,980
to the dashboard together with timestamps.

14
00:01:05,010 --> 00:01:06,870
We create this report from scratch

15
00:01:06,960 --> 00:01:14,670
in my Excel dash dashboard course. Finalizing an Excel workbook is usually not a one time task.

16
00:01:14,670 --> 00:01:17,690
Requirements change, company structures change.

17
00:01:17,730 --> 00:01:22,170
Keep an overview of the changes you make in the workbook in a separate tab.

18
00:01:22,330 --> 00:01:25,830
Make a note of the change and add a timestamp.

19
00:01:25,830 --> 00:01:32,070
No one likes to document but taking the time to do it is going to save you a lot of time later on.

20
00:01:32,070 --> 00:01:38,340
If you're distributing the workbook for others to use or for others to input, add an instruction sheet.

21
00:01:38,340 --> 00:01:42,270
If you're using abbreviations or keys, define what these are on this sheet.

22
00:01:42,270 --> 00:01:48,450
Outline the purpose of each tab and write a set of guidelines and instructions.

23
00:01:48,510 --> 00:01:55,140
It might be clear for you what your report or tool is meant to do but it's not going to be clear for everyone.

24
00:01:55,140 --> 00:02:00,450
Excel does have a good file recovery system. If something goes wrong and you want to go back to a

25
00:02:00,450 --> 00:02:06,810
previous version you generally can. But if you want to be on the safe side, it helps to keep a copy of

26
00:02:06,810 --> 00:02:08,070
the file.

27
00:02:08,070 --> 00:02:10,350
Now let's talk about visual design.

28
00:02:10,590 --> 00:02:15,570
If you think there's a slight chance that someone at the office will print out the sheets or export

29
00:02:15,570 --> 00:02:16,820
the file as PDF

30
00:02:17,100 --> 00:02:19,620
make sure you prepare it for printing.

31
00:02:19,650 --> 00:02:24,600
Check each tab, go to print preview and adjust as you see fit.

32
00:02:24,600 --> 00:02:27,930
Also add headers and footers to your pages.

33
00:02:28,020 --> 00:02:31,960
You may want to add the file address if it's an internal report.

34
00:02:32,040 --> 00:02:33,750
You might want to add the date.

35
00:02:33,840 --> 00:02:37,790
Just make sure whatever you do that you add page numbers.

36
00:02:37,800 --> 00:02:41,590
This makes it more obvious if something goes missing.

37
00:02:41,670 --> 00:02:45,240
Keep a consistent color code for different purposes.

38
00:02:45,270 --> 00:02:51,690
Let's say you're creating files that you want to distribute to collect sales information from different

39
00:02:51,690 --> 00:02:52,980
divisions.

40
00:02:52,980 --> 00:02:59,660
Use color to help the users know which fields are for input and which are calculated.

41
00:02:59,730 --> 00:03:02,310
For example, you could design them like this.

42
00:03:02,370 --> 00:03:07,720
Use a subtle color for the input cells or you can design it like this.

43
00:03:07,740 --> 00:03:12,480
Keep the input cells white and add a color for the calculated ones.

44
00:03:12,480 --> 00:03:13,860
Whichever method you choose

45
00:03:13,860 --> 00:03:16,720
keep it consistent throughout your work.

46
00:03:16,770 --> 00:03:23,900
Also make sure you don't use too much color. Or a background color that's too close to the font color.

47
00:03:23,940 --> 00:03:27,860
Ensure there is a good contrast so the content is readable.

48
00:03:27,900 --> 00:03:31,820
Basically, design it in a way that's appealing to consume.

49
00:03:31,830 --> 00:03:35,060
Use formatting but don't use excessive formatting.

50
00:03:35,130 --> 00:03:40,430
A lot of us spend hours figuring out how to do the analysis and then doing the analysis.

51
00:03:40,500 --> 00:03:46,200
We forget to put the time to organize our files and reports. Take the clutter away, give the data set

52
00:03:46,200 --> 00:03:50,190
structure. Make certain areas stand out and others not so.

53
00:03:50,190 --> 00:03:57,840
This makes it easier and more appealing for others to understand your file. And also for yourself

54
00:03:57,870 --> 00:03:59,910
when you come back later to it.

55
00:03:59,910 --> 00:04:06,900
Think of your file as your workplace. We are more productive when our workplace is clean and it's not cluttered.

56
00:04:06,900 --> 00:04:08,670
Do the same to your file.

57
00:04:08,670 --> 00:04:10,680
Take some time and clean it up.

58
00:04:10,950 --> 00:04:15,270
The other side effect of proper formatting is appreciation.

59
00:04:15,300 --> 00:04:16,170
Let me explain.

60
00:04:17,100 --> 00:04:20,140
Imagine your boss is coming over for dinner.

61
00:04:20,310 --> 00:04:23,260
You spend hours preparing a meal.

62
00:04:23,280 --> 00:04:30,000
You go to the farmer's market, you buy the best ingredients, make the best meal you've ever managed to

63
00:04:30,000 --> 00:04:31,230
make.

64
00:04:31,230 --> 00:04:39,480
Now when it comes to serving him or her, you just dump everything on a plate and you serve. Your boss

65
00:04:39,480 --> 00:04:42,690
will probably not be too impressed.

66
00:04:42,690 --> 00:04:49,890
It might still tastes amazing but they're not going to appreciate all the time you spent preparing it.

67
00:04:49,890 --> 00:04:53,530
Like the saying goes, you eat with your eyes first.

68
00:04:53,670 --> 00:05:00,870
So if you just spent a few minutes organizing everything nicely on the plate like good chefs do, your

69
00:05:00,870 --> 00:05:02,580
boss will love it.

70
00:05:02,670 --> 00:05:07,450
Put the same effort in your work. Organize before you serve.

71
00:05:07,710 --> 00:05:09,550
I hope you found these tips useful.

72
00:05:09,630 --> 00:05:11,100
Keep them close at hand.

73
00:05:11,100 --> 00:05:13,920
Refer to them when you create your next workbook.