My top secret Excel Productivity hacks - Revealed // FREE Masterclass

hello everyone welcome to our july live stream on my own excel productivity secrets now before i jump into the actual session just tell me in the comments if you can hear me loud and clear while people are uh getting their bearings i'm just gonna get started with our usual stuff so let's just put a welcome message there and i've got an excellent session planned for you today uh there is a lot of things that i'm trying this time and i am i'm really excited to share some of my secrets but also help you get more productive with whatever you do using excel or maybe the ideas itself you can take them and use them for any other software that you use it'd be power bi or you know powerpoint or whatever else that you're playing with so i am really excited about this i can see that uh in the comments people are telling me that the audio is good that is awesome and um and i can see that you know we have people from all over the world and the chat was kind of buzzing even before we started the stream and i was peeking in and out while i went downstairs to prepare my usual cup of coffee and you know get started with the the routine of setting up the stream and everything uh but feel free to say hello to all of us and you know say hello to each other uh send some positive thoughts around and uh you know let's let's let's get started so as i said i'm drinking coffee and um you know let me know in the comments what are you drinking uh this is the standard question that we all i always ask in my stream like you know what do you drink and well while you get that started i can see that now where people are pouring in and what we will do is we will go through a bunch of different things today because productivity is not really limited to a bunch of shortcuts or knowing this or that it is a whole spectrum of different things and i try to cover a bunch of different aspects so that you will feel like okay we are learning something practical that i can take away right now but also maybe use other um other ideas for longer term productivity let's see what people are drinking matt is having a a coffee or is it 10 coffees matt maybe slow down a bit [Music] nicholas is having milo oh wow and oh this is new peach tea i never had peach tea so that sounds like an interesting thing to try robert and someone is having tea uh vihanga sachin and nishanti ado is having beer coincidentally my son's name is also nishant and and baby is having vietnamese iced coffee wow uh all over the place sherry is having coffee as well and uh dom is having masala tea oh well it's a little too early in the morning for masala tea for me i usually start my day with coffee and then around four o'clock in the evening that's when i get my tea and of course you can't go wrong with water all right let's just jump into the actual powerpoint presentation uh as as people who are familiar with the live stream and have been to few of my previous sessions may already know there is a bunch of sample files available for you to download they are available in the video description so just open the description of the video and you will be able to see them but if you have just logged in like 30 minutes before on the stream you may have to refresh the page to see the sample files for this particular session though it is not necessary that you download any files you can just watch me talk about this and take some notes and always come back to the stream page to download the files but feel free to download if you want so um all right i will get into the presentation now and then i can come back to the comments uh please keep the conversation going uh and i can see that there is a lot of beautiful messages coming in from all all of you um i just want to read one or two anthony says nice to see you chandu very nice background scene specially for a small plant best wishes for you thanks anthony i i this is my home office and i really enjoy uh keeping it um like this for like my recording studio you could call it like that and uh it's fun um i want to introduce a special guest as well since we're talking about the background i'm not really sure if she would appreciate the disturbance but let's see [Music] do you see that that's that's our dog she's she decided to come and come into the office and and cuddle up today uh to give me company so i'll have to be really quiet when i'm being productive in excel today otherwise she'll wake up and probably start barking no not normally she doesn't bark yeah except that's uh that's our dog excel by the way so [Music] she's still sleepy all right let's jump into the screen here and oops that was the wrong slide okay so the topic for today is excel productivity my top secrets revealed um in the this is gonna be a slightly different stream because there's going to be a little more powerpoint slides and a mixture of excel uh to introduce various concepts so um but we will see how this goes so the agenda for the day is i'm gonna start with some shortcuts um because obviously when we talk about productivity in excel using excel faster is is the first thing that comes to mind but the real productivity is obviously achieving greater things with excel right it's not important whether you whether you know like the 600 shortcuts that are in excel or not you should be able to achieve things in your work or in your life so that's the real bit but we'll start off with shortcuts because without knowing how to get around in excel you will not be able to achieve much so then i'm going to talk a little bit about the mindset that you need to be really good really awesome uh the mindset that i i use and that the mindset that really worked for me so that's that's the next bit and then i will also talk a little bit more or kind of circle back to the shortcut but instead of just talking about the shortcut alone we'll talk about how the ui of excel works and how to get more out of it and and then i i will also take your questions so of course the question and answer session is not really limited to the end feel free to ask questions at any point of the stream and post them in the comment chat window i look at the chat window all the time so if you have a question then i will look at it and if it is relevant for the topic that we are talking at that moment i will address that otherwise i may circle back to that at a later point and as usual before we begin uh the three things that i want to highlight is the downloads questions and supers so as i said the download files are already updated uploaded this stream has three download files and i might actually add another file depending on how the stream goes what questions we get uh later when the stream is finished but right now there are three files that are already uploaded and linked in the video description i suggest that you at least download them if not now during the part of the stream or when it is finished so that you really know what i'm talking about for those of you who have the luxury of um both using excel and watching the stream feel free to download but if you're checking down the phone or if you are watching it on replay you can do whatever works for you but check the description for the links when you're more than welcome to ask questions that is the whole point of doing a live stream otherwise i could have recorded this video put it on my channel and let you watch um um so please ask your questions by putting a queue at the front and stay on the topic the topic for our stream today is excel productivity so i'll try to address your questions on that topic as much as possible of course you're welcome to ask any questions and i will try to answer them as well if time permits and if the conversation is relevant and if you are asking a question please do not repeat it is quite rude and also um it will kind of make the chat window too long and i won't be able to monitor i'm the only person who is doing the stream and we have already around 480 people watching the stream so it gets a little too much if if there is repetitions um and this is the most important bit i because productivity is a personalized thing right the way i use excel works better for me because of the things that i do with excel but you might be using excel for different things and your way of doing excel works for you because of what you do in excel so whatever you're using whatever works for you please share those tips in the chat window or the comments if you're watching it in the replay so that we all can learn from each other um every every week i learn new things in excel sometimes the ideas and the concepts that i learn come from the chat window sometimes from other other areas so please post your tips as well that will all help us uh too and if you would love to support what i do at shenzhou.org and on the youtube feel free to support me with the super chat or super message icon button on on the stream it's very very simple and you know you just go click on the button type a message or say something nice and you know buy a super sticker or a super chat and then use that so that's that um let's uh start with the dad joke there's a problem with my keyboard i think it's depressed it's not that funny but let's just jump in so the first bit is the shortcuts i will jump into the sample file this is just a bunch of dummy data and i'll show you some of the shortcuts that you need to get around um if you have been using excel for quite a long time you may already know some of those things but feel free to you know watch that because you will always be surprised um all right let's just see what's happening in the chat window first because there's been quite a few uh sherry says i love the chalkboard message i wrote it last night so there's quite a few questions on this theme it's not marked as a question but is excel enough for data analysis um and people always want to hear like a silver bullet answer for these kind of things like if i do this one thing would i be successful in in whatever field of work that i'm doing unfortunately the answer is never that simple excel tends to be the most used data analysis tool right now in the world so learning that would be the obvious choice but you might end up in a position or a company where they're not using any of the office tools they might be using google spreadsheets for that matter and so what is really required is that mindset right you you need to think about uh how do i learn new things how do i feel comfortable in new situations and how do i get into that so that's the real thing required not um not knowing one tool inside out um of course that also helps but you want to have that open mindset that yes i am able to learn so just to give an example uh the the last consulting work that i did in new zealand is with one of the government ministries and they they asked out like if somebody is able to do some of the excel reporting work that was that was the contract description so i approached them but during the initial talks they said but we also need somebody who's familiar with the tech one system now i'm not really sure if anybody is familiar with the tech one system but it is a payroll and a hr and administrative system that is used for processing that side of things now i have never worked with tech one system before but what i do know about myself is that i am fairly good with learning new technology i am i i really enjoy that process so that's what i told them that hey i've never worked with tech one but this is something that i would love to learn because i see that it is used in few other places so i might learn that and they gave me a chance and i really learned that system as part of my contract with them so that's that's really what you're looking for you're not really looking for like one tool to do data analysis of course excel happens to be the main tool so you would learn that but uh feel free to keep that mind's mindset open all right let's see what else okay so that's that let's just jump in uh there's a few other comments but i'll come back to these uh because um so i'm gonna open this i'll shut down my face for a while um and then we will get in here so this file the one that i prepared here is is a really just blank data file that we are not really going to use this data to do anything we are only going to use this to practice some of the concepts on shortcuts and productivity that i am teaching so normally when you go and um open a blank workbook you can do it in a few different ways and some of you may already know the shortcut like you can press ctrl n and for new and that opens up uh this because we i will be talking about shortcuts i'll try to get my on-screen keyboard um and and kind of pop that in once in a while just to show you which keys i'm pressing but hopefully it's not too confusing anyhow we will not use a new file i've got some sample data here feel free to just make up some random numbers if you if you have excel and you have not downloaded the file but you can also download this file and if i have this data and i just want to see the extent of my data there are few different things that i try when i get started in excel the most obvious ones are the navigation shortcuts that means getting you getting around the excel so you could use your arrow keys to navigate this is fairly straightforward but you can also hold down the control key and then navigate in the direction that you want so for example control down will take me down all the way to the last cell of that sequence so it took me all the way to this particular cell here um but if i if i let's say for example this one is blank then uh control will control down will only take you up to husain auger it won't take you down because there's a blank cell that's the control um control and in the arrow keys and if you hold down the shift then you will select this these are um i would consider these as like really basic shortcuts that most people who have been working within excel for a while are are familiar with all right there are some other things that i find really interesting within excel again this is uh depends on how you use excel and i don't really use this shortcut that much but the the shortcut is you can press either home or end and then follow that with the arrow key to indicate which direction you want to go so for example i want to go here to the boxes from the sales person so if i press the end key and then press my right arrow it will take me there because i'm telling that i want to go to that end so so i'll show you the sequence here with the on-screen keyboard so i'll press my end key and then arrow key i think this on-screen keyboard is not working so for that the reason why i don't use it this particular sequence uh simply because i got a smaller keyboard where the home and end keys are not really on the keyboard i have to hold down the function key to access them so i find it too tedious but if you have got like a separate number pad and arrow keys which is the traditional larger keyboard then then you could use that to also jump so you can press home or end and arrow key in the direction you want to go it will take you to the last cell in that direction all right when you have data like this one of the things that i also try to do is instead of just reading it like that especially because this data is um the the kind of data where there is a lot of information and i would be using this for data analysis what i would normally do is i'll turn this into a table and the shortcut for that is ctrl t t for table so you hold down initially it doesn't matter which cell you select as long as they're all together just select any cell press ctrl t opens this create table box make sure you enable the header option if you have headers there and click ok and then a nice table comes up the advantage of tables is the it will let you work on the data as a whole rather than individual cells and columns so we are no longer thinking at c11 or d15 like that we are thinking about all of this data as one set that is together that has a bunch of columns and rows and this is uh one of the important things when you are working with with complex data sets and multiple stuff within excel you want to deal with things as whole rather than individual cells that will liberate you from worrying about smaller things all right um i'm just gonna pop into the chat window just to see if there is any disturbances [Music] etc so some people are asking what happens if i press the end arrow does it go all the way or skip the blanks etc you can try it that's why i provided the file feel free to simulate what you're doing and then test it out and if you find something interesting post it in the comments i'll i'll switch back to the video now and then okay so that's the table one right we created a table we we set it up within once we have the table then we can deal with the data as a whole rather than individual set that as i mentioned and when you well some of the first things that i do especially if i'm starting a project or a workbook where i'm the whole purpose is to analyze some data and produce some insights um which would be about 90 percent of the case for the work that most of us do right we get the data we analyze it and we produce something so then i i try to do a sense check of the data first understand what is going on there uh and is everything all right so the some of the common ways to do this are you can when you set it up as a table automatically excel adds these filters so i can quickly use the filters to sense check but if let's just say the filters are not on you can enable them by pressing the control shift l l is for list so the shortcut is ctrl shift l again you don't have to remember any of these shortcuts um the as part of the sample files i provided this nice simple shortcuts poster you can again this this is there in the video description download it uh it is a it is a one page shortcuts list that that kind of shows everything that i'm covering in the shortcut section of the video um so um all the shortcuts are there you don't have to remember um but you you you want to really think about what what i'm saying here so then uh once the filters are there i'll kind of quickly scan through the list to see what's going on um and uh you know get a sense of what is the volume of the data so for example here we have six countries but is there a blank country as well what's going on so that is curious so i can kind of select the blank and i can see that oh this person is selling to a blank geography what's going on gunnar so i can then go and investigate and maybe it's wrong data or maybe something funny is happening there same with the salespersons again i can see there's no blanks there like that if i'm bringing this data from another system normally the data wouldn't even be in excel it'll come through um let's just say power query connection then we could do this kind of sense check within power query as well and that's how i normally do for some of the business situations but occasionally the data is already in excel somebody emails this to you and you just want to sense check this understand if everything is in the place before going going and analyzing another thing that i also do is i select some of these columns where numbers are there to really understand the size and the range of the numbers and when you have the data as a table here is a handy trick to pick the entire column place your cursor on the header of the table and then it turns into that little black down arrow okay it might be hard to see on the screen because the mouse pointer doesn't really zoom but that's the amount so if i click when it is in the down arrow shape it will select the entire column and once it is selected i can go to conditional formatting and i usually just do like a color scale or something so for example let's just um go with this color scale so red to white white will be smaller values and red will be higher values now i see that there is a lot of white values alone even though the numbers range all over the place this is suspicious so what's happening and it's not how easy for me to scroll through thousand rows at this level so what i also do is once i do this i take my zoom and zoom it all the way down and then at this level if you go all the way down then it doesn't really make sense but at certain level um ah this is the first my excel stopped responding all right i'll have to restrict the excel here uh not even sure what's going on that but i'm just gonna all right we're back apparently zooming down takes way too much resources who'd know anyhow when when you zoom it down it is still busy but i can see that there is a a a cell that is too red this indicates like an extreme outlier in the data and this is what i'm really looking for um so that that one is is really high and that's why the heat map was kind of skewed towards that that was the only one that has too much red and everything else is becoming white that looks a bit suspicious to me so i'm just gonna go back and and then see that that is forty nine thousand nine hundred and twenty three dollars hmm i mean this is randomly made of data but imagine i work at the awesome chocolates company then i i kind of know what this is so i can see this and i think ah that doesn't look alright i need to probably go and investigate and we might come to a conclusion that this is actually some garbage value that is coming in somebody missed a decimal point or something so we could go and fix that to to solve the situation but this gives you an indication without having to go and even analyze the data because before you analyze you want to make sure the data is clean so this is one one other technique that i that i apply and then i can just clear the rules for for the selected cells like that let's just see what's happening in the chat [Music] so siddhartha asks a question with with the super chat can you make a pivot and highlight the highest value in column wise and it should change when the slicer value change sure but i would probably uh thanks for the super chat anyway but because we are not really talking about pivot tables right now i'll come back to this question feel free to remind me again later on but if not i'll try to at least leave some links in the chat below after the stream ends that gives you more information on that but again some people have already answered like you could use min and max for that [Music] um asks what is the shortcut for zooming in and out this depends on i think a few different things but i think if i hold down control and use the scroll wheel on my mouse that kind of zooms down and up in the direction of the scroll just like how it would work on the browser but again um the actual thing might depend on how your mouse is configured and how your excel might be configured but that's how i've always done it i i don't use the zoom slider much but it's handy to kind of step in and go all right more shortcuts so we talked about table creation we talked about navigating now let's talk a little bit about how to get and type data faster not that you would be doing a lot of typing in excel but whether you're typing a formula or or creating values or whatever you we would be doing a lot of typing in excel so learning a little bit about that is also useful again if possible we don't want to type so the easier shortcut is always copy paste if the data is somewhere else ctrl c ctrl v but let's just say you cannot copy paste you must type things in into excel then you want to use some of the power features of excel that uh that let you do this typing tasks uh way faster than than than possible so how do we type things faster there are a couple of shortcuts that that i use again a lot depends on what what is really what i'm doing but let's just say i want these 10 cells or or 11 cells or whatever to contain the same value so then what i do is i i select all the the area and then i just type the value in the first cell so i'll say same value and i want that to go into all of them and then press ctrl enter control and enter and it kind of puts the value into all the cells so you just pre-select and then do it the beauty of this is you can kind of select all over the place i don't want to select anything next to the table so like this they are in different parts and then put same value control enter goes there okay you may not be super impressed at this point you might think huh okay i could have copy pasted i could have done something but because the control enter concept is really just putting the value into multiple cells i can take this and i can kind of use this to my advantage to type the same formula as well into multiple places okay so that that can change the game again usually within spreadsheets the data is more structured so you're not really doing ad hoc things like this but from time to time we may have to so that that that is useful to learn control enter the next one is let's say i have some value here and i want that value in the next cell that is immediately down right it could be a value it could be a formula whatever may be the case whatever is in the cell level i want that here you could use the shortcut ctrl d d4 down and it will just take the value from cell above and gets you down um ron already mentioned that in the comments uh big thanks ron um and um and girish uh sends in a super sticker as well thanks garish uh for for your support and uh so that's the control down arrow to to get there are a few more ways like you can push top and side as well but usually when when we are working in the spreadsheets they they tend to go down so i find that control down is good it works not just for values you can also use it for formulas so for example i got a count formula here i don't know that just counts the products so i got um it's zero because product is text so we'll just use count a so i got my value there 628 and i just want the same formula here down for some reason i can use ctrl d get the formula uh press f2 and then maybe change your edit too okay so that's uh that's the next shortcut okay now i'll go to this other data tab and i'll show you a few more things here let's just um i got here my product names and i want to for example just extract all the capital letters like mb db ac like that don't ask me why but maybe imagine these are names so you have to do it so then we can extract kind of weird things based on a pattern like that using um you know the flash fill feature of excel so what control d control enter though they are doing is they are just filling based on what you already have but if you have a pattern then you could use this so you will just type the first few values mb db and as you type you would see that excel kind of gave a suggestion but if if not you can type a few and then press ctrl e um e for excel i guess and it will extract the capital letters for you and kind of capitalize everything in one go you can use this for capitalizing names or you know generating something else like that so this is the control e you can again find this from the data ribbon flash fill option here okay and it will show you this it's this the thing to keep in mind with flash fill is it's not a um it's not a formula so if your data changes this output doesn't change so you will have to go back and adjust it um if needed so that's uh that's the flash field but again very useful to do certain things where there is a clear pattern and you want to just um do it okay now i'll show you one more shortcut this is again very handy um when when you want to fill a bunch of values down in a column or a row that that you want so for example many of you already might know like if i have one two three and i just want up to ten i can type a bunch and then just select and drag down and that works this is fairly straightforward but uh what people may not realize is it is actually pattern sensitive so if i type one and four and then seven so we are now skipping by three i can do it and it will follow that pattern for me the seven days pattern okay uh let's just extend this a little more and uh let's type today's date so 31st of july uh don't worry i'm in new zealand so we are in future um already in the 31st of july and um and i want to go to i don't know five days from now so for august 2021 okay so we'll drag it like that it'll it'll follow the pattern for the dates as well um in needless to say you can type a date and uh drag this down it will give you one day at a time as you can see but if you click on that little box it will give you a few more options so for example you can only fill the weekdays or months or years etc like that so these are some more additional things that you could do there is even more things that you could do with respect to data entry alone but i think we kind of covered a good ground there so i'll stop that and i'll see what if there is any questions otherwise i'll go back okay we'll go back to the the table and look and make sense of the data remember we had one of the employees selling to a blank geography i suspect that there might be more blanks spread across the data and that they might screw up when we try to analyze the data so what we want to do is first spot all such blanks and take a decision as to whether to keep them or discard them or whatever so to find all the blanks what you want to do is first select a range because this is a very big range it might be hard for us to visualize what is going on i'll select a sample of this data i'll add this into a a new worksheet okay and i'll just randomly make some of these cells blank okay you can see that there are some blanks all over the place and first task is identify these blanks so select any one cell and then you want to select this entire range first if it's a small range you can kind of hold down the shift and pick this but if you if it's a big range or you don't really want to do it with mouse you can select any one cell and then press ctrl a a for all that selects everything there is another shortcut as well you can also press ctrl shift 8 which is nothing but control star asterisk key on your keyboard let's try our luck here control shift star so that's the keys that we are pressing to select this entire range once you pick that then you want to just spot the blank cells alone so we use the f5 key function key five this opens the go to box and from here click on special and you will be able to spot the blank so you can say special blanks and then click ok what this does is it will go and identify all the blank cells and it will select them for you you can see that they're all selected now while keeping the selection you could for example fill a color in there so that they're easy to spot and you know maybe ask your team to work on why there are missing values but normally if there is a blank value then we find that they're not even useful for the data analysis work so maybe we just want to delete that so the alternative is while keeping the selection i can press ctrl minus ctrl and minus so delete stuff and then just select entire row option click ok all my blanks are gone my data is clean even if the blanks are spread across multiple columns it will just work beautifully so this is um another way in which especially when i get data from unreliable sources i try to do this step just to make sure that i'm only dealing with sensible data there are more ways to clean up data and i got a beautiful video on the channel some of you might have seen it but if not i'll put a link to that as well in the description later on and do check it out but i think that kind of nicely wraps up working with data getting around in excel i'll stop the shortcuts section with one other shortcut which is because there are many shortcuts in excel it is hard to remember every little thing and there is no point or no value in memorizing these shortcuts if you don't especially use them on day to day basis but there is one shortcut that is very easy to remember and you can easily kind of self learn more shortcuts as you go and that shortcut is this alt key now because of my accent some people find when i say alt key what is this art key so that's the key hold down the alt key go to excel just hold it down briefly for a second no it's not on excel and excel will show you shortcuts for every little thing on the menu so it will say if you now press n you'll go to the insert ribbon so n and then once you get into that ribbon it will show further shortcuts it will say oh if you press v i will insert a pivot table for you from this data beautiful right we don't have to use keyboard any mouse anymore within keyboard and i don't have to remember the shortcut i can look at the screen it teaches me what shortcut i need to press to navigate all the aspects of the spreadsheet so it is a very powerful and beautiful idea that is easy to remember and use the best part about this that i like is it works same way in excel outlook word powerpoint all the office applications where you find the ribbon so one shortcut that works across the spectrum so it is very easy and especially when i i don't really use outlook at home because i uh i'm self-employed right so my email is on gmail and i just use gmail and i know gmail shortcuts but sometimes when i go to clients places and work they use outlook and so i'm forced to use outlook and i'm not familiar with outlook that much so then i go look for things and i find that this shortcut is really easy for me to just get around and get what i want we'll come back and talk about more shortcuts uh that will help you get more productive uh at a different level but for now i'll switch back to our presentation uh to to talk about different type of productivity aspects let's just see what's going on in the [Music] chat our han sends a super sticker uh thank you arhan and kumar says field day or month is awesome yes it is really awesome isn't it like um there is actually an even awesome trick uh which i'm a little shy to show mainly because uh i'm not really i'm not really tested it many times but i did learn that and i thought this is something that i need to teach people so let's just say i want to put a start date right so we'll put this as one august 2021 okay and then somewhere down here i want an end date that is let's just say um 31 october 2021 right so we got these dates i want to fill these gap with the dates that are equally spaced now normally we would have to calculate all such things and everything but if you select these two dates and go to home there is a shortcut for this as well but it kind of gets tricky so and then home fill and i think uh let's just try with the series okay so we'll do this home you need to select both start point and end point fill series and then um so it says what is the step value step value is nothing but how often it should step again this part of the screen is a little small so i can't really do anything but instead we will try linear or no just say stop value oh you can't even put that there let's just try this or maybe yeah that's what i needed this is why i was rusty to i was instant to not show it because i don't use it that much but we'll do it again from the scratch now that i got it right so we'll select all of this and then we'll say fill series and then just say trend okay what this will do is it will figure out oh you want to start here you want to end there in between i will fill the dates automatically so that they go from here to there and it will calculate all the necessary dates for you to step through so in fact not just the date it figured out that if you start on that to end on that you need to actually stop at 2 pm and then go to the next step so slightly interesting thing again you may never actually have a need for this but if you do you have a way of doing this in excel so that's that um en also sends this to super sticker as well thanks ian and this is jagoda uh sensor super sticker as well thank you and uh let's just see there's a question here from balu when data is filtered and applied some formula in the filtered cells use flash fill later when i double click on the formula cells applied formula is not showing please answer if you get my question not sure why the formulas would apply the problem with this kind of thing is this is also the reason why i say use tables as much as possible because tables ensure that any formula you type in in a cell in a table column is equally applied across the table so it is the same formula all the way through so whether you filter or not the formula is there i'll show you what what the what i mean by that uh insert so we'll put a formula here this formula is nothing but amount divided by boxes so you see that i'm only typing the formula in the very first cell but when i press enter it uniformly applies the logic across the table so and then this works even when i filter for an individual product so if i say 99 dark and then type my formula which would be amount divided by boxes it's applying but it's not applying for these it is applying for the entire table this this is what as a data analyst you want you don't want repetitive work you don't want errors so this ensures that whereas if you have data like this we haven't got headers here so i'm just going to quickly grab these and and ctrl shift l to enable filters and i just filter something like this now amount divided by boxes is e5 by g5 it's no longer at the right amount by the red boxes so you're technically writing a formula that goes into e5 and g5 and doing the division so it only applies to that cell and when you drag it down you might think it is applying all the way to the data but it's just applying from five to seven so when you unfilter you'll see that the others formula cells do not have forma so you want to avoid these kind of things if possible and if you're doing it on filtered cells then you need to be mindful that you're applying formulas on filter cells so that's that um amol sensor super sticker as well thanks amol [Music] and some people are saying you know i missed the part of the presentation it's on youtube so it's recorded you can always go back and play follow here on follow from here on and then you can always replay the rest of it um and now zar says tables and custom filters don't work together custom views it is actually a limitation of tables that you can't use custom views when you have tables um so it's it's by design within excel so there's nothing we can do um other than living with that i i feel like that's a small price to pay for the amazing awesomeness that tables bring to data analysis world so let's just uh william says how do you copy paste data from a filtered table well you copy paste like the way you normally do there are some limitations though the depending on how you are copy pasting sometimes when you copy paste excel might bring in all the data even the unfiltered ones uh and that could be a hassle uh there are a couple of ways in which you can copy paste so the one shortcut that probably you may want to know is selecting just the visible cells so i'll show you by that what i mean if i let's just say filter 50 dark now if i say ctrl a and ctrl c you see that these dotted lines um they kind of separate so technically excel has only selected and copied the visible rows it's not really copying anything not visible so if i now go and paste it i only get the 50 dark i'm not getting anything else right but some other situations it may actually copy the things that are filtered depend a lot depends on how the data is set up and everything so in that case what you want is you want to be able to select only visual visible cells so there is a shortcut called alt semicolon and you can use that you can just select one and then say alt semicolon and it selects uh right now it is everything so we'll just pre-select this table and then alt semicolon and this is now literally just selecting only the visible cells that means anything filtered out is gone from selection and uh and now if you copy then it will give you those those values literally the same behavior there is no difference between this and that within this spreadsheet this is one more row because i copied the headers as well but that's pretty much it i hope that helps so the short shortcut that you want to remember is alt semicolon to select the visible cells anyhow i think we kind of rambled on the shortcut part of the productivity which isn't even a big thing right knowing shortcuts is important but it's not really the one that makes you super productive if you want to achieve awesome things in excel then you need to know other sides of the productivity equation so i'm gonna go and show you some of those uh now i just want to give a quick shout out to muhammad for uh the the super sticker as well and i'll come back and answer some more questions but um because we got quite a lot of awesome content planned and it would be a bummer if i don't share all of this in the live stream so i'm gonna move on and i'll come back to you promise so we'll get in here and so we covered the shortcuts number one as you could guess there is number two as well which is coming down some of the number two shortcuts we kind of meant in this part um and the next one is mindset so i'm just going to hide everything yeah the next one is the mindset when it comes to productivity now there is only so much you can learn about using keyboard and mouse but a lot really depends on on the mindset of how you are working within the application working within the frame that you are presented with and use it so i'm going to share some of the key ideas that work very well for me when it comes to achieving powerful and great things using excel or using other applications but first what is this see this i'm gonna just show you see this my keyboard is leaking something don't worry it's under control that's just a joke all right um the first concept is batching what batching means is that when you are doing any activity you want to do it in such a way that find similar things and then do them fast so it could be data analysis it could be preparing some report it could be making a complex model or whatever whichever may be the task especially if the task spans like multiple days or a lot of different spreadsheets or big volume of work you want to first identify the the similar activities in that thing and then batch them together this is a key concept within productivity and i use this all the time not just when i'm using excel but anything for that matter let's just take youtube where i make videos and produce content or on my blog or my courses i always do batching wherein i create a bunch of videos i i then go and i then go and edit them in a batch and then i go and schedule them in a batch i go and publish them in a badge so that's what you want to develop so let's just apply this to excel like how do i batch let's say you you have some data analysis and you want to produce a dashboard now in that there will be different chunks of work the first set of work could be working at all the data right getting the data making sure it is good and clean and all of that so that could be one set of things that you can batch together achieve that in in one way and then go and make some calculations go and make some outputs talking more about that later on in the slide so that is the batching concept that you want to develop and and use that the main reason why i think batching works is when you are doing similar activities you will become productive in that space so for example if i am just navigating the data and arranging my spreadsheets then then that mindset that i need for that is is completely one direction whereas if i'm making charts then i need to probably think a little bit creatively and outside the box to make sure that they look good they are presentable they work well so for that the the direction of productivity that i need is in a different way so the way that i approach this batching concept is i look at it in three silos the silos are data calculations and output usually especially within the world of data analysis you can clearly spot these three buckets right you got data where all the data related stuff happens you got your calculations where all the calculation stuff happens and you got your outputs where all the output stuff happens classic example is i can take my data and here i am for example setting up power query connections or copy pasting data and setting up tables or making sure that i am filtering out only the [Music] relevant bits and all of that and this is something that i when i do it i also need to probably talk to various stakeholders to understand if i have a if we have everything for the data ready uh how it is coming what is the refresh process and what not so that's the data side of things and then when i'm calculating i'm thinking uh should i use sumifs or filter or pivot tables and and then i'm doing that right so that's the calculation and when i'm making outputs i'm thinking more along the lines of what chart would i use here or what um what sort of formatting should i apply what kind of color scheme should i select so all of those require a different type of productivity in different shortcut sequences so especially when you're working with graphs and charts you use a lot of mouse than keyboard whereas the other two sections you use keyboard so it's good to silo them and then work in in that direction all right uh i was afraid that you could fly or not but many people got it so that's good to see uh and excel work says batching what is what put food on chandus table if someone thought it was excel in fact this is how i prepare my food as well uh it's simply because i um i work from home all the time whereas joe and kids they they got their school and their joe works full-time um now so she goes to work so um normally they kids don't eat as much lunch right they take their carrot lunch lunch box and go and i i prepare my lunch on monday and then i kind of prepare it for the whole week i batch it and then i put it in the fridge and then i just eat it every day it is easy for me then i don't have to because i am working from home i have to go downstairs at half past 11 or 12 to prepare lunch and that kind of breaks the synchronous thing for me like i'll i might be in the middle of a video recording or editing or whatever so i just batch it and then i don't have to think about my lunch for the week so that's uh works for pretty much anything in life for that matter but uh yeah that's that mary sends in a super sticker as well and uh i think i missed one and yeah thank you so much mary for that and uh let's just see what's the next concept the the the next concept is knowing your audience this is super important especially if you are preparing work where the audience is not you you're you're not doing it for yourself you're doing it for somebody else then a big time waste aspect in my opinion is if you don't get a good sense of what your audience want and you just go and make something then you will end up creating a lot of repeat work for yourself so you need to have a good sense of what your audience want and there is no point guessing it right even if you are the super expert of the world there is no point guessing this you want to talk to your audience understand clearly what they want and then go and do it so if i know what they want then i can just do it in one go but if i don't know what they want and i have to prepare like three different versions and show them it's gonna take me 3x the time and that is really not being productive so when it comes to productivity know where you're going then you can go there straight away that's the whole point knowing your audience how do we know our audience when it comes to excel data analysis or reporting work one technique that really worked well for me over time is making some mock-ups if i'm preparing let's say a dashboard or a report what i normally do is i don't even get into excel and and then do the work i ask them how so you want a hr report here um is this what you want and i draw a sketch and then i show them to show it to them then they might say looks good but maybe here we want to see this information or here we want to see that information and and then i can go and and kind of improve that multiple times and finally once i have like a clear idea and clear alignment with my audience then i can go and do it so that's the that is really the how i try to be productive because then i know what they want i know what i can produce this also eliminates a lot of waste in my opinion because when you try to do it one of the things that i find is when you ask your audience so what do you want they'll give you a big laundry list of items like they'll say oh i want these 300 things to be done but then as you start putting them in a in a box layout because an excel spreadsheet is like a box so whatever they want need to fit into that box so when we try to fit them you'll find that not everything can be fitted into that box so then it becomes easy for you to negotiate with them you can go back and say hey guess what if you want that information and this information the report is going to be like 70 pages long do you really want it and then they'll back down and then say oh that's fine maybe just cut down this or maybe because when people say they're wishlist they are not really actively thinking but a mock-up is the process where people sit down and think about these things so that's why it really helps i'll share one anecdote here this is a powerful story that i learned early on as part of my data analysis journey uh it goes something like this this is way before the time of your um your iphones and and all of that one of the predecessor for iphone and i might be wrong here with the names or the companies but i think one of the predecessors for iphone iphone is is a palm pilot it's like a handheld digital organizer that that helps you call and manage and things like that so when the company was designing this apparently the founder or the product designer or whoever that is he would usually carry just as a wooden block the exact size of that palm pilot so it will be like a wooden block and it would be on his hand all the time so he'll go to the designer meetings or developer meetings and the developer might say ah we got this fancy new feature we can put this here on the thing and then he'll open the wooden block and say where do you want to put it so i'll put it there and then very soon the whole block is full so then if the new designer or the new marketing person or new developer says i want to add this then you show the thing and then say where is the space on this like what do you want to drop to add this right so that's the uh and that lesson stuck with me when i learned about it uh almost 15 20 years ago now because it is such a powerful lesson right it's a good metaphor you're you're showing the space you're saying where do you want it if you wanted this then this needs to be gone so that's the and that helps you and your audience come in alignment and make sure that you are achieving something that is manageable and doable so yeah that's um that's a little bit about the mock-up process all right any thoughts any questions if you have some information or relevant experiences in that direction please feel free to put them in the comments and uh otherwise we will jump into our next thing which is another set of shortcuts and ui tricks that i have for you i'm gonna just read through some of these chats comments joey says mockup is like mvp ask the y question five times and find out what the real problem is again this is a powerful way uh and i i use this particular line of thing in my online classes as well especially when i teach dashboards when you are mocking up and when you are asking people what they want very relevant for information dashboards but even for other lines of work you want to ask them why why do you need it so for example they might say i want to see the trend of our sales then you need to ask the question why do you need it they might say i want to know whether they're going up or down then you can ask the next question why do you need it oh i just want to know if you are making profit or loss then you know why do you need it because profit is good but why do you need it so you keep annoying them not really annoying but you interview them to get to the real need of information because sometimes what people think they want and what they really need and what they actually want to see on the screen are like worlds apart and and when you get to the real why then it will be easy for you to go in that direction and present the data okay ron also shares an example like mindset break the big original problem into smaller sub steps again this is really something that is very much important as well early on when you are learning excel or even when you learn at an advanced level you might get into this trap that somehow writing a big complex formula or an array formula or or a one line formula to solve a big complex problem is is like the gold standard but i find that that is actually a waste and uh and and i mean it's it's like kind of uh not a valuable exercise there is some value in it like you will get intellectual satisfaction of doing something in less amount of code but it's it's even better to actually understand the problem break it into smaller things it doesn't matter if you put like 16 helper columns to get to the output because when you silo it you have data you have calculations you have output output is the only tab that your audience are seeing they're not looking at your calculations unless there is some problem or they want to go behind scenes so nobody is judging you and even if somebody is judging you chances are they probably do not have the same level of excel knowledge or expertise that you have so there is no additional value in shrinking the formula unless you feel comfortable and you just naturally can do it that's fine but otherwise you should not strive for that you should just go and break it down into smaller chunks that will be very easy you can also reuse the smaller chunks somewhere else for other parts of the work all right we are jumping into the third part of our presentation which is more shortcuts so i'll just get into excel here but first let me share my favorite shortcut that's actually me i like jumping high at beaches that is how i get my shortcut all right enough dad jokes i promise there's no more jokes in the presentation we'll just get into the excel now let me get my sample file here as well okay i'll show few more shortcuts so far we talked about keyboard shortcuts but do not forget mouse because i find that half of the time my hand is on my mouse so it's good that you learn how to use mouse better as well like try to uh get more productive with most the most is not simply just for navigating spreadsheet there is a lot of different things that you could do to speed things up so for example we already talked about selecting an entire column like that so if i select a table column and i find that oh product need not be here it needs to be between this and that or it needs to be all the way at the end one way to move this is you don't have to kind of cut and paste or do some massive circus there you can just select the entire column including the header and then when you place your cursor on the edge of the box it turns into a special symbol like this this symbol here the one with arrows and then click and drag while holding the alt key okay so this is uh what this will do is it will move the data okay if you do it too much time then excel gets confused and no longer shows that but let's try it again so it replace the pointer there in the middle and then once you find where it is you just leave the cursor most most button and it'll move the column there and automatically readjust everything so none of your formulas get messed up the columns are just rearranged within the place so this is way faster than that let's see some more shortcuts let's say i got this data and i want a copy of this here right next you can control c select there and ctrl v but using mouse you can do it faster select all of your data hold down control and place your cursor on the edge of the box hold down control place the edge of the box it will show the cursor as mouse pointer and a plus symbol on the top and then just click and drag you can put it anywhere really and you can get a copy this particular aspect is called control drag and it is a very simple shortcut to remember once you remember this you can use it anywhere i'll show it to you in in windows explorer here i got a file and i just want a copy of that file control drag it will create a copy of the file rename it and then put it in the same folder you can even move it around to another folder or another drive and it works as well so ctrl drag is is is is that um let's just see let's talk about a pivot tables as well i'll create a pivot table from my data normally when i work with tables i don't name them i mean i'd always name them here we have not named the pivot table table so i'm just going to name this as sales sorry my throat is already itching today so i'm just gonna get my hot water ready and then let's just insert a pivot table from this data here and for this pivot table let's just build something random here so i'm gonna just say by salesperson geography so we get some numbers just put the amount there so we get that you've already seen some of the mouse shortcuts that i'm using again i'm assuming some of you are familiar but if not uh if the row column width need to be automatically adjusted you can just select both columns place the cursor in the middle double click it will automatically make the columns as wide as the data is so that you can see it so once this pivot table is there one of the time consuming aspects of working within pivot tables is formatting the co data so amount needs to be currency and you you normally go value field settings number format and do it from there but you can also just right click and go to number format the third option is is really what that does and i can just do it from there it will apply uniformly there so that's the uh thing and let's just say for this i'm gonna put a slicer on geography so i'm looking at geography this table now i want another pivot table just like this that is also linked to geography but looks at number of customers by person or something like that we could go and insert a new pivot table and go from scratch but because we already have one pivot table set up this is how you can speed up you can just select your pivot table copy paste and just change these things i'm just going to delete some of these rows and now the slicer is linked to both of them so if you click on the slicer you can see that both of these numbers change this is because we copied the pivot table so this is this is a simple technique that i call as cloning the pivot table so once you clone all the connections to the slicers and everything is maintained and this is very handy because when i make a dashboard or a report normally there's like a bunch of pivot tables in the background and they all need to have same behavior so this is how i can speed up my pivot process i don't have to go and create pivot tables multiple viewer tables from scratch i can do it once and then just do it like this so that's that let's just um talk a little bit about charting as well for the purpose of charting i'm just gonna take this data again as i said this is all random data made up stuff and so there is no value in actual numbers here let's just say this much and we will insert a a chart like this though it's not the best chart but we will go with that and then i realized ah i don't want all of these things i just want the data up to 50 percent dark bytes there so we could kind of right click go to select data and change the source data settings for the chart but when you select the chart it will also show these boxes saying where the data is coming from and you can place your mouse pointer at the edge of the data and then just resize it so for example i can resize it like that i could even add an extra column if i want and that will automatically adjust the chart so this is how you can quickly change and for example you think i don't want to show the product names i want to show the geography i can [Music] move some of these not everything moves so for example because of the way we created it doesn't really want to move this but if you create this and that first then it will move the access values as well so that's uh that then suddenly i realize i want to show the customer information as well in the chart or maybe the box information what we could do is we can select the boxes ctrl c copy the box values select the chart ctrl v paste it so you can add additional data to the chart by copy pasting as well and this is another powerful way of adding stuff to chart without having to right click add series or any of those kind of things just copy paste and it'll automatically add the data adjust the series and format things for us i just want to give a shout out to some of these super chat stickers i came for the shortcuts but the mindset was what i actually needed thank you i'm bro i'm so glad you you said that because i believe uh many times people think shortcuts is where real productivity is but that is just like part of the puzzle there is no value to having shortcuts if your mindset is not uh in the right place so uh that's why i really wanted to dedicate a chunk of my presentation to that i have a few more slides on that and and talking more on the practical side of how to apply that mindset in excel so coming soon tom shares an excellent shortcut which is i created a macro that simply converts a number to currency and then moves the decimal over reduce the handful of clicks down to one yeah this is something that i find is like um a really pointed macro that can also automate some of the work for you the reason why i'm not talking specifically about macros in this live stream is for one there is compatibility issues like if you want to share your files or something sometimes you not everything that you do will work on other other other computers and then you will also have to be mindful of excel versions and changing things and all of that so it gets into a whole rabbit hole in itself that's why i try to shy away from that but that is another way and i use macros as well some of you can see that i got my developer ribbon on here and i got some nice little macros that i have there that that i use from time to time all right uh there's some more questions but uh i will i will add uh i will address them later on and let's just see what other chart okay we'll undo the step on this chart and i'm gonna move this chart down when you are moving charts one shortcut that i find working very well is holding down the alt key again the key is alt so if you hold down the alt key while you move the chart so hold down alt key and then you use your mouse to move it'll automatically snap to the grid you can see that it doesn't really want to be anywhere else it wants to be adjusted to a cell boundary this is a great way to ensure that your design is properly aligned and everything looks consistent so not just moving even when you are resizing if you hold down the alt key it will automatically snap to the cell grids like that but because i work with charts quite often especially when making dashboards and reports i also use the the format options quite a bit and there is a whole bunch of alignment tools that i use to work on these so i'll show talk about them briefly now let's just first make this chart a little pretty so i'm just gonna select the series i want to open the format options the shortcut for that is control one this is a really easy to remember shortcut because you're pressing ctrl and one number one key and it opens the format for whatever you're doing so here we are formatting the chart so it opens the format chart but if i select the cell and press ctrl 1 it opens the format cell option so whatever formatting you're doing whatever object you have it will open the format for them so it's a universal shortcut so we'll select this control one i'll just to set the gap width to 25 percent um we'll take this guy move it here make it bold and these things just just the color i don't really like these kind of rotated things but for now we'll just live with that and this is just to show you something else altogether so i'm going to fill up some background color as well so we made one chart the way we want and now let's just select this much and insert another chart so we got the second chart again using that alt key i can kind of size these things up everything is super zoomed out this is simply because my screen is really magnified so that you can all read it properly but normally i don't work at 190 zoom so we got the other chart now i realize i want this to look exactly like that but for that data do i need to repeat all the steps well you don't have to you can use the format painter but this doesn't really work for the chart so what you want to do is select the first chart ctrl c copy it select the second chart and then press alt e s okay let's try this with the keyboard here alt e and then let go of e key and then press s okay this opens up this hidden dialog box called paste special this is only for charts okay and from here what we want is we just want to paste the formatting of chart 1 to chart 2. it will copy the formatting funnily didn't copy the plot area background so i'm just gonna set that to no fill and then now both of these charts look the same except for different data a very very powerful feature that uh that works beautifully when you when you are setting up things like this thanks to mr n ball for for a super chat as well and lori says my quad is loaded with little macros to lift make life my life easier that's an awesome way to use squat think and act wisely says love your work brother thanks uh thank you for that so that's uh that is that william also sends a super sticker as well thanks thanks william and then barani says oh my god you saved me hours of work with paste formatting you're welcome thank you so much enjoy your day that's the whole point right you want to be productive so i use this trick especially the chat formatting one quite so much uh when i do my work but the real thing real trick is what should you want to get the first chart correct right you don't want to do this step like 16 times even though it saves you time so get it right and then place it in the output worksheet make sure that it looks good and everything and then just boom do it for all the sheets and all the charts and it gets done this is also the reason why i mentioned batching early on you don't want to be bogged down by these kind of my new details when you're designing them you just design them make sure that they show the right data and everything works and then when you're formatting you format them as in a batch so that you you just use your mouse you don't even have to touch your keyboard half the time when you're formatting sharian asks what version of excel are you using i'm using xl365 here but most of what i've shown and pretty much all the shortcuts they should work in excel any any version pretty much even the format painter with charts it it kind of should work from all the way back in 2003 i guess so yeah okay i think i okay i'll show you one more shortcut and then we'll jump back into the presentation because we are nearly at an hour and 25 so that's pretty good so when i have multiple charts i'm just gonna copy these for a few more times right when i have multiple charts sometimes i may i may want to select everything and work and you can hold down control or shift and select but it's kind of annoying if only i can kind of select like this that will be easy because then i can just drag but unfortunately when you select like this you're selecting the cells so here is the trick you want to go to the find and select on your home ribbon and there is a select objects option this will change your mouse pointer to a cursor like the way it is in powerpoint and other tools and now you can just click and drag to multi-select so because i use this so much i actually put it on my quick access toolbar here you can see that it is already there i just select that and then i immediately change my pointer to selection mode so i can select all of them i can go to for example um shape format and make sure they all have the same height and width so for example i say they all need to be two inches tall and two inches wide so that'll just immediately make them and then i'll just use these two alt and move them and instantly i'll get proper alignment and everything in in just a few clicks so the the tool that you want is find and select in the home ribbon and then select objects this is such a time saving one especially if you make chart so you can right click and immediately add it to the add to quick access toolbar option so that it is there the other trick is you can also use the selection pane this will kind of list all the objects on your worksheet so all the charts and images and shapes everything will be listed here and from here i can kind of click and select any one object i can immediately turn off the visibility of that so it's gone from the screen it is still there it's just not shown on the screen for working with some additional alignment or things like that so divya says i am having excel 2007 so is it advisable to upgrade to 365 um i'm not a sales person for microsoft so but i find that 2007 is really old like it's older than my kids now so maybe you want to upgrade i don't really know which way you want to go but if you are upgrading today i would say just go with excel 365 because xl365 is like a treadmill version it is always updating so you don't have to pay for upgrades you just pay subscription and and anytime microsoft has a new version you'll automatically get it so yeah why not um it probably gives you more features especially if you do a lot of work in excel and you make your living out of that okay so that's the shortcuts number two i kind of made like this notepad run sheet to make sure that i'm not really missing out on anything i did miss some of the things um so for example this is one productivity trick that i'm using quite often if you go to open or you might start seeing my personal files now but i use the pinned files option so i can take any one of these files and i can pin it and it'll always be there irrespective of how old or how often i use it and the other thing that i use is because i provide a lot of files for sample downloads for my audience i want all of them to have same consistent formatting so i got my file template here called file download when i click that i'll get this nice little template with my logo title and uh and and one tab so no three tabs and and the grid lines are turned off so i can just set up the file without wasting the time so these are some of the things that work for me you probably don't need it and if you don't share files or you don't have to follow consistent formatting all right so that's uh a little bit about the shortcuts i'll go back to presentation now and then let's just talk a little bit about the tools that you need to be familiar with if you want to be more productive in excel tools as in like specific concepts and ideas i'm not going to cover those concepts i'm just going to mention them and leave the pointers or the direction in which you need to go if you want to learn them and then you can go and learn that so that's that let me just see if there is any questions or any interesting messages there william asks is there an easy easy way to convert range table to range uh you can convert this if you go here select the table go to table design there is a button called convert to range that pretty much does what you're asking for converts to range so it'll turn this back it will adjust all your currently linked formulas in in the table formulas as well um let's just try this with a sample somewhere here this will just delete that and ctrl t and then we'll put this formula as amount by boxes so this is my new table if i convert this to range it gets converted you can see that these formulas are now referring to individual cells not really sure why you want to do that tables are more awesome than ranges but maybe you need to this is hilarious philip says wait you mentioned batching and you have twins for instance ah if only producing twins was so easy but i guess it's kind of like maybe yeah you never know what in a way i find it like it works with kids as well so sure why the heck not in fact not many people may be following me from that long but if you have been following me from quite some times when i got my twins they were born on september 24th of 2009 25th of september this is the article that i wrote twins clones and duplicates six section shortcuts for an excited dad this post really talks about how to make copies essentially a twin in excel so feel free to read that but i digress we'll go back to the presentation here quickly let's quickly talk about some of the tools and concepts that you need to be familiar with when you when you want to use excel faster there's a whole bunch of features in excel so if you are learning excel and trying to think that okay today i'm going to learn home ribbon tomorrow i'm going to learn insert ribbon that's not how you want to do it you really want to do it based on your job and your functionality so you want to think about the set of activities that you need to do and how what tools will help you so i find that the common spectrum is this that is as a data analyst or data analysis professional you want to work with data make some calculations and produce some output so again works with our batching idea so we'll see what is good for data when it comes to data analysis data preparation data related stuff these are the three features that you want to capitalize on learn about power query it is an amazing time saving feature once you know how to use power query you are really skyrockets because you will be able to connect to multiple data sets that are in different places mash them together get what you want and automatically refresh them when there is new data i'm not going to talk about power query in this video like explain how it is done but i got a detailed one and a half hour video on power query it's not a live stream it is actual planned video that i recorded and put on my channel and and it goes into full depth on power query there is a good article on my website as well so feel free to check it out uh i'll leave a link to the video in the description later on and obviously tables we kind of drilled that down quite a bit today and use the data model capabilities i have not shown data model but what data model does is it lets you take data that is in different tables and mentally combine them in the excel point when it is producing pivot tables that is way better than you writing any formulas to link up tables so use use these concepts and when it comes to calculations try to use pivot tables more than formulas again um the advice changes depending on what you are doing but if you are doing data analysis then you want to be as lazy as possible but still get the answer so for me pivot tables are that solution wherein i don't have to write complex array formulas to get the answer i can use pivot table slice or use pivot tables because they are more easy to learn and they let you achieve more if you are writing formulas try to use stable formulas over range formulas because they are also easy to write and within pivot tables enhance them with measures so that you can go and make more powerful calculations we did cover the data model and measures in in the live stream two months ago on the power pivot so feel free to watch that video later on if you are in the mood for more dad jokes in excel learning and the last one is visualizations when it comes to visualizations again the key idea is if you want to be productive you want to minimize the amount of work you do how do we minimize that if we can do it in simple ways like if i can make a simple chart it only takes less time for me to make and it will take less time for my audience to read and everybody is happy so the advice that i have is focus on simple things like use sparklines easy charts where possible use a table instead of a chart a table means like a tabular style of representing data because they are easy to read and easy to present easy to format use conditional formatting to bring in insight and stick to easy chart so that's why i put the second item in the four fifth item as well same bar and line charts don't go with complex charts that require like hours of formatting and fine tuning unless you know that is your line of work and most importantly focus on the story what is it that you want to communicate and this kind of goes back to our concept of audience first and mocking up so if i put my audience at the first then i know what they want i can then present that story and whatever that story is that is the visual will be aligned to that so that's the whole messaging on the outputs and visuals and there's more finer techniques but if your job is to make simple charts then you can get it done faster and then as a data analyst your job becomes how do i convince my audience that they are the good ones you don't have to convince this is simply because when i see a simple chart as an audience i can read it i get it immediately if i'm showing a very complex chart everybody's time is wasted so they are also not being productive in making the decision so that's the important bit there and tom says visuals pivot with slicers yes obviously i i didn't mention that but you want to also make it more playable and personable that that is where the slicers and having a little bit of interactivity comes in because then you are not assuming everything your audience will ask questions your visuals will give the output you don't have to make 20 charts you make one chart one slicer they talk to it they get the answers i think there's some joke going on i'm trying to figure out what is going on there but i probably won't get the context uh that's a personal question daksha asks where did you move to where you are did you always enjoy manipulating data and stuff like this uh i assume you're asking more like when did you move into where i am in my profession if so this is i didn't really start out as a data person i started out as a software developer but after i finished my mba i started working as a business analyst and one thing led to another and i was just using excel and powerpoint all the time and i figured oh i enjoy this i might as well start talking about it and when i started talking people were enjoying it so i just continued doing that and yeah that's pretty much it but if you're talking more like when did i move to new zealand it happened five years ago um simon says did you show alt f11 as a quick way to produce charts i didn't this is um i i mean this is a shortcut that many people use and they tell me all the time that alt f11 is the shortcut for making charts uh it's just so happens that i don't really uh kind of make the default chat all the time so for me um i i i try to just go and click on insert chart and i make my decisions there and i try to do it from there on but that's a good shortcut so if you are learning shortcuts feel free to remember that and use it if you program a macro to do something common like number formatting currency without decimals you cannot undo is there a way to keep previous actions undoable without macro macro undo block unfortunately no the what you could probably do is this gets a bit too technical so i don't want to kind of [Music] but let's just say this so what you can do is you obviously store these kind of macros in a personal macro workbook and the personal macro workbook also has a personal macro spreadsheets right so you could set up an area in your personal macro spreadsheet where cell a1 will contain the previous format so for example you could have like set format restore format as two macros when you click on set format it will do your currency formatting but before it does that it copies the format string of the active cell or selection to the a1 of my personal macro file so it takes back into personal microfile and stores the string there and when you restore it simply restore so you could kind of fake the undo action like that but uh i'm not really sure that my sounds like too much hassle for me like it's just a couple of clicks right that if i have to undo i can always undo by restoring the format i guess tom says if you build a dashboard with interactivity can you provide a file that is locked so the end user cannot modify the file this is the holy grail of excel where people want to lock and protect excel so that others cannot get into it but still use it unfortunately excel is not built like a vault it is built like a sandbox where people are welcome they can come in they can play so while you can try a lot of different protection things in excel not everything is is foolproof and many times it's very easy for your audience to just google uh un unlock excel without password and then just get some website where they upload the file and it unlocks or whatever so it's it's never really that simple if you really want that kind of thing where your audience should interact they can enjoy the things but they should not be able to get back to the data then power bi is the way to go just set it up on power bi and use power bi to share the insights obviously you want to do something like this because you're worried about the security and privacy of the data then power bi should be your first point of contact mohit says what online certification courses would you suggest for someone starting data analytics um i'm not a i mean again this kind of goes a little controversial and but i i i don't think the certifications are at the level where the real-world data analytics at least the ones that i have found so far they seem to be more focused on the theoretical aspect and screen aspect rather than the actual real life situations because it's very hard to judge how well you can analyze the data um in a universal manner like there's no one way of analyzing data there's multiple ways right so what you may want to focus is get a course that is covering a good length of let's just say excel how to use excel for data analysis again um i don't mean this to this live stream to be like a plug to some of the courses that i do but if you want you can consider the excel school program which is essentially that course where i built this course to teach people how to use excel to do data analysis and dashboard reporting so check it out it's it is certified but it's not like international certification or anything i give the certificate and you can always tell people you did the course from me and people have done this course like thousands of people have finished this course online so far and many people tell me it worked wonders for them so feel free to join that but you don't have to pay and join anything you can learn from youtube you can get a book you can read and you can enjoy and learn all right sherry has a question i have a fix it complex template spreadsheet where a user broke it 11 times already i finally locked it down and after testing my all my complex macros it still properly worked yeah this is what i mentioned like when you when you are trying to build something protected uh you will have to build it for the lowest common denominator like somebody who can be uh really determined to break it and then see if that that won't break so that's the audience that you're building for and that can be tricky but you could get lucky like sherry here and philip says power bi play date is excellent uh this that's another course that i run uh for power bs excel school is for excel power bi played it is for power bi just for data analysis and reporting so feel free to check out some of these courses and and consider enrolling in them um all right that that brings me to the last joke i promise that i have no more jokes but i have a good joke on computer mouse so i thought i'd share that well it doesn't click with everyone so that's it i think that's the end of the presentation um i'll stick around now for some more time just answering questions so feel free to post your questions in the comments window um if you enjoyed the session let me know how it went and uh um and yeah or just say hello whatever you want you can do i'll be here for some more time i'm also going to share some general news now um i hope all of you had a lovely july i thought this is what i should have started my stream with but we are now a little late into that so i might just say that um i enjoyed i had a very good july and i hope all of you had a a lovely month as well um and this is really good for me to wrap up the month on the live stream july is when usually kids have their school term break in new zealand so the classes have they have classes for 10 weeks and then two weeks break so we have usually one break in the april may time march time and then another one in july so this month the kids were home for two weeks and we all had fun like i played fortnite with my kids [Music] this computer that i'm using here it's it's built for video production so obviously it is also good for gaming so i i like to play fortnite once in a while and my kids play downstairs so they we all team up and enjoy that and it was really fun uh and finally you know they're back to school and uh and uh yeah i'm i'm back to full-time work and i got an exciting month of august planned uh wherein obviously we'll do a live stream at the end of the month but i might actually do a surprise live stream halfway through august this is just to celebrate hundred thousand subscribers milestone that we crossed a while ago um in obviously as you can guess youtube sends the play button so i thought we could do like a unboxing as well as a personal q a or or a q a session so i'll post more details in the community page in and feel free to join me it won't be at this time though it will probably be a few hours later in the day simply because uh i'm too lazy and i couldn't really get up very early twice in a month once a month is my limit so but yeah i'll try to make it a friendly time for some if not all of our audience [Music] so that dr ahmad says if you could share your presentation with us um i don't think i can share my slides uh it's not because there's some proprietary confidential information or anything or you'll steal my dad jokes it's simply because uh it's there's really nothing in the presentation right it's just a bunch of words and there is nothing of value here that that you won't be able to get by either watching the youtube video again or using referring to the files that i have provided i will of course save this file not that anybody will find it useful and put a link to this file as well it's the blank data practice file but updated after the stream so feel free to refer to that if you want um but yeah [Music] nirmal says lots of love chandu thank you nirmal uh love you as well and someone boy says you have a full night unfortunately not i do look a little bit sleepy though isn't it as if i'm not having a good night's sleep this is simply because i um i i go to gym on monday wednesday and friday so friday is my workout day and today is saturday in new zealand so um yesterday i had a little bit of heavy workout and then um i i i had a longer walk in the evening as well so i was really too tired and and i didn't get my enough eight nine hours of sleep so that's why i look a little bit groggy but no i slept i slept for five hours asks how to connect google sheets to excel which resides in different domains i am not an expert or even a a regular user of google sheets so i cannot tell you for the specifics but i do know this is possible this is because google sheets also has an api and maybe some other way to make the data available online so if you could make it available you could then use excel's power query data from web option to connect to that api fetch the google spreadsheet data into excel and use it divya says god bless thank you so much you're always awesome thank you so much divya you are also awesome not just you all of you who tuned in and took time to learn and be productive in your line of work so that is really an awesome thing to do and and thank you so much for being there tony marie says thanks for everything chandu have a wonderful weekend you to have a wonderful weekend tony uh and i'm glad everything went well and uh you all enjoyed it says thanks a lot your work is really helpful i have replicated wendy's project dashboard uh it works like a magic well uh me and wendy thanks you thank you this is really wendy it's just i changed the head and used it so yeah thank you keisha mutyas how can i add insert multiple colors in a single cell um i'm not really sure why you would want such so many colors in a cell but if you do want you can put up to two colors or a range of colors it gets a bit tricky i'll show you so we go to select the cell control one to format go to fill and you can select the fill effect option and from here you can select the gradient of two colors so for example i want to go from orange to blue and whatever direction i want and that'll give you that kind of a funky color in the cell um again as a as a as a rule you want to avoid anything too flashy you want to just keep it simple because this requires 16 clicks for me whereas filling one color is one click so being productive also means being lazy right you want to just reduce the amount of work so if your audience asks can i get a rainbow color in the cell you just flatly tell them no that's not possible in excel so that says chandra just a suggestion for the channel make a day in the life video showing what you usually need to do in a day uh this is too tempting but i'll probably stick to excel in power bi videos i do share my day and my personal life as a peak in every video i i show my face i i talk yeah maybe you never know but probably not at this point thanks into this was the first time i joined one of your streams i picked up some interesting stuff says jury thanks jory i'm glad you enjoyed it and welcome to the stream neeta says it was a great session thank you so much you're welcome nita i'm glad you enjoyed it tayo says same as somebody else lunchtime well spent god bless from ontario canada thank you so much and i'm glad you enjoyed your lunch with me and that's the original comment uh okay arhan says what advice do you give to someone like me to improve my excel skills um you didn't tell me what you are but i am assuming you are somebody who who's getting started with excel um what i've what i suggest is in fact i have a video that i'm i'm gonna release in the month of august i already recorded and edited it it's just it's in the pipeline it's called how to improve your excel skills so obviously i would say watch that video but the general outline is very simple you want to pick a small enough project not something too complicated but not something too easy and then get started so jump get started do some work and then go back and learn sometimes when we are learning new things we tend to focus a lot on learning alone rather than application and implementation and memorizing and using so don't spend hours and hours on on youtube or blogs or books just reading instead take some information take three or five items and then go and practice them go back for more for example in this stream i might have shown you 30 different shortcuts there is no point trying to get them all in one go instead of the 30 you might say oh that format painter one looks good i'm gonna implement it tomorrow so that's how you want to take it start small do small things and then build your confidence and then tackle bigger problems also learn in a more structured manner again this doesn't really work well with um with youtube or other because there's so many distractions on on web when you try to go and learn so instead set time for learning and just go and learn one or two things and then go back and implement at least that's how i learned um back when i started learning obviously there is no youtube or or high-speed internet or things like that so we are forced to more primitive ways of learning which is just watch a colleague do such stuff and then go and figure out how to do it on your own so yeah the other trick that i also find is sometimes just spend time wandering right there is a famous saying like not all who wander are lost so you want to just wander like this is what i used to do when i started learning i would go to formulas and back in that day we were using excel 2003 and 7. so i would simply go here and i'll be like hmm what that formula does let me try this and i'll just put that and i'll try the formula even if i have no idea what it does and this is how i learned excel like especially some of the trickier ones like offset and indirect and and index match that's how i figured it out like play with them without trying to look help look for help or tutorials and that worked very well garo says thanks shindu following you since 2015 always wonderful thank you so much garo and i wish you many more years of learning cell styles take up a lot of space on the home tab do you use cell styles a lot not many people seem to use the built-in styles i never really use them i'm not a big fan i think microsoft wants this to be in the home tab taking a prime real estate because a big chunk of their audience are the finance people and they use cell styles for modeling and setting up things it's a good practice too but unfortunately i don't build financial models so i i don't have use for them uh yeah i wish eventually in future not probably now but in a year or two time office 365 might even have an option to turn off some of these things that that are not relevant for individual audiences work but for now we are stuck with that so marcus asks how do you maintain a pivot chart formatting when using slicers that may mean the chart contains not data depending on the selection made this is why what i normally do is i use pivot tables for uplifting and doing the work of calculation but i don't specifically use pivot charts unless i am short on time or that's the only way to go instead what i do is i take the pivot table and then i refer to the pivot table data so i'll show you here what i mean by that where is our pivot table it's here so here's my thing if i make a a pivot chart from it i'll get this right this is my pivot chart it will you can see that we have everybody in every geography but if i go to blank i can link an r so suddenly the column is too wide everything is out of out of whack but if i go back to australia so this creates a jarring effect on the eyes of your audience you want this to be consistent so what you could instead do is instead of that i then set my pivot table and then right next to it i have an area for my chart which will be person amount and this is nothing but this is very primitive but you can kind of make it even more dynamic by making sure that it goes as far down as as the data and everything so now technically this data is also linked to the slicer simply because it is referring to the pivot table so if i change this this changes as well but because this data is is a static data i can make a regular chart from it this is not a pivot chart and and we get that so when i go to blank i'll get this i'll also get this funny looking grand total one this is because our pivot table has a grand total thing so we will have to think about some of these specific things but at least it will give you a little more flexibility on working with the limitations of the pivot charts and this is how i do my dashboards that are demonstrated on my website or usually other places as well we use that so pivot does the calculation but the referred cells do the charting part simon alfonsus is you're saying about index match your website article on this was the go to for me when i was expanding my excel knowledge thanks simon that's a nice beautiful thing to say and uh yeah at least when i started learning back in 2005 6 7 the online community was still evolving i mean there's quite a few people like john peltier was having as well he had his website all this time daily dose of excel and even spreadsheet page used to be there but it was kind of tricky to access these resources and also it meant that you need to be having internet access and all of that and it's not widely available back in india at that time so um most of that would be just kind of figuring it out but later on i started my blog and obviously this learning in collaboration that came through that so muddy mario says do you have an explanation for data validation not recognize structured references for table um i believe this is no longer true especially with office 365 i think they have made it all native now we could even try this so this is my table right so we will set up a validation cell here um select a product and data data validation list and then the source is this oh not really uh what is this called what is called products products ah i guess they didn't i remember reading an email so it might be something like an upcoming feature as well uh but i think microsoft is aware of this and they're trying to address that in in in a new newer version of excel 365. for the meanwhile what you could do is you could kind of create a named name from this table column so you make a named range this is formulas defined name and just say that the name refers to product stable product column and then refer to that name here and it will all be dynamic got one urgent requirement uh working on table and don't want to auto fill my formula when working with filter can it be done uh i'm not really sure why you want to have such inconsistent things in the table by definition a table column needs to have same logic applied all the way through if you're doing something like this then either that means you don't have a table or you're using tables for the place where they're not supposed to be used you could type a formula and then turn off the behavior so for example i'll show you the formula here this is just cost per box times two and i type it it'll extend there is this little lightning bolt button there and then just say undo calculated column now you're on your own ask i'm new to excel can you show how to generate a dashboard after this uh there's a couple of videos on the channel i recommend watching them on dashboards um i'm not going to show how to make a dashboard right now if that's what you're expecting but maybe a stream down the line might cover that topic but feel free to check the channel videos for that so this is a question i think some people somebody repeated this as well excel has many rows and some are hidden i have to give a deal number in one that is open but if i'm going to give all many numbers do not come uh not even sure how to answer that probably you may want to post these kind of things in a forum because it's a very very specific question why the negative numbers are not showing in the brackets and how to correct it even after changing the format cells um that depends on whether the number is negative or maybe there is a special symbol that looks like minus but you can apply accounting format and it will show negative numbers so for example let's just put some numbers here 19 0 -19 and you can just select these you can apply formatting the formatting can be done in a few different ways you can also use the shortcut ctrl shift 4 which is nothing but control dollar and that will apply the standard two digit accounting format with negative in the bracket but if it is not applying that could mean there is something else like a conditional formatting or something else that is acting or maybe your data is not correct taiyo says i'm new follower i'm wondering if you have a playlist of videos for hr reporting metrics i said a lot of tutorial more for finance and sales people um i do have a couple of videos on that maybe more than couple you can search on my channel videos not many people know even i didn't know this particular thing until a while ago so i'm just gonna show you let's just go to youtube this will open up all my all right so here if you go to my channel you can click on that little search box and from here you can search um within my channel so you can you can search for that because they don't really fit into any specific playlist and i don't have that many hr videos i didn't really create a playlist yet but you can see and i did a live stream uh a while ago on hr this was quite a while ago not not now but you can go to videos instead of uploads such for past live streams and you can see top five x tips for hr analysts so that's one i did and uh and then there's more hr videos there i i have been working in hr analytics since i moved to new zealand in 2016 so over the last five six years i've built quite a bit of hr analytics and reporting skill set and i plan to share more of hr related stuff on the channel uh in fact every time i do a live stream i try to include a hr topic we don't get enough votes to uh make it happen because i i ran a poll asking what do you want and some people say i want this some people say i want that so accordingly i'll just pick the winning one but i might add more hr topics feel free to suggest something to me in the comments uh in and i'll try to consider that basudev asks any plans for m core training program um not specifically m but if you go to either excel school or power bi play date there is enough coverage of power query and i do show some very advanced stuff not the simple ones alone so it does cover and i feel like because m is a language that is deeply technical probably as technical as dax or sometimes even more technical it is one of those things that is very hard to explain right away i mean you will get it once you get it but it is it requires jumping through few more mental hoops um i try to shy away from writing or showing them or teaching them because i believe there is more you can do by simply just clicking and using screen features of power query but i plan to have some more advanced power query videos that show both m coding as well as um the advanced features of power query on the screen on the channel as and and in my courses i do cover quite a bit of length like developing functions etc so yeah probably that feel free to check them out and sign up if you want so thanks for sharing your knowledge with us chandu thanks and prashanth asks how to do indenting um i suppose you mean like indent the value in the cell like select that and click on these things to move them maybe if there is something else that you mean then probably you want to explain that as how to get latest data which returns multiple records in vlookup vlookup doesn't return multiple records you could use the filter function that is newly introduced check out the channel or my website for examples on that all right i think that is uh that is time we have gone past two hours and excel my dog is now making small noises from the behind she's like what the heck are you talking for all this time man let me sleep so i'll probably hang up now i hope you all enjoyed the live stream and it was fun interacting with you sharing my secrets for excel productivity and i hope all of this has been useful and helpful download the files especially download the shortcuts poster if you have not already done that it's a very little very useful file that i prepared and it not only looks great but you can also print it and it just prints into one one page use it share it and maybe give it to somebody else who needs to improve their keyboard shortcut game and that is all for now thanks again and uh i'm gonna stop the stream now and i'll see you again next month at the end of august last friday of august every month we do the streams and uh as i said during the stream we're gonna do i'm gonna do a a surprise middle of the month stream unboxing my youtube play button as well as taking some more questions so watch out for the announcement on the community page and check it out and if you are watching this video on the replay let me know in the comments how you enjoyed it as well as you know tune in for the next month's live stream as well so that's pretty much it uh ahmut says please zoom your screen when showing stuff i try to but it's very hard especially with the with the resolution and everything but yeah that's why the files are there feel free to download them and refer to them it's really tricky to do the screen zooms and everything when you are doing live stream like this so um yep that's pretty much it thank you so much and you all have a good weekend i have got an exciting week weekend planned so i'm just gonna go downstairs take a little bit of nap before everybody wakes up and then look forward to the weekend and uh uh beyond says have a good week and i'm off to saturday sports nice and fresh in waikato waikato is one of the regions of new zealand it's always good to hear from somebody living here as well that's good beyond enjoy your weekend and some people are asking will this video be available for those who miss the live stream like me love you brother thanks a bit uh it is available you can watch it again on youtube just go i think once the stream is closed it'll take like a few seconds or maybe a minute and and then youtube will put a link for replay you might already have the link in your email or or if you enjoyed the video or you could add it to your favorites or like it and then they'll show up in your like videos and you can go and see that all right on that note i'll i'll sign off now thank you so much see you all again next month enjoy your weekend bye

Channel: Chandoo

Views: 11,992

Keywords: Excel shortcuts, excel productivity tips, work with excel faster, excel tips & tricks, customise excel

Id: yvwNWChgKZk

Length: 134min 31sec (8071 seconds)

Published: Fri Jul 30 2021