Advanced Excel Road Map for Data analysis

Two things happened in my life that changed the way I look at Excel the first one happened in 2005 and the second one happened in 2008. let me tell you the first story back in 2005 I started as a summer intern with Glenmark Pharmaceuticals my job is to meet 56 different dermatologists the skin doctor and run a survey on them this was like a really exciting thing for me back then because I was traveling to all these different parts of India meeting new people staying in exotic hotels and all of that when the whole trip finished and I did all these surveys I thought my job was done but only then I realized that I had to analyze all the survey data and make a project report the deadline I didn't know anything about Excel back then and that was like a swift kick in the bum that I needed to realize how important Excel is if I want to be successful in the workplace fortunately one of my colleagues kamalakar is pretty good with Excel so he helped me with some pivot tables and formulas and I was able to prepare the project report in time and submit it so today I want to talk to you about how I would learn Excel if I just have three months time now what is it that I would focus on and how I would structure my learning process I call this as the 433 plan so essentially I would focus on four things and I would structure my learning into three different Lanes if it is a road map you could think that you know it has three different lanes and I'll try to learn everything in three months hence the name four three three so let's get into the 433 plan where I'm gonna show you what is it that I will focus on and how how I would structure my learning process when we finish I'll tell you the other story from 2008. let's go so what is this 433 plan four stands for four areas of focus these are I call them as G cap that's why I'm wearing a cap so gcap stands for get data clean it analyze it and present it so those are the four main activities that I would do as a data analyst the three lanes of the plan are technical name that is where I'm going to learn how to do the technical bits project Lane and this is where whatever technical skills I'm learning I'll try to implement them as part of a project so that it all makes sense in a real world situation and then the domain Lane this is where I would be learning specific domain related things so that I could do my job better in the last three stands for three months this is because I think if you stretch it longer than three months then you will lose focus at least for me three months seems like the ideal sweet spot I could probably set my focus for that much time and learn it oh by the way if you're thinking this is all a little too much and you won't remember any of it when the video finishes I've got a PDF guide that kind of explains all of this with the clear links and pointers on what to do at each point in time then this link for that is in the video description below do check that out and download the PDF so that you could use it as a handy companion guide for this video month one in month one I will focus on the important bits of my gcap that is how to get the data and how to clean it because there is a popular saying in the data analysis world it's called garpe gym garbage out so if you put garbage data into your analysis all the outputs all the analysis that you do would be useless so this is why it is really important to get good quality data make sure that it is clean before we can analyze it so in month one my focus would be we are dividing this into basically five weeks week zero is where I will focus on learning a little bit about Excel like where everything is how to use the Excel UI itself how to access things on the ribbon how to load up files how to understand how to read a spreadsheet how to save it and all of that most of us would be familiar with this stuff but still it is helpful to watch one or two tutorials or maybe read an article or two about Excel so that you're not really confused when you're using some of the advanced settings in weeks one two three and four essentially there is no like you know you do this every week you try to focus on that activity for the entire month that's how I try to focus so in this time I want to learn how to get the data into Excel so this means I'm looking at how to use import from text files and CSV file options or how to use power query how to set up a connection to a database or a web page or an XML file and bring that into Excel for analysis once the data is in Excel then I will focus on how to clean it up how to make sure that it is in the suitable format for analyzing the data so this is where I would learn about how to use ranges and references in Excel how to use tabular formatting in Excel and then towards the end of that month I will also try to learn a little bit about doing some basic analysis of the data that is you know is everything there are there any duplicate values in the data are there any missing values blanks and all sorts of stuff so just understand the data explain the data to somebody like you know if I if I got a bunch of survey results from these dermatologists and I just want to know how many of them have answered yes for a specific question how to get that answer quickly how to do that in Excel remember the roadmap has three lanes so this is my technical side of things likewise I would also try to do a simple project this month so that I am implementing these skills in a real world situation for example back when I was learning I was kind of stretching this three-month plan into a full three-year plan really because that's how much time it took for me to understand and use Excel better but if I'm doing it today what I would do is I'll do a project where I'll try to take all my credit card statements for the last one or two years and consolidate them into one Excel file so either using automation techniques like power query or manual approaches or some other ways but get all of this data into one big table in Excel so that maybe I could do some further analysis of it in the follow-up months another project that you could also also try and this is something that I did when I was learning Excel is I am a big fan of movies especially I used to have this goal that I should watch all the top 500 movies in the IMDb movie database I would download the listing of the movies or top 500 or 250 movies and then I'll build a checklist so that I edit every time I saw a movie we would check that off in fact me and my roommate back then govardhan we we had this thing going on so we would kind of look up for these movies bring them up and watch them in the weekends and then when I go back I'll say oh I watched this movie I'm gonna check that so maybe he could do something like that you know if you're a fan of movies or sports or cooking or anything else for that matter photography whatever is that you enjoy as a passion in your life maybe you can add a little bit of structure around it through a spreadsheet so that you can track things and that will also give you a chance to build something where you're passionate about so you are not really feeling like this is what you're enjoying this as a hobby and in month two now that we kind of got the data we know how to do some basic cleanup activities on the data I'll focus on how to analyze the data and this is the month where we are also gonna bring in that domain Lane into the plan because so far we are just learning the technical bits and maybe doing a project but uh when it comes to doing analysis and presenting a lot depends on what is the domain or the functional area of business you're in because based on that your Excel analysis or any other analysis really changes so the focus in this month would be analysis in fact on two and three are kind of like overlapping because analyzing and presenting are not separate activities there is a lot of overlap whatever you analyze you present and based on the feedback you may have to go back and do more analysis and more presentation so things kind of flow between these two things but essentially in this month I'm going to focus on again four activities or five activities how to use various formulas in Excel that are relevant for your line of work if you are not sure what what your line of work is going to be let's just say you're still in college that that was the case for me when I kind of started learning Excel I was finished by summer internship then I went back for one more year of studies before I started working so I had time to kind of build up the skills so I would focus on important functions like sums counts conditional sums and counts writing if then conditions lookups and you know those kind of things and how to work with the references because Excel has various styles of references you have relative absolute and then tabular references and all of that so using those references and newer features like spill ranges Etc and then uh also not using functions so we learned how to use functions but you want to learn how to not use functions and still get the answers so this is where maybe things like pivot tables Flash Fill power pivot all of those things come into picture you don't want to overdo this because some of these are deeply Technical and unless you apply them in a real world situation you won't remember or appreciate what they are for very much so take it lightly just get familiarity understand where everything is and then based on the work that you are doing you can actually pick and choose what what aspects to learn so pivot tables Flash Fill power pivot are some of the things and then expand all the same things again how to do some simple statistical analysis not complicated stuff really simple stuff like calculating averages medians distributions frequencies those kind of stuff and then maybe uh some of the domain things so for example going back to my survey example from 2005 the thing that really bothered me was how do I present this survey data because it's all over the place how do I present it so there is some standard analysis that we use when you have survey data you know you have got likert analysis uh those kind of things so if you are familiar with those terms then only you can do the analysis but this is where sometimes you have to get out of excel out of python or out of power bi whatever is the tool and then learn about the domain so maybe talk to a colleague or a friend who is already working in the industry talk to your superiors all of that understand what is happening I particularly remember doing this a lot during my business analyst time because when I started I had no idea what are the analysis tasks that I would be doing I know kind of the other side of things but not the Excel side of things so I spent a lot of time watching some of my colleagues and seniors build up all these project plans estimation models and all of that and that kind of gave me a sense of okay these are the things that I need to learn and I was able to focus more so you know getting that like a sponge suck up what are the domain skills that are needed to be successful and then translate those domain ideas into what would be the relevant Excel bits that I need to learn to do that in the same month towards the end I would also start thinking about whatever I'm analyzing how do I present it so how do I take this and then make it presentable how does it how do I make it look good how do I apply formats how do I take out unnecessary bits so that it looks clean and nice when somebody else sees it so for this month the projects kind of could be we expanding the credit card thing that you are building or maybe doing something else altogether like you know build a retirement planner or a an expense tracker or you know a travel itinerary planner anything like that where there is a little bit of data but you also want to analyze one fun project and this is something that not everybody can do but if you are in that life situation where it applies to you I highly recommend doing these build a wedding planner especially if you have got a wedding coming up in your own life or someone else in your life try to build a wedding planner in Excel you know weddings are where there is a lot going on you have a guest list you have a vendor list you have this that so much things happening so building that could be a little bit fun and interesting and you know it kind of adds the whole whole another Dynamic to the to the things so in month three we're gonna break this again into four four weeks uh week one would be thinking about what charts to make for each situation because Excel has about 25 plus different types of charts and you want to understand when and how to use each of these charts in week two I would focus on how to create them and how to customize them and then the third week the focus would be more on how do I make whatever I'm creating Dynamic interactive and repeatable because in many business situations whatever you are creating it's not one and done you may have to keep on making them and updating them as your data changes as your demands change and all of that so you don't want to go back and every month to start building the same charts again and again it gets very boring and you know it's not inspiring at all so one thing that I would focus on learning is how to make stuff Dynamic so if things change I don't want to change anything Excel should be able to produce stuff for me how do I make them interactive so that uh whatever graph I make when I show it to my client or my manager they can actually change it to what they want by clicking a button or doing something and then finally towards the end of the roadmap I'll also start thinking about now that I know all of these things how can I be faster how can I get in and out how can I produce the results without taking too much time so start thinking about productivity and uh and improving your performance so that is the month 3's technical focus in terms of the domain Focus I would spend more time thinking about what are some of the standard reports and graphs that are produced in my line of work how can I make them how can I make them better how can I automate them all of those things are what I would start thinking about how can I calculate these and another fun challenge that I would encourage you to think about is let's say you are working in customer service thing and you need to calculate turnaround time so I would start thinking about what would be the formula for doing that and what can be the other ways to calculate it if not this formula what other formulas can I use or if not this function maybe can I use a pivot table to do it can I use some other thing to do it all of that so this will kind of give you more than one way to solve a problem which is a really helpful skill to have because when you have more time you would build something that is robust but if your boss says I need this yesterday then you can always come to give give them the results in a quick and dirty manner so learning different routes to the problem kind of makes it better and also you remember these things better if you know more than one way to get somewhere what about a project for this month at this point our try to really engage in a workplace project rather than doing my own personal projects because that will be better suited now that you are familiar with Excel but if you are still in college like I was at some point then I would start thinking about maybe building a small business dashboard or building a business plan using Excel or a financial model using Excel or one of those kind of things where you're combining all the little Concepts that we have learned in all these months into one thing okay so now let's talk about the 2008 story in 2008 I was working as a business analyst with Tata consultancy services our client Nationwide Insurance in Columbus Ohio I was thrusted into a project all of a sudden and I didn't really know anything like you know one day my manager called up and said you're going to be part of this project from tomorrow so I had this client meeting the next day and I was quite nervous like you know how am I gonna go and impress them so I talked to some of my colleagues and I understood what what is going to happen and what the big pain points were so all I did for my first big meeting with the client is I made a spreadsheet where I outlined all the important activities that we're gonna do what the deliverables are and what the timeline looks like it's kind of like a simplified project plaque but I just made it in Excel but I made sure that it looks nice and good and it can miss the points like you know this is what the project is all about this is what we are going to do this is how things are going to happen and I showed it to our client and he was super impressed mind you I didn't know much about the technology that we are going to work on or what the overall project or anything is but I was able to prepare a simple document to outline what the things that we are going to do he is really happy that he immediately said yes we are doing it and he gave me a really big compliment on the day itself so that's when I realized that the importance of not just having the skills but you know showcasing them making sure that people understand that so that is why I you know I wanted to share both of those stories to you from 2005 to 2008 it took me almost three years to become very proficient in Excel but hopefully with this roadmap you would be pretty good in three months or six months however long it takes for you so I wish you all the best in your journey and if you need a guide or a someone to hand hold you and make sure that you are process proceeding through this process then I suggest looking at my Excel school program it is an online class where I teach all the things that I covered in the roadmap in a greater detail and it is a step-by-step program so you don't have to go look in different sources and learn pieces from here and there and try to stitch together you can just focus on learning building your knowledge complete the projects and be good in Excel for data analysis work so if you're interested in that do check the video description for a link of the Excel school program and sign up for that course whichever way you learn I wish you all the very best and more power to you bye.