Spreadsheet Formatting Best Practices3:59 with Michael Watson
Learn professional best practices for working with spreadsheets.
- you can continue to use the spreadsheet from the last video, or
- open a copy of this spreadsheet to catch up to this video
[MUSIC] 0:00 As you start with your spreadsheet, it's important to keep formatting and 0:04 organization in mind. 0:08 How much time you spend organizing and 0:10 formatting the spreadsheet depends on the answers to a few questions. 0:12 The first question you should ask yourself is, what's the meaning of life? 0:16 I am just kidding, that a fun question to ponder but for 0:21 what we are doing now is actually, how is this spreadsheet going to be used? 0:23 Is it a one off piece of work? 0:28 Or this is something that I expect to use in an ongoing manner? 0:30 For example, 0:33 doing calculations on how much I have to save to buy a car might be a one off use. 0:35 Tracking a monthly budget or 0:40 rolling forecast are examples of repeated use of spreadsheets. 0:41 For spreadsheets you will use frequently and for a long time, 0:46 you should put more thought into how you organize and format it. 0:49 It's an important spreadsheet. 0:54 So put the time in up front. 0:55 Another important question to ask is, 0:57 am I going to be sharing this spreadsheet with others? 0:59 Formatting becomes much more important when multiple people will be working on 1:03 this spreadsheet. 1:07 You'll want the spreadsheet to be clear, easily read and 1:08 understandable to everyone you share it with. 1:11 We'll talk about some best practices for 1:14 formatting spreadsheets in this part of the course. 1:16 Let’s start with adding color to spreadsheets. 1:19 Lots of newcomers to spreadsheets will start to use color 1:22 as an organizational formatting tool. 1:25 I can change the background of cells by clicking on a cell and 1:27 then choosing the color that I want to change it to. 1:32 So here I've selected a bunch of cells. 1:37 I'm going to make them all a light gray. 1:39 You can also change the color of text in cells. 1:41 I wanna make this text green. 1:48 That's scary. 1:52 So I'm gonna undo all the color changes I just made. 1:55 If you're collaborating, 1:58 make sure you are aware if any of your collaborators are color-blind. 1:59 Be sensitive to their needs and make adjustments accordingly. 2:02 Share your spreadsheet early to get feedback from others you work 2:06 with to make sure the colors make your spreadsheet easier to read and understand. 2:08 In my opinion, the two most powerful uses of colors in a spreadsheet are, one, 2:13 to signal hard coded or soft coded cells, and two, to highlight important data. 2:17 Here I've opened a spreadsheet example that has good formatting. 2:24 In the first case of using colors powerfully in spreadsheets, 2:29 that is to signal hard coded or soft coded cells, best practice is to use blue for 2:32 numbers that are manually inputted or hard coded and 2:37 black for cells that are referencing other cells with no manual input in the formula. 2:40 Here we have colored text that is manually inputted blue 2:45 and cells that are referencing others through formulas as black. 2:50 Most financial analysts will be familiar with this formatting practice and 2:55 sometimes expand on it. 2:59 So for example, if they're using an assumption and 3:00 they're not sure about it, they might color it red, instead of blue. 3:03 For highlighting important data, don't go crazy. 3:07 Simply highlight a row or a column or 3:10 a collection of cells showing the most important items on a spreadsheet. 3:12 So here we have highlighted this as the key takeaway, it's yellow, 3:16 there's not a bunch of color, it's just this one row. 3:21 Keep in mind when working with colors that too many colors can quickly become 3:26 distracting and difficult to maintain. 3:30 Let's look at example. 3:32 In this spreadsheet, there is lot's of different colors and 3:35 it's kinda hard to tell what they mean, what's the important hing that 3:40 I'm supposed to looking at here Sometimes people will use colors for 3:45 rows, they'll use colors for columns. 3:49 You're gonna have to use your judgment a bit here. 3:53 But just be cautious about getting too crazy with using too many colors. 3:55
You need to sign up for Treehouse in order to download course files.Sign up