Top 20 Microsoft Excel Tips & Tricks

hi everyone kevin here today i'm excited to show you my favorite top 20 tips and tricks in microsoft excel feel free to use the timestamps down below to jump to the section that interests you the most otherwise why don't we jump on the pc and get started tip number one you can take a photo with your iphone or your android phone of data and then you can convert that into microsoft excel to be able to do this click into the app store on an iphone or the play store on an android phone and download an app called microsoft office once you download the app you'll have an icon on your phone with the office logo let's click on that this opens up the office app and in the bottom right hand corner click on the button that says actions within all of the actions click on the one that says image to table next you could take a photo of a table with data i'm going to select this sheet of paper and take a photo of it next i can select the data that i want to bring into microsoft excel i'm going to select the first three columns and once i'm all done click on confirm next i see a screen that shows me the photo that i took and i see a table down below showing all of my data digitized next in the bottom left-hand corner let's click on open check that out all of my data has been successfully imported into microsoft excel i have my date impression and clicks column this is now in excel i can save this excel sheet and i could also open it on my desktop tip number two you can get excel on the web entirely for free head over to the website office.com and log in with your microsoft account if you don't have an account don't worry you can create one for free by clicking on this link once you sign into office.com over on the left hand side there's an icon for excel let's click on this this opens up the excel start page and here we can click into a new blank workbook alternatively you could also start from a template this drops us into a new microsoft excel worksheet and we have the most commonly used controls directly here in excel on the web tip number three you can use ideas in microsoft excel to get interesting insights on your data here i have a whole bunch of data i could analyze it on my own or i could simply rely on microsoft excel i'll click here within the data and then on the home tab on the home ribbon let's go over to the far right hand side and click on ideas this opens up a pane on the right hand side and you can see that microsoft excel has analyzed the data and it's created all these interesting views that look at the data for instance here i see that the united states accounts for the majority of sales if i want to keep this insight i could insert it into my sheet up above what's interesting as well is i can ask a question about my data here i can see some example questions i'll go ahead and type in a question i typed in what cookie type has the lowest profit when you type in your query you just have to make sure that the words that you refer to match the different column headers here for instance i have a column header called cookie type and i have another column called profit if i click on enter this will now show me the result of my question how cool is that here i could see that snickerdoodle has the lowest sum of profit tip number four you can define names for cells instead of using the letter and number combination to make it friendlier to read and understand here in this example sheet typically if i'd want to calculate profit i'd insert an equal sign and then i would say d2 minus e2 when you look at this formula unless you can see the sheet and see the references it won't make that much sense instead what i could do is i can name the different columns that i want to run this formula on to do this let's highlight all of the revenue numbers and then if we click on the formulas tab it'll open up the formulas ribbon and in the middle we can define a name let's click on define name here it automatically detects that the column header is revenue let's go with that next i can also highlight my cost column and i'll define a name for this as well here it selects the name of the header and let's click on ok now if i go back to my profit column instead of entering in the cell values i'll type in equals and now i could type in revenue and that's referring to this column and next i'm going to subtract the cost look how much friendlier that formula looks now that i select this it also fills it all the way down and when i click on this column i see the formula up here is simply revenue minus cost that's so much easier to understand when you use names for cells tip number five i want to show you how you can master absolute and relative cell references as a quick refresher on what a relative or absolute reference even mean let's take a look at this sheet here i want to calculate the profit to calculate the profit once again that's the revenue minus the cost i'll select cell d2 and subtract cell e2 i've now entered in my formula and i can drag the formula down and here if i go to cell g7 you'll see that the formula automatically adjusted here i went down a few rows and here the formula takes that into account that's called a relative cell reference and as i move the cell the formula will also adjust let's say though that i want to calculate the sales tax the sales tax is 10 but that's a fixed value if i use a relative cell reference let's see what happens here i'm going to select the revenue and i want to multiply that by 10 percent here if i just use the formula as is and copy it down you'll see that in the very bottom cell it's also moved the sales tax i want to use an absolute reference so it locks to this cell to do this i want to show a quick shortcut key that you could use to make this so much easier if i go to the formula here where i see the reference to the sales tax in l1 i can press the f4 key function 4 and it adds a dollar sign in front of the letter and the number it is now an absolute cell reference when i hit enter and i paste this down if i go to the last cell now you'll see that it's locked it to this 10 percent this is an absolute reference and using the f4 key makes it really easy to set this in place what you can do as well when i go up to l1 over here if i press f4 again here it'll toggle it so only the row is locked if i press it again only the column is locked and if i press it again i'm back to the initial formula which is a complete relative reference tip number six i want to show you how you can record and use macros like a pro what are macros will they help you automate repetitive tasks here for instance i want to add some additional countries to my sheet and those additional countries will sell all these same types of cookies now i could type in the country name and then i could go in i could copy all the cookie types i could paste it and then i could copy down the name but this takes a lot of manual steps i want to use macros to help me with this so instead let me type in another country name italy and this time we're going to record a macro to record a macro let's click on view on the top toolbar and all the way over on the right hand side let's select macros within the menu let's click on record macro and we also want to make sure we check the box that says use relative reference just like we learned in the previous tip with a relative reference the macro will run from whatever cell you happen to be in let's click on record macro we can now give the macro a title i'm going to call this create new country once i finish entering the name you can also assign a shortcut key to the macro you can also define where you're going gonna store the macro i'm gonna leave it in this workbook let's click on ok now the macro is recording so let's fill out our steps first i'm gonna copy the country name and i'm gonna go in and paste it six times next i wanna copy all of the cookie names from above and i'm going to paste it down here i'm now done running my macro so let's go up to the top and let's click on stop recording basically this is excel remembering all the steps that you run through now that i've recorded all the steps i want to add an additional country and instead of going through and manually copying and pasting the country name and then all the cookie types i'm going to run my macro once again let's go up to macros click on view macros and i can now run my macro check that out it has now automated all of those manual steps that i took this is just a simple example of how you can use macros you can also use it for far more complex examples tip number seven i want to show you how you can use data types in microsoft excel to use data types up on the top ribbon let's go over and click on data and right in the middle there are data types currently excel has two there are stocks and geography but coming soon there will be far more data types available so what can you do with the data type well here down below i have three countries or geographies and i could pull in all types of data related to these so here for instance i'm going to highlight these three items i'm going to go up to data types and click on geography it's now converted these to a geography data type and you'll see this icon appears next to it next i could click on this plus icon and i could pull in all types of information related to these geographies for example i could pull in the population i could also pull in the largest city along with that i can also pull in the unemployment rate there you see with these geographic entities i could very quickly pull in related data next i want to run through some of my favorite functions in microsoft excel and the first one is x lookup if you've ever used vlookup before or hlookup before xlookup replaces both of those so what does xlookup do well just like the name implies you can look up data here i have a table with a bunch of data related to different countries and different cookie sales i want to list out the store manager based on the country i have another table down below that has the country and the manager i want to pull the manager from here and insert it up here and x lookup will help with this to use x lookup let's type in equals and first off type in x lookup and then open parentheses first i need to define what value i want to look up i want to look up the country so let's click on country next let's enter a comma and now i need to specify what my lookup array is this is basically where i'm doing my lookup and i want to look over in this table down below here i'm going to make it an absolute reference next i need to define the return array so basically what happens is if it finds the united states down here in this selection what is it going to send back and i want to select this column right here with the manager names here too i'm going to make it an absolute reference the reason i'm making it an absolute reference is as i paste this formula down i want it to continue looking just at this table right here i've now specified my return array next i can say what happens if nothing is found i'm going to insert quotes and i'll simply type in not found next i'll enter another comma and i could define the match mode i want it to be an exact match so i'll enter a zero basically if if i if it doesn't find an exact match for the country i don't want it to return anything now that i'm all done i'll hit enter and here you see that it pulls in heath chips because he's the store manager for the united states if i copy this down now you see that for china it correctly pulls in oreo baker as the manager you can use x look up for all sorts of neat scenarios this is just a quick example of how you can use it tip number nine another one of my favorite functions is the sum function you can enter equals and then type in sum and you could very easily sum up all the values right here and that'll tell you the total of the column there is an easier way and i love this shortcut key instead you can press the alt key together with the equals key and that will automatically pull in the sum function and sum up all the values above you can then press enter as an alternative to some values you can simply highlight all the values and down in the status bar down below you'll also see the sum one additional way that you can sum values click on insert on the top ribbon and then insert a table and then click on ok this inserts a table and under table design i can add a total row now when i click down below here too from a drop down list i can select from a number of different functions i'm going to select some and there too i also have my sum tip number 10 i want to show you how you can connect or concatenate different values here i have cells a and b cookie monster kevin stratford excel tips and tricks and i'd like to bring them together in one cell i'm going to show two different ways you can do this you can use the concatenate function to pull these together you simply type in concat and here select concat and then you can type the first value i'm going to insert a comma because i want a space between them so i'll insert a quote space quote another comma and now i'll select the value in b1 and close my parentheses this now concatenates those values but there is an even easier way in microsoft excel instead i'm going to type in cookie monster because this is what i want it to look like in cell c1 now instead of entering a formula i simply drag this down now currently it says cookie monster if i click on this little icon i can apply a flash fill and here what excel does is it detects how i merge these two values and it does the same thing as i flash fill down so i don't even need to use a formula anymore to connect cells tip number 11 i want to show you how you can use sum if to only sum values if they meet a certain criteria here i have profit data by country so i see all my profit for the united states and i see all of my profit for china what if i just want to know the profit for the united states we could use some if to do this down in this cell i'm going to enter my formula and we're going to type in some if next i want to open my parentheses and i want to look in column a to see if it matches united states next i need to type in my criteria and i'm going to type in united states in quotes next i'm going to insert a column and now if it finds a match over here i need to indicate what i wanted to sum and i wanted to sum the profit so for the sum range i'm going to highlight this column next i'll close my parentheses and hit enter here now i see that the profit for the united states is just shy of 3 million tip number 12. and this is similar to some if but this time we're going to count if it meets a certain criteria here i want to know how many countries sell fortune cookies here i see all my countries and i see the cookies that the company sells in those different markets so here let's insert a formula i'm going to type in countif once we enter countif let's open the parentheses and here i need to define the range so i want to look across all the different cookies that we sell in each of these markets next i need to define the criteria and i want to look for fortune i'm going to insert quotes type in fortune and close my parentheses next let's click on enter here now i see that we sell fortune cookies in two different countries here i see in the united states and here i see in china and there's no fortune cookie in germany tip number 13 i want to show you how you can use the if function to run a logical test on the data in your excel sheet you can build some pretty complex if functions i'm going to start off with just a simple one here right here in my data i have all these different markets and i have the cookie types that we sell in those markets there's also a column with the favorability rating if the favorability rating is too low we need to investigate here we see that in china they don't seem to like our fortune cookies so up here let's insert an if function i'm going to type in if and then i'm going to open the parentheses first off we want to do a logical test so let's say that maybe if the favorability rating is lower than say 25 percent we want to investigate so i'm going to say if this cell is less than 0.25 or 25 then we are going to investigate otherwise if it's greater than that amount then we'll simply say all good now that i've typed in my formula let's press enter and here i see that this is 94 that's greater than 25 so all is good when i copy this formula all the way down here we see that for the 8 case we should investigate this tip number 14 i want to show you how you can calculate the difference between dates and then use the convert function to take it from days to years but you could use the convert function for a lot more than that here we have a store location that opened up on 1018 2017 and today's date is 10 22 2020 as an added bonus you can get today's date by typing in equals today open and close parentheses to calculate the age in days this is pretty simple we simply insert the equal sign and i take today's date and then i subtract the date that we open the location here i can see that the location is 1 100 days old let's say i want to figure out what that is in years the easiest way to do that is to use the convert function i'm going to enter an equal sign again and then type in convert with the convert function you could switch a number from one measurement system to another that's exactly what i need first i need to pick the number that i want to convert so i'm going to choose days next let's insert a comma and then i need to indicate what unit i'm converting from when i go down this list i want to select days next i want to convert it to years and here because i selected days it narrows down my set of options i'm going to click on year and now i can close the parentheses hit enter and i see that it's been open for three years here i could add some decimal points if i want to see the very precise age in years tip number 15 i want to show you how you could very easily adjust column widths and row heights automatically now here my data you see a few of the rows are a little off and a few of the columns are a little off now i could click between columns and i could drag it larger to make sure that the data shows up and it takes a lot of manual effort now i can also click in between and that'll automatically adjust it but i have a lot of columns to go through i also have a bunch of rows to go through instead if i want everything to simply fit i could click up here in the corner and that selects my entire sheet next i could click in between two of these columns and that automatically adjusts all the columns similarly i could click on the space in between the two rows and then this also automatically adjusts all of the rows tip number 16 you can freeze panes in your excel sheet here when i scroll down i lose the headers if i want to keep them up here i go up to view on the top toolbar and i can go over to this option that says freeze panes i can then freeze the top row so when i freeze the top row now my column headers stay i have other options as well i could freeze the first column and if i want to unfreeze the panes i simply click on unfreeze as an added bonus you can also select a row go to split you see it adds a bar there and then i can freeze those panes so it freezes it in that specific location and you could do the same with columns as well tip number 17 is pivot tables and pivot tables are extremely valuable in fact using pivot tables you don't have to enter as many formulas anymore in microsoft excel to insert a pivot table simply select anywhere within your data set and first off you could insert recommended pivot tables so here it'll pre-populate the pivot table for you if you want to just insert a pivot table on your own go back up to insert and then click on pivot table it'll select your data let's click on ok this now inserts a pivot table probably one of the easiest ways to learn is to simply explore and test things out i'll show a few quick examples i also have a more in-depth video in the description if you're interested here i could pull different fields down into these rectangles down below for instance i'm going to pull the country down into rows and here i can see how many countries that we're currently operating in if i want to see profit by country i click on profit and i can drag it into values so here i see in the united states we have just shy of 3 million and in china we're just short of 1 million i could also pull cookie in and then here i can see the country with the cookies and what the profit is i can right click on this and i can even sort from largest to smallest to see what the sales look like along with that if i click up here i can filter by specific countries if i just want to see china one of the nice things as well is you're not just limited to viewing your data in a table if i go up to the pivot analyze toolbar on top i could go over and i could insert a pivot chart let's insert this bar chart and here i can visualize my data this chart will update based on what i'm doing in the table pivot tables and pivot charts are extremely powerful and it's definitely worth exploring tip number 18 you can use drop-down lists to reduce errors with entering data here i want people to insert a store manager for these different locations i have a table down below and someone could come in and they could write the name out or they could simply select it from a drop down list how do we insert a drop down list well first off let's go up to the top and click on data within data let's go over to the data tools and click on the data validation icon this opens up a prompt and we can set it so we allow values from a list when we click on list we need to define what the list is let's click on this icon to define the list i want the list to be one of these two managers next i'm going to click on this icon i've now inputted the correct source let's click on ok now if i go up above i see that when i select this cell i can select from a drop down list and i can insert these values i could also click to drag down and this will apply the drop down list to any one of these cells and i can now select one of these store managers tip number 19 you can very easily pull data in from the web into your excel spreadsheet and your spreadsheet will automatically update to always have the latest data here's a table from wikipedia that i want to pull into microsoft excel how do we do this well let's jump back into excel back here in microsoft excel let's click on data on the top bar and then go over to the option that says from web this opens up a prompt let's paste in the url where we want to get the data from and then click on ok this now brings up a navigator and we could click through to see all of the data that it was able to fetch from this website here i see a table called varieties and it looks like this has all of the data that we're interested in there are a few issues though and there are a few columns that we don't need so let's clean it up before importing it by clicking on transform data if the data just looks fine you can also directly click on load for now let's click on transform data this opens up the power query editor and i can now make edits to the data before it pulls it in for instance i don't need this top row so i'm going to click on remove top row this now removes the top row i also don't need this flavor column so i'm going to right click here and then remove this all the rest of this looks fine to me so let's click on close and load here now you see that it imported all of the data from wikipedia into my excel workbook now one of the really neat things is when i go to the top ribbon i can click on query and then go over to edit and properties and here i could indicate how often i want microsoft excel to update this data for instance i could have it refresh every 60 minutes or i could simply set it to refresh the data whenever i open this file tip number 20 and this is the last tip of today i know sad i'm shedding tears over here on my end to collaborate with others let's go up to the top right hand corner and there's a button that says share if you save your excel file in one drive or in sharepoint you can very easily share your file with others when i click on share this opens up the share control and if i click here on top i could define whether people can edit or if they're only able to view it you can set an expiration date a password here too i could specify specific email addresses to share with or i could simply copy a link and then share it elsewhere once i share the file with others up in the top right hand corner i could click on comments and i could insert comments for the other people who i'm working with i could work with any number of people on this excel spreadsheet and we can all work together in real time all right that was a quick look at my top favorite 20 tips and tricks in microsoft excel down in the comments below let me know if there are any tips that you especially like or let me know if there are any other tips that you use that i didn't cover i hope you enjoyed this video if you did please give it a thumbs up if you want to see more videos like this in the future hit that subscribe button that way you'll get a notification anytime new content like this comes out and lastly if you want to see me cover any other topics in the future leave a note down below and i'll add it to my list of videos to create alright that's all i had for you today i hope you enjoyed and i hope to see you next time bye you

Channel: Kevin Stratvert

Views: 212,446

Rating: 4.9689779 out of 5

Keywords: kevin stratvert, excel, excel tips and tricks, excel functions, excel formulas and functions, formulas and functions, formulas, functions, function, formula, excel tutorial, excel tutorial formulas, how to use excel formulas, how to use excelo, microsoft excel tutorial, formulas in excel, excels basics, beginners, tutorial, how to, formula excel, learning excel, microsoft, microsoft excel, excel tutorials

Id: AOPa7ORJj4Y

Length: 25min 38sec (1538 seconds)

Published: Fri Oct 23 2020