Streamline Your Processes with Bilty Format in Excel for Sport Organisations

Bilty format in excel for Sport organisations

hey this is matt ting from peak frameworks and today we are going to be doing an excel tutorial where i teach you how to be slightly more efficient and effective at excel specifically we are going to be looking at the quick access toolbar which is really the very first thing you should be setting up when you are using excel to begin with i'm also going to be walking you through the formatting sheet that i have in every single one of my excel workbooks and it just makes me so much faster because i can pull different cell formats as well as sell colors from this sheet if you'd like to follow along you can download the excel workbook in the description below and now i'm going to jump into the corner of the screen to help you follow along so the very first thing we actually want to do is make sure our excel quick access toolbar is properly set up if this is set up then your efficiency by itself is already going to go up by you know 20 to 30 percent because you're going to be so much quicker and you're going to be able to access commands much more quickly than native excel allows you to do so the quick access toolbar is essentially this thing in the top left it allows us to essentially customize our toolbar so we can have the most frequently used features all up at the top and we can access these tools through what's known as alt codes and alt code is essentially a function in all windows products where you press alt and then these letters pop up now when these letters and numbers pop up it essentially allows you to toggle the next command simply by pressing a button so for example if i want to go to insert i can just press n and then if i want to make a chart i'll just press c and that brings me to the ability to create a column graph so very easily i can select all this do alt and c and that brings me to a graph it makes it much more quick now the main utility of this is it allows you to not use your mouse and if you're not using a mouse you can navigate through excel much more quickly as well and that's essentially how people in finance accomplish that task now i don't want to make a blanket statement that you should never use your mouse there are some functions like when you're making graphs as well as almost all the time when you're in powerpoint you want some control over your mouse but generally speaking alt codes are going to make you faster now the alt codes i would recommend are as follows they are font size font color fill color borders more borders and then size and properties and if that doesn't make a ton of sense to you it'll make sense as we go through the exercise but essentially you're going to be changing font sizes and colors all the time because you're going to be getting data from different files and different formats and then borders are just a very annoying task to do i think the number one overall excel formatting tip i can give you is to always do borders last and the reason i say this is because borders are kind of sticky if i'm constantly moving cells around meaning that the data is not yet finalized then i'm going to have all these random lines everywhere because the borders aren't properly kind of set yet now that might not be very clear yet it'll be clearer once we actually dive into the exercise so finally how to do this you can do alt f and then t or you can go to file and then options so that brings us to general we're then going to go to quick access toolbar right here and you're going to want to go from popular commands to all commands and then just look for these different commands right here i already have them added and you're probably gonna have to remove some inferior ones that they had by default but again i recommend these ones font size color fill color etc so i will also say that this cheat is heavily inspired by the investment banking training i did where we were essentially taught to format things as quickly as possible so we're going to try to do the same thing so on this first sheet and you can download this in the video description this is kind of the unformatted information so if you're going to go to the sec website or capital iq they might give you kind of this unformatted data it's our job to bring it to this formatted solution i would say this is much more ready to be presented if i press ctrl p you can see that this is nice and clean so now what i'm going to do i'm going to do alt w n that's going to create a new window and have this one on the right side solution on the right side so on the left hand side we have the unformatted exercise so this is what we're going to be working off of ideally getting to this point so the first thing we're going to do is get rid of the grid lines and it's finance convention to get rid of gridlines because it can kind of obstruct a lot of other borders or other formatting and it generally it looks unpleasing as most people would say so we're going to start by doing alt w v g that gets rid of grid lines and i think we can just work our way down sequentially so the first thing we're going to do is this border across so you're already seeing alt codes in action but i just do alt h b p and that is a quick way to get to the borders now you can either do this or you can do our alt code alt 4 and then p either way is acceptable both are about the same amount of speed next i'm going to use the quick access toolbar again if i press 1 it's going to bring me to this top this first command and you can see i don't even have to type anything more it already brings me to the font so what we're going to do i'm just going to check this size right here and you can see at the top it's 12. so i'm going to do alt 1 1 2. i'm going to bold it as well now the next thing i'm going to select all of these cells if i do alt 3 that brings me to the paint bucket and i want this dark blue now another way i can check this if i go into this cell i can do alt 3 and then if i click m for more colors it's going to bring up this color pane and it essentially tells me the color of the cell that i'm highlighted so i press ok and that's going to bring it into recent colors so you can see here it's now in recent colors next i'm going to keep just going down i'm going to do alt 2 to get into the font color and there we go now the next thing here we're going to do alt hbp again in order to get the top line we're going to bold this top row make another border line and you can see how essentially the typical format is to have a line right above a sum there's a line right above gross profit implying that gross profit is the sum most of the time for the big subtotals like gross profit ebitda etc we're gonna bold it and that's what we're doing here ebitda will again bold and margin looks fine dna looks fine ebit we will bold dbt will bold and you can see net income there is no net income so we'll have to do one formula and that's just going to be ebt summed with taxes one other small thing that you might not have noticed but there's a dollar sign row here now we don't want dollar sign rows on second rows we essentially only want them on the top row the main reason for that is so we don't have like dollar signs repeating all the way down so i'm going to take this correct formatting you can notice there's no dollar signs here and then paste it and what i'm actually going to do you want to do ctrl c and then you want to paste special now paste special is arguably the most important excel function that you should know most people use ctrl c and control v when they're starting but the problem with that is you're gonna carry all this other information every single excel cell has a bunch of data in it it's gonna have the format it's going to have the if it's a formula or not if it's a value or not so if you just do ctrl c ctrl v for example if i do this it's going to carry over the border it's going to carry over the formula so we need to do paste special to just paste exactly what we want so here we only want to paste the format we definitely don't want to paste the formula if i just do ctrl c ctrl v it blanks out because it's moving the formula as well so all i want is the format control c select this alt e s t this is one to remember so that's alt e s t and that's the format and i just quickly check to make sure that this cell also has appropriate formatting so for free cash flow i'm just gonna do some for these four cells so that brings me to free cash flow now i can tell that something is different because the numbers at the bottom are different they're extremely different so when i dive into the actual assumptions i can see that revenue growth is completely off and if i just type that in from 25 to 25 it matches completely so for free cash flow most of the time if the final sum is correct the rest of it is correct so you can see from 250 building to 610 that's the same in the solution and for these percentages percentages are a little annoying rather than do our own custom formatting again which we could do the problem is that it's a little bit more complicated to remember and that's why i have this formatting sheet so i'm just going to copy this percentage and then i'm going to paste it here and again i'm only going to do alt e s t so now you can see all the percentages and we want blue for inputs anytime there's an assumption we essentially want it to be blue that's also one of the main formatting rules we want blue for inputs and black for formulas as the starting things you should think about so we're not done yet this title is also bolded and i want to show you custom formatting for years now years are an interesting thing because they are both numbers but oftentimes in finance you're going to see that they have letters as well now the letter here means a for actual and e for expected so this would be in the future and that this would be something that's already passed now the best way to do this is press ctrl 1 to get back into format cells i do alt cc and i'll show you that one more time because when i press alt cc alt c gets me into category you can see anytime something is underlined that will tell me what menu i'm toggling for example if i do alt c that brings me to category if i do alt n that brings me to negative numbers you can see again right here this underlined letter tells me where i am in the menu it's the equivalent of all these letters popping up except in certain menus only the underline will show up so again i'll do control 1 alt c and then i can press c again to get to currency and this is kind of like a standard windows menu now if i press a letter it's going to bring me to the option that corresponds to that letter for example if i press d brings me to date and if i do c it's going to cycle through the c's so i'm going to do custom now what i want to do here is this is getting into excel custom formatting which we will also cover but for now i'm just going to teach you how to do this year function so we're going to do the number and that's going to have the entire year and then i'm going to do quotations a and what this is essentially saying is that the letter a is always going to pop up the end but it's still a number format now the reason why that's useful for years specifically is because if i do plus one it's still going to be a number if i just made it 2020a and i typed it in all these things wouldn't work and that means i would have to manually enter all of the dates and if i change the years in the future for some reason then it wouldn't be flexible so this is an extremely handy tool making sure that it's still a number so that's the number sign and then just the letter a the thing is that these years in the future are expected they're e so i'm going to do the same thing alt c c c number sign and then e and this is just much nicer now so now i'm going to ctrl b to just bold it and the final thing i'm going to do is create this dotted border so that's where this is really useful when i press alt 4 i am going to i think the border i want is actually not none of these default lines so i have to go to line style and then i'm going to select the second one here i think so just to do that again we did alt 4 and then i'm going to go to y because i'm going to line style so that essentially selects the line style and then i'm going to do alt for r and why did i do all 4r because that brings me into the borders pane and then to the right border so i'm doing a quick scan here and i think we've got all the formatting so far the last thing we want to do and i would say this is the thing that you can do to make your models look the most correct is to add this print formatting it doesn't make it any more technically correct but it's an industry convention and it just makes you look way more you know professional so what you're going to do is select the data that you want to cover in this rectangle then you're going to do alt prs then you're going to do one more which you don't have to remember but you're going to change the view alt w i and that brings you into this cool little print format view so what we just did finally is to make the print formatting a little more clean now the reason we do that is because when we print it it means it's going to print the selected information if i didn't create that if you had to show it to someone in real life if you had to print it out then it would show up with a bunch of kind of misaligned columns or maybe it would spill over to the next page so i think that's like the last thing you should do that kind of just makes it look a lot more correct and a lot more thoughtful so i hope this was a helpful tutorial i would at least recommend that you set up your excel toolbar like this it's going to make you so much faster and you're more than welcome to download this sheet as well i again use this in all of my models and it saves me a ton of time because i don't have to look up the colors i don't have to look up the specific formatting code and i can make a new sheet very quickly so that is it for this video if you would like to see more excel content can you please leave a comment below because i'm not 100 sure but i do appreciate you watching this video and i'll see you in the next one [Music] you