Excel Tips and Tricks

you can move a column simply by dragging and dropping you can sum up cells with a simple shortcut key you can extract information out of a column with just the click of your mouse hi everyone kevin here today we’re going to look at even more excel tips and tricks just like these and i’m sure you’ll likely find a new one that you haven’t seen before to follow along i’ve included a sample workbook down below in the description alright let’s check these out this brings us to the first tip you can move columns simply by dragging and dropping here i have the customer name and i want this to be the first column in this table now i could insert a new column then i could copy all these contents cut it paste it and then i could delete this column but that’s a lot of clicks and on the kevin stratford youtube channel we don’t like clicks so instead we can highlight all the contents of this column and here i’ll hover over the edge of the selection and there you see that it changes to an arrow icon now i’ll press the shift key together with my left mouse button and now i can drag this column wherever i want it i don’t place it all the way over on the left you can also do this with multiple columns here for example i’ll select multiple columns and do the exact same thing here i’ll move all of these over and that works just as well you can also do it with just individual cells here i’ll select two cells hover over the edge and then once again shift and left click and here i can drag it up or down i can also use my right mouse button to do the exact same thing but it requires a few more clicks here for example i will highlight the phone column i’ll hover over the edge now i’ll press my right mouse button and here i can drag it to a new position and this opens up a context menu and down here here i can shift right and move and that once again moves that column and if you notice with that context menu i had a few other options so let’s see what those do here this column header has no formatting i could also move over the formatting i’ll select zip hover over the edge and with my right mouse button drag over to country and here i have the option to move over just the formatting that’s exactly what i wanted over here i have the customer names and i’ve used various functions to clean them up but i just want to keep the customer name here i could highlight all the customer names i could hover over the edge right click drag over drag back and here i have the option to just copy over the values and all of those functions now disappear tip number two i want to sum up various cells in just an instant and here we have cookie sales by customer at the kevin cookie company and i want to know how many chocolate chip cookies did we sell across all of our customers and if you know excel there’s a function that does that you can type in equal sum open parentheses and i could highlight all these cells close parentheses hit enter and that gives me the sum but once again we don’t like clicks here so how do we make this easier well instead i can press alt and equals and then i can hit enter and i have my sum so alt equals is the shortcut key for summing now here i could also select all of the cells ahead of time and then i could press alt equals and that just gives me the sum i’ll delete these two values now i could also highlight three columns and here i’ll press alt equals and that gives me the total across all of them so even easier now you might be wondering well this works top down but does it also work left to right and it sure does here i can press alt equals and that also gives me the sum now let’s say i want to get the sum across all of these columns and all of these rows you guessed it you can also use alt equals here i’ll select all of these cells along with the column in the row where i want the totals and once again let’s press alt equals and look at that my job has just gotten so much easier this brings us to tip number three you can perform calculations without formulas here we have profit by customer and year at the kevin cookie company and hopefully you notice this almost immediately but obviously this profit’s wrong i mean we made way more than this i mean this this is in no way our profit here it’s it’s way more it’s probably at least 10 times as big so let’s say i want this all to be 10 times bigger so how would we do this well one way you could do it is you could enter equals take this value multiply by 10 and i could basically recreate this table multiply everything by 10 and do all that but once again that’s a lot of work and i can copy and paste this in but it takes a lot of clicks so instead if we want to make everything 10 times bigger let’s enter a 10 here and then i’ll click on this cell and let’s copy that next let’s highlight all of these cells within the table then click on home and here click on paste paste special and right here in this dialog you have different operations so here i can add 10 to every single value that’s highlighted or here i can subtract 10 from every single value that’s highlighted i could also multiply and divide now i said our profit was 10 times greater than what’s shown here so i also select multiply and then click on ok and now it’s multiplied everything by 10. now instead of going through that menu and clicking on paste special i want to show you an even easier way to do it so here once again i’ll copy 10 highlight all of these and a quick shortcut to get to that paste special menu you can press control alt and v and that opens up that same dialog and here i can click on multiply and it still does look kind of low so maybe i should go through and let’s multiply this by a way bigger number and that will accurately reflect our profit tip number four excel now makes it really easy to get a unique or distinct count here at the cookie company we had lots of orders and each order contained cookies but i want to know how many unique or distinct cookies do we sell here at the cookie company excel now has a function that does this let’s click on insert function right up here and let’s type in unique that’s the name of the function click on go and let’s select this function here it asks me for the array or basically the range or array from which to return unique values so here i’ll simply select this entire list and then i have two other parameters that i can pass into this function but they’re optional so i’ll just leave them as is and then click on ok so here now i can see all the unique types of cookies that we sell here at the kevin cookie company now i wanted a count of how many unique cookies we sell so i can combine this with another function there’s a function called count a and that counts all of the non-blank rows then i can insert a parentheses and then i can close the parentheses basically i want a count of all the unique items that we got back here then i could hit enter and here i see that we sell six types of cookies here this brings us to tip number five and this is especially helpful if you happen to write complicated functions here for example i wrote a function that pulls out the domain name from an email address and here down below you can see what the function is to do that it’s a little complex i have a function and then two other functions within it so how does it work well we can use a very easy technique to understand how it comes together when i have this cell selected right up here in the formula bar we can see what the formula is and let’s say i want to understand well how does this find portion of the function work well here i can click on find and here i can click on the function helper and this will open up the function arguments just for the find function so here i can see what it’s looking for within what text and i can make sense of how this portion works here i’ll click on ok then i can click on len here i can click on the insert function helper and then here i can understand just how this function works and what arguments i need to pass in and here i can click on right click on insert function and i can see just how this portion works and also for this function i can see what the result is now another way to see the results of just a portion of the function here i can highlight just one portion and then i can press the f9 or the function 9 key to give me the results of just that portion here i’ll press control z or here i can select the len portion i’ll press f9 and i could evaluate just that portion of the function or here i could select the entire thing and press f9 and evaluate the entire thing so these are some quick techniques that you can use to understand complex and nested functions tip number six you can make your own custom list in excel so here we see the month of january and here i can click on this fill handle drag it down and i can fill out all the months of the year that’s pretty cool also here we have the abbreviation for sunday here i can click on the fill handle and i can very quickly populate all the days of the week and over here we have chocolate chip and you might be wondering well this is what does this do well we sell cookies here and here if i drag down i can populate all the different cookies that we sell here at the kevin cookie company so you can create your own custom list and it’s actually pretty easy to do click on the file menu then go down to options within options let’s click on advanced and then go all the way down to the bottom and here you have the option to edit custom lists here you’ll see some of the lists that we already looked at you have sunday monday tuesday you have all the months of the year and down below here’s my list that i created with different cookie types you can click on new list and here you can even select values from an existing sheet to make a new list and then click on ok then click on ok again and now you have your own custom list that you can use and the neat thing is you could also sort by your custom list so here we have a whole bunch of orders and they’re currently sorted alphabetically i could click on data and then here i could sort alphabetically in ascending order or i could sort in descending order but let’s say i want it sorted in this specific order i can do that i’ll click on data then let’s click on sort and over here i want to sort based on the cookie name but instead of z to a or a to z i can select custom list and here’s my list that i created i’ll click on ok and okay and now it’s sorted in this exact format tip number seven you can make it easier to write formulas by using named ranges here for example i want to calculate the tax that we had to pay on the profit for all of these different cookie sales now here i could enter a formula i could type in equals and let’s take the profit here and then multiply it by the tax now i probably also want to make this an absolute reference so here when i copy over the formula to these other columns it’ll continue to work and that works but the formula is not really that clear and it takes a bit of effort so instead here i’ll click into the tax rate cell and then i can click up here and i can call this tax rate so i’m giving this cell a name now i can click on enter now instead i can click equals select 14 and then i can multiply it by the tax rate look at that now let’s say i forget what the name is here i could enter equals select this cell multiply and then i can press the f3 key that pulls up the name manager and here i can see that i’ve defined one name the tax rate i can double click on that that inserts it into my formula i can hit enter and then it calculates the profit i can also add several items to the name manager here for example let’s say i want to add each quarter’s profit to the name manager so i could reference this anywhere in my workbook here i can select the header and also the values then i can click on formulas and here i can create from selection and here the name is in the top row then i can click on ok and now if i want to let’s say reference the first quarter profit i can enter equals and then i’ll press f3 and here’s the q1 profit i can click on ok hit enter and then i can reference this anywhere in my workbook if i want to manage all of these different names that i’ve created i can click on formulas and there’s something called the name manager when i click on that i can see all of the different defined names i could double click in to edit it or i could delete the different items here tip number eight you can rely on the excel status bar for some quick metrics here we have orders at the kevin cookie company with the order totals and i want to know how much total revenue did we collect how many orders were there what was the average order revenue all that kind of stuff now i could go to the bottom and i could enter in formulas but that’s going to take a little bit of time instead here i’ll select this cell and press ctrl shift and up arrow that way i could select just all of these cells now if i look down on the status bar i can see that we had about sixty six thousand dollars of revenue we had about fifty orders and the average order size was about thirteen hundred dollars i can right click on this and if i scroll down a little bit here i could also add the min i could add the max the numerical count and i can add all of these metrics now when i click back here you’ll see them all in the status bar and this is a new one now i can click on the status bar to copy this value now i can click into a cell and paste and this is now pasted in the sum so once again a neat way to not even use formulas to get some quick metrics tip number nine is flash fill so what can you do with flash fill well you can extract information from a column or you can combine various columns together let’s see how you can do this here in this first column i want to pull out the first name so here let me type in the first name i want to give excel an example then i’ll hit enter now i can select this cell go up to the data tab and then you’ll see an icon with a flash or lightning icon this is called flash fill there’s also a shortcut key control e so here let me go back and let’s try the shortcut key i’ll press ctrl e and there it takes the model of what i’ve entered and it applies it to all of these other rows and there i just get the first name out now what’s cool is you can also format it in different ways so let’s say i want the last name comma first name then i can hit enter and it follows that same format for all of the other entries you’ll see that it’s not always perfect here for example it has the fifth and it includes that as a last name so you’ll have to watch it just to make sure that it’s working how you expect it to i could also use flash fill to combine different columns so here i want to pull the names together so i’ll type in kevin stratford now you could use a formula to do this but that’s a lot of work instead i’ll select this cell press ctrl e and check that out it couldn’t get any easier than this this brings us to tip number ten you can very quickly enter a formula or function into multiple cells at once here i have an investor meeting coming up and i need to tell them how many cookies we sold by market and i don’t have that data so let’s see if i could use a function to do this here i’ll highlight all of these different cells and we can use a function called randbetween i just want a random number and here for the bottom let’s enter in 500 and for the top value i’ll enter a thousand then i’ll close the parentheses now if i press enter right now the function will only show up in the cell that i’m currently in but if i press control enter that automatically applies it to all of these selected cells and look at that i’m now ready for this investor meeting next i want to show you a really creative way you can use control enter down below i have a list of customers and all of their different orders so here tray delicious they had a number of orders abc groceries had a number of orders but if we look over here we didn’t enter the customer name for every single row and i want to fill that in now of course i could select this customer i could use the fill handle and i could fill that in i could do the same for abc groceries and i could go through this whole list but now imagine we have thousands of customers which of course we have at the kevin cookie company this is of course just a subset of our data now we could use that same control enter to fill in all these blanks here i’ll select all of these different cells let’s click on the home tab go over to find and let’s go to special here i can select all blanks then click on ok and now all of the blank cells are selected and i want this first blank cell i just want it to equal the cell above it so here for the formula i can enter equals and then i’ll select this cell now it’ll apply this formula relatively so here the next cell will look at the one above it now i can press ctrl enter and look at that it filled in the name for every single row and here once again you can see it looks at the cell above here it also looks at the cell above so that works exactly how i want it to now let’s say i wanted to go back to the original state well once again i can highlight all of these cells then i’ll click on home here i can click on find once again and this time i can select all formulas and here now we see that all of the formulas are selected so these are all the cells that look at the cell above it and then i can press the delete key and we’re right back to where we started this brings us to the very last tip of today and it’s the quick access toolbar here i have a table and let’s say i want to sort it in maybe ascending order now of course i could click into the table i could right click i could go to sort and here i can now sort it alternatively i could also click into data and here too i could also sort this table but either technique requires me to either right click or i have to click into a certain tab and then i can find my command and if i end up sorting all the time maybe i want to pin the command so it’s easier to access in the future and i could very easily do that here for example i could go up to this command which i use all the time i could right click on it and then there’s the option to add to quick access toolbar i will select that and here now you see a toolbar appear and i could go through any action on the ribbon and i can add it to my toolbar let’s say i want the name manager i can add it to the quick access toolbar and there it is i can click on this drop-down list and here i can add some of the most common actions to my toolbar and if i go down i have some other settings for example i could show it above the ribbon if i don’t want it to use extra screen real estate and i have a few other options as well right here i can also view more commands and here and here i have pretty much any command in excel i can add it to my quick access toolbar so this is a really easy way so if i want to sort this data it doesn’t matter what tab i happen to be on right up here i have my sort controls and i can do this with any control in excel alright well those are the excel tips and tricks let me know down below in the comments did you learn any new ones to watch more videos like this one please consider subscribing and as always i’ll see you in the next video [Music] you