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 could 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 in to 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 results 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 okay. 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%. Here, if I just use a 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 up to the formula here where I see the reference to the sales tax in L1, I can press the F4 key, function four 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%. 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? Well, 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 to store the macro. I’m going to leave it in this workbook. Let’s click on okay. Now the macro is recording so let’s fill out our steps. First, I’m going to copy the country name and I’m going to go in and paste it six times. Next, I want to 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 could 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 a 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 XLOOKUP. 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 XLOOKUP will help with this. To use XLOOKUP, let’s type in equals and first off type in XLOOKUP and then open parentheses. First, I needed to find 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 needed 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 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 XLOOKUP 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’ll automatically pull in the sum function and sum up all the values above. You can then press enter. As an alternative to sum 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 okay. This inserts a table and under table design, I can add a total row. Now when I click down below here too from a dropdown list, I can select from a number of different functions. I’m going to select sum 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 Stratvert, 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 could type the first value. I’m going to insert a comma because I want to 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 filled 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 SUMIF 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 SUMIF to do this. Down in this cell, I’m going to enter my formula and we’re going to type in SUMIF. 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 want it to sum and I want it 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 three million. Tip number 12 and this is similar to SUMIF 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’s 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%, 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 10/18/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 equals sign and I take today’s date and then I subtract the date that we opened 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 equals 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 are 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 can click between columns, and I can drag it larger to make sure that the data shows up. That 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 can 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 can 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 the 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 dataset 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 okay. 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 could 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 could 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 dropdown 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 dropdown list. How do we insert a dropdown 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 okay. Now, if I go up above, I see that when I select this cell, I can select from a dropdown list and I can insert these values. I could also click to drag down and this will apply the dropdown 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 okay. 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 OneDrive 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 could 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 could 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. All right, that’s all I have for you today. I hope you enjoyed, and I hope to see you next time. Bye.