Top 30 *Advanced* Excel Tips to make you awesome ⚡💡

30 advanced excel tips and tricks this video is a bit like what forrest gump said my mom always said life was like a box of chocolates you never know what you're going to get and in this chocolate box you're gonna find 30 excel tips and tricks to improve your excel usage you're going to find chocolates on shortcuts and productivity how to use excel formulas how to use pivot tables and how to make better charts we have got quite a lot of ground to cover so let's jump in as this video is kind of long i have put timestamps for each of the 30 tips in the video description as well as in the comments use those time stamps to jump around and access the tip that you want to learn immediately or watch them in the order that i have presented to be amazed by some of the beautiful things that you could do in excel let's start off with one of the new functions introduced into excel the filter function here i've got some sales data and i just want to filter all the new zealand sales we can use this filter function like this in a new place we can say filter sales table sales geography is equal to new zealand it will give you a snapshot of all the new zealand sales data as a new table here it is actually one of the new functions introduced in excel 365 and it supports this spill behavior because there are multiple records for new zealand excel will automatically extend the formula to the box that contains all the records if your data changes and suddenly one of these things becomes new zealand you can see that instantly it gets added here as well let's say you got a formula and that needs to split in the middle of the formula output into two lines we could apply word wrap but excel will split that at a random place so here is a better way of doing that wherever you want to have the split go to that point and press alt enter now the formula will go into two lines but actually at that point there is a new line so when you enable word wrap it will split at that specific point that you specified how do we do that when the input values are actually in separate cells so here i can say equal to first line ampersand and then put car code 10 ampersand second line that will also add that new line character character code 10 and when you enable word wrap it will show first line second line in two separate lines in the formula output time for a shortcut you have some amount values and you just want to quickly turn them into currency formatting just select everything and press ctrl shift 4. excel will apply default currency formatting with two decimal points you can just reduce the decimal points to see the values as this you can use the subtotal function to calculate the totals of just the filtered values let's say you want to see the total amount so instead of using the sum function use the subtotal and then specify that you want the sum which is 9 of the amount column you will get the total amount but now if you apply a filter so let's filter for uk this number will reflect the total amount just for the uk staying on the subtotals when you have a subtotal you can use table slicers to make interactive totals to do that you need to have your data as a table then go to insert and click on the slicer button select the fields for which you want to have a slicer i'm going to pick geography and sales person and click ok you get two boxes one for each of the values and from here i can pick any particular geography so for example india and then i'll see my total for india here i can again then go into one of the persons chess bonnell to see all the sales of just bonnell within india you can also multi-select on the slicers by simply clicking and dragging to select all the items or hold down the control key if you want to select items out of order [Music] you can make a quick graph like what i have shown on the screen using the rept function say you got some numbers you can use the wrapped formula like this rept and then specify the symbol pipe comma the number this will print that particular symbol 26 times just fill this down and you will kind of get a graph but it doesn't look pretty select these wrapped formulas go to the font and then change this to play bill that will give you a kind of graph if you adjust the font size you will get a nice looking graph inside the cells using the wrapped formula unlike the conditional formatting data bars the wrapped formula bars can be colored and conditionally formatted again using formula based rules so this gives you a little more flexibility when you're making a dashboard or a special report let's say you want to calculate the maximum amount we can use the max formula on the amount column to get the answer but what about the second highest amount well you can use the large formula large sales amount and then specify the largest value that you want so in this case we want the second largest value and you will get such a value if there are ties let's say there are two values that are second largest then both 2 and 3 will be the same number you can also use the small formula to get the second lowest value 5th lowest value like that time for another shortcut normally when you copy data into excel that has some date components they either come up as numbers or some other formatting but you can select the date and then press ctrl shift 3 to quickly turn this into ddm why by formatting while staying on formatting here is my pivot table that shows the total amounts for each of these persons now we know that these are amounts so it needs to be in currency formatting so normally we right click go to value field settings click on number format from here but here is a shorter way of doing this you can right click and directly go to the number format and from here apply the currency formatting that you want and it will apply all the way through in the pivot table let us turn our pivot table into a quick dynamic chart so we can make a chart like this that shows the total amounts by each of our sales persons but now if i want to for example show this for any specific geography all we have to do is right click on the geography add it as a slicer just position both the slicer and the graph and now if you click on the slicer this graph will instantly update showing you the information for that particular geography let us say you like the slicer idea so much that you made another graph that shows the monthly amount trend but now if i click on a specific geography only my top graph changes how do i link this slicer to this one as well all you have to do is right click on the slicer go to report connections and from here link it up to the other pivot table as well and now both things will update so i can go to india i can see who are my sales people what is the monthly trend i can select new zealand i can even multi-select countries to see the overall trends let's say you got a pivot table and instead of seeing the monthly totals you want to see the year to date total you can set up the pivot table and then right click on the value column where it says show values as instead of no calculation pick the running total in option from here set the base field as month and then it will add the running totals on a monthly basis you can use the same idea to show running totals within a month so you will have month to date totals as well to do this you would have to go show value as running total but the base field need to be the individual date so that means every time date changes running total will be added but once it kind of goes over to the next month it will reset that total time for another shortcut let's say you have some data and you feel like the ribbon is coming in the way you can collapse the ribbon instantly by pressing the shortcut ctrl f1 if you press it again ribbon will expand you can also use this little up arrow button on the corner to do the same thing so you have a chart here that shows the average customers by geography and you would like to add a subtitle that says usa has highest average customers but there is no option to add a subtitle there is only chart title option well no need to worry just select the chart go to insert click on the shapes and from here pick a text box and now draw a text box inside the chart type whatever you want so for example usa has the highest average customers and then you can position it wherever you want you can align it this text box cannot go outside the chart so it will be kind of attached to the chart and you got a nice little subtitle on your chart have you tried the new tree map chart that is added to excel it is a great way to show how various things contribute to the overall picture so here i got my products and the total amount data and if i try to for example insert a pie chart it's gonna look very busy and probably ugly if i try a bar chart or a column chart that is gonna look very busy as well so this is where the tree map comes in handy you just select your data click on this little thing and then select the tree map and it will come up with a graph like this with a little bit of tweaking it will look pretty good there you go you can try the tree map from an insert ribbon hierarchy tree map use spark lines to show valuable insights in less amount of space they're very easy to create and here i have got a sample let me recreate that here as well so assuming you got some monthly sales data for these people just select all of this data go to insert click on the line option from the sparklines area and then select the location range as the cells where you want it this doesn't have to be on the same spreadsheet so it can be on another spreadsheet as well and then when you click ok it will add the sparklines with a little bit of formatting by changing the colors adjusting the weights and adding the high point and low point you can turn your sparklines into valuable charts so you got an important presentation coming up and you would like to present one of your charts but you just want to spice it up a little bit consider adding an icon from your insert ribbon these are freely available in excel 365 and other office software so go to insert click on icon and pick the icons that will add a little bit of value or color to your graphs something like this imagine you have a list of values and you want to look up the last value for a corresponding name so here i have chess bonnell appearing three times in my list and if i want to get chess's amount i could use either x lookup or vlookup and do it like this x look up the names and the amounts and naturally you will get the very first amount but what if i wanted this last amount you could use this special operator with x lookup so x lookup names amounts and then for the search mode if you specify minus 1 excel will look from the bottom up so it will start looking for chest panel from the bottom of the list and then it will give you 1281 which is the last amount time for another shortcut let's say you have a large spreadsheet and you just want to jump to a specific position in the workbook you can press ctrl g to open the go to box from here you can type the reference or select one of the named ranges so if i want to go to for example a b 201 i'll simply go to that cell very quickly here is a bonus trick if you don't want to press ctrl g you can also click on the name box in the corner and type a cell address or type a name and it will take you there so for example i can type az33344 and then it will take me to that particular cell as well let's say you have a pivot table where customers age and how much amount they have purchased is listed and you just want to quickly group this into ages of five at a time you can right click on the customers age column well really any age column and then just say group and from here you can specify the starting and ending points so let's group the ages from 20 up to 60 in the buckets of five years at a time and excel will create you a grouping hierarchy like this summarizing the values within each age group just a quick note of caution though this type of grouping will not work if your pivot table is coming from a data model in such cases you will have to create a mapping table and use table relationships say you got a pivot table like this and you really want to see india on the top just select the item and then click on the border of the item so when you select you will have like a box around it click on the border and drag and drop where you want this will instantly readjust the ordering of that to the top you can use this to arrange items in a random order or an order as per your preference staying with pivot tables here is a handy trick to use when you are looking at a large pivot table and immediately want to understand what is going on here i got my sales persons and monthly amounts now there are quite a lot of numbers here and i'm not immediately sure if something good or bad is happening one quick way is select any one of the numbers go to home conditional formatting and pick a color scale and then just apply a color scale and once you apply it on a single cell click on that little box and select the last option this will instantly color the values based on the scale that you selected in this case the whiter a value is the higher that number is so i can instantly see which sales people have score made higher amounts and which people have made lower amounts this works beautifully when you have a very very big pivot table and immediately want to narrow down to the important bits dealing with tricky dates is a everyday problem in many business situations here i got some dates and these are not as per my excel's default format which is month day year so when i try to convert these two dates by going to home and set the date format it doesn't really treat them as dates it treats them as text here is a handy fix select the data go to data select text to columns and from here just go next next and then where it says date pick the date format that is as per your input data so in this case it is date month year so i'm going to say say dmy and then click finish and instantly they will be converted into dates and necessary date formatting will be applied on your data to use emoji in your formulas all you have to do is press the windows and dot key let me show you from scratch so here i want to type a if formula and print a thumbs up message anytime my average customers is more than 30 and thumbs down when it is less than 30. so we say if and then check the condition open the double quotes and then press the windows dot key thumbs and then pick the up symbol again open the double quotes windows dot key thumbs down and we will get this nice little emoji symbol there is one limitation though when you use these emojis inside a cell they will always be in just black and white but when you use them in a text box that is independent or as part of the chart titles etc they will be colorful oh well that reminds me if you are enjoying this video give it a thumbs up you ever need to get the latest stock price of one of your companies you can use excel's new stocks feature to do that all you have to do is type the symbol code so let's type msft apl o g and then as you type you should see a pop-up message saying convert to stocks and when you click that excel will go and do an online search convert these into necessary symbols now it will treat these as stock market symbol codes at this point you can go and ask excel what is the latest price of microsoft share all you have to do is press equal to select the cell where your symbol is and then press a dot and then you can access any of the features so you can see what is the 52-week high what is the change from previous day etc but to get the current price just use the price value and then it will give you the price in the dollars as well and you can see microsoft is currently trading as of the recording of this video 266.69 if you want to adjust the printing layout of your workbook go to the page layout view and from here you can see the blue lines is where excel would insert the page break but here is a trick that not many people know you can place your mouse pointer on those blue lines and move them around to readjust how the printout would be so for example i can move this dotted line here to indicate that i want all of this to be in one page and then the page breaks will now you can also adjust the horizontal one and the thick blue line on the outside is your entire print area you can also adjust that for example if you don't want to print this blank area just drag this here and now excel will not print anything on this gray area only this bit will be printed you have a table of data and someone told you that you can use data bars in conditional formatting to make them look pretty so you went ahead selected all this data home conditional formatting data bar but now the data bar is going and sitting on top of the number that's not pretty you can easily fix this by going back to the conditional formatting manage rule select the rule and edit it and from here where it says minimum and maximum as automatic change them to numbers so minimum will be from 0 and maximum can be any number that you want so in our case the data goes up to 41 000 so i'm gonna put this to sixty thousand of that and apply and now your bars are not really that tall you can tweak the number until you are satisfied with the way everything is displayed and use that configuration you can even write a formula for the maximum number and use that as an input value excel doesn't show squiggly lines when you make a typo but you can use the f7 key to quickly trigger spell check to make sure that any information that you have typed in the cell doesn't have a typo and as you could see there were quite a few typos and you can quickly fix those mistakes you should try to make your chart titles compelling and strong but the default titles that come with excel are kind of lame what you can do is you can generate a title in a cell you can type it out or you can use formulas to make the title and then select the chart title click on the formula bar say equal to and point to the cell where your title is this way your title gets fed from the cell that is there anytime this cell changes your title will also change so this will create such a dynamic and powerful storytelling scenarios for you let us say you don't want to show the amount by month trend chart for a certain presentation you don't need to select and delete it or move to a separate sheet you can use the selection pane to temporarily hide it to find that go to the home ribbon go to find and select and click on selection pane this will list all the objects on the page and from here select the chart this is chart 7 and click on the eye icon there it will instantly hide it to see it again just enable it you don't need to keep the selection pane visible so once you do this you can close it off and now your chart is gone from the screen but it is still there and you can bring it back if you need it did you enjoy that video you are going to love my 10 data cleaning tricks video as well it is linked on the screen somewhere here and if this is your first time here on my channel thank you so much for stopping by and leave a comment to tell me what you enjoyed most see you again bye

Channel: Chandoo

Views: 69,473

Keywords: chandoo, chandoo.org, Excel, spreadsheets, excel tips and tricks, excel tricks, advanced excel tricks, learn excel in 30 minutes, excel advanced, 30 excel tips & tricks, useful excel tips and shortcuts, excel shortcuts, collection of excel tips, excel productivity secrets, pivot table tips, microsoft excel tips, top tips and tricks for excel, power tips for excel, excel for analysts, excel tips

Id: m2C_-6PW6WE

Length: 22min 24sec (1344 seconds)

Published: Tue Jun 29 2021