50 things you didn't know Excel can DO 💡

do you know that Excel has a built-in GPS or we can use Emojis with Excel charts or there is a powerful feature of excel that can help you get bird's eye view of your workbook in this video Let's uncover 50 of the most amazing time saving features of Microsoft Excel it is a lot so I brought this video into five segments General usage of excel working with your data writing formulas creating pure tables and making Excel charts let's go let's start by opening Excel the first trick is let's say you have got some files here and you want to take one of these files and make a copy of that you can just right click on the file here and then say open a copy this is going to create a new workbook with all your formulas and data and everything you can make changes and save it as you want you may have noticed that in my Excel I have got some quick tools on the top this area is called quick access toolbar and you can add other things to it quite easily let's say you often format data in percentages you can right click on the percentage thing and then say add to quick access toolbar that will be added there one of the cool things with items on the q80 or quick access toolbar is if you hold down the ALT key you can see that all of the buttons that you add there get their own shortcuts so for example if I press alt 5 I can directly trigger the filter buttons right away but you can also move it from above or below to do that right click on the quick access toolbar and select show q80 below the ribbon do you notice that if you type few months let's just say Jan and Feb and when you drag this down you will get all the months of the year automatically this thing is called custom lists and let's just say you have got a list of products and you always type them manually you can create a custom list for your own products for example here are my awesome chocolate products and I don't want to type them every time so what we can do is go to file options advanced and edit the custom lists and you can use the import from cells option so place your cursor here and then select all your products and import them so now you have got a new list with all your products click OK and either in this file or the new file you just have to type the first few items and drag this down in Excel will give you all the products do you know that Excel has a built-in GPS in it that's right any cell you select you can see the address of the cell in the corner in the name box so wherever you are Excel is always telling you your address but there is a second purpose to this name box you can also use this to go to somewhere else so let's say we are on J5 and we would like to go up to a b 230 so type that address and hit enter and boom you will instantly go to that location this next one is really cool let's say you have got a list of items but you don't want to copy all of them you just want to copy a few and then paste them elsewhere either in this sheet or in a new workbook so from home click on this little corner thingy here and expand out the clipboard and you can select the item that you want to copy Ctrl C to copy it and you'll see that item appear here Ctrl C on the second item third item fourth item now to paste the data go to the separate sheet select the cells and then hit paste all it will paste in the order you copy so the bottom most one will be pasted up top here is an awesome time saving trick if you go to the file menu and you notice that every day you open this particular file you can actually pin this so if you tap on this pin button that file is pinned now so now anytime you go to the open you will see that particular one available to you right up there without having to look through all the list of files you may notice that when I open a new Excel file I only get one sheet by default I don't really like to have three sheets because I'm not doing that much Excel work so how do I set this to one sheet this is really simple go to file options and from General you can see that I have got this to just one sheet by default it would be three but I set this to one while you are here you may want to also adjust the body font size I like to have my text a little bigger so I set this to 14 points do you know that Excel has different color themes right now I'm using a colorful theme but if you want you can go to the file menu account and then from here you have got the office theme option you can change it from colorful to for example black that will give you a dark mode especially if you work in a darker room or in the evenings this is super helpful or you can go to dark gray or use the colorful option last one is my favorite when it comes to getting things done faster in Excel you don't need to memorize all the hundreds of shortcuts in Excel instead just memorize this one shortcut hold down the ALT key briefly in Excel will show you all the shortcuts on the screen so for example here it is telling me that if I press now n I'll go to the insert ribbon at that point it's going to show me the other shortcuts in the insert ribbon this is way better than memorizing all the shortcuts in Excel these set of Tricks talk about working with the data in Excel so here I have got some data and I just want to see my top 10 items I don't want to see all of these here is a simple shortcut just go to the filter button here number filters and top 10. just hit OK and you will see the top 10 items quickly the second one is sorting by more than one column you probably already know that you can sort by any individual column but what I want is for each person I want the amounts to be sorted in the designing order so to do this go to sort and filter custom sort and here we already have salesperson A to Z now I'm adding the second level which is sales largest to smallest and hit ok now for each of them I'll see their amounts going down in that order this third one is super helpful let's say you're working on an Excel workbook and you would like to insert a screenshot of something else into Excel you just have to go to the insert ribbon and then go to screenshot button here and it will show you all the other files or windows that are currently open and you can insert a screenshot of any of them for example here is a screenshot of my Windows Explorer in Excel you might think chendu when would I really use this well while you normally don't insert screenshots into Excel if you have got a presentation to make and you would like to add a screenshot of a report or a graph into PowerPoint directly you can use the same thing here as well go to insert and you will have screenshot here if you tap on that you will see all the open Windows here and you can insert a screenshot of excel directly into PowerPoint works with word as well this next one is fun let's say you're looking at some values but you don't want to see certain numbers in the data you can make them invisible normally what people do is they select that and make them white color but there is a better way to do this because even if it is white if I fill a color in there I can see it to make things invisible in Excel just select the cells that you want to hide for example all of these guys and then go to format cells from the current formatting go to custom and delete everything that is here and type three semicolons that's the secret password and when you click OK those values will be gone of course the numbers are still there so when you select them you can see how much is the count or what the value is in the formula bar but they are not visible on the screen do you know that you can rotate the values in a Cell so right now these are all horizontal but you can make them slanted like this and if the cell has a fill color even that color gets slanted by 45 degrees this next one is super handy when you're working with mixed data like this let's say you have got some data like this and you just want to grab all the numbers so select the data and then hit F5 and click on the special this opens the go to special screen and from here tap on constants and uncheck everything else except numbers here now when you hit OK Excel is going to highlight all the number values and point you can Ctrl C to copy those values and Ctrl V to paste them elsewhere let's say you're looking at the headers but you would like to have a new line between sales and person just edit the cell place the cursor where you want the new line and hit Alt Enter that makes the line into two lines make sure you enable wrap text otherwise Excel is not going to show that in two lines this next one is a fun trick with the find feature of excel let's say you're looking at this data and your boss wants you to highlight all the 600 in yellow color and you don't want to write any formulas or conditional formatting for that just select the data and click on Ctrl F to open the find replace type what you want to find so in this case 600 and then instead of clicking find next click on find all this is going to find all the 600s in your data and list them here it actually found some 1600s too but let's just go with everything so now if you hold down the shift you can select all of these values at this point you can close away the find box and you will end up with highlighting all the 600s and 1600s and just fill the color onto those cells and done let's say you are looking at a file that your colleague made this dashboard for example and you just want to understand quickly what is in this file what sort of data is there what calculations are there what graphs are there Everything At a Glance do you know that you can go to the view ribbon and click on the navigation button to see the internal structure of this workbook it's gonna give you a tree like view with all the items on this file so it's giving you all the four worksheets right now we are on the dashboard worksheet and then it tells you what shapes are there what ranges are there what charts are there if I click on a chart I'll see that highlighted I can see what pivot tables are there for example there is a pivot table three and when I click on that I'll immediately go to that thing really helpful to understand complex workbooks that get shared in team environments this next one is one of my favorite new features to be added in Excel let's say you want to grab some data from a picture so here I have got a picture I selected this picture and copied it to my clipboard Ctrl C now I'll come back to excel I don't want the picture here I want to grab all the data in that picture here so I can go to the data ribbon and here I have got from picture option and then I can choose the click picture from clipboard thing alternatively if you have got the file on your computer you can also use the file option so let's click on that Excel is going to analyze the picture and tell you all the data in that picture I'm gonna insert it here it's saying some warnings but let's just see what is there so here is what the picture is telling me out of 1.4 million views my channel got recently this doesn't make any sense 75 percent are not from subscribers really disappointing that reminds me if you are watching this video and enjoying it consider subscribing to my channel that way you get to know whenever I put new videos as well as you get to enjoy more of my content thank you these next 10 things are about formulas in Excel you know that Excel formulas can now return more than one value that's right for example there is a new function in Excel called take it will take a list or a table of values so here I'm asking take data sales column and then give me the top 10 values the first 10 rows of data and when you hit enter because there are 10 values Excel is going to return all the 10 in one go this behavior is called formula spilling and we can use that to build some really cool and amazing calculations in Excel I got other videos on the channel that talk more about this here is a fun trick here I have got a sum formula that adds up the first three days worth of data when my boss saw this he wanted me to change this to look at first six days not the three days normally you would need to change the formula but do you know that when you are editing the formula you'll see the blue boxes for the ranges that you are working with so here is my range and I can just point my mouse pointer at the corner and drag this to adjust the range when you let go and hit enter you will see the updated result of your formula here whenever you are writing a formula Excel shows this little tooltip sometimes it can get in the way of things do you know that you can place your mouse on that and then just move it away this next function is super helpful Excel now has a function to remove duplicate items from your data for example here I have got all the products but you can see that many of the names have repeated and I just want to extract all the product names we can use the unique function unique and then select the data hit enter and we'll get one row per each product you can send this unique function to the sort function to see the products in alphabetical order okay if you think these things in Excel are awesome you are going to be blown with this next one now Excel formulas can return images so here I have got three images when you insert an image you need to set it in the cell so every time you insert an image now you will have an option to place it in the cell or over the cell so let's bring an image here is the image of my company logo and I can select this image and you will see this little button next to it and if you tap on that it goes and sits in a cell now you might think what's the novelty of this the thing is this image is actually in the cell so if I go somewhere else and then say equal to and point to the cell with the image see what happens I'll get that image here in this other cell how awesome is this again you might think still novelty feature when would I use this so let me show you here I've got three images and each of them has a code now if I want to search for number two I can use x lookup function lookup 2 in the code column and then get the image column as a result X lookup will look for number two and then get you the mind blown image as the result it's all Dynamic so if I change this to 1 I'll get the finger if I change this to 3 I'll get Excel and if I change this to 4 I get a like symbol that reminds me if you are enjoying this give this video a like let's say you want to know what formula is in this cell but you want to also show that in a different cell as text value you can use the formula text function and point to the cell that contains the formula and then you can see the formula that is used in the cell above like that do you know that when you are looking at complex Excel workbooks you can calculate in between results using a simple shortcut for example here I have got a really simple business calculation workbook and it calculates our profit as 59960. want to verify some of these calculations so I'll go to something like let's sets of these values I want to double check what that number would be I can select that and press Ctrl equal to or F9 to see the actual result of that sum formula right there in fact you don't even need to do that when you select a formula Excel actually shows you a tooltip now with the in between result we can use that to debug or audit different workbooks very quickly now let's take this to the next level you have built this and your boss asks you how many units we need to sell if you want eight thousand dollars profit you don't need to go and figure it out yourself you can select the cell that you want to set to 8 000 and then go to data what-if analysis goal seek this is a powerful feature in Excel that can calculate the input value that you want so for example we want to set the c19 cell to 8000 by changing the number of units sold when you click OK Excel will simulate all the values and then it comes up with the answer if you sell 5020 units you will make eight thousand dollars profit this next one requires using a browser do you know that if you type excel.new you can create a new Excel workbook in your browser it will ask you to login to your Microsoft account and creates a brand new Excel file for you online this is super helpful especially when you are on the go or when you are on a device like a tablet and you want to make an Excel file these next ones are about pivot tables do you know that when you are looking at a value in the pivot table like this 200 and 2000 value and if you want to see what data points add up to that if you just double click Excel will show you all the data points sometimes we don't know what pivots we want if you select your data and then go to insert Excel now has a recommended pivots option and if you tap on that Excel is going to generate the pivot tables from your data whichever pivot you like you can just say insert it into a new sheet let's say you like this pivot but you would like to see one of these names up top do you know that you can select the item and then drag and drop it wherever you want that will move the values up there here is a pivot of countrywise sales by dates but sometimes people don't like these minus and plus buttons you can easily hide them by going to the pivot table analyze ribbon and toggling this plus minus button let's say you create this pivot but your boss says I don't want to see it like this I want to have one pivot table for each country with all the daily values you can quickly do that by taking the region and putting it into the report filter area here and now if you go to pivot table analyze ribbon options and click on show report filter pages select the region and click ok Excel is going to generate all the individual country level pivots for you automatically another trick is instead of using the report filter you can take it out and then right click on the region and add it as a slicer this offers a fun way to see the report updated based on which country you want you just tap on the country and the report updates you can single select or you can hold down control to multi-select different countries imagine you are building a pivot table from a really large data set you might end up having a lot of fields in this Fields area do you know that you can actually change the order in which they appear instead of the data source order if you put it in sorted order you can quickly find the fields that you want for your analysis do you know that we can calculate distinct counts with Excel pivot tables it's really simple just select your data insert a pivot table and make sure to add the data to the data model and now you can select a salesperson and you can see how many distinct products they have sold by dropping the product into values area initially it will count but you can right click on the number and then go to summarize and select a distinct count another advantage of using the data model pivot is you can also calculate median values easily normally with pivot tables if I put something like sales I can either sum it or if I right click I can average it if I go to more options even then I don't find median value at all but because we are using the data model option you can right click on the table and add a measure and create median sales and use the median of data sales that will add the measure median sales and when you put that into a pivot table you can see how the median sales are for each of our sales people here is a power tip to speed up when you are working with pivot tables go to file options data and then from here you can actually edit the default layout of excel pivot tables here you can specify how you would like your pivot tables for example I don't like to see subtotals I don't mind the grand totals and I would like to use the compact form you can also customize the default options of pivot tables I don't like pivot table to auto fit columns so I uncheck this and you can adjust many of the other things and set that to the memory this way every time you make a pivot table Excel will apply these default settings for you and saves you heaps of time okay these last 10 are about Excel charts do you know that when you have an Excel chart you can hide a element of the chart using that filter button for example let's say we don't want to see the spicy special Slims for a presentation you can use this and uncheck the product that you don't want to see and apply and that's gone from the chart it is still in your data but not shown on the chart another way to filter Excel charts is if you apply filters on the main data for example if we go here and uncheck this and this product they will also go away from the chart if you don't want that to happen you can right click on the chart go to select data hidden in empty cells and then check the show data in Hidden rows and columns option with that Excel won't pay attention to the filtering on the screen and always shows the data in the chart one simple way to enhance your charts is by giving them a good title but you don't want to type the title manually do you know that you can select the title on the Excel chart click on the formula bar say equal to and point to a cell that has the title that you want and when you hit enter your title is now linked to the cell this way if you change your mind or you want to save a different title then that's what your chart will say another cool way to enhance your charts is to introduce a little bit of emoji for example every time the sales are above 9000 we would like to celebrate so we can have an emoji column right next to the data and use a simple if formula if sales value is greater than 9000 open the double quotes and use the windows and Dot Keys together to bring up the Emoji keypad let's put a smiley symbol when the sales are more than nine thousand else let's put a crying emoji I know it looks a bit dramatic and now you have the Emoji in the cell next up select the data labels on your chart right click to format them and use the value from cells option and link up the Emojis as well click OK and you can see the Emoji already showing up we are going to bring all of this outside and use a new line and change the color now all the products that have more than 9000 are smiling whereas the ones below 9000 have that little crying Emoji sometimes you have a lot of data and if you try to make a chart from it it will look really busy here is a simple trick you can actually make a chart in the cell itself just select the cells where you want the charts and then go to insert sparkline and then point to your data range click OK and you will get the individual trends of the products right in the cell you can customize the sparklines from the sparkline ribbon another cool way to enhance your charts is to use images for example here I have got a chart for our products spicy special slim and instead of the bar I want to use this little chili image copy the image Ctrl C select the bars and Ctrl V initially the image gets pasted and looks a bit stretched out right click to format this series and go to the fill options and instead of stretch use the stack and scale width option and specify how big each chili needs to be let's go with 2000 and here is a nice little image chart do you know that you can easily apply conditional formatting on charts for example here is a trend of the customers that we added or lost in the first 10 days of August and I would like to see the negative values in red color and positive values in green color just select the columns and go to the fill options and use the invert if negative button change the color and then it will give you a different color for negative values so the negative will be let's just say this color and you now have two colors one for positive and one for negative all of this is beautifully linked to the data so for example 3rd of August is not negative one there was an accounting error and it's actually 12. instantly it'll become green and show up on the positive side many times when you want to make a chart you might have two two different kind of values so you have got sales and units try to make a single chart from that it won't look so good but you can easily combine items to create a combo chart in Excel just select the data go to insert and instead of these charts use the last one here and make a combo chart as the units are too small I'm going to select the units line right click and then say change series chart type and use this on the secondary axis so now I can see sales and unit Trend clearly in one chart let us say you make this chart but your boss says let's not use the July values because the accounting for that is not done yet you can easily adjust the chart Source data by selecting the chart and then you will see these blue or red color rectangles around the range of the data as we don't want to see the July values I'm just gonna place my cursor here and it just this size down to just up to June let go and your chart gets adjusted automatically you can use the same technique to point to a different range of cells or make it bigger sometimes we don't know what chart to make from our data you can just select your data and then go to insert and use the recommended chart option in Excel so that Excel can actually suggest the charts for you here it tells me that I can use pivot charts for this or this or I can also use a Pareto chart or a histogram to understand my sales values whatever you like you can click on that and say OK and such a chart will be created for you automatically by Excel like I said at the start 50 is a lot so thank you so much for watching until the end here is a bonus trick for you do you know that I also have a free newsletter that's right once a week I send an email with some amazing Excel slash power bi tips did I mention it is free you can get the newsletter by signing up using the link below check it out bye thank you [Music]

Channel: Chandoo

Views: 262,158

Keywords: chandoo, chandoo.org, Excel, spreadsheets, microsoft excel, excel tricks, advanced excel tricks, excel tips and tricks, excel tips, excel shortcuts

Id: Tzuk3NH_u3I

Length: 30min 3sec (1803 seconds)

Published: Tue Aug 15 2023