☑️ Top 30 Quick Excel Tips & Tricks for Beginners

hey everyone it's John here and in this video we're gonna take a look at the top 30 quick Excel tips and tricks for beginners now if you haven't already done so make sure you subscribe to the channel for future Excel videos like this one now let's get started in this tip we're gonna take a look at how we can create a drop-down list in Excel and this is going to allow a user to select values from a predefined list so here we've got a list of sizes and we want the user to be able to select from that list of sizes so let's create the drop-down list let's go up to the data tab and in data validation we have the ability to add a drop-down list so right now that cell allows any value to be entered but we're gonna limit it to a list of values and we can create a comma-separated list here by typing but we also have the option to get our values from a range in Excel let's do that and let's hit OK and you can see that we now have this drop-down arrow here and we have our values in there so we can select from those values so that's how we can create a drop-down list in Excel in this tip we're gonna take a look at the fill handle so the active cell cursor has what's known as a fill handle in the lower right corner and when you hover your mouse cursor over this your mouse cursor is going to turn into a small black cross and we can use this to fill down simple patterns of data in Excel so here I've got a series of dates increasing by one day we can fill down this pattern let's click and drag on the fill handle and that's going to fill in my series of dates here our series is increasing by one month you can click and drag that and fill that down here I've just got some numbers let's click and drag that and fill it down here I've got some even numbers and we can fill in that pattern as well we can use the fill handle to copy and paste down formulas so here I've got a formula that calculates the tax based on this amount let's click and drag the fill handle and copy that formula down now I'm just going to undo that another trick with the fill handle is you can double click and that's going to fill down to the end of your data here now we can also fill in de names so here I've got Sunday to start with and if I click and drag that down I can fill in all the weekday names we have the same option for months let's click and drag that down and we can fill in all our month names and we can also fill in patterns based on text in numbers so here I've got item one and if I click and drag that down I can fill in item one two three and four etc so the fill handle is a very handy feature in Excel it allows us to quickly fill data down based on simple patterns in this tip we're going to take a look at flash Ville so this is going to allow us to perform transformations on our data based on examples that we provide so here we've got a list of email addresses and let's say that we wanted to extract the last name from those emails we can do that pretty easily with flash Ville so let's provide the first couple examples and when Excel understands the pattern it's going to fill in the rest of the data for us so here we can see in light grey excels suggested values and we can accept those by pressing enter and there we go a quick and easy way to extract the last names from those email addresses let's just undo that we can also do the same thing from the Excel ribbon all we need to do is select our examples and the cells that we want to fill data in and then go up to the data tab and flash fill is right there and that's gonna fill in the values for us so flash fill is a very quick and easy way to transform your data by example and this tip we'll take a look at a quick way to email our workbook as either an excel file attachment or a PDF attachment so here's my workbook and let's say I want to email this to a colleague then the quickest way to do that is by using a command that we can add to our quick access toolbar so our quick access toolbar is up here and the quick access toolbar just allows us to quickly access some of our most frequently used commands and we can customize this so let's right-click on the quick access toolbar and select customize and here right now all the popular commands are being shown let's select all commands and if I click in here and hit E on my keyboard that's gonna take me down to the section where the commands start with you and if I scroll down a bit I'll find email let's add that into our quick access toolbar and also email as PDF attachment let's add that one as well and let's hit OK and we'll see those two new commands in our quick access toolbar let's try them out let's email this as an attachment so that's gonna create a new email for me and attach the workbook let's close this and let's try our email as PDF and again here's a new email and we've converted our Excel workbook into a PDF file and it's attached here and this tip we're gonna see how we can auto adjust column widths so that our data fits so here I've copied in some data and you can see that we have columns that aren't quite big enough so if I adjust this we'll see the data properly now we can actually automatically adjust our column widths so if we go up to this button here and click on that to select the entire sheet then we can place our cursor in between any of the columns and if we double click then that's going to automatically adjust all our column widths so that it fits the data exactly we can quickly add the current date or current time into a workbook using a keyboard shortcut so ctrl + semicolon is going to add the current date into the workbook and ctrl shift semicolon is going to add the current time into the workbook now these are going to be static values and they're not going to change or update but we can add dynamic values with formulas so we have a function in Excel the today function and that's going to return the current date and this is also going to update whenever the workbook recalculates now we have a similar function to get the time so we have the now function and that's going to return the current date and time into the workbook and again this is going to be dynamic and update whenever the workbook recalculates and this tip we're going to take a look at naming a range so this is going to help with making formulas a lot more readable so here I've got a tax calculation and if I press f2 you can see that it references that 7% tax value up there but if I'm just looking at the U formula and it's not very obvious what this formula is doing and so what we can do is use a named range instead so if I select this cell and go up to the name box here right now it says c2 that's just the cell address I can actually give it any name I want so I'm gonna call it tax rate and I can press ENTER and now if I rewrite this formula I can multiply that amount by my tax rate so I can reference this cell here and you can see that it's referenced by the name now and that's going to make the formula a lot more obvious and we can copy that down and we see that's a absolute reference now one thing we can also do is apply any new names to existing formulas so here I've already got the formula written and it's referencing that cell but it's referencing it with the cell address so I can come up to the formula tab and under defined names I can't apply names so I just need to select my tax rate and now let's take a look at that formula we can see that the tax rate name has been applied to that formula and this tip we're gonna see how we can add some data bars into cells so that we can visualize our numbers a little bit better so here we've got some revenue and I want to visualize these revenue numbers so let's select them and if we head up into the Home tab under conditional formatting we have data bars here and we have some different color options here let's try out this green color and we can see in cell we have a nice data bar representation of those numbers so here our largest number has a full bar and our smallest number here has a smaller bar now these data bars are dynamic so if these numbers do change let's just try changing one of these numbers here to something really large and we can see that all the data bars here are going to update based on the new numbers so that's a quick tip on how we can help visualize numbers by adding data bars under conditional formatting and this tip we're going to take a look at the quickest way to create copy of a sheet in a workbook so we can create a copy by right-clicking on the sheet and then selecting move or copy and then selecting create a copy and choosing the location and pressing ok but there's a much quicker method so if we hold the ctrl key and click and drag on any sheet then you'll see that the mouse cursor now has a sheet with a little plus sign on it and that indicates that we're going to create a copy and we also see a little black arrow indicating where we're going to place that copy and if we release and we get our copy of our worksheet so that's the quickest way hold ctrl and click and drag to create a copy navigating through an Excel workbook is a big part of using Excel and learning these keyboard shortcuts is going to speed that up considerably so the first keyboard shortcut we're going to take a look at is ctrl + + that's going to take you to the last used cell in a sheet so the last used cell is just the intersection of the last used row and column the next shortcut is control home and that's going to take you to cell a1 we also have some speedy shortcuts for navigating through data so we can hold ctrl + use the arrow keys to quickly navigate around data and that's going to take you to the last use cell in the data set we can use that same keyboard shortcut to quickly get to the next used cell so I'm just gonna press ctrl + right arrow and that's going to take me to the next used cell and if there is no next u cell it's going to take me to the end of the workbook so that's holding ctrl and using the arrow keys the last set of keyboard shortcuts is to navigate between sheets and if we hold ctrl and use page up and that's going to take us to the next sheet to the left and ctrl page down is going to take us to the next sheet to the right so those are essential keyboard shortcuts for navigating an Excel workbook in this tip we're going to take a look at how we can quickly add totals to our data using the autosum feature so all we need to do is select the area where we want to add our totals and I'm just going to hold ctrl and select this range as well and with our range selected we can use the keyboard shortcut alt equals and that's going to add in formulas to total up our values so if we look at one of these let's press f2 and edit the formula we can see that it's summing up the values above it let's press escape and if we take a look at this formula here let's press f2 we can see that it's summing up the values to the left now if we want to add other types of summary functions instead of a sum we can go up to the Home tab and the autosum feature is here and if we click on the arrow to the right we have the option to add averages counts maximums and minimums as well so that's the autosum feature it's a quick way to add totals to your data you can quickly see summary statistics for any range of values that you select so if I select a couple values here in this table then down in the status bar we can actually see the average count min Max and some and we can also customize what we see here if we right click on the status bar then we can turn on or off various summary statistics and then only those ones that we have selected will be shown down here so that's an easy way to see summary statistics for selected data in this tip we're going to take a look at how we can clear formatting from a range in Excel so here someone's made a mess of this data and I just want to clear out the formatting and start again I can select my range and go up to the Home tab and use this Clear button and then from here we can choose clear formats and that's going to get rid of all that formatting when you select a range of data in Excel you'll be able to access the quick analysis tools so this little icon will pop up and if you click on that it will give you access to various features in Excel like conditional formatting so we can quickly add various types of conditional formatting to our data we can create different types of charts or add grand totals to our data and also turn our data into an Excel table or a pivot table or add sparklines little mini charts to our data as well so that's the quick analysis tools which give you quick access to various useful features to help you analyze your data in this tip we're gonna take a look at how we can keep selected rows and columns visible in our worksheet at all times so if we have a large set of data like this usually we'll want to see the column headers at all times and if we scroll around in our data set they'll become hidden and in this case I want to see the first column here that contains the film title but if we scroll over then that's gonna be hidden as well so to keep these visible we can use freeze panes and first what we need to do is select the cell just below the row we want visible and just to the right of the column we want visible so for me that will be this cell here and then we can go up to the View tab and use the freeze panes command here now if you just wanted the top row or first column visible we could use these commands here but we're going to use this freeze panes command to keep the row and column visible and now when we scroll around notice our column headers are still visible and if we scroll to the right our first row remains visible as well so that's the freeze panes command to keep rows or columns visible at all times and this tip we're gonna take a look at how we can quickly transpose data so here we've got some sales figures and we've got months going across the columns and then the region going down the rows and let's say we wanted to reformat this so we want the months going down the rows and then the regions going across the columns instead so in other words you want to convert our columns to rows and our rows to columns then we can do that with pay special transpose so let's select our data and if we copy it with ctrl C or up in the Home tab we can copy there and let's go to a new location and up in the Home tab again let's paste and we're going to use the paste special option down here and we have a variety of options we want to paste everything but we also want to transpose our data let's press ok and there we go we have our months going down the rows and our regions going across the columns now there's also a really handy keyboard shortcut for paste special and that's ctrl alt V and that's gonna bring up our paste special dialog box and this tip will see how we can set a default save location for our excel files so this is a brand new workbook and I haven't saved it yet so if I go to save it fan I'm gonna be prompted to tell Excel where I want to save this and right now the default is set to onedrive but maybe I have a location that I'm always going to be saving my files and so I don't want to be navigating to that each time let's cancel this we can set a default save location by going to the file tab and going to options and under the save options we can set a default file location here and we can paste in our new location so let's say I want to save it in the demo file my desktop and I'm gonna enable this option to save to my computer by default and I can press ok and now if I try and save this then that's going to be the default location for me and this tip we're gonna see how we can avoid pasting blank values from a range so I've got two ranges here values and we have blank values in both and what I want to do is copy these values into the blank areas here in this range now if I just select this entire range here and copy and paste then I end up over writing this range so I've copied the blank values from this range into this range as well and that's not what I want to do so there is actually a command for us that we can avoid pasting these blank values so let's just undo this and instead what we're gonna do is go to the Home tab pay special and there's an option here to skip blanks let's choose that and press ok and there we go so you can see that we've managed to avoid pasting these blank values into this range here but we've still pasted our values that weren't blank into the range so that's pay special skip blanks and this tip we'll see how we can quickly create absolute references in our formulas so if I create a formula to calculate the tax so I'm going to multiply the amount in this column by the tax right here then this is a relative reference so if I copy this down and what's going to happen is the ranges that I'm referencing in the formula are going to move with the formula so the formula always references the range one cell to the left and two cells above let's just press escape and let's undo that instead what we want to do is create an absolute reference for our tax value and we can do that pretty easily so let's create our formula this times our tax rate and now to create an absolute reference for cell c2 I'm gonna press f4 on the keyboard and you can see that Excel inserts a couple of dollar signs so those indicate that the column and row are an absolute reference and won't change when I copy the formula around now if we press f4 again then it's going to create a partially relative reference so column C is relative but row two is absolute and if I press it again we get column C is an absolute reference and wrote to his relative reference and again we're back to the regular relative reference so that's f4 to create an absolute reference and now if we copy this down and we can see that this reference here has remained absolute now we can do the same thing when referencing ranges so let's just create a formula that references this entire range and again f4 will cycle through the various options for relative and absolute ranges and now if we copy this over we can see that that range has remained the same so that's f4 to cycle through all the relative and absolute reference options in this tip we're going to see how we can merge values and separate cells into a single cell so here I've got four values and I want to have these in a single cell so we can do that up in the Home tab under fill' there's an option here justify let's try that out and you can see that it's almost got what we want so it's added the first two into one cell and the last two into a second cell let's just undo this where we actually need to do is give Excel enough room in this column to fill the entire thing in one cell so let's select our values again let's go back to fill justify and try that out and you can see that Excel has added all those values into a single cell and it's even added spaces in between each value so that's fill justified to merge values into a single cell in this tip we're going to take a look at how we can quickly copy chart formatting from one chart to another so here we've got two different charts and they've been formatted quite differently so they've got different fonts and colors and this one's got labeling and this one doesn't and let's say we wanted to create this chart to match this chart then we can select this chart here and go up to the Home tab and copy it and then select our other chart here go back up to the Home tab and paste special and we can select just the format's and press ok and you can see that we've got our chart and all the formatting from the previous chart has been copied over into this chart here so we can use pay special to quickly copy chart formatting from one chart to the next excels ribbon commands can take up a lot of room on screen and if you want you can actually hide the ribbon so that you have more room to view your spreadsheet so you can do that pretty quickly with a double click on any of the tab headings up here if you double click that's going to hide the ribbon and of course you can double click to show it back again if you're in a workbook that has a lot of sheets then navigating between those sheets can be difficult but Excel has a handy feature that allows you to see all your sheets and then navigate quickly to any one of them so down here in the left hand corner here we have two arrows if we right click any of those then that's going to open up this window here that lists out all the sheets available and you can select any one of them and hit OK or just double click on it and it's going to take you to that sheet in this tip we're gonna take a look at you go to special command so this allows you to select cells based on certain criteria so for example we can select all cells with a formula or blank cells etc first we just need to select the range of cells that we want to look in let's select our data here and then up in the Home tab under find and select we have this option here go to special that's gonna open up this go to special dialog box and we can select various options from here so for example we can select all constant values let's try that out and that selects all the hard-coded values in that range for us let's select our range game we also have a keyboard shortcut control G that's going to open up the go to dialogue box and we can click on special from there and open up go to special let's try formulas and here are all our formulas in that range let's go back up to the Home tab and go to special and we can also select blanks this is a very useful command let's try that one out and it's selected our two blank cells in that arrange so that's the go to special command it allows you to select cells in a given range based on certain criterias like whether they are blank cells have formulas or constant values etc and this tip we'll take a look at using control enter to enter multiple values or formulas into cells so here we've got some sales data and we've got sales for vests in various different regions and the first value has been entered but the values underneath have not and we want to fill that down so we want vests to appear everywhere here and then gloves to appear everywhere here etc we can use control enter to quickly do that for us so first we're going to use our go-to special trick to go to all the blank cells let's select the entire range and let's use ctrl G and let's go to special hey let's go to those blank cells and now let's use a formula let's type equals and then let's just reference the cell above and instead of hitting Enter let's hit control enter and with all those cells selected it's going to add that same formula and all the blank cells for us now we can just copy and paste this as values let's ctrl C and copy ctrl alt V and paste those as values now we can do the same thing with values instead of formulas so here we've got blank cells and we want to add zeros in there instead of blank cells let's select our entire range ctrl G go to special it's like those blank cells and here I'm going to type 0 and then again ctrl enter and we're going to enter 0 into all those blank cells so that's ctrl enter for entering multiple values or formulas into selected cells in this tip we'll take a look at how we can show all the formulas in a sheet at the same time so if we head up into the formulas tab we have this option to show formulas and we can click on that and you can see that we can now view all the formulas simultaneously that are in our sheet and when we select any one of them we can also see the preceding values that this formula depends on now we can click this again and this will show values that the formula calculates to and we also have a keyboard shortcut for this so ctrl tilde will show the formulas hand control tilde game will toggle back to values when you press f2 you can enter edit mode and edit your formulas and while you're editing your formulas Excel is going to highlight the ranges of values that the formula depends on and you can actually adjust these ranges by clicking and dragging them so for example we can adjust this range by clicking and dragging any of the corners and we can change the size of that range and we can also click and drag any edge to move the range around let's press ENTER and we can copy that and you formula down let's press back to again and here we want to adjust this range to include April let's click and drag that over and increase the range and copy that formula down now so that's an easy way to edit your formulas you can adjust the ranges in those formulas by clicking and dragging them and this tip we'll see how we can highlight duplicate values in our data so first let's select our range of values and if we go up to the Home tab under conditional formatting we have highlight cells rules and we can highlight duplicate values let's try that here we have options to highlight either duplicates or unique values and then we can choose what formatting we want we can also apply custom formatting let's press ok and here we can see our duplicate values highlighted so we have Mazda and Subaru listed twice in our list now this is dynamic so if our data changes and our formatting is going to update to reflect that so let's say that we now have Ford here instead and both those values are going to be highlighted as well in this tip we're going to take a look at spark lines so spark lines are just tiny graphs that fit into a single cell and they're great for showing a trend in a set of numbers so I want to show the trend for this set of sales here let's select a cell to add our spark line into and if we go up into the insert tab we have a section called spark lines right here let's try inserting line and we need to pick the data range so that's gonna be our set of sales here and we want our spark line in cell oh three that's fine let's press ok and you can see that we have a little mini graph of our sales let's copy that down and when we have our spark lines selected we get a new tab in our ribbon so we have a spark line tab here and you can add a couple options so you can change the color of the spark line and you can also add markers so we can add a marker for the highpoint let's add that has a green marker and for the low point let's add that as a red marker and these are dynamic so if we change the cell size here let's increase that a little bit and those are gonna change to fit the new dimensions over the cell let's go back to our spark line let's try out the column graph and we also have this win/loss graph the data we have doesn't really work for this so let's go back to your column graph and if any of our data changes our spark lines are going to change to reflect that so let's try changing this value here and we can see these spark line graph updates to reflect that change so now we have our maximum value displayed right here so spark lines are a cool way to visualize your data with a miniature graph and this tip we'll take a look at how we can group sheets together to perform the same edit across multiple sheets so here I've got a couple sheets set up and they're set up very similar except for they have different data in them and I want to change all three of these in the same way we can do that by grouping the sheets together first so if I select the first sheet and hold ctrl and select the other sheets Dan I can make the changes in the current sheet so let's change this title from January to sales let's also copy this row down and change this to central and I'm going to delete this value here and let's add a total press ENTER and let's also fix these row heights here now let's take a look at the other sheets you can see that that change has been made on those sheets as well now we can ungroup these sheets just by selecting another sheet or by right-clicking on them and ungroup sheets so that's how you can perform the same edits across multiple sheets by grouping the sheets together first so there you have it the top 30 quick excel tips and tricks for beginners if you enjoyed this video make sure you hit the thumbs up button and subscribe to the channel for future excel videos like this one and we'll see you in the next video

Channel: How To Excel

Views: 24,397

Rating: 4.9629631 out of 5

Keywords: Microsoft Excel, Excel, Microsoft, Tutorials, Beginner, Tips & Tricks

Id: L44R-CB2cqM

Length: 36min 11sec (2171 seconds)

Published: Tue Feb 25 2020