How to use Microsoft Excel - Beginner to Intermediate Class (with sample files)

meet tandy she is a new sales intern at the awesome chocolates company as part of the induction she signed up to sell urban choco to some of our customers but her manager wanted her to keep track of all the sales records using microsoft excel so that he can ask her questions and understand how she's doing in that process but she never really used microsoft excel so she's not really sure how to go about this process welcome to microsoft excel level 1 complete tutorial in this video i will explain how to use microsoft excel how to enter data and easily apply any formatting on it how to read and filter the data how to calculate any totals or summaries so that you can answer business questions and how to make a simple graph so that you can understand what is happening my name is chandu i have been using and teaching excel for the last 15 years it is a pleasure to have you and tanmi in this class my mission is to make you awesome in your work let's get into our beginner tutorial we'll start by opening the microsoft excel application in your computer the actual welcome screen differs from version to version but this is how it looks like in the microsoft excel 365 i'm going to click on the new and click on blank workbook this will create a blank excel spreadsheet for you where you can enter the data for this sales information before we do the actual data entry let's first quickly understand the key areas that you see on the screen there are four main areas in microsoft excel screen the top area from this green bar all the way up to here this is called ribbon this is where all the important functions of excel are listed and you can click and access them from various places we'll talk about ribbon in a minute then you have this formula bar this is where if you write any formulas they will show up this area here where you are seeing the numbers and a b c d e f g h on the top this is called the spreadsheet grid this spreadsheet grid in turn is divided into rows and columns the number ones are the rows so this is row number three and when you click on the number three that entire row is selected likewise this is a column when you click on the column name it will select that entire column within an excel workbook you can have any number of sheets as you can see right now i have only one sheet and it is named sheet1 you can add a new sheet by clicking on that little plus button there if you click on it once excel will add a new sheet and whatever sheet you are working on that will be highlighted so if i click on sheet1 it gets highlighted and this is where we are currently working let's just delete the second sheet for now we will add it later on to delete a sheet right click on the sheet name and select the delete option it will delete the sheet and any data that is in that worksheet the next area in the excel screen is this bottom area this is called status bar this is where some status information will be shown along with the zoom scroller to increase the size of the actual spreadsheet so if you by default this is set to 100 but i can increase it or decrease it let us start by saving this workbook and giving it a proper name so that we know how than we can keep track of our sales data to save the file you can press ctrl s or alternatively click on this save button on the screen that is shown all the way at the top when you save the workbook excel will ask you which folder you want to save it i'm going to save this in my beginner excel course folder i will give this workbook a name like thanves sales tracker let us start by setting up a title on the top to tell us what this workbook is all about you can select any cell in the row number one and type the title we'll type the title thanvee's chocolate sales tracker because this is the title we would like this to stand out so you can select the row number one place the cursor between one and two and adjust its size so that that becomes bigger select this cell we will make it bold as well as increase the font size you can also adjust the alignment of this for example i can align it in the middle of the cell or top of the cell let's put it in the middle and i will select this entire row and i'll fill that with some color so that it's easy for us to spot that this is the chocolate sales tracker here we will keep track of the sales information as it happens let's define some information that we are going to collect every time we sell a box of chocolates we will track the date on which we are selling name of the customer their email address will make a note of how many boxes are sold and how much amount we collected from them also the payment mode is it a credit card cash or mobile phone payment such as google pay this seems like enough information now as you go and make a sale you just record the information here for that date the name of the customer their email how many boxes sold amount and the payment mode let's type in one of our first customers data here to see how it will be entered in excel and how it appears on 25th of august 2021 tany met our very first customer wallab nagaswamy and she sold him 23 boxes for about 900 rupees and this payment is collected by cash now as you type this information you quickly notice that some of the names or email addresses are not fully visible this is because this column is only this wide whereas the name is too long you can see that when you select the name the name does appear fully in the formula bar but within the cell you cannot read it to quickly adjust the size of these columns you can select the column place the cursor in the middle and drag it to make it as big as needed if you don't like what you have done you can always press ctrl z to undo the actions but because we have multiple columns we would like to make adjustments all in one go so what you could also do is you can select all these columns together just click on the first one drag and you will select all the columns now place the cursor anywhere between the columns double click and excel will make the adjustments necessary to fit all the content while this is good the date column is too wide so i'm just going to adjust that notice what happened as you type this information date boxes and amount are automatically right aligned this is the default behavior in excel when you put in any dates or numbers excel will automatically right align them and anything else like text will stay on the left hand side you can change the alignment using these alignment options on the home ribbon now that we are talking about the ribbon let's go ahead and see what other ribbons are there the very first ribbon in excel is home ribbon and then you have your insert page layout formulas data review view these other ribbons developer and powerpivot are the extra special ribbons that i have added because i do some advanced work in excel you can enable or disable these depending on when you need to use them because you are just getting started with excel i recommend not touching anything else and just using the ones that are available there for now let's enter a few more customers data so this is how tanvis sales tracker looks after she finished some of those sales in the last week of august and the 1st of september to start off we will understand how to make tanvi's life easy because everything looks quite busy here we don't really know where the heading is and where the information is one easy trick that you could do is you can select the first row where you are maintaining the headings and you can make them different just as we have done it for the top row here so you can se make them bold you can add a background color on the cell and that will make it easy for you to keep track another common technique that people use is select this entire data and using the border option here just add borders so that it is easy for you to read this information and as you make new data it will be easy for you to type in as well now because we don't know how many more records we will enter into this file it may be hard for us to actually apply all of this formatting so normally what people do is they select a big kind of range like that and apply the borders on all of them so that you can go and type in the information as you make progress but excel offers another powerful way to handle especially when you are doing data entry or maintaining data in this format so i'm going to undo all these steps and i'll show you how that will work the shortcut for undoing is control z you just keep pressing it a few times until you go back to the way things are this type of a data is called a table data or a tabular data because it looks like a table with few rows and a few columns each column contains one type of data this is my name column and that is my boxes sold column when you have a table format of data you can actually tell excel that this is a table treat it in that special way to do this just select any cell inside the table and go to insert and click on the table button there is a shortcut for this as well you can press ctrl t and that will open the table thing we will click on the table button and because we already put the header information in row number one we can leave that box ticked and when you click ok excel will apply consistent formatting for this table notice that it has made the headers look different it has automatically added the necessary borders and colored every other row in a different way so that it is easy for you to type or read the information in a more consistent manner all of this formatting without us doing any extra work so i recommend that you start using tables when you are keeping track of information in excel if you are not a big fan of the blue color theme that is applied by default you can also go to the table design ribbon this is one of the special things in excel when you are doing a certain activity if that activity needs some extra buttons they will automatically appear on the top of the ribbon in a new ribbon so you can see that if i click outside the table design ribbon is no longer there but when i click inside the table i'll have my table designer bun so let's go there and change this blue color to the yellow color that we are following now one of the first things that tanvi wants to do is she wants to understand which customers have paid by cash so that she can add up all of that cash amounts and submit it to the finance department this is how you can identify all the cash customers once you have set it as a table format excel will also apply these little buttons next to each heading and these buttons are called filters i can use the filter to filter out just the cache customers to do this just click on that little button and select cash and it will show you all the cash customers here you can see that all of these people have paid cash if you want to know how many people have paid cash as you filter you can look at the status bar here and it will tell you 9 of 21 records found what this simply means is 9 out of the 21 items are payment mode where it is cash if you want to add up the total amount you just select the amounts and look at the status bar and it will tell you what the sum is your status bar might look different but it will always at least show the sum you can add these other numbers by right clicking on the status bar and adding those things for example i have added average count minimum and maximum as well but if you want you can just show the sum so you can see that than me has collected 12 968 rupees in cash that reminds me because these amounts are in rupee instead of just saying that wouldn't it be nice if we format it in rupee format you can do that just select the amounts in the amount column go to home ribbon and from here instead of general we will format it as a currency the currency formats will be by default in dollar but you can do more so we'll click on more number formats from here we will change to currency we will switch to zero decimal places and the symbol need to be indian rupee so from dollar we will go down and find the rupee symbol there it is english india with the rupee symbol and now all our amounts will show up in a rupee format and automatically excel will add a thousands separator as well so 1369 will read up like that let us say tanvi wants to see these amounts in the descending order that means highest paying customer on the top lowest paying customer at the bottom that sort of an operation is called sorting to do this again you click on that button and from here you just tell excel how you want the sort so we'll say largest to smallest and that will put shishupal on the top and bibawasu at the bottom now let us see what happens if you add more data to this information to add more data because this is already in a table format you just have to go to the very last row and the cell beneath it and start typing the information let's type the details for the 2nd of september here so we will type the date 2nd september 2021 and then to go to the next cell you can press the tab key and automatically excel will move you to the next cell but at this point because this is a table excel thinks wait a sec you're typing data at the bottom of the table let me extend the table formatting automatically so it will extend the formatting let's get some customers detail here notice that as you do this because of the way we have set up the formatting excel will do most of the things automatically but what about the sorting you can see that the amounts are going top to bottom up until here but this new record is not sorted so when you are applying any sort in excel it is only as at the time of sorting so if you have new data or some of the numbers change you need to reapply the sort to reapply simply click on that button again and select largest to smallest and it will apply the sort it will move this person in the middle now that we have recorded some data let us calculate some totals so that tanya can report back to her manager and tell her how many boxes she sold and how much amount she collected in total we will do all of that in a new worksheet so i will add a sheet here and now you can see that we have sheet 1 and sheet 3 but let's say you're not a big fan of these numbers you want to give them proper names which is a good idea so you can go to sheet1 double click on it and you can type whatever name you want here so we will say this is my sales tracker and here we will say summary we will print some summary information here but we also want to keep this heading and everything as it is so i'll select my row number one ctrl c go to the summary click on row number one control v so we can copy that same header here as well now here i want to say total boxes total amount to calculate the total boxes you simply want to just add up all of these numbers and print that summary there we could see how many boxes we sold by selecting it and looking at the sum value here 783 but every time you add new boxes you would have to come and look at this it would be cool if that number can be printed here this is where the excel formulas come into picture i'll show you the simple demo formula here first and then we will add up the totals from that table here so let's say we got some number of boxes here 23 25 34 and i just want to add up all these three we can use the sum function to do it to sum up a bunch of numbers you simply say equal to and then type the formula sum open bracket and select the range where your numbers are you will see that excel will say this as f4 to f6 this is because the values that you have typed are listed in the cells column f row number four so they start from f4 and they go all the way up to f6 so this is how excel will maintain that range address and when you close you will get the total as 82. i'm just going to fill some color here so we can see what happens to this number if one of these changes let's say this is not 23 this is actually 93 notice that as soon as you finish typing and press enter the total will also automatically change it will now be 152. so sum is one of the many formulas that excel has excel has hundreds of functions and formulas these are the two words that people use interchangeably and you can find more about them in the formula but here if you go to formula you will see that there are many formulas available to you and they are categorized into different areas so you have financial logical text date time etc what we want is we want to count up how many total boxes are there so we will say equal to sum open bracket and then go and select the boxes data from the sales tracker so after you open bracket you just click on the sales tracker select these box values like this and then close the bracket enter and you will get the answer 783 now let's observe this formula earlier it was saying f4 to f6 but because we have the data in a table instead of saying any physical cell address like f4 to f6 excel will use this table name column name notation why table1 this is the name of this table so when you click on any cell in this table and if you go to the table design you will see that it is called table 1. whatever name you give for this table that will be the name that excel formulas will use so let's call this as instead of table 1 we will call this as sales and when you press enter if you go back to this formula excel will realize that table name has changed so it will rearrange the name and it will say sum of sales boxes sold so total boxes is 783 let us write the total amount formula this time we will use a different approach we'll say sum instead of going and selecting because we already know that the table is called sales we will simply type that name here we'll say sales table open bracket and excel will show you all the columns available in that table for you to add up using the arrow key select the amount and then press the tab key to fill that out automatically you can also type it out but i like to do the auto suggest option in excel close the brackets and when you press enter you will see the total amount here listed as 31 569 these formulas are what i call as dynamic formulas that means if you change your data this summary will always show you the correct amounts and totals so if i go here and for example if i add one more information like this so we have added 32 more boxes and 1010 rupees more these totals will automatically change and it will show you the new information here let's clear away these things to clear away something you just select the items and hit the delete key remember that this will only delete the contents it will not delete any formatting that you have done to clear completely select the cells from home ribbon use this clear all option that will clear everything including the formatting information now that we understood how to use the sum formula let us calculate the average amount and average boxes as well so that you become familiar with the formula concepts we'll type average boxes average amount and this is equal to average of sales table boxes sold so on average tanvi is selling 35.4 boxes you can see that the decimal point will automatically appear based on the result if you don't like that level of precision and you just want to show one decimal point after the value you can select the cell and from home ribbon here you can use these buttons to adjust so we'll just set it to 35.4 and we will do one more average average of sales table amount column and that will be 14 16 rupees 478 again i will use this and it will automatically round as well so 14 16.5 is what appears let's say i want to apply the rupee formatting for this cell as well like the way it is there you could select this cell and go through the steps again but because we have already done it once i just want to get that formatting applied here you could do this with the format painter option select the source cell where the formatting you have already done click on the format painter and select the target cell that will automatically apply the formatting so here you can see that it is actually showing no decimal points because this cell has no decimal points let us highlight these informations by selecting that entire range if you want to select the whole thing you can just select and drag like this but if you want to select these two cells these four cells and these four cells you can hold the control key and select like this once you do this you can apply some borders around that so that this look nice and clean [Music] then we also want to visually represent how much money each of the customers have paid in a graph you can use excel's graphs to create multiple types of graphs to show the information excel has a rich library of various options for this i'll show you a simple graph so that you can understand how to work with them so we will select the amount column because this is what we want to represent and then go to insert click on a bar chart and we'll select a two-dimensional bar chart this is how that will look like it will list all the amounts as a bar notice that especially with excel bar charts the ordering of this is reverse to the way the data is laid out so here sisupal is the highest amount and it is showing on the top of the table whereas within this it will show up at the bottom you can also change the direction i'll talk about that in a minute but for now let's adjust some of these things we want to show the names of the customer here instead of the numbers so we can right click on this chart go to select data and from there you can tell excel various things about the chart so for example i want to say the horizontal category access labels those are the labels that you are seeing here they are one two three four five i want to edit them and i want to use the names that are listed there and when you click ok it will automatically have those names appear on the chart next up as you add new information you need to sort this again so we will quickly apply the sort so that it will do it correctly let's say you don't like the blue color you want to change it to the green color or red color you can select the columns or the bars and as you select you will see that these new ribbons will also appear just as we had the table design ribbon we have now chart design and formatter buttons i can go to the format ribbon and from here i can change the color so instead of the blue color we'll change it to this orange color or the green color you can do multiple types of formatting you can even add an outline around it so let's add a black outline you can see that now there is a little bit of outline around the boxes let us change the ordering of these so that shishupal shows up on the top and b also shows up at the bottom to do that we will have to select this axis to select the axis simply click on it right click and open the format option as you could see within excel you will be doing a lot of formatting to change the way things look so here is one handy shortcut that you can use very easily it is control 1. what control 1 does is it will open the format options for whatever you have selected so in this case i have selected my axis and when i press ctrl 1 it will open format access panel on the right hand side within this we will simply tell excel that this ordering is not right we want to see them in the reverse order so we'll just have to check this and sishu pal moves on to the top assume moves to the bottom if you think there is too much white space between the bars you can select the bar and from the format data series series options change the gap width i'll make this gap width as 50 percent that will make them nice and thick and our graph is now ready this graph because it is coming from the table is also dynamic so if you change any of the numbers the graph will instantly update let us say than we made a mistake with this particular record it was not two thousand one hundred and sixty it is actually two thousand six hundred and ten so she can type this information over see what happens to the graph and you press tab unboohmady's column or bar grows and everything else readjusts not only this even this formulas will recalculate can again apply the sort order we'll say largest to smallest here is something fun and interesting with excel charts by default excel chart responds to what data is shown on the screen so if you go and click on the payment mode and just select phone payments this graph will now just show the phone payments as well this is a powerful way to just see what's happening for your selected data the graph can be on this page or you can control x and put it on the summary page it will still behave the same right now we are seeing the amounts for our phone payments these formulas however do not respond to such filters they will always add up all the data that is shown on the screen if i go here and now change to cache i will see the cache records let's clear away the filter to clear you simply click on the button and then select clear filter from payment mode now all the records come up this graph is also showing like that of course it is too tiny so i'm going to just adjust its size select the box place your cursor and drag it as much as you want let me wrap up by showing you few more tricks let us say tanmi has actually a target of at least selling 1200 rupees of chocolates every time she meets a customer so she wants to highlight when she met the target and when she didn't meet the target because we are talking about amount you can select the amount column you can select all the cells or you can just click on the heading when the mouse cursor is that black down arrow and you can use the home conditional formatting to quickly highlight any amounts that are not meeting the condition that you have set so we'll say conditional formatting highlight and we want to highlight anything less than 1200 rupees so we'll say less than and then just type the amount that you want we'll say 1200 and you can just leave that as it is or you can customize the formatting as you want we'll just leave that as it is and it will automatically highlight these values notice that this is actually a dynamic rule so if something changes let's say than we made another mistake shakum is not 1050 it is actually 1500 it will automatically change the number see that excel sees that oh it's more than 1200 i don't need to highlight and it'll remove the highlighting from that cell needless to say your graph will also change and your formula results will also change that brings us back to tanmi she is super happy with the way her sales tracker is going and really impressed her boss in her first assignment but she also knows that nothing stops at that place there needs to be some more learning so she has got some homework assignments for you if you are up for the challenge please complete these three tasks and tell me in the comments how you went about them or if you have some struggle let me know in the comments so that i can provide some guidance to you those are number one totals by date tanvi wants to know what is the total amount and boxes for each of the dates when she went out and met the customers she also wants to know how to apply sorting by using two conditions that is on each date amount by descending order finally she would like to know how to add amount per box to the table as a calculation so that she can see which customers have higher amount per boxes which customers have lower amount per boxes all the very best and let me know in the comments if you have any struggle with these types of things i hope that has been very helpful if you are looking for more information on how to use excel for data analysis situations i recommend checking out my recent video on complete data analysis course it is shown somewhere up on the screen here and if this is your first time here on my channel big thanks for stopping by and a big hearty welcome to you i give a free excel tips book to all my viewers so feel free to download that a link to that is also shown on the video and screen there thank you so much i'll catch you again somewhere else bye

Channel: Chandoo

Views: 231,421

Keywords: chandoo, chandoo.org, Excel, spreadsheets, excel crash course, beginner excel tutorial, excel tutorial for beginners, excel tutorial advanced, excel tutorial intermediate, excel formulas for beginners, how to use excel, how to use excel in the computer, help with microsoft excel, excel basics for beginners, excel basics tutorial for beginners, xl basics, teach me how to use excel, can i learn excel in a day, quick course on excel, learn basic excel, excel tutorial 2021

Id: F7aPazuS8QY

Length: 31min 44sec (1904 seconds)

Published: Tue Oct 05 2021