☑️ Top 30 Advanced Excel Tips and Tricks

hey everyone it's John here and in this video we're gonna take a look at the top 30 advanced excel tips and tricks so these are the things that you need to know in order to go from beginner to an advanced user as always make sure you hit the thumbs up button and subscribe to the channel for future excel videos like this one now let's get started the first tip we're going to take a look at is combining data from different files in different folders so if you've ever had to combine data from different files it can be quite a pain and what you might end up doing is opening each file and copying and pasting the data into another excel workbook but excel can do this for us and it's actually quite easy so let's take a look at an example here we've got a couple folders and in each of those folders we have files so we have a file for each month of sales data let's just take a quick peek at one of these so here's our sales data and what we want to do is get the data from each of those files let's close this and to do that we can go up to the data tab and in the get and transform data section we can go to get data and from file and there's an option to get data from a folder now here we just need to supply a folder path of our data and so I've got that copied into my clipboard I'm just going to paste that in here and this folder path here this is the top-level folder so this is the sales data folder and then within that we have each of our folders for the data for each year and we can press ok and here we'll get a preview of all those files so this isn't the data quite yet it's just a list of all the files that we have in our folders and what we want to do is come down here and use the combined options and we're going to select combined and transform data and that's going to open up this combined files menu and now Excel is going to base how these files on a sample file and you can choose which file to use so we're just going to use the first file which is the default option here but you could select any of those files and since they're in the same format it won't really matter and here we've got a list of all the sheets in that file so if we click on our one and only sheet here that's going to generate a preview of the data in that sheet and we can press ok and that's going to open up the power query editor and it's going to show us a preview of our combined data here and if we're happy with the way that looks then we can go up to the Home tab and close and load this into the Excel workbook or if we want we can further transform this data to suit our needs using some of the commands in the other ribbon so we have a transform and pad column ribbon here and these ribbons have various commands to transform our data now in this example our data we just want to combine it so we're going to go back to the Home tab and close and load this to our Excel workbook and then we have a couple options of how we want to view this data so we're going to load it into an Excel table but we could also load it directly into a pivot table or a pivot chart or if we were using power query to perform other queries on that data then we could use a connection only and not load it into the workbook but we're gonna stick with the table and let's Pat it into our sheet here and press ok and then Excel has loaded our data and let's just format our order date here so let's format it as a date and let's check out what we have here so you can see that we've got our order data or sales data from 2010 2011 2012 and 2013 we've gotten and combined the data from all those four folders and have loaded it into a single excel sheet now the amazing thing about this is that it's dynamic so if we go back to our folder here and if I go up one level I've actually got data for 2014 in the folder here and I'm just gonna move that into my sales data folder and go back to excel and if I right click and refresh this query then Excel is also going to load that 2014 data so I can come up to my order data and check that it's there and there we go so we now have 2014 data as well so that's how you can easily combine data from multiple files in multiple folders using power query the next tip we're going to take a look at is using text to columns so sometimes when you copy and paste data into Excel it ends up in a single column and what you really want is to have it in multiple columns so I've got some data here and it's in a single column but we've got multiple items in our data so we have a first name and a last name and a company and an email address all separated by a comma and what we really want is to have each of those in its own separate column so we can do that pretty quickly with text to column if we select our data and go up to the data tab we can use text to columns and that's gonna open up the text columns menu and here we have the option to separate out our columns based on a delimiter or based on a character account in our case our data separated by a comma so we're going to use the delimited option and we can press next and now we have to choose our delimiter so we're gonna remove the default tab option and choose a comma notice when we select comma we get these lines separating out our data in the data preview so we can check and make sure that's done what we want it to and we can select next and here we have some various format options available and we can also choose where we want to put our results so let's put it over here and let's hit finish and now we have each of those items in a separate column so that's how you can quickly separate your data into multiple columns with text to columns the next tip we're going to take a look at is removing duplicate values so we've got a small set of data here and we can see that some of the values are duplicate values so we've got a 1995 Jeep Grand Cherokee and we've got the same data down here again we can easily remove our duplicate values by selecting our data and going up to the data tab and using the remove duplicates command and that's going to open up our remove duplicates menu and our data has column headers so we're going to leave this option checked and we want to base our duplicate detection on all three columns of your data so we're going to leave all three columns here checked as well and we can press ok and Excel is going to tell us that we had three duplicate values that were found and removed and we have a unique values remaining and we can press ok and you can see that those duplicate values have been removed from our data so that's how you can easily remove duplicate values in your data the next tip we're going to take a look at is using Auto fill and fill handle the active cell in a worksheet has a fill handle and if you hover your mouse cursor over the lower right hand corner of the active cell the cursor is going to turn into a small plus sign and that's how you can use the fill handle so we can use this to do things like copy and paste formulas so this cell here has a formula in it and if I click and drag that down then it's gonna copy and paste my formula down we can also use this to create simple sequences so here I've got a sequence so I got 1 & 2 and if I click and drag that down it's going to complete the sequence for me here our sequence is going to increment by 2 and we can do the same thing with dates so here we've got a date and if we click and drag that down then it's going to create a sequence of days for us here we've got a sequence of months defined we can click and drag that down and it's going to fill out the sequence of months for us and we've also got the same thing with years and we can do the same thing there so here we get a sequence of increasing years and we can also use autofill to create month names and weekday names so here I've got January and if I click and drag that down and it's gonna fill in the rest of the months for me we have the same thing with the shorthand month and here I've got Monday listed and I can fill in all the rest of the days and the shorthand weekday name we can do the same thing so that's autofill and the fill handle feature it's an easy way to copy and paste formulas or create sequences of data automatically the next tip we're going to take a look at is using flash fill so flash fill is going to allow us to combine extract and transform data based on examples that you provide so let's take a look at using this here we've got a list of email addresses and if we want to extract the first and last name and the company name from this and we can use flash fill to do that pretty easily so you just need to provide a couple examples and as we start to type out our second example you can see that flash fill has caught the pattern and is suggesting that we just want the first name in that email address and we can press ENTER and that's gonna fill in the rest of the data for us let's get the last name and we can also access this from the ribbon so if we select the data examples and the area we want to fill in fan we can go up to the data tab and use flash fill from there and that's gonna fill in the rest of the data for us based on our provided examples let's get the company name here and we also have a keyboard shortcut for this so if we press ctrl E and that's also going to fill in the rest of the data for us so here we were extracting and transforming the data so we also capitalize those notice we can also combine data so for example maybe we want to create an email address based on some given names so again we can just provide the first couple examples and flash villa will guess the pattern and fill in the rest of the data for us when we press enter so that's how we can use flash Ville to combine extract and transform our data based on a couple given examples the next tip we're going to take a look at is using a custom list so if you find yourself continually entering the same data over and over gain in your spreadsheets and you can use a custom list to alleviate this manual data entry so for example here we've got a list of products and maybe this is a list of products that the company I work at sells and I'm gonna be using this list over and over again in my work so we can create a custom list with this data and to do that we can go up to the file tab and go to options and go to Advanced Options and if we scroll down to the bottom here in the general section there is an option to edit custom lists and here we can manually type in our list items and then add our custom list but we also have the option to import a list from Excel so we're going to do that and we can select our products here and click on import and that's gonna import those list items and can see our custom list here has been added so let's press ok and press ok again and now whenever we want to use this list of products in our worksheet we can simply type out one of the products so let's type out caps for example and we can use the fill handle and drag that down and it's gonna fill in the rest of that list for us so that's how we can use custom lists to avoid manual data entry the next tip we're going to take a look at is freezing the windowpane so if you've got a section of your spreadsheet that you want to keep in view whenever you scroll around in your worksheet then do that with the freeze panes feature so for example here we've got a set of data and if we scroll down and we can no longer see the column headers and if we scroll over to the right then we can no longer see our sales order number and if we want to keep those in view at all times what we can do is select the cell below and to the right of those columns and rows and if we go up to the View tab and freeze panes and freeze panes again damn that's gonna freeze those parts of the spreadsheet and now if I scroll down notice the first row there remains visible and if I scroll over to the right that first column also remains visible now if you want to remove this you just need to go back up to the View tab and freeze panes and you can unfreeze the panes so that's how you can keep parts of your spreadsheet and view at all times using the freeze panes option if you want to view multiple sheets in a workbook at the same time you can do that by creating a new Excel window so here I've got a report for 2017 and in another sheet I've got a similar report for 2018 and we can view these at the same time if we go up to the View tab we can create a new window and now if we go back to the View tab we can arrange these windows so we get some options here to arrange them let's check out vertical and press ok and that arranges our two windows vertically together and now we can set one of them to our 2017 report and that way we can view them side-by-side now notice when I scroll in my 2017 report my 2018 report doesn't scroll but we can set these to scroll synchronously if we want to so let's go back and enlarge this and go back to our View tab and in there there's an option to view side by side if we click on that now if we scroll in 2017 report our 2018 report we'll also scroll synchronously and if you want to turn that off you can go back to the View tab and turn off a synchronous scrolling and now just our 2017 report Scrolls so that's how you can view multiple sheets in the same workbook at the same time you can create new windows and arrange them in this tip we're going to take a look at excel tables so excel tables are containers for your data and excel and they work really well with tools inside Excel like pivot tables and power query and they also work well with tools outside of Excel so for example if you want to use your Excel data in Microsoft power apps or Microsoft flow and you're going to need to have your data inside an Excel table so we can add our data to an Excel table by going up to the insert tab and using the table command here and Excel is going to guess the data that you want to add into your table based on where your active cell cursor was so you just want to make sure that Excel has guessed the correct range here the first row of data in art data here has column headers so we're going to leave this option checked my table has column headers and when we press ok then our data is added into an Excel table now I'm just going to undo that we also have a keyboard shortcut to do the exact same thing so ctrl T will open up our create table menu and we can create a table that way now the first thing you want to do when you create a table is give your table a new name so if you go up to the design tab then you can replace the generic default name here and you want to give your table a short sensible name so I've got housing sales data here so I'm going to call my table house sales and if I press enter name's my table now tables have a lot of awesome features in fact too many to mention here but we'll just take a quick look at a couple examples so for one thing you can give your tables quick Styles up in the design tab there's a lot of options here to restyle your table we also have table style options here so we can turn off banded rows if we wanted to or turn on banded columns we also have a total row option and that's going to allow us to add in formulas in the last row to sum up our columns or count them or to find the max or min etc I'm just going to turn that off another thing is that tables will absorb data so if you start typing something just below your table then that's going to be absorbed into your table and the same thing with columns another handy feature is if you're adding in formulas into your table then that's automatically going to fill down your table so here I just entered a formula in the first row and it's copied that formula down for me automatically let's go to the bottom of our table another nice feature is that formatting will automatically propagate throughout your column so here I've got the sale price and it's formatted as a currency and when I enter new data you can see that the formatting gets applied as a currency and I don't have to format my new rows of data we also get table references when we create formulas that reference a table so if I sum up this column here then you can see that my formula reference is my house sales table and the sale price column in that table and that makes reading formulas a lot easier so those are just a few of the benefits of tables if you have data make sure you put it in an Excel table if you have a set of data and you don't know where to begin with analyzing that data then you can use excels ideas feature so ideas is a new AI feature in excel that's going to help you summarize and find trends and other patterns in your data now it works best with data inside an excel table with column headers and we can use ideas by selecting our table and going up to the Home tab and the ideas button is here and this is going to generate various pivot tables pivot charts and charts from your data that Excel thinks you might find interesting so you can scroll through those and you can also show all the results if you want so for example here Excel has found that the overall quality field and sales price field appear to be highly correlated and if you want to use this chart you can click on insert the chart and that's going to create the same chart for you in your workbook so if you need help with your data analysis check out the new AI powered ideas feature in excel if you want to get more out of your data you can use excels new rich data type feature so here we've got a list of companies and we can convert those to stock data types by going up to the data tab and using the stocks data type and that's going to convert them into rich data that contains multiple pieces of data about that company and then you can extract the data using this extracted grid button so for example let's get the headquarters for each of those companies or let's get the current stock price now what this is actually doing is creating a formula that references our rich data type and that cell now has various properties and we can use this dot notation to extract that property so for example here we're referencing amazon.com and we're using the price property to get the price from that cell now with the stock data type we can also do currency pairs and currency conversions so let's go back up to the data tab and convert those into stock data types and now we can get the current exchange rate for those we also have a geography data type so we can convert geography type data into data types and here Excel had some problems identifying what we actually want so we can go over to our data selector pane and select the country of Ireland and now with those we can get various information like the capital city or the land area etc so get more out of your data with rich data types the next tip we're gonna take a look at is using fuzzy matching so you might be trying to compare two lists of data and it's the same data but the values in them are not quite exactly the same so here we've got two product lists and you can see for example we have men's watches here that's probably the same thing as watches in this list we also have women's watches again probably the same thing as watches with this kind of data you wouldn't be able to use something like vlookup or index match to match these up because those would only work if you were dealing with exact matches but we can use fuzzy matching in power query so first let's load these two tables into power query so let's go up to the data tab and create a from table range query and we're just going to load these as a connection only so let's just create a connection to that table and we'll do the same thing with this and let's load that as a connection only and now that we've got those two tables loaded into power quarry we can come back up to the data tab and get data and we can combine those queries with emerg so that's gonna open up our merge menu here and we can select our product list one and we're gonna merge that with our product list two and we're gonna do the merging based on our only fields here so we just select those two fields and we can choose what kind of joint type here we're just going to get all the data from our first list and matching data from our second list and if we just did that we wouldn't get any matches because we don't have exact matches in our data so what we're gonna need to use is fuzzy matching to perform the merge so we can enable that and we can take a look at some of the fuzzy merge options so there's a threshold and this is gonna be a value from 0 to 1 so 0 we're going to return everything as a match and 1 we're only gonna return exact matches and by default this is just set to 0.8 we can also set how many matches we return so if there's more than one match we could set something to only show one match the default value here is a really large number so we're just going to leave that blank we also have the option to supply a transformation table so if we know certain things are going to be abbreviated in our other list so for example maybe we want to match Microsoft with MSFT and we can set up a table that has those mappings in it beforehand and use that in our fuzzy matching we're not going to use anything here and before we apply this we can see that from our selection of matches we have 6 out of 7 rows from the first table have been matched we could come back to you our threshold and play around with that and see if we can improve that so here threshold of 0.6 we get 7 out of 7 rose and we're matched let's try that and here we have a new column attached to our first table so a product list two column and this contains all the matches so we just need to expand this out and let's not have a prefix and we can press ok and we get our matches there and now let's load this into the workbook so let's upload it as a table and let's put it in our sheet here and we can take a look at the results so here fuzzy dice it's been matched with blue fuzzy dice hair freshener matched with pine air freshener men's gloves here we got it matched with gloves etc so if you're trying to compare two sets of similar data fuzzy matching can really help out if your data isn't quite an exact match the next tip we're gonna take a look at is creating a drop-down list in Excel so if we select the cell where we want our drop-down list we can go up to the data tab and use data validation and for the validation criteria we're gonna select a list and then for the source of that list we're gonna select this range here of car mix and we can press ok and you can see that we now have a drop-down list so if we click on that then we have our available options from our range here and we can select our value now if we want our drop-down to be a bit more dynamic so that if we add something into this range then it's going to appear in our drop-down list without needing to update our data validation range then what we can do is turn this list into a table so if we select our data and go up to the insert tab and use the table command there and let's just give our table a name and let's go back to our drop-down list and back to the data tab for the data validation of that cell now instead of directly referencing that range what we're going to do is use the indirect function to reference that range based on the table name so in double quotes we're going to write our table name and then in square brackets the column in that table that we're going to use as our data validation source and close off the double quotes and close the function there and we can press ok now and we still have the same values in our drop-down list but now if we add a value into that table then it's going to appear in our drop-down list so that's how we can create a drop-down list in Excel using data validation and this tip we're going to take a look at how we can display a message whenever a user selects a particular cell so to do that we can select the cell we want to display our message in and go up to the data tab and data validation and in our data validation menu there's a tab up here input message so by default it should be checked where it says show input message when cell is selected we want to leave that checked and we can put in a title for a message and we can also input the message and press okay and now whenever we select this cell it's going to display our message and we can also move this around so if we click and drag it and release and we can change the location where that message is going to pop up and I'm just going to press the arrow key and the message will go away and the arrow key back to that cell it's gonna pop up again so that's how we can display a message whenever someone selects a particular cell in this tip we're gonna take a look at creating a named constant so in Excel we can name any cell or range of cells so for example we could name this cell here by going up to the name box and giving it a name and pressing enter and now we'll be able to refer to that cell by the name so if we create a formula then that new name is even going to appear in the intellisense and we can reference that in any of our formulas but it's also possible to do this without using a cell so we can create a named constant that we can use in our formulas but won't appear anywhere in a cell that could accidentally be edited to some other value unintentionally so to do that we can go up to the formulas tab and we can define a name and we can give it a name and here it refers to right now it's referring to this cell right here in our workbook what we're actually going to do is delete that and input the value we want for a constant and press ok and now we see that named constant and intellisense as well you can select that and use it in our formulas and there's no risk of that value being accidentally changed so that's how you can create a named constant in Excel and this tip we're going to take a look at the if function so the if function is going to allow you to do conditional type of calculations so for example here we have a list of sales data and maybe the company is doing a promotion and they're offering a 10% discount on any sales over three hundred dollars we could use the if function to calculate this discount based on our total here so let's do that and the first argument on the if function is the logical test you want to perform so in our case we want to test whether the total here is greater than or equal to 300 and if it is greater than or equal to 300 what we want to do is take the total and multiply it by 10% so that's the amount of our discount and if it's false if that total is less than $300 then we don't have a discount and we're gonna return zero and when we press enter you can see that for some of these rows here we have a zero so that's where our total is under 300 and the rows where we have a total over 300 we get our 10% discount amount so that's the if function that you can use for conditional calculations the next tip we're going to take a look at is using the countifs function so this is going to allow you to count the number of items based on one or more criteria so for example in our sales data here we can count the number of sales for yellow products with an extra large size using the countifs function just let's try that out so the first argument in cowntess he is the first criteria range that you want to test so for us that's the color column here and we want to see if that's equal to color yellow and we also want to test the size column here and we want to see if that's equal to extra-large and we can press ENTER and what we get is a count of two and if we look in our data and scan down for yellow and extra-large we see one and two here and that's it the next tip we're gonna take a look at is using the sum ifs function so similar to the countess function this is going to allow us to some data based on multiple criteria so for example in our sales data here we can add up all the values where we have a black product and a large-sized so let's try that out so the first argument is the range of values that we want to add up so that's going to be from our total column here and the next argument is the criteria range that we want to test so we want to test and see if our color column here is equal to black and we also want to test our size column here and see if that's equal to large and when we press ENTER we get our total value here 997 and if we look in our data we can see that we have this value this value this one and this one so we have four black products that are large and when we add up the corresponding values here we get 997 the next tip we're going to take a look at is using the vlookup function so vlookup allows us to find and return data from another table so for example here we have a category ID our sales data and then in another table we have the category ID and the corresponding category name we can use vlookup to return our category name into our sales data here let's do that so the first argument in if you look up function is the thing you're trying to find so for us that's going to be the category ID in our sales data and then we're going to try and find that in our category table here and the next argument is the column index so the thing we want to return is the category name and that's in the second column so we need a two there and we can do an approximate match or an exact match we want an exact match so let's choose that and when we press ENTER you can see that we now have our category name in our sales data the next tip we're going to take a look at is using a combination of index and match functions so similar to what the vlookup function does we can use the index and match functions to find and return data in another table so the match function allows us to find a match in an array and then return its position and the index function allows us to return a value based on a position so we can combine the two to get the value that we need so first off let's take a look at the match function so the first argument is the value we're trying to find and that's our category ID and the next argument is the array you're trying to find it in so for us that's our category ID column and the category table and the last argument is the match type so a game won an exact match and when we press ENTER what we get is some numbers here and these numbers correspond to the position of the match in our category column here so for example here we have a 3 and that's because we're trying to find category ID 3 and our category ID column here and that's the third item in our list now with this position value here what we can do is return the value here in this column based on the position and we can use the index function to do that so let's add an index function in here and the first argument of the index function is the array that we want to return our value from so for us we want to return our category name and the next argument is the row number to return so for example if we had a four then we would be returning mountain bikes because that's the fourth item in our category name column and in fact the position value is going to come from our match function here that we already calculated so that's going to be the row value that we returned from our category name and the column number that we're gonna return values from he is gonna be one and that's because we've only got the one column our category name column here so let's close that function off and you can see that we now have our category name in our sales data so for example category ID six here we have caps and that's exactly what we have in our table here so that's how we can use index and match to find and return data from another table in this tip we're gonna take a look at excels new dynamic array functions so with these dynamic array functions we now have the ability to return multiple values from a single function so previously we were confined to returning one result from one function and that's no longer the case so there are six new functions filter render a sequence sort sort by and unique and these are definitely some functions that you want to start using so for example let's take a look at the filter function so that's gonna allow us to filter an array of values so we can filter a table here for example and then we can filter it based on some criteria so let's filter it on the color and let's filter it when that's equal to black and when we press enter you can see what happens is even though we just entered one single formula in one cell we get an array of values returned so we get all the data from our table there where the color is black let's take a look at the sort function next so that's gonna allow us to sort some data let's sort our table of orders here again and let's sort it on the 4th columns or a quantity column and we'll sort it in ascending order and when we press ENTER you can see that we get the same data returned but in our quantity column you can see that we have an ascending order now so from a single cell formula we're able to spill multiple values into our worksheet and now to do this to spill all these values we need to have blank values here so for example if something's in the way if there's a value here then what's going to happen is our function is going to return this spill error because there's a value in the way where this function wants to spill its values so if we delete that you get our spilled results back let's try out the unique function so here we can get a list of unique values from our data let's check out the unique values in the color field and here we want to compare each row yeah let's press ENTER and check that out so here we got our list of unique colors from our data set we also have a sort by function and that's gonna allow us to sort our data based on more than one column so let's try sorting our data based on the color column and let's sort that in ascending order and then let's sort after that based on the quantity and we'll sort in descending order for that and let's try that out here you can see our color column has been sorted and then in the quantity that appears in descending order for each color so here we're going from 12 to 1 for all our black items and here we're going from 5 down to 2 for all our multicolored items and etc the other functions that we have is a sequence function and that's just going to allow us to create a sequence of values so we could create a sequence of values in a range of 6 rows and maybe 5 columns and we can start at one and maybe increment by 2 and then we get a range with increasing values starting at 1 increasing by 2 each time and we have 5 columns here and 6 rows we have a similar function called Rand array and this allows us to create a random range of values so let's try return seven rows of values and three columns and let's return values from 1 to 100 and we can decide to return decimal values or just integers let's return integer values and here we get an array of random variables and each time we enter that formula those values are gonna recalculate so those are the six new dynamic array functions now with these new dynamic array functions comes a whole new calculation engine under the hood so it's not just these dynamic arrays that can spill older functions have now gained this ability to spill data so for example let's try out an if function and let's test and see if our color column here is equal to black yeah if it is let's return a 1 and otherwise let's return 0 and when we press Enter we get that spilling as well and you can see wherever we had black we have a 1 and where we don't have color black we have a 0 value so those are the new dynamic array functions and the new calculation engine behind the scenes that allow older functions to spill results as well definitely a new feature then you want to start exploring the next tip we're going to take a look at is showing all your formulas and the worksheet so normally the only way to tell if a cell contains a function is to click on that cell and see that there is something in the formula bar but if you want you can switch to a formula View mode so up in the formula tab you can show formulas and that's gonna switch the view to show your formulas instead of the resulting value and when you click on any of those formulas and you're gonna be able to see the dependent values so here we can see that this formula references our category ID and the table over here now we can turn that back off by clicking on you show formula button again and this also has a handy keyboard shortcut so ctrl tilde is going to do the exact same thing and we can use that again to toggle it back off in this tip we're going to take a look at filters so we can add filters to any list to explore the values in that list so you can add filters to any list by selecting the data and going up to the data tab and using the filter command here and that's going to have these sort and filter toggles to our data and in here we can sort our data and we can also filter it so this is a text field we have some text filter options here so we can filter things that begin with a certain text or ends with a certain text etc let's try filtering on things that contain bikes and let's press ok and then the data that we get yes just for those values that contain the word bikes and what this is actually done is just hidden the other rows of data in our list now we can filter on multiple columns so for example we could also filter on black here and now we're just showing products that contain the word bikes and are the color black now we can clear out any filter by using the sort and filter toggle and just clear out the individual filter for colors or we can go back up to our data tab and clear out all the filters here now one handy feature is if you select a value in your list and you want to filter on that value you can right click on the value and go to filter and we have various filtering options here and we can filter by the selected sales value so if we select that now we're just filtered on the color yellow and let's just turn off filters for a second here and it turns out that actually works if you don't have filters applied to your list you can still use the filter by selected cell value and that's gonna apply filters and apply our filter for our selected value let's clear that filter out we also have the ability to manually select our filtering so we have a list of all the items in our field and we can manually select which ones we want to filter on yeah and that's just gonna show those values here now there's a handy keyboard shortcut that you can use along with your filters and that's to just select the visible cells from a filtering so if you select the range of data and use the keyboard shortcut alt semicolon that's going to allow you to just select the visible cells from your filter so that's Excel filters that are great for quickly exploring certain values in your data and this tip we're gonna take a look at advanced filters so these are gonna allow us to filter our data based on more complex filtering logic and to use our advanced filters we need to first set up a criteria range so we're going to have the three columns and our data here and then underneath we're going to have our filtering logic so if we want to filter our data on Subaru cars you can add Subaru here and let's select our data hand go up to the data tab and use advanced filters and we have the option to filter the list in place or copy the results to another location and we're just going to filter the list in place in this example so we have our data selected here and now we need to add our filter criteria so it's going to be this selection here and if we press ok then that's going to filter that to just our Subaru cars as you can see now let's clear that filter and let's try filtering on Subaru and in present model so we can add Impreza here and let's go back to our advanced filter and press ok and now we're just filtered on Subaru and Impreza let's clear out that filter now if we want to filter on a certain make or a certain model then what we can do is offset those so let's try filtering on sue bruised or a 5s so let's just delete this and down here what we're going to do is enter a 5 which is an Audi and let's go to our advanced filter and we're going to need to modify that range so it includes our a5 and now if we press ok you can see that we've got Subarus here and we've also got all our a 5 cars now let's further filter these results so that we only get Subarus or a 5s where the year is 2009 or less so we can do that by adding in our year column criteria here so let's add the criteria less than or equal to 2009 and we're gonna copy and paste that to this criteria as well so now we're gonna be filtering Subarus that are less than 2009 or a 5s that are less than 2009 so let's just clear out our current filter and go back to advanced filters and adjust our criteria range here and let's press ok and here we have Subarus and the year is 2009 or less and we have only two a fives this time and that's because some of them have a year that's greater than or equal to 2009 so these two rows that we have in our filtered results are 2009 so they're less than or equal to 2009 and show up in our filtered results so that's advanced filters it allows us to create more complex filter logic so if you have your data inside an Excel table then you can add a slicer to that data so a slicer is just a user-friendly visual way of filtering your data set so to add a slicer you can select a cell inside your table and go up to the table design tab and there's an option here to insert slicers when you select that you have the ability to choose a slicer for any or all of your fields let's add a slicer for our product and color field in our data and press ok and we get these two slicer objects here so one for the product and one for the color field and you can see in each of those we've got buttons for each of the items in those fields and that's going to allow us to filter our data so we can select for example just chains and our table gets filtered down to just chains we can also click and drag to select multiple items and we can use our two slicers to add multiple filters so here we're selected on five of the items and let's just select the yellow items in there and we can clear our slicer filters from here and we can also turn on a multi-select mode here on our slicers and that's going to allow us to select and unselect multiple non-adjacent items in our slicer so slicers are a great way to add an interactive element to your table data and this tip we're going to take a look at pivot tables so pivot tables are a really easy way to analyze and summarize your data so we can create a pivot table for any set of data that we have by selecting a cell inside our data and going up to the insert tab and selecting the pivot table command and then Excel is going to ask us to select the table or range of data that we want in our pivot table and because my data is in a table excels selected that table name here for me and we also need to select the location for our new pivot table so we're going to add it into this existing worksheet here yeah we can press ok and that's gonna create a new blank pivot table for us now when you select a pivot table it's gonna open up this pivot table fields window and this is the command center for building out your pivot tables if you don't happen to see that you can right click in your pivot table and that'll be an option to show the field list so right now we see heíd field list and that's gonna hide that feel this weekend right-click and show the field list and in this field list we have all the fields from our table listed out here and we have four areas here that we can put those fields into so we have a values area here and that's where you place fields that you want to summarize so for example in our data maybe we want to summarize our monthly salaries so we can click and drag that field into the values area and you see what happens is we summarize our monthly salary by summing it up so this is the total monthly salary in all our data and then we can add other fields into the rows or columns area and that's going to allow us to see a summary of our monthly salaries by those fields so for example if we wanted to take a look at the monthly salary by gender we could have that into the rows area and in our pivot table here we have the total monthly salaries for females and males we can click and drag that field into the columns area and we got a similar thing but across the column here we have male and female and our total monthly salaries and maybe we want to look at job role so we can drag that into our rows area and now our pivot table shows the job role by gender so we can see that manufacturing directors that are female have a total monthly salary 533 thousand so pivot tables are really good at slicing and dicing your data and allowing you to analyze it in many different ways so right now our monthly salary is being summed but we can also change that if we click on the monthly salary field here and open the value field settings then you can select various different summary types so we can count average max-min or take the product as well as standard deviations and variances so let's check out the average and now for example male human resource employees make an average of 4100 per month we also have this filters area and if we pad filled into there for example maybe let's add the age and that's gonna add this filter to our pivot table and we can use that to filter our data down to maybe just those who are 20 to 25 years old and now our data is just showing summaries for age 20 to 25 so that's pivot tables great for quickly summarizing and analyzing your data and this tip we're gonna take a look at slicers and timelines for pivot tables so we previously saw slicers for tables so we also have slicers for pivot tables so if we select our pivot table and go up to the pivot table analyze tab we can insert a slicer there so let's create a slicer for gender and press ok and that's gonna filter our data in our pivot table and right now I've got gender in my pivot table we don't actually need the field that our slicer is based on in our pivot table and we're still going to be able to filter our data based on that field we also have a timeline object so if you go back to the analyse tab we can insert a timeline now this is just like a slicer but it's specifically for date fields so here I'm only gonna have my date fields listed let's insert that slicer and here we have our time periods listed horizontally so we can select different time periods this way and that's gonna filter our data based on those dates right now we're selecting on months we have the option here to select different time periods so we can filter years or quarters or days as well so time lines are just like slicers but specifically for date fields in this tip we're going to take a look at the show values as calculations for pivot tables so these allow you to view your pivot table results with different calculations applied and we can apply that to any field in our values area so we can right click on the field and go to show values as right now no calculation is applied but we have several different options here let's take a look at percent of grand total and you can see that that changes our pivot table results to a percentage and it's a percentage of the grand total value so for any item in our pivot table here laboratory technicians the value we get here is the sum of the monthly salary divided by the total monthly monthly salary in our data so let's just take a look at that calculation let's add monthly salary into our values area again and let's just scroll over here and if we take this sum of monthly salary for our laboratory technicians and divide it by the grand total you can see that what we get is our eight point seven seven percent that's displayed here so that's the show values as feature it's a great way to view your results with different calculations applied in this tip we're going to take a look at the data model and building relationships between tables in our data model and that's going to allow us to analyze and summarize data and one table based on the values in another table so here we've got a set of order data and we've got a category ID in that table and here we've got a set of categories and that also has a category ID here and then the corresponding category name and what we can do is build a relationship between the category ID in this table and the category ID in this table and that way we can summarize our order data based on the category name so we can do that by creating a pivot table with our order data let's go up to the insert tab and use the pivot table command so that's selected our orders data and we're going to put the pivot table in this worksheet here and the important part is to add this data to the data model so let's check that off and press ok and we get our new blank pivot table here now we're also going to create a pivot table for our category table let's go back up to the insert tab and pivot tables again and it's selected our categories table here let's put that right next to this table and again we're going to add this data to the data model and press ok and now whatever pivot table you select you can actually see both tables in the pivot table fields list so if we select all and we can see both those tables so we don't actually need this second pivot table here we can delete that so we just need to add our data into the data model when we were creating the pivot table and now we can just use this one pivot table here first we have to build our relationship between these two tables with their category IDs and we can do that by going to the analyze tab the command here for relationships and we're going to build a new relationship between those tables so we're going to relate our orders data with the category ID to the categories table also based on the category ID and we can press ok and here's our new relationship built let's close this menu now in our pivot table we can add fields from both these tables so let's summarize our total value in the values area and we can actually summarize that total now by our category name so we can drag our category from our categories table and here we're summarizing our total orders by the category name so this is a field in our one table here and this is a field in our orders table but we're able to use both those fields in the one pivot table another very powerful feature that we get with the data model is the DAX formula language so this allows us to create extremely powerful and flexible calculations in our pivot tables that we can't normally do with a regular pivot table so you can add one of these calculations or measures as they're called by going to our pivot table fields window and if we right click on any of the tables we can add a measure and then we can give our measure a name and we can write our formula here in the formula editor so for example one of the things you can't do with regular pivot table is get a distinct count of items but we can do that pretty easily with our Dax formulas so there's a distinct count formula and let's maybe count the category ID in our orders table and close off that formula and let's press ok and in our pivot table fields list we have this new item here and you can see it's a measure we have this little FX symbol before our name and we can click and drag that into our pivot table just like any other field so let's add that into the values area and you can see we have eight distinct items so if you find that you can't quite get the calculation you need from a regular pivot table then you're likely going to be able to do that with Dax formulas and the data model want more awesome excel tips then sign up for my excel newsletter so the link for this is in the description below and when you sign up for the newsletter I'll send you a free copy of my excel tips a book if you enjoyed this video then you can help me out by hitting the thumbs up button and subscribing to the channel if you haven't already done so that's it for this video see you in the next one

Channel: How To Excel

Views: 764,862

Rating: 4.9418797 out of 5

Keywords: Microsoft Excel, Excel, Microsoft, Tutorials, Tips and Tricks, Advanced, Functions, Dynamic Arrays, VLOOKUP, INDEX and MATCH, Flash Fill, AutoFill, COUNTIFS, SUMIFS, Power Query, Tables, Pivot Tables, DAX, Data Model, Filters, Slicers and Timelines, Ideas, Rich Data Types, excel tips and tricks, excel tutorial, excel tips

Id: Hj4PVFYhqhQ

Length: 70min 55sec (4255 seconds)

Published: Wed Aug 21 2019