Basics of Data Analysis

all right so I welcome you all to today's uh training uh we are still here thank God so far so good we've uh achieved we have gone 50 percent so we are starting now with the other 50 percent aspect of our training the 50 means that we have covered three weeks already remaining uh another three weeks and today another three week starts and I'm very happy that we are starting another three weeks with a a very wonderful aspect of uh of what level markets of today requires so and as a matter of fact data analysis is one particular area that is very important now and you as an applicant whether you are an applicant for a job or you're an applicant for admission whether graduate program or even undergraduate you need to know some basics of data because there's nothing you can do without data even if you are going to study um you can't do without data I am doing management but I didn't last semester I I was faced with a lot of data analysis crosses so that's why we added this is also a part of what you will need and today's training is not just for applicants for graduate admissions but also applicants for jobs and also even those who are already never markets or even those who have graduated and you are looking for a job you need and even those of us who are already in our graduate programs we need this particular aspect of data I mean a lot of training so um I thank God and then we are here today for this program we were able to get a very uh talented young man I have had a personal contact with him and I have seen the stuff instead of and like I always told you guys is I can't possibly bring someone who I am not so sure of so I am sure of this young man he's very uh proficient in data analytics so he's not a person that Eric he's a young turgolist and is an experienced professional with an SQL certification and a strong background in data analysis analytics problem solving data management and data modeling he possesses a solid foundation in operations management and information technology and a minor in business that in business and data analytics from our own our our indefatigable University Northern Illinois University United States one of the best universities in the United States of America so he specializes in translating complex data and sets into actionable insights for informed business decision this young man is also a musician and he sings afrobeat songs for those of you who love our travel bits you can always link up with him I have put up his social media platforms there so and then he's a lover of football and then he also likes playing music and then also attending party so it's not just about data data he is also a very Crossing so you can you can link up with him any of his social media platform his Facebook is ericus and then we know him as anything on his social media platforms so I that was not for any time I want to welcome Eric into this program today maybe you have the floor thank you and everybody hear me yeah I think I can hear you from here but if you can make it a little bit louder all right guys it's better now okay yeah first I want you to say good morning everybody uh like Stanley mentioned earlier my name is Eric at DJ I am also a student at NIU studying operations management information technology and my area of focus is data analysis um if if if people that are not in a noisy place I want I want everybody to just say their first name and just say your background what your area of study is which will make it a little bit easier we all get to know each other a little bit before we dive into what we have prepared for the day uh Stanley I don't know if you have like the names in order you can just mention the names and everybody um okay you know in that case let me mention them according to all right um everything on commute yourself yeah yeah that's your name and your background I'm live yes your name and your background are you there okay um next person he cannot hurry he cannot hurry can you just don't see your name in your background you want to know the um background of our audience you know I told you guys that today's program is will be more of a practical and it's not like the other ones we've been having the person will be doing a PowerPoint presentation so it's more of a practical training that we can even allow you you if you want to share your screen uh if there is any issue you have you can possibly share your screen and then the instructor will be able to tell you what to do so that's why we are having this session like this so it's gonna be a kind of a session it's not it's not going to be a instructor participants 10 but participants through in and instructable having a kind of um communication so um so just meet your mute yourself say your background your next person but can you call the next person okay just you say just use it anybody anybody can do that anybody can do that if you're just if you're not a neighbor in a noisy background okay my name is Marcel um I hold a bachelor degrees in electrical electronics engineering and a master's degree in Federal Communication engineering I don't know all right next welcome if you're not in a noisy background hello yeah yes my name is I I have a BBA degree in public administration and I'm a mass stock internally African universities thank you welcome Paul yeah next okay my name is okay say yeah continue every text or whatever my name is MSC students yes welcome next all right my name my name is education accounting but I pivoted into Tech as a career after school and then I'm trying to also considers career paths I think that's my that's my background okay okay iPhone you're saying something iPhone yes my name is and I'm joining from Ghana yes and a potential application okay welcome thank you yeah okay everyone yeah my name is and I'm currently in the banking and finance industry I'm looking forward to a master's degree in Business Analytics uh wonderful yeah um Daniel those all right my name is Daniel and thanks for this opportunity my background is a educational psychology I'm currently pursuing a masters in counseling psychology thank you welcome all right economy I look forward to studying in Canada or us in political Community thank you welcome um okay yeah go ahead good evening everyone my name is from Nigeria I wrote the bachelor's degree in Biochemistry and I'm looking forward to posting a postgraduate degree Master's in environmental psychology welcome yeah next uh hello good evening everybody yeah um from Nigeria BSC holder microbiology currently pursuing good microbiologists you're welcome thank you hello everyone yeah hello yeah wait let me know continuous continue okay uh PSC electrical electronics engineering and Michael University of Agriculture currently one of my Master's Degree also in Atletico Electronics engineering yeah thanks everyone yeah my name is Lydia moredi I'm from Kenya I'm I've already done my masters and I'm looking for a PhD in agriculture economics maybe in Canada USA or Netherlands thank you for this opportunity I learned to people from LinkedIn and I've really learned a lot okay you're welcome so I think okay is there another person remaining hello oh my name is hello okay hello chemistry we are welcome okay Pascal hello mute yourself and speak okay if you can't commit yourself okay somewhere somewhere Isaiah my good everyone my name is coming over to NIU by August to pursue my masters in school science with specialization comparative qualities you're welcome yeah one of my NIU people coming soon oh okay next person hi everyone [Music] yeah okay good good evening from Nigeria I'm Pascal my background is in public administration and also a prospective graduate student I'm looking forward to post you in a master's degree in possibly Social Work non-profit or or a public administration too glad to be here thank you yeah and good evening everyone from Nigeria my name is yeah I know America very well she's she's my she's my colleague at michaelaba University I think our network was disturbing so yeah he's my colleague you're welcome yeah okay hi everyone scientists so I'm rounding off in my MSC program here in Nigeria Michael my University of Agriculture I am looking forward to pursuing a PhD program abroad thank you hello [Music] hello yeah go ahead gentle music okay my name is Victor from Nigeria I have a DLC and MSC in finance thank you very much welcome yeah you're welcome okay so I think we've gone around um olabisi said something okay so I think we've gone around so yeah the introduction I think everybody has a has been able to if you would have been able to introduce themselves yeah accept accept me ah oh Dr Johnson yeah so my my name is Johnson and uh I'm a PhD student at Large yeah I'm glad to be part of this session knowledge and they say too much of fish can but only enrich a soup so I'm here to tap into the knowledge of the resource person and I'm glad to be part of this session sorry I'm driving now but I'm with you guys yeah yeah yeah yeah yeah thank you all right so Eric you have the floor all right I'll I'll share my screen here thank you let me know if you guys can see this uh you that's it yet can you see my homepage yeah everything yeah we can see your four points all right uh uh here's here's what we're gonna go through let me just walk everybody through the agenda real quick and we'll just Dive In so this is basics of data analysis uh we all have various background everybody can hear me clearly though right yes yes I can hear you great so first we will talk about me a little bit Stanley did but I'll I'll just do a little brief overview my background what I'm going for and then we'll move on to quick anti-cleaning in Excel after that we'll go to the coding language that we use as uh data managers which is called SQL structure query language um we gonna do some data pooling and go over some basics in SQL and after that uh I will show you guys a little visualizations on quick so when it comes to visualization we use various platforms we use like we use Tableau we use power bi we even use Excel which I will show you guys you know in a second Excel is a very great tool for visualization so without wasting time we'll go right onto the link in here and see all right some people already sent me a request since yesterday um my background is in management information system so as far as uh experience go I've had the opportunity to work with various through through the school some of them are internship the first one I want to cover is this one right here I was a consultant as a business analyst for a non-profit what they do is they grow food in Chicago area and they help the poor or the the less fortunate so what we did for them is build a new business plan by using their their past data we also did a lot of public speaking because the CEO of this business here called we saw we grow came to my school and then a group of three we we presented to the CEO and our teachers and some some people that are against a lot of people and this one I was excited about Stanley know all about this yeah this one was a consultant firm no we we went there as a consultant uh it was a group of six two of them are computational degree holders we have two other accountants and one Finance so what we did here is we went there and they wanted to know us as new graduates what websites and where do we go to find jobs so we were kind of data analysts and uh in a marketing department so they want to know where they can go to get Fresh Heads fresh graduates that are competing so what we did with TLS we use power bi critical thinking of course problem solving so they wanted to know out of the U.S the central U.S where are the best school for the degree they're looking for they're actually this company here they're actually the number one company globally when it comes to supply chain they deliver uh Goods they deliver goods from anywhere from Lamborghinis Mercedes they deliver to Walmart any company you can think of if you order something on Amazon and Amazon doesn't have a plan go into your where you live they will go through them they're like a third party they'll go through them to deliver the item efficiently so we went on their site we visited and it shows all they do day to day they have very high security just like airports because people can do drugs and a bunch of things trafficking through them so they're very and they're also certified by the government and this is this next one is my part-time job that I do while I go to school uh to keep my day-to-day needs man so as of now uh what I'm doing now this summer is this internship I'll joined this company as a business intelligence intern uh there's a lot going on I've not done yet I will be done in August so I'm working as a full-time 40 hours a week and then we're doing okay for people that may not know what business intelligence does let me just do a brief overview what what we do is if any Department in the business have a question that's data related anything data that's us so we have a customer relationship management databases that we collect data's in but the CEO might have a question okay we're selling various of products which products it's the best selling in which season um that may be a big question but that question can be answered using data so now what we may do as business intelligence now is we need to know what the deadline is when we need to deliver the final report then share the work among ourselves well for this team here uh captive resource they have about 10 people and they don't treat us as interns you go there you join the team they give you your task you get it done uh we're in office three days a week and two days remote we can work from home they have a great security system um what what what is very interesting about them is you have your own paste but unless the the customer or whoever is asking the question have a deadline then they'll pull a lot of us on one tasks and knock it down so that's a brief overview about business intelligence any question in the business that can be answered using data that's what we deal with so we will clean the data most data is on our clean we'll clean it up uh build dashboard build reports that everybody does not take background will understand so that is what we get paid for as business intelligence um position holders uh captive resource there's more informations about that uh we may go in later now after the LinkedIn let's go let's go right in Excel we don't we don't need to come back here every time but after the LinkedIn we're going to clean some data here in Excel all right that was in time let's open up a file here um one thing I want to say is if you guys want to practice with data or anything we're doing today I can send you guys these files uh the video will be recorded I know Stanley will post this video later is that correct yeah yeah definitely yes this video posted um even if you can follow along as of now uh you can always go on Stanley's YouTube page you can see everything today one is filed I'll leave it by Stanley and he'll start with everybody this data actually was created by Ken Kenji here um you can go online and just pull free data sample to practice with and that's all we do um even at school and I work since I work we use very sensitive data we can share it I can't share because I'm actually building some really cool things I work now but it's with real clients real people's information their their addresses their phone number and we can't share those we sign NDA for that um that is why I can't really show that so we have a free sample data here so what we're going to do in Excel here is just think like analysts data analysts or business analysts uh those are the those titles are the same thing in a way business intelligence data analyst business analysts all those can vary um so no wasting time we're gonna go right in here everybody can see the Excel file here yeah yeah yeah all right so what we're gonna do first is clean up this data what do I mean by clean up this data if you guys pay attention here you'll see that there's no clear definition between the uh the headline the the items here and we have some empty space even if you pay attention here you'll see that these formats are not right who writes air mass like this it's it's not it's not good and also all these spaces are all over the place what this does is if you go in our software that we use to visualize or to clean to work with this data it will give you hard time and it will just waste a lot of time so what we do first is spend time with data understand what the data is before we start doing anything so after you have a good understanding of the data what you're working with then you clean it up to look neat now if you want to pull insights out of it it's really great it's really simple all right so first thing I want to do is we can highlight all this or we have a shortcut this you click this you click control shift down it highlights everything Ctrl shift right it highlights everything because the reason why we have those shortcuts and we don't highlight manually excuse me you say what I just did just continue Ctrl shift and the down arrow Ctrl shift down that's what I just did Eric you know what just be taking this most of them will be having their laptops there so yeah so if you just do just let them be following what you're doing okay so okay thank you yeah that's that's fine so what I did was instead of highlighting this whole table manually I click in this cell and I clicked Ctrl shift now and control while holding Ctrl shift down I just clicked right and then it just highlights everything else Ctrl shift down and right just highlight the whole thing so if you kill if you click Ctrl t it will ask you is this including a header if I say yes my header has I mean my table has a header and I click OK all I did was I I just turned the data into a table by clicking Ctrl T but I can click Ctrl Z and go back I just cancel what I did but those shortcuts um there's a lot of them I'm talking 500 but we we focus more on the ones that we use day to day to to make our life easy well okay even before we turn in this into a table um okay this this column here column H say price and why is price not in the dollars price has to be some type of currency we can use dollar or whatever currency we're using since well we're in the US here we're gonna use data dollar if you if you highlight the whole column you just click H and you go to um uh the Home tab here I'm on the Home tab you go to General it will ask you what do you want to transform the whole column into if I say currency if I say currency it should change it to dollars uh actually money but I don't know why it's not showing money let me click yeah I think it's showing but maybe showing so here you can change it to um the sign when you sign that you want okay if we put English I believe it's because the column is not big enough maybe others I want to start typing all these things [Music] not just yeah you guys see that this column just dated but I don't know why my dog calling me don't want to do it let me do control I think I think on that price that I didn't want to show you have to shift you have to make um that price to be at the center so it will not contain no just just go to um yeah yes no the damn one like that yes now yes that's nice let's see okay or maybe let me just move it to this side let's see okay yeah okay let me let me try something real quick we're gonna we're gonna copy this they stay here it is changing okay why is this column changing into dollars and that one is not but it's fine it's fine that's fine um it's supposed to change the dollar just like this if you click uh the dollar sign or you can change it to euros whatever you like um so that's that's one thing I think that g column you put fulfillment brackets what what is that percent so suppose somebody's talking yeah all right yeah hello hello yes boss I was saying that since you are logging in for the first time yeah yes choose setup choose setup all right okay this is fine this is fine let me drop this one real quick all right um this can be in dollar it doesn't it doesn't matter next thing we're gonna do here is we have contacts we will see here if you pay attention we have first name and last name okay what we can do is let's say we want to have have them separate have them uh first name on a different column a last name on a different column okay we can pull first and last we can separate this by using some really great functioning Excel okay one thing we can do is do Frozen we want to separate these first names and like that sorry please so I think it would be nice if we can have this data so that we can be following what you are doing and get the same result at the same time please you say you want to I'm thinking I'm thinking that yes I'm thinking that because uh as a has a very nice observation if they have this disaster as you are doing they are doing but I don't know is it possible is it possible for me to send is it possible for you to email this to me and then I'll forward it to them immediately have it in there if I can fold it probably through WhatsApp or whatever or do the chat box but if it is not possible for us to for them to have the data now since this thing is on record they can have your data later and while they watch the video they do it on their own in their own um convenience time so we have two options here oh because the options the first option is if they can have this data this data on your laptop now I mean this data if they can have it if you can share it probably with me I will share it with them currently at the because we have a WhatsApp group chat so I can share it on the WhatsApp group chat when I show you there they will all have it in their laptops and then while you are doing they are following you because this program today is for just is more of a a practical thing while you are doing their following so that at the end of the day they will get the same result with you if they have any problem while they are trying to get the same results you can ask questions probably wanted something that did not click well or something okay once I remember I remember last semester when I did this data something like this we always have our laptops in the class and we have the same data we are working on the same data yeah that's fine I I think I think who record this and then I'll make sure I say everything I'm doing on the step along the way and then I'll share the data with family and you will share on the platform later because now even if we share it now everybody will have to download it and go back and follow and we have we have a lot of things to cover this is literally yeah I think yes so the second option works so what will happen is just uh do it as you are doing it they'll be following you then later we'll I will share this data with them and they can practice it okay no problem all right uh we will continue here so what what I'm trying to do here is separate if we pay attention the first name and last name are separated by uh the under the underlying the underlying so now we want to separate this into first name and last name so if we highlight this instead of on the Home tab we'll go to data Tab and we'll click this icon right here let's say let me cancel it which say text and columns if we click next if we click next it will ask us as if you pay attention here we have the same data that we have in the table here it will ask us what is our what we want to separate what is this separate some of them some data comes and they're separated by tags some of them are separated by semicolon some of them are separated by commas and all so now I've gotten yes I've gotten something instead of okay Amanda please meet your mic yeah [Music] let me meet you from here continue okay that's fine yeah okay instead of we don't have our option here so we will go to others and just pull whatever I did a separate I don't know if you guys paying attention you'll see down below here the name just shifted the name the first name and the last name is separated by you will see the line comes so now that's just separated our first name and last name the reason why this is efficient and we don't want to write it manually is we deal with thousands of data I am talking a hundred thousand rows in Excel and you're not gonna go manually and type first name repeated last name that that will take months so when you learn the shortcuts it just makes your life way easier so now we want to separate this and we know that the first name and the last name is separated by the underscore now we come here others and as soon as you put underscore it separates them here so okay when we click next it will ask us where do we want to pull the First Column that we separated which is the first name so I can click this here and just say okay put it here that's where I have my first and if I click finish voila what just happened first name last name got separated from this instead of going one by one typing it just a quick tab in data here just separate those two for us great now what else is looking um excuse then alternatively alternatively someone can actually look at this screen it doesn't have exactly the same names and the numbers you have anybody can just look at the screen what if you have your laptops there you can look at the screen and and look at the numbers of How It Was Written here do it on your own screen like that and then while we are doing you are doing your own so you can possibly get the same result but probably different um answer I mean different names so I think the only thing you need to do just open your Excel in Beauty all these data yeah because you can just make it like four four or five uh columns four or five columns maybe from number one to five or number one to six faster than you cannot be following what we are doing but um someone said someone said if you repeat this which you did please disturbing us now somebody else I have mitted people before I don't know why they are still on meeting themselves so we ask you to please look through continue okay you guys want me to show again how how I separated this first name with the last name correct yeah all right I'll do Ctrl Z anything you do in Excel and you put Ctrl Z it goes back so now if I put Ctrl Z my first name and last name disappeared boom now we'll highlight the column that we want to separate so our boss gave us this data like this that ah this is how they collected but we need first name and last name which I created I just inserted if you want to insert a a new column you go here and you click you right click and it will ask you insert you click insert boom Another column so if I do Ctrl Z here it will just go back I'll recreate my two column name them first and last name now instead of contact we want to have first name and last name matter of fact first name and then last name good instead of contact because that's too broad it can be email it can be phone number we so we need the data clean and fine-tuned so now when you highlight this you go to data I hope everybody's following now we go to data under the data type we go to cancel it again we go to test to count so now we want the the delimited is basically what separates them what is separating our our data here in this column so when we click Nest it's I already have this in let me uncheck it it will ask you okay the the names or the words basically the words you want to separate what is between them is it a tab is it a semi-color is it a comma or is it a space so the SL know that okay I want to separate these things are in the same column into different columns oh [Music] so now what we what we do at least we don't have the underscore here that's separating them you can go to others let's say yours is separated by um end you can put it there which is the sign but my hair is separated by underscore and as soon as I put it Excel is smart enough to know that okay I want to separate these into two different columns it just separated for me let me go back if I put the underscore but okay it created it just separated into two columns right away if I click Nest now it's asking me where do I want to pull the first separated uh word which is the first name in this case okay I have a spot for it it says destination so if I click here I can this I can put the destination here so now as soon as I pull that I can go back if I click finish boom it just separated my first and last name was that was that clear enough this time yeah okay good now what else what else can we do um I'm looking at this data here if you guys pay attention to this column you'll see that well the shoes it says casual suit shoes and the shoes s is capital H is capital O it just don't look proper so what we can do is insert right click inserts you just insert another rule for us okay we can just limit the same thing products we have a formula in Excel that will clean the the format of the test for us so the formula that we're going to use is called proper if you click proper in Excel It'll ask you okay what is the test that you want to be in a proper former meaning professional form nobody goes and writes all these are out of place it's supposed to be all uh capitalize non-capitalized or only the first letters are capitalized we all know what uh professional test format look like so if you pull properly and I click this I want that to be proper let's put enter what just happened here if you pay attention it just cleaned up the former for us instead of Magista and the TA is capitalized everything is out of place you'll see that this is more this is more professional we want the data to be clean enough this is not in order who writes Japan like this is capitalized and a a n is not so that's what we're trying to fix and I'll repeat it one more time we have a formula in Excel that will fix the format for you you pull equal proper and you just click the test that you want and you click enter it cleans it up right away and you can come down here this Arrow here or okay when you come to the cell right bottom corner you double click it what happened it just cleaned everything from this cell onto this cell and now I can drop this cell if I like because this one is not professional enough to to give to your boss this one on the other hand everything is clear if you pay attention Air Max Air Max here air is kind of proper but Mass what is this so Excel cleans everything up for us Japan shirts what is this nonsense here it says shirts and RTS capitalize no so now with one formula call proper we can clean clean that up okay if we want we can come here and just delete oh no let's do Ctrl Z we can um it's because the formula is related to this so it doesn't it will delete my other one this is is G3 part of this formula no green terrain is not part of the formula G3 is the column this is G and then the column is three I didn't mean the number is three okay okay okay this is what we want to clean so after that if okay I can't even insert another one just because this one hello hello yeah just just a quick one um during the formula when you're inputting the column like G3 you can't put the whole of the columns in you have to really do for the first line and drag it rather because I was trying to put the whole of the column in leading way yeah okay if you look at what he did there he put in the first listing and now dragged it down so what whatever you do if you even in an Excel whatever you do on the First Column and drag it down it it takes care of the rest of uh your data so you can if you have your laptop there just put in there then do it the way he has done it and then just drag it down go there drag it down and it will drag it will correct every other thing on the other column yeah sure sure what was his question was um if you can just during the when you are inputting like G3 you can just put maybe G3 to um G10 straight up in the first formula for it to create that best thing for you rather than only G3 then you would have to drag this all right let's pull let's put your saying into practice real quick I can do control Z Ctrl Z okay um product is already there you're trying to delete my products I don't know why the thing is yeah here okay I can delete it just delete this let's create another column uh products now he's saying that why denied pool proper and just drag everything is that correct no yeah like G3 then um column one to ten so that you've now solved everything on your own rather than sorry column one to ten exactly yeah yeah that's good to enter it is also yeah it's also another way it is also another way but what what what am I reason for that being is like I mentioned earlier we deal with that was I I wish I could show you guys the data that I was dealing with last time Excel couldn't even think Excel was crashing why because we have 10 hundreds of thousands of data so the one I was personally dealing with it has 200k rows like these ones that we have one two until they have two hundred thousand we we can and do that it's it's a it's a it's a long process and then instead of highlighting everything that would take you forever to go all the way to the bottom because we want to clean the whole column if that is correct so if I click Ctrl Z what you are saying in fact you are correct 100 if I put proper and then instead of doing one and I want to do 10 like you mentioned this is 10 columns that's correct it will do it will clean all the 10 for me yes but we have 100K We're Not Gonna drag it until 100K row that that would be forever so what we do instead is okay what will make your life easier let's just pull proper and then we just clean one column after we clean one column instead of doing that we just double click the bottom here and it just clean everything all the way down so it comes it comes in very yes when we have thousands of rows which is what you will be dealing with real life this is an example table why I've dealt with sometimes me myself I feel out of place because okay first of all the machine can't handle it and it's just a lot of work but what we what we've done here we have cleaned the columns if you guys pay attention here I don't know if I want to call it this and let's see Ctrl shift down okay as you guys can see here we have cleaned this column you will see that this one says shoes capitalized all over the place this is clean uh what else have we done control Ctrl shift down you know let's just do these are the things that we have modified so far ultimatify it no we already done it okay yeah this this we got these from contact they're separated and we just separate these two we with a formula and this one now we just clean it with a formula also so now next thing I want to do is trim the data what do I mean by that let me pull location uh underscore clean or yeah clean okay what do I mean by Clean if you click in this columns or if you just pay attention to the comments what is going on here you will see that London UK is in the middle this one is on to the side this one is tabbed out this one is in the middle the spacing is not clean enough we need everything to be in order so now we have a formula here we can do trim we can do Trim in Excel and what just happened it removed the space okay if I double click on the bottom again what happened is every space that's all over the place is now looking great that's just called trim it trims the space around you will see that even Japan here has I don't know about four space between Tokyo and Japan here it cleans it up make it look nice now the reason I'm highlighting these is okay really after we clean it up we drop the ones that don't matter to us but I have a clean version of another data set that we'll use to to get insights from this one I just want to show the process we go through while cleaning the data while prepping forward okay the real the real thing we want to do those are a couple things we do um you might ask what is this this this is very important but I'll go into it more when we're using the coding language because customer ID what it really is it's a unique identifier yes we all know what id stands for but the reason why this comes in very handy is let's say there's a quick formula I wanted to um if you go anywhere in the US today you gonna use Excel let's say in your work day-to-day life the interview question that will come up is how well do you know I sell they will ask you do you know if you look up or do you know index match do you know pivot tables Stanley mentioned those earlier so Stanley um I'm giving my word so we will build we'll build something I know before tables I know pivot tables are crucial yeah if you don't know pivot table you don't know vlookups you don't know in this match well you might as well claim you don't know Excel you have seen the logo before but yeah I don't know what you have done with it personally but those are critical even me when I started my internship they asked me how well do you know do you know Excel and I told them well eight out of ten the guy looked at me and say okay do you know if you look up I say of course it's a pivot table I say of course and he pulls something else he said index match I'm like whoa what is index match so now great you learn every day so now what I did was I went back do research what is index match I'll show you guys vlookups I'll show you guys um index match and I'll show you guys pivot table what we use it for why is this so important all right um without wasting time let's see um let's do vlookup first what is vlookup vlookup basically means vertical lookup what does that mean we have verticals and we have horizontal horizontal is hlookup vlookup is vertical vlookup is what they use most of the time we want to find a specific item in a in a table so vlookup is basically like um a filter or a search engine yes a search engine in Excel if I put equals vlookup it comes right up okay it will ask me it will ask me down here what is the lookup value okay let me just delete everything let's say let's say let's say I want I want uh this 30 right here okay let me just pull let me just put 30. let's see if this won't work and so I put this in dollars I don't know if you guys remember it's still holding that filter let me see 30. okay we have 30 here now let's say I'm looking for this 30 here yeah but or we should do ID I think ideas more unique 30 we have a bunch of studies here Let's do let's do IDs okay let's say I'm looking for one three two two one three two two which is here one three two two is here and the reason why I'm using ID is because it's Unique and then or maybe we could have used contact too but okay let's let's let's jump in if I'm looking for that it's vertical Apple vlookup vlookup now is asking me where where what value am I looking for the lookup value so here I'm looking for this okay now if I pull comma is saying where where is the table array okay I can come here and say Contour shift down that that is that is what I'm looking for now is asking me um okay let's let's see it's saying okay let's say the index okay come here let's see one more nope okay now it's asking do you want the true value of the approximate we always choose true because that's just what we always have done let me see the table array honestly because this is not I didn't turn into a table let's see Nike bear with me here trying to see foreign let's try okay let's try let's try the the index match now and then we'll go back to the look of the vlookup I'm trying to see something here if it's me or is my Excel okay let's say we want to find this same ID but instead of look vlookup is actually outdated the reason why index match came up in the first place in this match what in this match does is okay if you put index it's asking okay what are you trying to return what value do you really want to return why using something okay let's say I want to return the price what I would do is just highlight everything from the price now Apple match purple match it will say okay what do you want to match okay uh this is covering my column here oh my God uh okay let's say I want to match hmm Nike he's gonna ask me okay where is Nike located well Nike is located on the brands after that it's gonna ask me do you want the exact do you want the exact answer hold on he's gonna ask do you want the exact answer less than or greater than or exact match okay if we play as that match that's that is zero for shortcut yes okay what just happened let me see Nike here if you look at the row let me highlight the row Nike's price is what one two two seventy nine yes and that's what we got here okay what is index match index you're telling Excel that okay what I'm actually looking for is the price but the price of what that's where match comes in I want you to match this name brand name Nike in this column and return the exact value that is literally index match so I already do it real quick you put equal okay let's just try Puma let's try Puma Puma is right here Row 18 let's see if we can return this much this amount the reason being in this match is very very crucial is you can you can go up right left Center whatever you want to go that's why we're leaving vlookups and hlookup in the past because in this match combine both of them instead of doing H lookup which is only horizontal and vlookup only vertical we do index match and you can navigate anywhere you want to go in the table and it can be millions of rows as long as you tell it okay I'm looking for Nike's price this they will pull it right out for you so we will try one more time instead of Nike okay if I put Puma here Puma that is what I'm looking for okay I'll go to the column okay I want to know what Puma 's price is okay I'll I'll come here and put equal index if I put in this it will ask me okay where is the the value that you want to return is located great it's on that price Ctrl shift down I highlight the whole thing comma instead of finishing index I will not use index alone index match is a combo Max Apple match as soon as Apple match mask match is asking me okay what value are you looking for okay I said Puma I want to find Puma it's asking me where is Puma located okay Puma is located under branch control shifts okay let me just highlight the whole thing because we have empty cells and it is not highlighting the whole thing another reason why we clean the data because you have a time of your life not having a clean beforehand okay after that uh we match that and we want the exact value from the Puma column we can close the parentheses if you like if you don't close it you click enter it will give you it will say do you want me to close it fine you close it okay where is Puma located Puma is here 18th row what just happened it returned the price wow you returned so that is that is the best skill you can learn as of today me comes as far as I'm concerned in Excel yeah you can navigate throughout the table however you like and you will find exactly the value you're looking for okay let's let's see if we want to try for instead of returning price we want to return let's say we want to return location let's try that let's use Puma for example again let's pull I'm just doing it a lot of time so you have a grip even even if you're gonna re-watch the video you understand it personally if it will index I still pull it right out okay the index what do you want from this okay I want location from this whole conversation I want I want you to show me location okay I highlight the whole location okay it says fine now let's combine it with match what do we want to match I want to match Puma the brand good now where is Puma located Uma is located on the brand good let's highlight the brands go find me puma and I want to know where Puma is located okay now I want the ex exact match let's close parenthesis let's do enter oh it says located in Paris okay let's let's let's let's fact check that is that true how true is that okay we click this voila so we use this for for quick checks uh let's say somebody sends you a data let's uh they say okay this data is clean blah blah blah this should be this okay you can check things real quick to make sure everything is on on on on point one thing also we check for before we move into visualizations or analysis we we check for duplicates I'll just rush over that a little just real quick let's say okay I don't know if these are going to bother me but let me just highlight the whole thing okay highlight the whole thing and the shortcut for that is Ctrl shift down while holding both you click right and just highlight the whole table let's say I want to turn into a table I'll do Ctrl T now where is the data located or your table I already highlighted so he shows me he showed me that now it says it's my that's my table has headers of course it has customer ID those are my headers I checked it and I said okay oh voila you turn it into a table okay now why do we turn data into a table before we do anything the reason why this is just the cleaning part after I clean everything I make it look nice I have to turn it into a table okay there's there's more ways to go about it instead of using the shortcuts let me see Ctrl Z goes back cancel everything let's say I want to turn this into a table and I don't know the shortcuts that's fine I can go to um let's see let's see data hmm I've used the shortcuts so much I forgot how to manually find table okay I think it should be under okay table right here insert a table okay oh okay you don't have to know shortcuts you can navigate Excel until you find what you're looking for which is gonna take more time I can do Ctrl z um okay if you go under insert you can insert a table instead of when you highlight the thing you could Ctrl T which will ask you the same question and you turn it into a table so those are just shortcuts we use that makes you like very easy okay why do we turn data into a table you may ask okay for example let's say we want to know who who has the who has the highest prices right okay we can improve descendants let's see what happened no that's that's uh that's the opposite way let's you can put ascending okay what happened okay it it turns it turned them into yeah yeah it turned them to you can turn them too big from Big to too small let me see you can color them let's say let's say you want to you want to know how many how many people okay or how many brands are bringing in or the price is less than one hundred thousand dollars for example okay I can come here and just uncheck everything that's not you'll see that it's reacting with my table everything that's not what I'm looking for everything under a hundred thousand that's what I'm looking for okay that is possible only because I've turned into a table okay if I do Ctrl Z I'll pull everything back okay let me just and just uh select everything back my table is back so when you turn something when you turn it into a table what it does is give you options you can do various things you can say Okay sort these names from A to Z for me oh I want to know who's which company is doing the best on the market you can find that and that's the reason why we we are the ones that answer every question that they may have in a business that have some relation to to data because if you have a question okay I want to know in U.S specifically because here we have UK we have Germany we have friends you want to find out in U.S okay which brand is doing the best okay I can go in that data filter it there's another um uh Google great tool I want to share it's called filter okay if you go to see okay all right if we come here we can go to the data and we'll see filter here what it does is it remove the filter from the table or there is very Advanced ones you can customize your own stuff but why we remove filters okay sometimes it can it can bother a lot and we we as soon as you sometimes you create a table and if it don't come with the filters automatically you may be using an older version of excel that is one two we have to one thing we have to keep in mind also Excel have various versions they're updating it every day so if you're using a very old version there's some certain things people will be doing you can't do it and you have to understand that okay there's different versions of excel but so far so now things that I'm covering everybody should be able to do it because Excel is a great tool Excel I go as far as Excel is a programming tool because Excel is scary what Excel can do it's it's very scary since we have we have a lot of things to cover my initial time I have passed a long time ago let's forget about that but I have a hard stop because I have an appointment but what we will do now is we'll continue people that have questions about cleaning this data here is not clean at all what I want to do is I was going to go here turn this into a percentage make this into dollars because it's price it's money change it into dollars I already trimmed this the spacing will clean I already changed this the formatting or clean I spaced out these into two separate columns and the last thing I wanted to do also was to change the brand names you'll see okay this one I say Nike incorporation this one said Nike alone this one say Nike UK there's a a way to clean that up we can change that let me see if I can control that real quick uh if I come here and I pull equal unique if I pull unique and then a highlight this column okay I like the whole corner and I click enter okay what is going on here I did not like that yeah let's see unique equal unique and and Nike it says Nike Inc okay if I click this let me see right there Ctrl H what I just did was Ctrl H okay let's say hmm find me find me Nike incorporation with the dot you can't forget the dot uh that's one thing we pay attention to when coding everything matters the the case the capital the the dots everything you must pull everything let's say we want to change um Nike incorporations into sample Nike and replace okay replace all okay you'll see that what just happened it changed it and if we go back to our column here there is no more Nike incorporation everything Nike incorporation will change into Nike okay the last thing we have here is it has Nike UK we don't want UK that we want Nike to be Nike if you come to Nike here okay we can do control h control h ask you what do you want to replace okay I don't like uh Nike UK I want to replace all of them to Nike alone okay all right I close this let's go to our brands oh something happened there's no Nike incorporation there's no Nike UK why because if you give this your loss and they want to find okay Nike and you in because how we have Nike and have UK by the name if we already have location we want everything to be formatted in a in a way that when you go here you know what I'm doing here is legit the the cleaner the data is the the more efficient your worker will look the more better you will be at what you're doing so what I just did was I I changed all the Nike because Nike is Nike we don't care if you're located in the UK us or Africa we don't care Nike is Nike so we changed all Nike related to our table into simple Nike by doing controllers and it will ask you okay what are you looking for what do you want to replace it with you click OK it will replace everything in a table so that that is also one thing um yeah I think I think after that my data will look a little bit more human more useful because here we don't need to change anything here we change we separated the first name and the last name into two separate columns here we will change the names to be consistent if you say Nike you say Nike reason being is I'll show you guys by building a small dashboard um you'll see why that will come in in hand when we put a filter on the dashboard and you click Estates you don't want the states to say okay Nike UK like no no no Nike is selling this much in the US Nike is selling this much in Africa that's it that's what we're interested in here what we did to this column was we changed the formatting the formula is still here we put proper we put proper as a formula and proper will just change the case that's some of them are capital some of them are it's not looking good so we change it here we use the formula called trim we trim the spaces around the words because some data comes in like this because people that enter data they're on a fast timing and they most of the time they just give us a lot of tedious work to do and we have to find a way to do that fast and efficient because you we don't have all day here you can see that there's three space between Germany and Berlin and we changed that by just simple formula and um here uh I show you guys how to change this into currency um click this it will give you a dollar but that that that specific column is not supposed to be in dollar my columns are supposed to be in dollar don't want to cooperate so I'll leave it alone except to that price uh up there in the price you have to put the the dollar sign there yes yes that's good yeah yeah maybe you can try putting the dollar sign on that price because I was practicing uh with this so I don't know what I put Excel is very complicated sometimes because there's something I think I've done that's given me that hard time because I can't even go as far as Excel is so crazy right you can I can give you this and I'll pull password on it here I can lock it down and you can't do certain things let's say I'm an intern at a company and they give me an Excel file that's very sensitive and they don't want me to change things in it my boss will go and put password and protect the sheet do all sort of things and I'll give you the same thing you can't do anything with it because it's protected unless you you copy uh you take a picture and you type it in yourself and I think I've done some I don't know what I've been clicking but if you want to change any kilometers price you click the column you go to home that's why I'm showing it with this column I know price is supposed to be in dollar when I do it there I do not know what is going on but it's fine we know how to do it that's the most important this is a practice data who apparently invest anyway so those are the cleaning okay now so quick question please okay yes okay first question is um after cleaning the data what would you would the raw files uh are we going to delete the columns that will we cleaned I mean the original columns so that the table or this the sheet will look um more on that understandable and second one is look up but I want to also know the difference the actual difference between the vertical lookup and index match yes okay in this match how do I put this I always say that the vertical lookup only considers the vertical session of the of the table and while the index match uh just look up anywhere and figure out what you're looking at and that was my thinking but I want to make sure that I got it right yes you got it right we have eight lookup it even gets better right we have one more that a lot of people don't have even me I don't have it it's called X lookup we have so we have can you see my screen here you can see my my screen right I can't hear anything yeah yeah we can yeah yeah we can see the screen we have V lookup we have age lookup we have index match we have the new the latest one it's called X lookup um I don't have it it's it comes with updates and Excel in the US we have to pay for it and then it's um it's a lot of I use it at work because those people have so much money they it's a big Corporation they they have those in deck you can use it all all day you like X lookup for my understanding is X lookup is an alternative for vlookup and H lookup combine I've seen a couple videos about it on YouTube and then that that also is a great way to learn by the way YouTube we we use YouTube a lot it will give you everything you need index match instead of vertical lookup and horizontal lookup index match just flow through your table anything you're looking for it will give it to you so that's the that's the difference between vlookup and then indexed match slookup I can't tell you much I haven't used it personally but I know I know that index no x x lookup is the combination of vlookup and hlookup is the latest the most updated version of the lookups as far as I'm concerned is that is there anything close to your your answer yeah yeah so um back to the first one what we'll do with the tables that should clean I made the raw file before not transfer it to the new uh yeah yeah what I wanted to show you what I wanted to show you is uh okay what I would do is I'll come here right click and just where's my duplicates okay let me see and somebody see duplicates here maybe click on the right um yeah yeah it's supposed to give me actually I think I think where we get that duplicators from the is easy that will yeah yeah I wanted to duplicate but if you don't okay you can just click new sheet but what we will do is Excel is smart enough right let's say okay let's see I want equal Robert equal proper is I'm gonna come back here it's supposed to give me the the value Excel can do formulas across sheets right so instead of you cleaning the data the way I did it you can do it in two separate sheets or as soon as you you're done with the one that you you clean you click delete and you click the the non-valuable ones and then your data will be clean or you can do um what else you can do um okay let's say I want to inserts Ctrl Ctrl shift down thank you next destination yeah did you see what I done yeah so so it got that that means yeah this area still confusing me this particular thing is because oh yeah it looks complicated let me go through it one more time and just it's it it can it can be overwhelming so what I'm doing here is I'm highlighting my whole column that I'm going to be working with I want to separate the first name from the last name because they did not separately well it doesn't look good enough so what I'm doing here is under data tab every Excel columns I mean when you open it it stays on the home page you go to data tab and you click test to column did you see that did you follow that step yeah okay now it's asking what is what is my name that I want to separate it's separated by so you click instead of clicking this I've already done let me reject it so now some names some names can be separated by similar color Commons space and others mine in this case is separated by underscore so I'll go to other and put numbers order I don't know are you paying attention as soon as I put underscore it separate the name into two under you see it yeah yeah so as soon as it does that I just say okay that's what I want and where do you want it will ask you where do you want to paste it destination here okay what destination I want it to be next if I click enter and finish with your separate first name from last name wait and then you go back and clean and delete it and you can just click this column here and just click delete and then now instead of contact you have first name is more now uh do do too much time here well okay I got it thank you yes like I was saying I have a lot of things playing but I have a I have a hard stop at one o'clock which is less than 30 minutes but anybody that have questions you all have my LinkedIn or ask Stanley Stanley will get to me um I'll go right ahead and then start with the next thing I wanted to show which is Coca-Cola data yes exciting Okay the reason why I picked this is is just to Showcase this is just a bunch of roles that we deal with all right um this look a little bit more realistic all right what let's see the columns here this says key American Coca-Cola retailers all right this is real data the retailers we have retailer ID we have a invoice date we have regions we have States and that's that's uh in terms of U.S states where they're located you'll see that when I scroll down it turns from New York to Texas to Houston I'm just giving an overview of the data here so we can have an understanding what we're dealing with we have the beverages brand that are under Coca-Cola of course these are all Coca-Cola's brand or people that don't know Coca-Cola own a lot of things they're not just Coca-Cola they own Diet Coke they own Sprite they own fans big big company all right the unit price how much are we selling it for okay units sold by region by state how much are they selling in those specific area Okay the total sale we have uh operating profit we have operating margin okay now what I want to show here is let's let's say we have passed the clip the data cleaning you'll see that this is presentable everything is consistent there's no funny spacing there is no but data don't come this clean that's why we went through the cleaning phase and then we'll analyze what the the cleaning is supposed to look like okay first thing I'll do is um did somebody say anything okay yeah first thing I was I'll do with this cleaning day for now is um make sure it's in the table it looks nice but is it in the table all right we'll find out Ctrl shift down Ctrl shift right it highlights all my columns in it in uh in the sheets I'm just scrolling up just to show that it's highlighted and everything okay if I do Ctrl T It'll ask me do you want to turn this into a table and Does it include header yes you check it it has headers region states yes okay if I click ok now what happened you change it to a table just like I showed earlier I can if I don't like blue if I don't like whatever I can change my table format if I like okay the the the the blue look good not bad uh now this is in a table it's more efficient this this then we can use but do Ctrl shift down Ctrl shift no no no no go back up okay I click this and I do Ctrl shift Ctrl shift down it highlights my whole table Ctrl shift where you want to go and you just highlight everything you can highlight it but it's tedious you can have thousands of rows you don't understand and you say oops I let the clicker go not to go back up it's not good so it's the best practice to use Ctrl shift Ctrl shift images do uh you do it more efficiently okay now the exciting part Stanley this is for you we'll create a pivot table all right uh what we will do is Click insert you see here we have two options it has recommended recommendation recommended pivot tables and we have our own pivot table okay Excel is smart enough let's say we want to trust Excel let's say what are your recommendations Excel well it's saying I can create a pivot table for you by uh by region which is Midwest Northwest of uh if you want I will insert it I said well no I don't like that I can do control Z I don't I don't like your pivot table I can go back my table is highlighted I can go to inserts and I will create my own pivot table okay it says what what is your range uh your range I already highlighted so it selected it okay he said do you want the pivot table in a new worksheet we could the sheets are the bottom here or do you want a an existing sheet that you already have let's see I say I want it in a new sheet just to make it cleaner okay now it automatically created a new sheet for me why do we use pivot tables pivot tables are like calculators uh calculators on steroids why because I don't have to go there and pull equals total or equal some of this I can just drag it drag and drop and it will do all that for me okay let's say the first step with pivot table I want to create is by States okay okay let's not put in filter let's say okay these are by States these are the states that we have okay I want to know by States I want to know um the total sale they made okay I'll drag the cell into values what just happened here pivot tables are amazing by states in the US it was telling me the total cell every state made and this is amazing because this will take time to go through that sheet and do Plus on the paper I tried to find like States each state how much are they selling that's a lot of work pivot tables no more issue okay that is not bad that is great uh good let's see we know what they're selling by States okay but I'm not satisfied I want to create a new one again I want to insert a pivot table again and it's asking me do you want in a new worksheet or an exist okay I want an existing worksheet okay when I click here and I just click ok I want all my Facebook table to be on one sheet so I click assistant sheet okay I want to know um by the beverage brand is it Coca-Cola is a is it Fanta is it what what is it I want to know um I want to know their total sale also so we can compare them which one is doing better okay when we look here Sprite has some great number Coca-Cola has some great number but it looks like big boy water here is doing some amazing numbers also um so by brain we know now the total sale of 2021 which is the data how much are they selling okay that's great now but I'm not satisfied I want another pivot table uh they have other questions okay fine I'll go and then go to my data and set up inside a pivot table I want it in an existing worksheet okay I'll say put it next to this guy you know put it next to this guy for me all right on my next pivot table what do I want all right um I want units sold right all right that is the sum of the units sold oh no unit price okay I want the units sold also that's great but what do I want to buy I don't want it by State I already have some states here I don't want to buy a label but I want it this time by let's see cities are too much it's too long let's not do CT if you don't want anything you just okay let me show it again if you don't want something you drop it here and you say well I don't like that anymore you can drag it and act like you're gonna drop it here and it just vanish great now I want to buy a region in each region right of the US what is going on with these cells unit cell okay it's showing me what is going on well I said great no problem now we have some we have three pivot table here we can do more like I said these when it comes to analysis you can pull your own twist to it because you can be creative but one thing you have to watch out for is what you're presenting has a story to back it up and it's accurate sometimes it's not going to be as accurate as you want it because there's gonna be NT sales empty rows and you have to come up with some stuff yourself sometimes on the spot because just because of that you would just stay in the office and say well I'm not going to do anything because uh Coca-Cola doesn't have their email listed and then the column is blank well why don't you why don't you create something and you have to come up with an inside they're not going to now go back to each region and say well who collected this data and I need to know whose email is supposed to be here and they didn't put it we don't have that time so that is why we go through the cleaning that goes through the cleaning process also you can come up with like things you want to insert there but we have better uh softwares that we're going to go over in a minute which are the coding language and then the physical position platform they ask you if you want the empty columns or no you can check it off and it will give you accurate um insights okay now I'm getting off track here let's say let's say I want to you know okay my voice is oh your voice cracks because I think it's okay now it's okay now yeah all right sounds good okay let's say okay this uh data we're building the pivot table on and this is our we have three different pivots so we can build 50 if we like well okay from this pivot table okay I want to do something interesting if I click my pivot table I don't think I need to highlight even if I click my Pivot tip when I go to now go to data the data okay here it says pivot chart all right there's two things you must be careful of you can insert you can insert regular charts it'll build you notice dashboard but it will not be interactive which is what every data analyst is going towards I can build charts that okay in this region here in Nigeria this is how much Coca-Cola we sold this year but what if what if the guy wants to change the region and then the chat updates itself all right we can build that with pivot charts okay this is not bad this is just showing me Coca-Cola is doing better than every other brand they have um with the numbers okay that's great that's not bad I can come here and clean up the charts let me see if I can make I can bring this into my I want to bring this into my dashboard control Z right let me see if I copy this Ctrl C to copy Ctrl V to paste Ctrl C is copy Ctrl V to paste okay not bad not bad um what do I want to do with this well I don't I don't like I don't like the formatting it came with all right no problem no I can change everything about this chart I just I can click on fill hold on this will bring me back to my old pivot table that I was working with I can um close this I can close this okay now I want to change the formatting of this chat here so okay when I come here first I want to do something before I change the formatting okay let's let's move this around to make your to make your dashboard look good what we do a great hack we do here is uh let me insert the shape you go to insert Tab and you insert a shape all right the ship is not bad the ship is not bad if I insert a ship here is for a reason it makes your chats 10 times better okay now if I double click on the ship I can now format it also I want it to be gradient all right here let's see you can change your coloring your yes oh it says test all right this is what I want okay I am looking for where I can change this all right here's where I can change it if I double click this right I want to change this color I can I can change the color let's say okay okay I don't like that let's say that's in one purple or one ingredient right uh this is the most we use uh because they just give you a chart it takes your child to another level now you can see that it's it's um it's making the blue somehow it's it's gradient it's uh we all know if you have edited a picture before you know what I'm doing here it's just to make it a little nice and after that what we can do is okay now we can very well yes yeah that's what that's what I'm thinking also because because the the the charts itself is like blue also so if you if you take the if you take this I think we should use orange or purple yes if you can use orange background because if you take this um back this chart this is a graph to this place you will not you're not able to see it again yeah yeah yeah yeah I see I see where you guys are coming from okay yeah so if you can change the background to um I think we should click on the on the what I want to do let's let's yeah instead of changing the background blue right because you guys are worried about the bark being blue also yeah yeah also change uh on chart let me let me show you what I mean instead of the bar being blue I can come here and be like okay okay okay wow so now the blue is going with the my Bend and we'll the theme that I have going on but the chats are up do you see where I'm what I'm going yeah yeah instead of instead of that I can come here and say why is this white I don't want it to be white I'll come here and I'll just drop the background and the borders and I dropped the Border also and if I click out of it the chart is looking already something better than we had we can what about the right top the Red Top here yeah the right of the figures the the oh the the right tops okay okay yes yeah you can change those also um we can come to let me see he said all right uh I mean let me click these numbers [Music] so you guys see how I'm just going around you play around with it you you come up with what you actually like and you can change everything about your chart but the most important thing I want to show now is how we make the charts talk to each other in their Dynamic all right uh due to my time I will not make it look the prettiest Stanley has seen some of my work um but if you take your time you build your own charts you say okay when the Black chart is going up I want it to be gradient I want it okay when people are spending more money or we lose money we want it to be right the charts itself will be red we want this one to be green when the profit is positive we can change all that in in the dashboards but that's why data analysts and as we have a job because we take out and we build it into what hello hello yes um please I have a question go ahead um I can I can see that uh on the rule we have rule labels so because of uh because of the rule levels we don't have a title on our bar chart I can see that it's written total but how am I able to differentiate between a pivot table of this brand against this brand because I don't see any title on their on the voucher it's not described okay um that is a good question but it's if you're building your charts you can see here the okay Coca-Cola and this this is from this baby chip chart this is from this these are by region and this is by state but that's a that's a great question that's why I'm saying we if we have time this will not look like this we can say okay uh we can change the title we can change the title to anything you like anything you like you can change the title to it and it will tell you or the the reader whoever you're you're you're given the final work to that okay this table is um total sales by brain okay we can we can name our chart that you can name it that so to sell by brand um let's see yellow is crazy I am not going to like Orange okay let's just pull let's just pull white because right now it will pop and we can change course these are in Microsoft Word and everybody understands how these work um so this is your the title issue has been saved you can do whatever you like and even these lines right even these lines I can remove it I can say okay I don't want these lines it's clouding up my space or whatever you want and these charts it takes time to to you know bring to life what your vision is and what color schemes you want to use one great thing too is if each chart is used for a specific reason few ones I want to go over as a as if you want to build charts right if your dimension which is let me go to my pivot table let's say this one say Coca-Cola blah blah blah blah if this one is more than five right what you're measuring the thing by which is your dimension if this is more than five uh a pie chart is not recommended why because a pie chart if anything is even more than three or four it's not recommended because you just have a bunch of slice like pizza different colors it don't make sense we want to show clear difference so if somebody see it they're like okay this is leading in this region this is leading here if you have a bunch of slice let's say I want to create for example let's say I want to create a pivot chart where's my Pivot chart table inserts with a pivot chart look at this okay this is this is this is not bad but I want to see if I can come up with the pie chart which is TD it's which is dumb which is look at this oh what is this he's like um that's two yeah what are you going to give this to and what does it mean I should be frozen I should go and go find Alabama as Owen so it's quick rules we don't use pie chart if it's a lot of slice no another one is um you want to be careful with the maps maps can be tricky I want to create one to give you a a good understanding here um let's go here instead of instead of the let me insert mops favorite chart okay let me see if I can just change charts okay chain chart to map all right that sells acting stupid like expected um let's just highlight this control shift instead of this it's not going to work because it's a pivot table I don't know why Excel is racist if you use a pivot table you want to build map it will not take it so what we do is we copy and paste the value okay I'll copy this and just um let me just go to my dashboard but a quick second let me see I want you paste it here normally there's no numbers here in our dashboard we don't like that this is supposed to be clean this is our final report we make it Dynamic and there's no numbers to scare people off in other departments so um let's say I want to insert a map okay [Music] um you can't create this chart then you just type update though okay this is what pivot table does it doesn't work well with maps that's why I was trying to cover it a little bit but I've I've figured out to build it before I don't know it's giving me a hard time now but let's see you I don't know go somewhere let's come here okay copy our states by sales um we'll come here did I even copy it okay to copy to copy we use Ctrl C shortcut Ctrl C and to paste it we use Ctrl V all right with this let me see if I can enter a map now okay the map is here it's loaded uh and we can also represent this in a graph form right in a graph form what what does that mean exactly you mean like representing these uh these figures the states and the figures in a graph form um yes yes we can use graphs but uh what what kind of graph exactly because maybe yeah go ahead okay maybe maybe just um normal familiar graph s i s y z graph you know just showing the rising and the following of the sales like what we have there in the in the chat like this chart you're showing now yes yes you can do that but um depending on well us what we do on a day-to-day is we build uh interactive dashboards which is what I'm trying to show here but what you're saying yes you can build that short answer yes but we have various ways you can improve your point better okay time is gone here but let's see if I can do something real quick all right here the the dashboard here is supposed to be interactive but it's not going to be why because we didn't build the chart the the map right directly on uh on the pivot table we did not because we copy paste it and the pivot table won't work so what we can do to bypass that we can go here and say okay now I want to select separate data that will work with this all right no problem no we'll come here and now select from the pivot table you can't even see the Highlight but that's right okay what is that what that does is let me show you guys real quick um let's go to insert my favorite thing to use nowadays is called let's see data [Music] okay all right it's called a slicer what is a slicer um let me click States what does what is a slicer all right the slicer is what we use to to filter our data let's say I I give this to my boss and he wants to know okay your dashboard look good it means that ourselves the darker the color the more cells we have the lighter okay that makes sense everybody can get that California is doing great uh we have uh Miami here doing amazing Texas relatively good the middle here I don't know I don't know maybe they don't like Coca-Cola but we don't know that maybe it's our marketing maybe something is not going right okay if the bus come here and click Alabama let's see what happened you'll see that this chart changes if you click this it'll show you exactly what's going on in each state normally the the this guy is supposed to change too let me see if I can fix that real quick select data I don't know why it's not selecting this oh okay let me just cancel it let's see if I do it here it will be more leaning if I come here I can so let's see if I come here I can select my data he'll ask me what data am I selecting no problem I want to select this okay now it's working you have to barely Excel sometimes it's very can make you mad if I select this data what I've just done is instead of this copy here is not interactive and this one we have formula we have formula on this pivot table and it will work because let me let me go here let's see okay working on dashboard still not working on the other side let me come here see um let's insert let's insert a slicer a slicer is what we use to filter data okay I want to do it by State let me see Alabama yeah this this map is loaded so you what I'm trying to get to here is the reason why we don't use Excel for dashboards is because it does stupid things like this let me show you what we actually used to build visualization any question here before I close this I'm not going to save it because all this thing if I have time I can rebuild them in a second but normally everything is supposed to be interactive let's say I build a where's my Pivot tables they are all right here okay let's say I come here and I insert paper chart instead of that chart I don't want that chart I can change the chart to um I like I'm busy please come back give me a second I can change the chart to sometimes I sell itself will not let you because they know that the chart that you're picking it's not interested let's say you want to do sell by brand and you pick map how can you do that it doesn't have a state give me a second all right uh yeah like I said my time is up but let's let's cover this thing a little a little quick if I copy this by chart and I stay here let's see this is a this is a time a time a time one we have a slicer and we have a time what is that thing called again my timeline yes so if you want to know in February or in 1993 what was the sale of Coca-Cola in the US is specifically in California data analysts can pull that out for you so after we build this you can click February what was it you'll see that the dashboard is reacting is reacting accordingly uh and also the one on top is reacting accordingly we give this to the boss and it's easier for him to interpret the data that we have worked on we cleaned it and build this little application for them normally this map is supposed to be reacting but that's one reason why we don't build this stuff in Excel but it's great to unders to have an understanding that Excel is in fact capable of doing everything a data analyst does but it's definitely not efficient all right uh I'll close this here I'm not going to save it all right um the visualization I want to use all right first I want to go over something called ETL what is an ETL sometimes we don't have all the data at hand so we'll either go buy it from a third party or we'll uh extract it from the internet which is what I did let's say we have we have a world cup data we can come here and if you go here you can see they have World Cup data here in a table can everybody hear me fine yes yes go ahead oh yeah so ETL is basically where script and data from a website to Excel or in a uh data warehouse that you want to play with so ETL stands for extract transform and load before we start working what is a ERD I want to also touch up on this ERD is a entity uh relation ERD entity relation uh diagram diagram okay I want to show this because in the coding language there's something going in the background every table is connected so we connect those table by coding the SQL language and I'll tell SQL that okay select select control header and then connect it with this table so we use something called primary key and foreign keys what is a primary key a primary key is what I was referring to in my first Excel sheet that is very important but we will not use it here this is a primary key of this to you because it's a unique identifier the primary key is the only thing that can connect a different table to a different table so if this primary key there's that primary keys and a different table we call it a foreign key so we connect those two keys together which is the exact thing but this let's say that table has customers information and this table has sales information what do what may they have in in common customer and customer and then sales table customer and sales table what can they have in table let's see order ID they must place an order right the order ID might must be in unique so the author ID is only for you so if the other ID is in the customer table and it's in a sales table why don't we connect those and we can pull information across both table at once so that's that's what we do and that's why ERD is the more terms that we use everything that's under this is called um an attribute an attribute and an attribute is under an entity we call tables technically entities we connect them together so that's this is the back end of the the language that we use uh this is the language actually SQL let me open and see what I can show here all right let's let's let's let's let's let's let's I've created some tables okay let's go to table and click employees okay in my employee table what do we have here we have Lisa in their salaries that's great and what just happened it coded something for me I clicked it manually but it coded for me okay let's say I don't want everything from the table this is the table I just I only want the name from this tip all right I'll go here and say instead of selecting everything select name name from this table or is it giving me red already so the interesting thing about it is sometimes you sit here and your code will not run well you will see that what just happened is instead of the whole table it's selected only the name if I go back if you want to select everything in SQL it's just Stars the start the star the star you put star select everything from my table and if I run in here this is the run if I run it select everything but let's say I don't want everything just like the column we were dealing with with the previous uh The Columns we're dealing with like the goca colors and everything let's say you don't want to know what region is from I just want to know the name brand that's winning oh so okay select salary from employees table enough I run it it will only show me salary and I can't make it big okay let's say select salary um from this table the table name where where salary is better than what's the numbers we have here forty five thousand blah blah blah let's say it's it's more than fifty thousand fifty thousand oh okay so this is the only salary that's more than 50 000 in this company but let's say why would we solicit we want the name of the person that's making more than 50 000 in the company so if I run it you'll give me the name Maria Rodriguez is the only one that's making about 50 000. I'm just gonna give a brief overview of like how we can pull information from like you can give me a million row of data if I go in there I'll tell as long as I can connect it to my coding language here I can pull so if we have um much data can we select two things maybe the names and the the sorry so that's yeah so you can appear only the two of them yes that's correct plus no it's a common okay come on okay and this okay I want to show everybody one website what happened I want to show everybody one website that will really uh help you if you want to learn SQL SQL it's you can't claim your data analyst without knowing SQL so it's better to if you want to do anything with data in a in a bigger manner you you need SQL but the only reason why we learn Excel and we're very proficient in it is because people are still old school and then everybody's not going to learn SQL also if I'm done with this and I want to extract this data I'm gonna export it into Excel and just share it everybody else that will that would be very great um let's see yeah the website I want to I want to share with everybody is this it's called where did my website go yeah here it's called w 3school all right what this website does is it will take you anywhere from beginning to being comfortable with any coding language you want to learn as me me as an uh data analyst I only need SQL and then a little bit of python but python I have a class only and SMS are coming up but python when it comes to data we only use Python for we use python in pandas pandas what is pandas it's a library impact so python is a big language people code websites application in Google app store or Google Store Google's Play whatever you can code those things but as as data we don't use it for that we use it for to get insights from it to get more efficient to do regressions on it to do uh aggregations we can do aggregations in SQL so so SQL is really what we need as data analysts but python if you bring it it's just you're undeniably good so now uh SQL is what I want to cover if you go to w3school it's free it's a free tool you just go to this website here it's called w2schools.com if you want to learn SQL it takes you step by step everything you need to know about SQL see now it's selecting column one column two from the table name simple you can pull that off and it gets advanced and you can take quizzes and prove yourself me uh I've I've got I've gone through those and then the one that are the latest one I've done is this one this is the course I took on LinkedIn uh you pay for it well I didn't pay for it you can do free trial and then with the timely manner if you get it done for us you don't have to pay for it you cancel it before they charge you and that's fine you can't do that um yeah so briefly let me show you the visualization platforms the the one that I the ones that I know is power bi all right what is power bi this is what it looks like this is the logo power bi is made by Microsoft and you guys can even see what it does beautiful this is the type of dashboards out the building so here what we build in power bi and other tools as soon as I click I click on this stage no no no as soon as I click on the state it should react dynamically what that state the bar chart should react everything should be like okay if you click this thing this is what's going on if you click this thing this stuff on the cells it reacts so that's what we we want and power bi is really great too I've used it a couple times um that's one visualization to use the next one we use is called tableau uh it's actually the number one in the leading Market it's you as you guys can see it it does almost the same thing Tableau I've had a class on it it's great see it builds map and these is probably like the amount of sales they're doing or the offices whatever if I take time I will understand exactly what that person is building it should be straightforward here you can tell that the guy has customers okay oh it's the actual video so quick uh for my internship at my company what we're using that I had to learn it's a click so this one it does the same thing also but this is what I'm using uh my current or let me see if I can pull it up here like [Music] so click is free technically but the advanced version is not free Tableau is free also but the advanced version is not they're very expensive tools by the way power bi I think is like three hundred dollars to get it um so this one let's say I want to create dashboard I can just um create a new analytics apps let's say Eric created but this is online here I'm supposed to be using it like uh on a desktop that's just better okay I can click here let's say the data that I want to import is uh that sell data that we used earlier oh no this is not my data okay if if I don't have the data here I can upload it from my computer and then let's just say upload data okay okay if I click upload It'll ask me where's your data located okay so my computer uh clean myself out dashboard let's click there it will automatically upload my data once we'll click it will tell you okay do you want all the columns like this or do you want to change the name you can transform here you can do whatever you like if you want to drop a column I can uncheck it if I uncheck this one it's not going to show up there if I clicking I click next it will it will load my data so after we clean and do all that the question that we're answering because most of the time our job is to answer people's question clients or whoever so now we can just create sheets you can duplicate this sheets or you can create as much as you like so those sheets or what we build them just like Excel we all know how Excel we have you know I was showing we have this the sheets yeah Sally what's going on there you're right yeah so so these are the visualization this is very uh close to what we do if I click Dimension I say okay I want to measure by dates or don't just drop it into a table you can click a pie chart you can click anything you like really I want the sum of something it will create this this is tedious but these are more high quality more efficient and all of them will be interactive and that's why we use visualization platform so um I'm gonna live here uh but I want to just cap on what we what we have done we didn't have much time but I hope somebody learned something new from it I am not a doctor by any meme boy what I know I've shared it um definitely not all that's not all and there's a lot of things oh we do but the to cap everything we we talked about me my LinkedIn should be we talked about Excel we cleaned data we use the most the most important things over some of them um do you look up hlookup X lookup and we have index Maps which is the master of all and after we clean the data we went into xql what the code looked like now after that click is the visualization platform and um I think I think that was that was like Theory basic of data analysis but what what I want to say is if you want to know more about it um shoot me a test on LinkedIn and say okay I want you to cover this or help me where can I go to find more deep understanding about this specific topic because we clean data we make it look nice we answer the question they're asking with the sequel or even in Excel and I will upload it to our data visualization platform and we get insights from it we make it easy visually pleasing to everybody else in the company or the CEO that may want to know okay Based on data past data what's the best item we can sell you can answer that question by using them so we're all about um data and yeah this this I'll stop here any questions I'll say thank you guys very much for introducing yourself that was very kind I have an idea of the people I'm dealing with and if anybody has questions please reach reach out to me and I'll share everything I know about uh because we our job is included by public speaking also we just published if the big mans are sitting you must you must explain what you are built what is it what does it so public speaking is heavy also in it all that to say that there is various aspects of things and if any specific topic interests you is a sale the coding language of the physical musician reach out and I'm open to help as much as I can thank you guys very much yeah thank you so much thank you so much um Eric you have really you know I I never you know data is a serious thing and um it's something that you give it a time you give it time I know that uh last semester I I did a data analysis course and uh it wasn't easy but what I want to know about data is that it comes in different forms and it comes in different in area in any area you are studying because from our introduction we knew that some people were from management to make platform sciences and all that so whichever part you are you cannot run away from data I I thought I thought I I ran away from data when I went and choose public administration only for me to meet the kind of data that I did even this semester even this Summer that I'm currently doing now I'm doing it doing a summer course currently and because it's a budgeting analysis in a public public service that's a cost title and there we I met a different form of handling data on spreadsheets and uh it's not easy the assignments are so tedious and these are the kind of things what Eric taught you guys today is part of the things yeah yeah I don't want to be rude boy I'll join or my I don't know if you guys can see me I'll join on my phone I'll be heading somewhere and I'll be listening but I'll close my screen here okay yeah yeah you you yeah it's okay you can join you can join on your screen probably if anybody has any questions you can ask but if there are further questions uh I think probably I have shared your lingling profile and then other social media platforms they can contact you from there um he's there can you can you cannot he can also because I know that when I was doing a project management there were some places I had issues and then I had to contact some of the ambassadors online and then we scheduled private meeting where he shared the screen and I I shared my screen and we were able to solve things out so data is not something you can learn in a day like what we in fact the time we have we can't even think we can't even what the kind of time we we're going to exhaust what we have in data data is too is a is a whole lot of things that's why that area is a place that is currently selling in a labor market so and some of these things that Eric taught us today is the kind of assignments we do here in school like they will just give you as they will just give you data and say give you questions go and analyze the data they said describe this data based on city describe reason based on state describing based on the unit price all those things so at the end of the day you are presenting something on a dashboard you're not you know after doing it in his accessions you will now present on a dashboard you you build a dashboard mostly what we use to do our own last semester was pivot table that's why I'm I'm a little bit conversant with before table I think there is another course that I'm gonna do next semester we are going to involve with this dash button so no matter where you are that's why I brought this thing to be part of this uh training no matter the area you are you are going to meet this data and whether you like it or not you definitely be data so um I would also advise you guys uh to look at some videos on YouTube because they help a lot most of the assignments I have done so far the lecturer cannot even cover everything in class what we have in class is just two to three hours and uh most times you can't cover so when they give us assignments what we do is we play the video as we are playing the video so that's why I'm recording this that's what we recorded this year we're gonna publish it out soon so when we publish it you can you can go back because you have the video already downloaded in your phone so you can always switch if the video is passing you switch it back and listen to it again so that's how I did someone most of my assignments I used to download those videos when it is playing my laptop I will share my laptop because I have like two I have I make images of like two or three screen in my my house so I'll just share the assignments on my laptop and then share the screen on another screen why it is playing there I am looking at the screen and then I'm doing it in my laptop that is how we learn here yeah in fact there's nobody here that learns with just one laptop or one screen we learn with like two or three screens we played the video on why it is playing on the video on the screen you are also doing it looking at the screen and then you're doing it so that's how we teach ourselves so I I'm teaching you guys this way you can also learn a lot of things about this data because data is where we where the market is heading to right now so thank you so much Eric thank you um I don't know um is there any of the Scholars on the ground that has one thing or the other to add otherwise we wrap it up here and call on Emmanuel Emmanuel is Emmanuel or somebody yeah Emmanuel to give a vote of thanks Emmanuel so that you can wrap it up here because um I think we've really today I plan two hours I even have something I think um do we have questions on the card box uh I'm going through the ladies since later yeah okay so some of all these things I'm gonna meet with uh Rick after now um most of the things he has done even this uh event or these are examples he used to do here I will I will I will meet with him he will send it to me then I will provide them in the WhatsApp group chat I will write them in the WhatsApp group chat you can download it in your laptop then this video is going to be published within the week you can also go back to this video while you're while we did this is for you to be here live and see how it is it was done then with the live session you can replay the video anytime and learn because while one of the reasons we are doing this is you know currently um some of you are about to take uh project management data analysis and you're gonna meet some of these things on the on the way so having the basics because in that project management I mean I mean sorry in that Google and Google program foreign speech you need to do on your own so that's why we have this video you can possibly consult this video you can possibly consult other videos online so thank you so much thank you everyone for your participation um Emmanuel can you give us a vote of thanks okay um okay nobody okay all right I think Emmanuel is not on board so um yeah thank you everyone thank you for your participation thank you Eric for bringing out your time to do this Eric really has program today and the first team is it was like don't like force I begged them please just try and be here and that's why you see him leaving now with him although he's still on the program but he's not only he has now logged in with his phone so thank you so much you have really done well you have really impacted on this on us me myself I even learned something new because um my current place of internship this is something I am handling I'm handling um I'm working in the in the village uh in the in the mayor's office the mayor of uh one of the local governments here in the United States so I'm gonna be handling about what I do is I handle the the data and then present it on a table and we have very much more confidential pivot table I do say a lot of things with people and then I also draw present it in a graphs in a graphical form so but from what Eric told me that I just learned that I can still do it in another way so thank you so much for your for your class thank you for your so much for everything I've done and I wish you guys good luck next week next week is another week we are going to have a a good luck speak to us on how to write statement I know I know a lot of everybody a lot of people have been asking me about that one statement of purpose yes because statement of weapons is something that you cannot do without it's something that you you whatever you are whether you are applying for masters undergraduates PhD statement of approach is what you need and uh very soon semesters are gonna start I mean implications will start soon so you need to learn how to write statement of purpose so this person is going to handle it next week so I don't know the time yet within the week I'll discuss with him and whatever be the time we're gonna publish it on what like I told you guys our time is always between three four five six seven PM Nigerian time so anything you are planning from now until that next two weeks we're gonna end this program doesn't know that this time it can come any of these times so just block those periods for yourself it's your self-development it's for yourself it's for you please by God's grace most of us have crossed this stage we are moving to another state but we really want you guys to come up to our level and also even teach others so students of people is it for next week and then I also want to remind you guys today is the final day for submission of Google scholarship uh program they are gonna by Monday they will release release result of Those whom they have chosen I wish you guys good luck and I pray that they will at least choose most of it that I've applied and then one more thing one more thing before I leave I have read some statement of purpose of some of you and I observed that some of you are trying to maneuver the statement of people's I mean the aces with the AI please be careful how you use AI to write statement of purpose be careful how you use the AI I know when I read a segment of paper was written by AI I know I will know that you have you I just was written with students with an AI so and that is if I can know they will also know so be careful how you make use of statement AI to read to write statement of apples you don't use AI to write statement or purpose you write your statement of purpose from yourself then you try to use some correcting tools some of these like grammarly to correct some of the things you are writing then AI can possibly give you suggestions of you can possibly take it to AI but you don't have to use what AI has given you rather it is you that will feed AI what he should give you I don't know if you get my point so please be careful how you use AI so that you don't um and the glass AI gives you a lot of things yeah AI gives you more of a generic thing that's what AI gives you yeah I can never give you a specific seven dollar papers because hey I don't know what you have done so now on top of Sops what you have done so how will AI know what you have done so you end up submitting a generic thing so please let's be careful of what we do and I continue to pray that God will help you guys all you do thank you so much for my participation and I wish you all good luck and see you again next weekend thank you

Channel: EDUCATION AFRICAN SCHOLARS

Views: 129

Keywords:

Id: 1_tbZyPKmFQ

Length: 154min 53sec (9293 seconds)

Published: Thu Jun 29 2023