Speaker 1: If you've ever managed a project, you've probably received, in your opinion, stupid questions from colleagues that you could have easily found the answer to in your shared project spreadsheet. If you are one of those colleagues, you probably asked those definitely not stupid questions because the spreadsheet was badly organized and difficult to navigate. In this video, I'll go through 10 best practices for project management spreadsheets designed to be shared with others and can even download my Google Sheets template to follow along. Let's get started. Hi friends, welcome back to the channel. If you're new here, my name is Jeff. Come for the career tips and stay for the not so stupid best practices. There are a lot of things going on here. So I first go through tips you can use within tabs, then take a step back and recommend best practices on how to structure the overall spreadsheet. Tip number one, no matter how self-explanatory the content is, always include a sentence or two in the top left-hand corner explaining what the tab contains. Once a spreadsheet is shared with other teams, you don't know who might come across it. The way I think about it is if a new hire who joined yesterday opens up the spreadsheet, this tab, or even this one, will they be able to understand what's going on here without having to message me? So no matter how obvious it may seem to you, the project manager, always include a note explaining what's in the tab or instructions if other people are supposed to input information. On that note, tip number two, if there's a deadline for when action needs to be taken, make it large and obvious like this or like this. The nice reason being user-friendliness. The honest reason being, if a salesperson missed a deadline and complained they didn't know, you can just point to this and call them stupid. Just kidding, don't do that. Call them illiterate instead. Nah, I don't do that either. No, but seriously. Shared spreadsheet tip number three, hyperlink to specific tabs. If you click on the shared button here and add people, by default, they'll actually land on the first tab when they click into the document. What most people don't know is that different tabs have unique URLs. So if you wanna make sure someone lands on a specific tab, you can copy and paste this URL and grant them edit access. Pro tip, within a spreadsheet, if you hyperlink another tab's URL, Command C to copy, and then Command or Control K to hyperlink like so, you can actually just jump to that tab directly without having to open up a new window or something. Super professional. Spreadsheet formatting tip number four, for tables where you wanna be crystal clear on how it should be filled out, it's smart to include an explanations row, usually in light gray, italicized, and in brackets right below the header. For example, for this column, if you don't input your full corporate email, you're not gonna have access to the live stream link. When I have particularly low faith in another team, I include an example row in light orange, so there's no way they can screw up, right? By the way, I'm kidding with these jokes. I used to be account manager in the sales team, I drove my marketing teammates crazy, so I have the right to make fun of my previous self since I've graduated from being dumb. Okay, I gotta stop. The sales team is gonna kill me. Spreadsheet best practice number five, data validation is your best friend. For those of us not familiar, here's a simple example. In this status column, I just want three input possibilities. I highlight the entire column, data, data validation, list of items, not started, comma, WIP for work in progress, comma, and done for completed. Pro tip, click reject input and press save. Now, it is physically impossible for anyone to make a typo or error in this column. If I try to type in completed instead of done, it will not let me do that. Jeff, you're just a control freak, it doesn't really matter. The thing is, if another formula is dependent on the value of these cells here, perfect accuracy makes a huge impact and will save you from troubleshooting headaches down the line. But yes, I am a control freak. Speaking of perfect, pro tip, I like to add conditional formatting to status columns. So with this column highlighted, format, conditional formatting, format cells, text is exactly done, and make that green, add another rule, text is exactly WIP, work in progress, and make that yellow. Now, it's nice and tidy. Oh, pro tip number two, for data validation, it's always smart to add a catch-all option if you're worried about missing something. For example, here, I'm fairly certain in data validation, there are only four teams involved, right? But I've added an other option just in case. Shared spreadsheet tip number six, use formulas whenever possible, but avoid fixed numbers within formulas. In this very simple calculations tab, we want to forecast three possible scenarios, ad spend increase of 20%, 30%, and 40%. One way to do this is to obviously equal this number times 1.2, repeat this, but obviously this is very manual and susceptible to human error. A better way is to actually change the headers to the percentages directly, like so, equal this number, function F4 three times to lock the column C, multiply by one, plus this percentage up here, function F4 twice to lock the fifth row, close bracket, enter, and highlight this command R, command or control R to drag all the way to the right. And special shout out to my colleague Henry who taught me this trick slash stole it from Reddit. If you just double click the small square here, you can actually bring the formulas all the way down without having to drag them. By the way, if you want more practical productivity tips I steal from Reddit and or my colleagues, sign up for my No Bullshit Productivity Ping Newsletter, link down below. Best practice number seven for spreadsheets. Import data without messing with the raw input. So for this very realistic Google Forms, the responses are shown in this spreadsheet here. I don't recommend manipulating the data in this spreadsheet, in this tab, because you might mess up the raw data. Instead, use the import range function. I talk about this in my Google Sheets functions video, check that out, to import the data over and highlight in some way that there's an import range function in this cell. Because if someone overrides this by mistake, the entire thing breaks down. Now let's move on to the overall spreadsheet structure tips. To start off, you probably already noticed, but I have these empty tabs here, key info, working tabs, raw data, that acts as visual separators. And I combine these with a simple color code. It's a very small thing, but imagine you're in a video conference, there are a lot of tabs, and instead of saying, go to the whitelist tab, you can say the whitelist tab is in the green section. Spreadsheet structure best practice number eight is to highlight key information up front. A good way to think about this is, if I had to show my manager the most important information about this project in five minutes, what would I prioritize? In this oversimplified example, since this is an event, the number of signups in real time, and the topics to be shared are probably top of mind. For more complex projects and spreadsheets, it's good to have a read me first tab that clearly shows the project owner and breaks down instructions, definitions, and shares links to relevant files. Tip number nine, always have a separate section for raw data and even better, protect these tabs so only the core working group can make edits. You can do this by right click, protect sheet. You can enter a description that's optional and set permissions and select people who can actually make edits and click done. Best practice number 10 is more of a communication tip because a perfect spreadsheet is useless if other teammates can't easily access it or don't even know where to find it. So during team meetings, you wanna present the spreadsheet as you talk about the project to let everyone know it exists. And in follow-up emails and team meeting notes, always hyperlink the spreadsheet with the appropriate URL. If you don't know what I'm talking about, highly recommend you check out my video on how to take productive team notes at work. See you on the next video. In the meantime, have a great one.