7 Things You're Doing WRONG⚡in Excel

if you're here you probably use excel daily and you've developed your go-to routines now after a while it almost becomes natural and it's hard to imagine that you're not really doing it the way you should let's end that i collected a few things that many of us are consistently doing wrong i'm also going to show you then the better way of doing it let's take a look [Music] one common mistake is to create external links to files without intending to do that so if you go to the data tab here and you see that edit links is not grayed out and you click on it and you have a lot of links there that you don't need you need to clean those up this was a common thing at my previous company because we copied over many different worksheets from different files to new files and while we did that we brought over links that were still referencing the old files and we never intended to do that so how can you copy sheets the correct way well let's take a look at this example we have a start file here and i have a calculation sheet and a master sheet now in this calculation sheet we have formulas that reference that master sheet now i want to copy the calculation sheet over to another file that's called local file in this file i also have a master sheet and when i copy over my calculations i want them to reference this master sheet well let's see how we can do that i'm going to go back to the start file right mouse click on the tab here move or copy place a check mark to create a copy and then select local file so now this is going to bring over the calculation tab to the local file but the problem is the formulas are still referencing the old file notice it has the name of the old file in here if that's something that you intended it's fine in this case i don't want it to do that i want it to reference the master tab that i have here so of course i could go in and update this manually but assume you have a lot of different formulas here and you don't want to take the risk of doing this manually what you can do is go and edit the links you're going to change source and then select the local file as your source so the file that you copied it to we're going to select that as the source so basically it's going to be referencing itself and once you do that edit links is grayed out again because you have no more formulas referencing an external file if you wanted to remove the formulas completely you can break the links you can also do that right here under edit links next one is something you shouldn't do if you want to enjoy working in excel so it's more of a productivity tip let's say i'm writing out instructions for this page here and i notice it's getting too long i want to break it up into multiple cells so i'm going to go and say okay right after the two here i want to break that up that's this two right here so i'm gonna grab that cut it out then go to the next cell paste it in then take a look at where i need to do the next cut after this cut it out paste it in and then let's say i forget to write something here so this cell gets longer and i have to repeat that process no no don't do this what you want to do instead is this decide where you want that cutoff point to be for me is right after this cell just highlight the area until where you think your text is going to flow to and then go to home all the way on the side select fill and justify that's what you need everything automatically adjusts if you ever want to put all of this back just make your selection wide enough until where you think it fits in the first line go back to fill justify and everything is back next one is merging cells instead of centering them in the wrong places there is a place to use merge cells and that's usually on top of your report but you shouldn't be merging cells in the middle of your data sets so for example here i have 2019 20 and 2021 these cells are merged if i want to write a function here that sums up these values i can't just make a selection like this because the merge cells cause my range to automatically expand what i could do is to go in and manually update this to make sure that this is restricted or another way would be to manually make my selections basically just highlight this hold down control highlight highlight and so on now if you have a lot of rows and a lot of merch cells in between this is going to be a headache so a better way of handling this is to use center across selection so i'm just gonna unmerge this again just to go back to two separate cells what you're gonna do is select your range the same way you would do if you want to merge but instead of merging you're going to right mouse click format cells or use the shortcut key control 1 go to alignment here change your setting to center across selection and then click on ok and this is going to give you the exact same effect except that you still have two separate cells so i'm just going to copy this let's double click this apply it to this one and this one now if i write my formula again i can make my selection like i normally would and i'm not going to run into any problems now i have two tips for you here one is that you can add alignment settings to your quick access toolbar you're going to find it under format cells options when you click this it's automatically going to take you to here and you just have to adjust your settings and click on ok another tip is if you want to find out what cells are merged so let's just merge this one let's merge this one now you are looking for merge cells in your workbook you can use the find feature so just press ctrl f decide on whether you want to do your search within the sheet or within the whole workbook and then click on format here decide on the format that you're looking for here we just want to look for merged cells and then click on ok and then we can find next we're going to see this it's going to find the other one you can find all you can select them you'll see them selected here you can add a color just so you can locate them better close this and then go and update the formatting and make it center across selection next one is poor file structure i often see files where everything is condensed and thrown in the same sheet so we have the source data on the side we have calculations in the middle and then we have some graphics on this side don't put everything on the same sheet instead split it into separate tabs so you're going to have a separate dashboard or report tab you can have a separate calculation tab and a separate tab for your source data in case it's coming in from excel so in this case the source data is coming in from power aquarium from the data model so we don't keep it separately in excel but we keep the calculations separately then these flow in to the dashboard side you might want to add instructions and a tab where you control the changes that you make to your file you can also add different colors to make things easier to find or as a way of visually grouping certain tabs together next one is about hiding rows and columns in the wrong places so generally it's better to group rows and columns instead of hiding them why well let's take a look at this example so let's say you received this from your colleague and you quickly want to get the total value for sale so you just jump to the bottom here and you do a quick sum let's just highlight everything and grab our number and then we report this to our boss problem is this number is wrong why because if i select these numbers the sum of these equals 167 000 not the number that i have here why am i seeing this well my colleague has hidden some rows here if i select this right mouse click and unhide that's what they've done they've actually done a data preparation for their chart on the bottom of this data set i didn't see it because i didn't really have any visual indication that things were hidden if you look carefully you will see it so if i go and hide these again notice we see these two very light lines there this is a visual indication that something was hidden so if i also select all of these you can see that better a better visual cue is to group cells instead of hiding them so let's just go ahead and unhide to group this information together you just need to select it and use the shortcut key shift alt the right arrow key you can also add sub groupings just make your selection shift alt right arrow key you can get to it from the menu as well if you go to the data tab under outline here you have group and ungroup there is a shortcut key to ungroup as well it's shift alt and the left arrow key to ungroup these shift alt left arrow key the grouping makes it more obvious that something has been grouped together because if i collapse this i can see that box here and it makes it more obvious that something is hidden than just those thin double lines there now a better practice when you're doing such an exercise is anyhow to split your calculations in a separate tab than to have everything in the same tab next common mistake is to input values or hard code values in your formulas this is especially difficult to track in the more complex models so for example here i have a financial model where i'm deciding on whether it's worth it to invest in a new printing press or not down here i'm using a discount factor now i expect this discount factor to flow into my model but if i click on the side here it's not using it six percent has been hard coded in this formula so maybe someone came in and decided to test stuff out and they just input directly the value in the formula now these are difficult to track one way to see where this is flowing to is to just select a cell go to formulas and click on trace dependence so now i can see the discount factor so this cell is going in to this calculation here it's not going into here i can also do it the other way around i can go to this cell and trace precedence this one is not going here let's go and remove the arrows so the right way of doing this is to have a cell where you input your value in this case eight percent and then we're gonna do a cell reference here so we're gonna reference this one and since i wanna drag the formula i'm gonna fix the referencing by clicking on f4 and now we can drag this to the side and now everything is consistent so if i want to change this i'm going to update this to 6 percent and everything updates accordingly now if you have values that don't change so for example you want to divide something by 7 because you have 7 days in a week or by 24 because you have 24 hours in a day those ones you can hard code in your formulas but values that might change like a discount factor here is something that you want to have in a separate cell and then make sure your other formulas are referencing that cell next one is not taking advantage of tables so if you have a data set like this turning it into a table is what you need if you want your chart to update automatically once you get new data if you want your formulas to update automatically once you have new lines added and if you want your pivot tables or your formatting to update automatically as well if you don't turn this into a table you have to make those updates yourself manually which you don't want to do so let's turn this into a table by just going to home and formatting it as a table you can also use the shortcut key ctrl t here you can decide on the type of format that you want let's say i'm going to go with that it's going to ask me if my table has headers in this case it does i'm just going to go with ok and the formatting is automatically applied if i'm not happy with this formatting and i want to create my own just select none and then apply the formatting that you want so let's say i want to get a light blue for the headers and i want this column to be light gray now the moment i get more data added to this the formatting is going to come with what about the formulas if i have a formula here like account if formula i'm just going to count the division column so the moment i select division notice i get table referencing my criteria here is game right now what should i get one two three four right the moment i add a new game underneath this table and my table expands it becomes 5. if i add another one it becomes 6. if you want to delete these table rows but you don't want to delete other things that you might have on your file here you just select the rows that you want i'm just going to select these two cells actually right mouse click delete and delete the table rows so not the rows of the actual workbook but the table rows notice these didn't get deleted and my table got smaller so this is where i know the table ends that's that little marker on the bottom right hand side if you have a chart based on this data it's also going to update automatically so we can quickly check that let's insert a column chart here that last one is squids with 100 we don't see anything let's just increase that to 3000 now i'm just gonna add a new one here i can automatically see that let's put 5000 and my chart updates automatically i don't have to worry about expanding chart ranges it's done because i'm using a table okay so that's my list of common excel things a lot of us go about the wrong way and my suggestions on how you can improve so i hope you found some new techniques here let me know in the comments if you enjoyed this video hit that thumbs up subscribe if you aren't subscribed yet and i'm gonna see you in the next video [Music]

Channel: Leila Gharani

Views: 959,927

Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, XelPlus, Microsoft 365, Excel 365, excel mistakes, excel mistakes to avoid, excel tips and tricks, excel tutorial, excel merge cells, excel edit formula links, how to

Id: cnCvn6CmP2k

Length: 15min 21sec (921 seconds)

Published: Thu Jan 20 2022