10 awesome things you can do in Excel - WITHOUT any formulas or pivots

hello and welcome to shendu.org today i am collaborating with microsoft to feature this awesome video in this video we are going to look at 10 incredibly cool things that you can do in excel now normally anytime you see something magical or truly powerful happening in excel you think that there is some sort of a formula or a pivot table engine behind it well in this video i am going to show you 10 awesome things that you can do without using formulas or even pivot tables you might be thinking ah what can we do if we are not using formulas not using pivot tables you know then our options are very limited well you would be surprised so the 10 things that i will cover are names from initials totals for filtered values comparing two different lists visually and highlighting mismatches calculating frequency distributions and plotting histograms calculating running totals forex conversion so one currency to another currency spotting extreme values doing business forecasting creating interactive charts and finally if you think okay i can't do anything else then asking excel to do the analysis work for you so all of these 10 things uh require you to just click and point and and touch various options within the screen ui and then you will be able to do that okay let's get into the video where i will walk through these 10 things feel free to download the sample workbook to play along or experiment on your own with the data set and the final examples that i have provided and once again a big thanks to microsoft for featuring this video where you know i can share these ideas and they can spread it to their community as well so here is the data for our 10 awesome things that you can do i will be using this data set for pretty much all the examples except maybe one or two so what i have here is five columns of data some names some product groups age of the person which date they started selling and how much sales they have made this could be literally anything and let's just get into some of the fun things that you could do without writing a single formula so the first thing is i want to take the names and i want to extract the initials from them so for example for this person it will be bf and that will be dc gc like that all you have to do is you don't need to figure out the formula logic or anything you will simply type bf and then dc and as you could see as you were typing dc excel already suggested but in case that went off after typing one or two values you just press ctrl e and excel will figure out the initials for rest of them notice that excel has identified the pattern here as every capital letter in the word that you picked so here it's will own or killed is w-o-k so that's uh how it is this is actually called flash fill and you can um uh use this to uh do something and excel will follow the pattern for you so this is very very useful especially if you want to let's just say do initials or you know extract the last last name alone so you would just type ny and then and as you type one letter excel has already figured out anything after the first space is what you want and you see the suggestions you press enter and that will go through in case the suggestions do not come out you just press ctrl e to trigger the flash fill you can also see that from here in the button flash fill so that's number one number two is filtered totals so we have a bunch of values and we would like to uh for example do some ad hoc analysis on seeing what is the total for tablets or gaming pcs or whatever but as you filter you would like to see the total so the first thing that you want to do is take your data and turn that into a table this can be done by selecting any cell and pressing ctrl t and that will make a table in excel so once you have a table you can add this total row thing and it will add a grand total row at the bottom right now the grand total is added for the sales alone but you can add it for any of the columns for example you could put average age as well um for some of these things you can't really do anything because this text value but others you can put that so once this is there one of the tricks normally people don't realize is the total row goes at the bottom so you have to leave that there but you can actually cut it ctrl x and put it right on the top so now you have a grand total row that is on the top and you can go and apply your filtering so for example i'm selecting consoles and home security products and i will immediately see that the total sales is 6.8 million dollars i can for example uh have an age condition as well anybody between uh 20 sorry 25 and 40 years old uh what is the total sales that's 5 million dollars i can for example just select the people who join in 2020 and that's 1.6 million dollars and we can understand the data so this is filtering and totaling all the time we have not written a single formula although you might think uh how is this working well we have kind of cheated here we didn't write the formula but excel wrote this beautiful subtotal formula that only pays attention to the unfiltered data so anything you filter out it will not be there so you can clear the filters and it will go back to the way this work now here is a bonus trick let's just say there are multiple filters here um and you want to just undo all of them in one go one of the shortcuts that i use is ctrl shift l this will basically remove the filters and takes you back to the original data to get the filters back on you just press ctrl shift l again and the filter buttons will be enabled so that's the second thing let's go to the third one you want to compare two lists and see which names have appeared in both lists or which names have appeared in only one of the lists i got some names here so all you have to do is select the first list hold down your control key and select the second list so this way you picked both lists now from home conditional formatting highlight duplicate values by default excel will highlight all the names that appeared in both lists so you could see brian gunnar etc they have all been in both of the lists alternatively you can select the unique which will highlight the names in each list that are appearing only there and not on the other set of the data so you can pick whichever and this can be used to compare lists visually and see which items have repeated very useful when you are comparing data you don't have to figure out the formulas for this you can just use this method directly the fourth one is frequency distribution analysis again this is something that many people think you need to write complex formulas or countifs or frequency formulas or whatever you don't have to do a thing to get the answer let's just say i want to see the age distribution of our staff i pick my age column and then i go to insert and then click on that list little histogram button and i will get a histogram of ages i can see that we have quite a few people between 19 to 35 37 years and then the distribution kind of tapers off immediately so this is the age distribution let's just say i want to see the age distribution by product group i can select product group and ages and then i can use uh the box and whisker plot this way i can see the ages in each group of each department for example we can clearly see that the whiteware department has quite a wide spread of ages a home security department has narrow spread but there is an outlier here someone whose age is um you know beyond um here when there's a 43 year old person so you can see the distributions like this you can use the same graph and see the sales as well all you have to do is select the graph we are looking at ages so point your mouse on the age h column the blue thing and then it turns into bold format click and you drag it to the sales figures and you're now looking at sales figures as well i think i've moved it or something so that's why it's not properly aligned but it will come up and you can see the distribution of sales again home security has a very widespread some of them have narrow spread and everything is between 100 000 and 300 000 except for home security which has up to 600 000 in sales so this is a frequency analysis both with the histogram and box and whisker plots again you don't have to do any any formulas any summarization nothing you don't even have to set up a pivot table everything works just straight out of the box the fifth one is running totals let's just say you got some numbers and you would like to kind of get a running total here that will be some of this here some of these two here some of these three here like that all you have to do is um you know just uh select this data and then this will appear if you don't see that you can press ctrl q to trigger the quick analysis thing and then from here just go to totals and then pick the running total you want to use the grading total on the column one not on the row level and this is the running total it will add the formula for you necessary formula essentially excel is going to write that sum if some formula for us i think i made a wrong selection here it was actually adding the dates and everything but you get the picture so you can um i'm just going to undo this and i'll just select my sales alone and do the running total so we can see that so we are now looking at the running total of sales so we start off with uh 256 000 and then it goes all the way up to 22 million dollars here okay so this is a running total again no formula need to be written of course excel will write the formula for you the sixth one is quick exchange rate conversion let's just say you're looking at dollar sales and you want to see how much this would be in euros for some purpose you don't really want to um you know you could do like a multiplication of 0.85 but let's just say you're lazy or whatever so all you have to do is so select these values copy them and then come here and paste them as values so we will get the values now to convert that to 56 thousand into euros uh two fifty six thousand dollars would be zero point eight five times that so you go to the 0.85 which is the exchange rate copy the exchange rate control c and select this range of numbers right click paste special and then from paste special what we want to do is we want to multiply the exchange rate so you just say paste multiply so multiply with the 0.85 in the values here and excel will do the multiplication it will give you the answer in euros this is a one-time thing so if your number changes nothing happens to these values so because it's kind of overwrites the new value there by multiplying with 0.85 i find it useful in some situations where i just want to quickly convert something or whatever the seventh one is outlier analysis we got some sales figures here we just want to quickly eyeball and see which numbers are very high or very low all you have to do is select all of them home conditional formatting color scale and pick a color scale where you can see the higher values let's say say the focus is on high values then you want to go from light green to dark green so the darker green will be higher value the alternative will focus the darker color on the lower values so this way you can quickly eyeball this and then spot where the out layers are so that 698 000 is a big value this is a big value those are half a millions there that's some 950 4000 is a very big value and you can quickly spot them one of the things that i normally do at this point when i do that is i kind of zoom it all the way down to so that i can get a spread of the data and see if there is anything that is looking extremely funny and you can use this for not just positive values so let's just say you have values that are both positive and negative you can apply a diverging color scale like you know red to yellow then negative values could be red and positive values will be extreme green and you will be able to quickly eyeball and spot those out layers so that's the seventh one the eighth one is forecasting this is uh for this we are using a different type of data let's just say some months and sales figures are available to you and you would like to forecast what would be the value in the future months if this trend were to follow uh you don't need to again write a formula to get this kind of an answer you just select the data go to data and click on forecast sheet excel will write the necessary formulas for us it will show you a preview of how this forecast is going to look like it will build both formula logic and output visual as well so excel is saying your current sales trend is like this given this i am predicting that your future sales will be like this with those two boundaries indicating the 95 percent uh probability confidence interval what this means is xl is 95 percent sure that the future values would be anywhere between those two lines again this is randomly made of data so i wouldn't read too much into it but you can use this to analyze and understand the trend and seasonality of your data and do forecasting you can adjust the confidence interval let's just say you want to be very high confident you can set it to 99 you want something that is just enough so 80 percent confidence interval and you can even specify the seasonality excel will detect automatically but you can say okay wait a sec our sales follow a three month pattern so you set this to three then excel will say oh if this pattern repeats every three months this is how i think future sales is going to be you have an up and down pattern going on and once you like all of this you can go and click on create this will create a new worksheet with the necessary calculation and output both put together for you so that you can use them or you can take that and add some additional things so that is forecasting again no formula written but you can examine that there is actually formulas behind all of this that excel builds for us and you can use that to build on top of them so the ninth one is interactive chart with slicers again this is something for which you don't need to write a formula to create an interactive chart all you have to do is turn this data into a table that's the first step once you have a table you can add a slicer on on the table so i will insert a slicer on my product group and i will put that there and then i'm taking my sales figures i will insert a regular column chart now this is not going to look very pretty because this 100 sales figures but we will just put that there right next to the data this is not a very appropriate chart but you know you understand the technique here so once these two are there you can click on these things and the chart will change of course what is happening is the chart is changing but is also shrinking because excel is hiding some of the rows so i'm going to just right click on this uh go to format chart area and quickly set the properties to don't move or size with cells so that the chart will not move when the additional cells are getting hidden and now i can click on gaming pcs home security phones or tablets and i can see the sales for those alone it would help if there is actually names of the people here we can do that quickly by going to select data and edit this and point to the range of names there so we can actually see who these people are and and understand what's going on there and you are now looking at individual product group people and their sales as well so this is a a simple interactive chart built with slicer and it works just beautifully without us writing a single formula or doing anything it's all just click point and done so that's number nine what is number ten well let's just say you ran out of ideas and you really want excel to do the work for you all you have to do is pick any cell and go to home and click on ideas now some of these things that i'm showing especially ideas is only available in excel 365 so if you're not using xl365 you're not able to do that your best bet is to use ctrl q to do the quick analysis and let excel do some work for you but otherwise just select anything and click on ideas and what excel does is it will apply some machine learning artificial intelligence ideas on top of your data to come up with some interesting insights so for example it is saying oh i think you want to look at sales and age by product group or it says sales has outliers for these three dates joined so people who have joined on those three dates have brought in most sales or this is how the age frequency is looking uh for example this is uh saying age and sales appear to have five out layers so if i insert this chart i'll get a nice little scatter plot that puts um age on one side and sales on another side and then tells me that this is how everybody's looking but look at these five people they are far apart from the rest and i think these are interesting so you could probably understand what's going on like why someone aged 46 is selling law and what is going on with these four people who are aged all over the place but they're consistently selling about six hundred thousand dollars so that's uh number ten to use ideas to do the work for you so that is it uh for for this particular video ten awesome things you can do in excel without formulas now i don't want you to go and walk away thinking oh i don't need to learn formulas well all i'm trying to say here is excel is a truly powerful application and if you if you are getting inspired with what i have shown you so far imagine how much more you can achieve with other tools that are available in excel like formulas or pivot tables and you know you could go and produce something that is truly remarkable and valuable for your business or yourself so all the best and i hope you enjoyed this video i'll talk to you again in another one bye

Channel: Chandoo

Views: 206,778

Keywords: chandoo, chandoo.org, Excel, spreadsheets, Excel tips & tricks, Easy Excel, easy excel tutorial, How to use flash fill in Excel, Quick analysis of data in Excel, microsoft excel, pivot table, excel tutorial

Id: W1QhTQCasb4

Length: 20min 1sec (1201 seconds)

Published: Tue Oct 27 2020