Excel for Beginners - The Complete Course

This is Excel for

beginners

, the

complete

course

, no matter how little experience you have in Excel, I promise that as you watch this video and follow along, you will learn everything you need to know to start using Excel effectively. Be intimidated by Excel, you can do it, and regardless of what version of Excel you're using, this video is what you need to get started using Excel. In this first section, we will look at how to create workbooks in Excel and how to save them and we will also learn the basics of what I call the anatomy of a spreadsheet and also the layout in Microsoft Excel.

Let's get started, so every time you start Excel it should first take you to a screen similar to this one that you can see. a list of recently used spreadsheets, but it will also have a way to create a new blank workbook and I'll click here just to show that often there will also be templates that you can click to open and use. search and there's a button here for more templates, but I'm going to click on blank workbook to open a new

complete

ly blank workbook in Microsoft Excel and before we create anything in this workbook, let's talk about the anatomy of a spreadsheet when When If you work in Microsoft Excel, you will always have at least one sheet.

You can see here on the bottom left, it says sheet1, but it is possible to have multiple sheets and all those sheets together are called a workbook right now, my workbook only has one. spreadsheet in

excel

spreadsheets are made up of columns, you can see this is column e, this is column c, this is column k and when I select the letter for that column, the entire column is selected. We also have rows in Excel, this is row 3. row 9, this is row 10. so each column has a column letter and each row has a row number. Now the intersection of a column and a row is what we call a cell, for example, this cell here is the intersection of the column and the row. 10. and an Excel workbook can contain more than 17 billion cells when you click on a cell that becomes the active cell.

Now one of the most powerful things about cells in Excel is that each one can be described by the intersection of its column and row. so for example this is cell f8 and this is how you say f8 what cell is this is cell l4 and this is cell b7 now due to the fact that you can describe each cell in microsoft

excel

there are many possibilities interesting things to come This is because of that and you will see that as you start using Excel, the next part of the anatomy of a spreadsheet is the range.

A range is a collection of cells that are usually grouped together, so I clicked and dragged to select a group. of cells in Excel, this is a range and we can also describe each range in Microsoft Excel once again using the column letters and row numbers. The way you do it is you start at the top left corner of the range and describe that cell so d4 up and then you go to the bottom right corner and describe that cell j14, so the description of this range your reference is basically d4 to j14 and in Excel the way you indicate until is with a colon so d4 to j14 this is very important and it will become more and more useful and important as you continue using Excel so that's the anatomy of a spreadsheet, we have columns, we have rows, we have cells, the cell you have selected is the active cell and we have ranges and all this is done in a sheet or spreadsheet or it is also called a worksheet, you can add more sheets of work by clicking on this plus sign now I have two sheets now I have three sheets and the collection of all these sheets together is what we now call a workbook in addition to the anatomy of the spreadsheet it is also important to understand the layout with the that we have to work in microsoft excel as you use excel you will notice that there are tabs at the top of the screen, usually you will start on the home tab, but we also have the Insert tab page layout tab, data tab, etc Every time you click on a tab, the tool buttons that you have here below change and this part of the layout is called the ribbon, so if I click on the formulas tab, I get the formulas ribbon if I click. the view tab, the ribbon changes completely now I have the view ribbon and each ribbon is divided into groups.

You can see that the groups are separated by lines, so I have a chart group, I have a tour group, a comment group, a table group, etc., not now. all the options can fit in such a small group, for example my graphics group is not big enough to hold all the options, so some of the groups have what I call a home button or sometimes It's called a dialog launcher, but just call it a home button, not all groups have these home buttons, but if you click on a home button it will open with even more options than would normally fit in the space provided on the page. ribbon, in addition to tabs, ribbons and groups and home buttons we also have some other layout features that you really need to know about here on the right we have a scroll bar that you can use to move down and up the sheet of calculation.

We also have a horizontal scroll bar below it. that we have a zoom slider if I slide it to the right I zoom in on the spreadsheet if I slide it to the left I zoom out on the spreadsheet doing this it doesn't change the data at all it just zooms in or out I have some View Buttons here on the lower right corner. I can go to page break preview or page layout view, but most Excel users spend most of their time in regular view. Here at the top left we have what's called the name box every time you click on a cell. you can look in the name box to see the description of that cell or the name of that cell and then you will learn that this name box can do even more for you here, at the top of my Excel layout, I have the quick access tools. and I've customized it, yours may look different, but this gives me quick access to some of the most used functions in Microsoft Excel, my Save button, an Undo button, autosum and more.

Here is my title for this spreadsheet. Here I have my Close button. like an of a spreadsheet and also layout in Microsoft Excel, you are completely ready to start learning how to use Excel. At this point, I'm going to click on the file and save it, but since this is the first time I'm saving this document, I have to save it as myself. I need to decide where to save this Excel workbook, so I'll click Browse and save it to my documents and type a name for it. Excel for

beginners

, complete the

course

and save time now. for the second segment of this course, which is how to enter cell values ​​in Microsoft Excel and let's say I work for a small business and I was asked to create a payroll spreadsheet for the employees here in the a column that I would like to have. the employee identification numbers in column b the names of the employees and then some information about their salaries hours worked how much they are owed etc. and any time in Excel when you want to enter data into a cell, it is important to first select that cell, there is a saying that I once heard that I use over and over again which is select to apply if I want to affect this spreadsheet.

If I want to affect the data in the spreadsheet, I have to select something first, so I want to apply a1, so I'll select it alone. By clicking on it once, I can now modify it by typing, let's say employee ID number 1 is simply 1. I type number 1 and that data appears in the cell, but it's not actually entered into the cell until I hit Enter . or go back on the keyboard now the data is inside that cell notice when I tapped enter the active cell moved down one and that's cool because that's where I want to type the next number two enter three enter four enter and I could continue doing this go down the spreadsheet, but it is a repetitive task and Excel has a built-in function to make it not so repetitive.

There is something called a fill handler or autofill handler in Microsoft Excel and the way it works is that it has been set up to identify patterns and then extend those patterns, so I want to show Excel the pattern that is developing here, the pattern is adding one to the previous number, so one, two, three four, the pattern is clear, I'm just adding one, so if I click and drag to select that range from a1 to a4, the pattern should be clear. Now I'm going to zoom in a little bit so you can see it a little better, but if you look in the bottom right corner of this range, selected you'll see a little green square which is what we call the autocomplete handler or the fill handler if I hover the pointer of the mouse directly over that little green square.

Notice that the mouse pointer turns into a black plus sign now if I click and hold the button. Click, I can drag down the spreadsheet. I'm going to go up to 70 and then I can release the mouse button and see what it does. Excel automatically extended the pattern I showed you below. I'm going to click on cell b1. because I want to affect that cell and I will type the name of the first employee and then I will hit enter and again the active cell moves down the spreadsheet. I can type the next employee name and I will use it first. names to make this go faster and the third name now you will notice that I misspelled one of the names, it is supposed to be james, not jams, so this gives me the opportunity to teach you a very important concept in Microsoft Excel and that is the difference. between clicking a cell and clicking a cell when you click a cell once the entire cell is selected, so if I click jams and try to fix this so it says james, the first letter I type will clear the content of that cell already is, that's what happens when you click on a cell and type it, it deletes everything that is already there.

What if I have five or six words in this cell and only one word is misspelled? I don't want to have to do it. Retype everything, so instead of clicking on the cell, I'm going to double-click on the cell and that takes me inside the cell. Now you can see my blinking cursor and I can use the arrow keys to move that cursor exactly to the right. place and then I can type and correct the misspelled word then I can tap Enter on the keyboard and that correction is entered into the cell now that you are entering data in Excel every time you tap Enter the active cell should move down but what if you need to move up, you can hold down shift and hit enter and that should move you up the spreadsheet.

Give me a few seconds to finish entering names and then I'll continue now that I've finished my list of names. I'm going to click here on cell b1 because I need to show you that in addition to moving down the spreadsheet by tapping Enter and up the spreadsheet by using Shift Enter, you can move right and left by using the Tab, so if I tap the tab, I move to Right now I'm in cell C1 and I can type Julia's hourly wage. If I need to move left, you can probably guess which keys I need to press.

The Shift tab moves me to the left, so Enter is Shift down. Enter. The top tab is on the right, the Shift tab is on the left, so I'm going to go to cell d1 and put in the number of hours that Julia has worked this month, 160. I'm going to move on to the next thing. What we need to learn about how to enter cell values ​​in Excel is how to delete and copy the cell content, so we have already seen how to edit them by double clicking, but what if you just need to copy the content from one cell to another ?

Click on a cell or even a range if you prefer and then hold down Ctrl and tap c to copy. Now there are other ways to do it. You can go to the home tab and click this button here to copy the cell content. c1 in this case it is also possible to right click copy, but in most cases just using ctrl c to copy and ctrl v to paste will be the fastest way to copy and paste, so you could just keep pasting the number 15 in each one of these. cells, but remember we have a tool called the autocomplete controller and it's great for avoiding repetitive data entry, so now, with just cell c5 selected, what is the pattern that the autocomplete controller will see?

The only pattern I'm showing is a number, if I click the autocomplete handle and slide the spreadsheet all the way to the bottom, it will extend that pattern from just the number 15 and basically copies that same number across the entire page, so It's a shortcut that we can use, but what if this number is not accurate for all of these employees? SuchMaybe Amelia is making more than $15 an hour. You could click on that cell and just overwrite it with another number, press Enter and that's a very quick way to do it. but there are times when not only do you need to delete the information that is there, either by overwriting it or by tapping the Delete key or the backspace key on the keyboard, but sometimes you need to completely erase everything that is in that cell , not just the data, but also maybe. the formatting and other information, you can do it in two different ways: one is to right click on the cell and choose clear content, but one of my favorite ways is to select the cell or range of cells and then move up here to the home tab . on the home ribbon in the editing group we have this button and if you click on the arrow next to the button you can delete everything or just delete the formats, contents, hyperlinks, etc.

I'll click delete all and everything will be completely deleted from that range. I'll hold down Ctrl and tap Z to undo that, but it's important that you know that function now that I'm creating this spreadsheet. I realize that I will have a hard time remembering exactly what each of these columns of data represents. 15 what 16 what 160 what I should have put in the column titles or headings, so let's see how to insert new rows and columns in Excel if I need another blank row above row number one, what can I do right? All I have to do is right click on number one and choose insert.

If I need a new blank column, all I need to do is right click on let's say column a, choose insert and I get. a column to the left of column a I can also insert columns between the data so I'll right click on d insert now I get a blank column between c and now e and I can do the same with the rows I'm going to undo all of that to come back to this point where I just have a new blank row on top of my data and I'll click here on cell a1 and call this employee id.

I will use the tab to go to cell b1 and I will type the name, I will use the tab to go to c1 and I will write the hourly wage and I will touch the tab to go to d1 and I will write the hours worked, I will go to e1 and I will write the taxable income in At this point, I'll press Enter on the keyboard now I understand much better what all the data in these columns is about. You've probably noticed that not all of my data in row number one actually fits inside the columns, for example, in c1 every hour.

The salary doesn't really fit in the space I've provided, so let's see how to fix it. You could click and hold between letter c and letter d, columns c and d, and then you could drag that column to make the text fit and you could do the same with column a, column d, column e, but let me show you a faster way if I click and drag from a to e, clicking on the column letters and then if I double click between two of these column letters, let's say between b and c. I'll double click. What happens is that all the columns are resized to the perfect width so that the data that is inside those columns will fit and look much better now.

I am entering data into Excel from time to time. You may need a little help with that, for example, let's say the standard hourly wage increases from fifteen dollars an hour to fifteen dollars and fifty cents an hour. How could I quickly update all this information? one way to do it would be to use find and replace, so here in Excel, if I go to the home tab on the home ribbon in the editing group, you will find find and select, you can see the text or you can just have a magnifying glass. glass, if you click the arrow next to that notice, there is an option to search and there is also an option to replace.

I'm going to select replace and this allows me to find whatever let's say 15 and replace it with 15.5 and I'll select replace all. Done, we made 70 replacements. I click OK and close. You will notice that all 15 have become 15.5. Now there is a problem with that notice employee ID number 15, so I can update it manually, so it's a great time. protector if you want to keep your hands on the keyboard more instead of using the mouse to go here and click find and replace, you can hold down the Ctrl key on the keyboard and tap h and that opens the find and replace option, in terms general, at any time.

You can keep your hands on the keyboard and not use the mouse. You will be able to work more efficiently in Excel. Another trick for entering cell values ​​and data in Microsoft Excel that you'll definitely want to know is how to use the autocorrect functions. So if I go here to archive and I choose more, I can go to options and here in my Excel options, I can go down here to review and here you will see that the autocorrect options change the way Excel corrects and formats the text to as you write if I click on this. button allows me to adjust some of these settings, for example right now, if I type a day, it will automatically capitalize the first letter of that day and there are some other settings here that you can change, but down here you will notice that there is a replacement. blank on blank and there are already some examples here of common misspelled words and how they will be corrected automatically, so let's say I need to type the name of my YouTube channel quite often instead of writing the beautiful name, technology for teachers and students and again.

And what I could do is I could just type t4 tas and then I'll tab and this will make it so I can type t4 tas and it will automatically be replaced with the most descriptive and catchy name in the history of youtube. channels I'll click add, I'll click OK, let's try, I'll click OK again, so now if I click on a cell and type t4 tas, I tap enter, it automatically corrects to be the full name of my YouTube channel . I'm going to undo that, but definitely think about the autocorrect features in Microsoft Excel and adjust them to your advantage.

Now I've updated some of these hourly wages and put in all the hours worked for each employee. One last thing I want to show you. on how to enter values ​​and manage the data you enter in Excel, I want to show you how to move the contents of cells we have already seen how to copy and paste by clicking on a cell or range ctrl c to copy ctrl v to paste You can also cut and paste ctrl of that cell on any of the edges you will see the mouse pointer intersect with arrows coming out of the ends, at that point you can click and hold click and then drag the content of that cell to anywhere you want it go and then release it. the mouse button I'm going to undo that with ctrl z I just want you to see that you can do the same thing with a range now that I've selected the range I go to the very edge of that range click and hold and then drag the data where I want it are, release the mouse button and the data has moved again.

Ctrl z to put them back. Now we are ready for the third segment of Excel for beginners, complete course in this segment. I'm going to focus on the formulas. I would like to calculate the total taxable income for each employee and for that I need to multiply each employee's salary by the number of hours they worked so I can do it in my head or I could use the calculator and then just enter the information here, but that is exactly one of the things Excel does best. Excel is a spreadsheet tool, yes, but it's also a calculator.

It has almost all the calculator functions and operators that you would need here on the cell phone built in. e2 I am going to create my first formula in Excel. Whenever you are creating a formula, you should start by writing the same. When I first started using Excel, this was confusing to me until I thought about algebra in my Algebra 1 class I took. I remember learning about variables and there were formulas like and then I type equals, so cell e2 is equal to what comes next and here I could just type 13.9 and then I could put multiplied by or times and in Excel we use an asterisk to multiply, so 13.9 multiplied by 158 and then I can type enter on the keyboard and that is the taxable income for Julia, so that's one way to do a formula in Excel.

You can type the numbers you can use plus signs, minus signs, asterisks to multiply, forward slash to divide and then just press enter and that formula will run but there is another way to do the same and it is a better way again. I need to start by clicking on the cell and then typing the same, but this time instead of typing the numbers I'm going to type the cell references if you remember in Excel. We can describe each cell in this spreadsheet, we can name it basically and remember we have this name box in the top left corner to help us with that.

So it's equal and I'm going to name or describe this cell to be c2. I'll just type the asterisk c2 to multiply by d2 and type d2. Now you'll notice that as I was typing those cell references, Excel highlighted them in different colors so I can see exactly what I'm about to do now. I hit Enter on the keyboard and get the same result, but this time it's a little better. The reason it's better is because this formula is now dynamic. If I change the contents of one of these cells, let's say that's a mistake that Julia doesn't make.

I don't really have a salary of 13.9, it's more like 14.25. I can press enter and see the taxable income. It's going to change, so this is a superior way to create a formula in Excel. There is another method that I want you to consider. I could type the same and then instead of typing the cell references c3 asterisk d3, I could simply click on the cell so c3 asterisk and then click d3 hit enter and my formula produces results now looks like the content of the cell e2 and cell e3. they are simple numbers, it looks like the content of e2 is 2251.5 and the content of e3 is 2480. but that is not really true if I double click on e2, you can see that the content of that cell is just a formula, what happens with e3, double click on The content of that cell is a formula, so we see the results of the formula, but what's actually in the cell is a formula and let's talk about the type of formula we have in both cases.

These are relative cell references. What that means is when i say c3 multiplied by d3 what Excel interprets is to simply multiply whatever is in two cells to the left multiply that by whatever is in one cell to the left so notice what happens now when I use the autocomplete controller if you remember the autocomplete controller allows me to extend a pattern if there is no pattern what it does is it copies the contents of a cell so I'm going to use the autocomplete controller and I can click and drag down on the spreadsheet like I've done in the past, but I want you to see a shortcut if you have a table of basically data that's all together instead of clicking and dragging in the autocomplete controller, it's faster to just double click the autocomplete handler and it automatically extends down the page, so see Also, because I use the autocomplete handler, I don't have to keep creating formula after formula after formula.

My formula was copied and spread down the spreadsheet because I used the autocomplete handler. How did it work? The reason it worked is because of what you said a minute ago Excel is interpreting these cell references as relative, not absolute, so c3 Excel simply interprets that as two cells to the left, d3, one cell to the left, multiply the two together and you get a result, so down here when I copied that formula. using the autocomplete handler, you just adjusted those cell references, there are still two cells to the left multiplied by one cell to the left, you don't bother with c3 and d3 anymore, you moved on to c4 multiplied by d4, how about down here, double click on c7 multiplied by d7?

The reason this works is because these are relative cell references. Now, what if you wanted to calculate the amount of taxes to pay? Now I understand that's not how it's normally done, but let's pretend and say the tax rate is seven percent and I. I'm going to enter this as a .07 decimal and hit Enter. Let's use a formula now to calculate the taxes payable. I'll click here on cell f2 and in my head I'll say cell f2 and then I'll type. It is equal to Julia's taxable income in this case multiplied by the tax rate h1.

I'll press Enter on the keyboard and you can see the results. This worked great, but what happens when I use the autocomplete handler and double click to extend it down? page didn't work why didn't it work let's look at these formulas because I used relative cell references here in my original formula excel look for acell to the left and multiplies it by two cells to the right and one above, so when I copied that formula into the spreadsheet and continued that pattern. You're looking at one cell on the left, two cells on the right and one at the top and since these cells are empty it's like multiplying by zero, so I'll fix this formula by clicking on the cell. f2 and in Excel whenever I want to extend a formula, work on a formula, what I like to do is click on a cell and then instead of editing it here by double clicking on it and making some changes, in most cases it's easier and better to just select the cell and then go up here to what's called the formula bar.

This is a better, safer and easier place to edit and work with your Excel formulas. For now, these are relative cell references. I want to change the h1 reference so that it becomes an absolute cell reference. The way I do it is I put a dollar sign in front of the h and a dollar sign in front of the one and then I hit Enter on the keyboard and what those dollar signs do is force Excel every time it copies or uses this formula. Always refer exactly to column h and row one so that part of the formula, no matter how far down the spreadsheet you copy it, will remain fixed in cell h1.

Let's try it now. I'll double click on the autocomplete handler and it copies down. page, let's see if it's working, it seems like it is. If I double click on any of these formulas, I can see that they all refer to cell h1 and that is where the tax rate is held, so it is a very important concept to understand the difference. between relative cell references like this and absolute cell references like this and the cell references could be partially relative and partially absolute. I could have left the dollar sign out of the h and just kept the dollar sign in a, there are two more things you need.

The thing you should know about using formulas before moving on to the first one is that from time to time you can make a mistake in your formulas, for example if I try to divide the contents of cell e2 by zero, that is not possible, it is a error, I hit Enter on the keyboard. and I will receive an error message. Error messages often look like this with hashtags or other symbols. Sometimes you will be warned that what you are entering may be an error, for example if I do it this way I get a warning.

I want to accept your correction yes or no, so if you see things like this, okay, this is Excel trying to warn me that I have created something that is producing an error. and in most cases I will go to the formula bar to correct that error and finally it's important to know that you can change the name of a cell and also the name of a range, so for example I could click here on f2 and drag everything. down on the spreadsheet and then I could go here to the name box and I could click and I could change the name that's in that box to be something like taxes due and I'm not going to put any spaces in there.

Hit Enter on the keyboard and now that range has its own special name. If I forget the name that I gave that range, I can always click here on this drop-down arrow and it tells me the taxes due now, why would that be important? You will learn it later. but let me give you a quick preview of my formula instead of just referring to a cell like e3. I could refer to a named cell or a named range so I can type in the taxes due and then continue with my formula for the taxes due plus a tap on Enter. and one was added to each of those cells.

That's not a great example, like I said, you'll learn later in this video and other videos I've created, how you can use those named ranges in interesting ways in the next one. excel segment for beginners the complete course we are going to focus on functions and many excel users constantly confuse formulas and functions, you will even find those errors in other youtube videos but there is a definitive difference between the term function and formula in Excel and the first function that I want to teach you is addition. Let's say I want to add up all the hours worked and all the taxable income that will be paid to employees.

I could just navigate down the sheet here to the end of the data column d has the hours worked and I'll click here on d72 and I'll type equal again thinking in my head d72 equal and then I'll use my first sum function now as soon as I start Typing a word into that cell Microsoft Excel searches its functions database and tries to find the function I might be looking for and there's its sum, so Excel suggests to me that might be the one I should use. He tells me what he does. Sum all the numbers in a perfect cell range, that's exactly what I want, but you might want this if or if some product has all these different features that deal with sums and some of them are amazing.

You definitely need to check out my other tutorials on some. The product of sum if and some ifs is also great, but in this case all I want to do is add all the numbers in a range of cells so that it equals the sum and when you use a function in Excel, after writing the function, you place it in left parentheses. As soon as I do that, Excel shows me a popup with some suggestions. It's trying to help and guide me to write a good formula using this addition function, so Excel expects to have a number and then a comma and another number.

I could put in a number and then another number to add, let's say five point six and then I should put in my right parenthesis even though it's not necessary and then I could press Enter and it adds those two, but in this case I don't want to add five and six, I want to add this entire column, so how do I well describe this entire range of numbers that I want to add? If you remember, we can describe a range using the top left corner and then the colon and then the bottom right corner in this case it's all just in one column so d2 and then I'll come back down here so d2 and then the colon , so I want to add d2 to d71.

I should put a right parenthesis, but I don't. I don't have to press Enter on the keyboard. There is the total hours worked by employees. Let's see another way to do the same instead of writing the cell references. You could just say this cell and type equals sum in left parentheses and then. I could have clicked and dragged to select the entire range, all the numbers I want to add, and then go back down in my formula. You could have pressed Enter and gotten the same results now because the addition function is so common in Microsoft Excel.

I've added what's called the autosum function, so instead of typing that formula, I can select the cell under the data that I want to summarize and then I just go here to the Home tab, Home ribbon in the edit group, this symbol. means autosum, so I'll click on that and Excel will automatically figure out what I want to add from d2 to d71, hit enter and I'll do it, in many cases autosum is the quickest and best way to do it at this point, I should probably type something here . like the total colon and maybe you could click on it and go to the home tab font group and make it bold, maybe go to the alignment group and change it to be right aligned and now you could do the same formula autosum for taxable income or instead To redo the formula, I'll just click on d72 and use the autocomplete handle to scroll and adjust the formula because these are relative cell references, they don't have dollar signs when I autocomplete them to the right. the cell references were adjusted and it worked wonderfully, besides the sum function there are some other functions that are quite common and important, for example we can calculate the average, let's do it with cell d73 selected, I will write is equal to this cell is equal to the average and you can I see that I receive help similar to what I received with the sum.

Excel explains what this is. You can also tell me about these other average functions, but I'll stick with the generic average now that I've entered a function I need. left parenthesis and I will put in my range d2 to d71, hit enter on the keyboard. There is the average number of hours worked by this group of employees in the last month. I can autofill it on the right and there we have the average taxable income. now if you remember we set up a named range for the data here in taxes payable let's see how named ranges work with formulas and functions so here at f72 I'm going to tell myself that f72 equals the sum of and i' I'll put a left parenthesis, you're looking for a number.

I'm just going to type in the taxes due and then put in the right parenthesis and notice that Excel recognizes the taxes due. Oh, you want to add up all the taxes owed and excel. You know what that means because I named that range, so now when I hit Enter, it automatically summarizes everything in the tax owed range. I can do the same thing with the average, so f72 is equal to the average of the taxes due in left parentheses, I hit enter and it calculates it. Take a look at three other important and common functions in Microsoft Excel.

We're going to calculate the highest hours worked, the lowest, and then we're also going to count the number of employees, so here I'll write the highest, the lowest, and the number of employees. Alright? Am I going to figure this out if I want to know what the highest number of hours worked was for all of these employees and I also want to know the highest taxable income for all of these employees? There has to be a good way to do it. which in Excel that's easier than just scanning and looking for the highest number, luckily there's a function called max, so in my head I'll think that d74 is equal to the maximum left parenthesis and then I just need to describe the range of cells to look in i want to find the highest number, the maximum number in this range d2 to d71, I should put it in right parentheses, but I don't have to press Enter and the highest number of hours worked in this month by this group of employees is 208.

I can auto populate that on the right and now I'll go to both columns on the right and now we know the highest amount of taxable income and the highest amount of taxes owed. Let's do the same with the lowest cell. I think cell d75 is the same. from max I'm going to put min and you can see Excel is giving me hints giving me suggestions min returns the smallest number in a set of values ​​ignores logical values ​​and text so it equals min left parenthesis d2 to d71 hit enter and there is the lowest number of hours worked.

I use the autocomplete handler to extend it to these other data columns. Our last common function that I'm going to show at this point in the video is counting. I want to count the total number of employees. You could be saying: don't we already have it? Yes, we have it, it's here, but in some cases you don't have that or you want to count different parts of a spreadsheet, so it's important to know this in my head. I think d76 is the same. to count and there are different types of counts, count a count, definitely check out my other videos on those different functions, but for now just count and you'll be able to see what it does: it counts the number of cells in a range that contains numbers, so I left the parenthesis once. again d2 to d71 right parentheses hit enter and it counts 70 different cells in that range that have numbers if I wanted to I could autocomplete this but it will only give me 70 again so those are the five or six most used functions in microsoft excel if you want to learn more functions in excel watch my other videos.

I have a ton of different features that I show in those other tutorials and there will be more in the future in the next segment that we'll focus on. on how to modify the formatting in the spreadsheet, including how to format numbers and text, we will also look at how to format cells, rows and columns and a couple of tricks that will help you format more effectively, we are going to format some of these. The numbers note that it is difficult to distinguish between money, dollars, in this case, taxable income, and just regular hours worked. They all look like numbers, so let's change the format so it's obvious what we're dealing with to do this.

I'm going to click on column c, it's going to select the entire column and then here on the home tab, home ribbon in the number group, I can change the formatting right now, it's just the general formatting, which is the default, but if I click on this arrow, I can change this to Let currency now be able to clearly distinguish the difference between the data in column c and the data in column d. Now there are times when instead of using currency as a number format, you may want to use accounting. Let's see the difference when I click on accounting.

I still get the dollar sign. I still get commas and decimals for cents, but the dollar sign is separate from the numbers. I think it makes it a little easier to see the numbers without getting confused by the dollar sign, so those are two different ways. to indicate that we're dealing with money in these columns, the same as this one here, I'll change that to accounting as well. Now there are other number formats you shouldconsider, including dates and times, percentages, fractions and more. Check out my other tutorials to learn. more about different number formats what about text formats?

I could leave my text as general formatting or I could go down here and select text and in Excel by default, when you've entered text into a cell, it will be aligned to the left of the cell. so all of this text is left aligned if Excel interprets what you type as numbers loosely it will be right aligned in the cell so you can see all these numbers are right aligned these names are left aligned , but you can change some of that if you want by using the alignment options and also changing the number or text formats.

There are also a few more formats. You can click here to see what they are. You can set some custom formats. There are different time and date formats. that's worth checking out I'm going to cancel and let's look at some other ways we can format our data and especially let's look at how to format columns and rows in Excel let's say I want to format this entire first row so that everything in that row is bold, I think that's a good idea in this case because I would really like to separate that row so people can see that it's not actually part of the actual data, these are column titles or labels, so after selecting row one, I'm going to do Click the Home ribbon on the Home tab in the Fonts group.

I'll just click on this bold symbol now that the entire row is bold and if I were to go to, say, cell i1 and type something in there, you'll be able to see that. comes in bold, as does the rest of this row, other ways I can format rows include changing the alignment of cells in a row, so here on the Home tab, home ribbon in the alignment group , I can click this button to center each one. cell in row 1 within your column, if you wanted you could change the background color of the row and there are many other formatting options for rows and columns in Excel.

If you want to learn more about them, check out my many tutorials covering formatting in Excel below, I want to show you a shortcut that you can use when you're trying to format columns, rows, cells, etc. In Microsoft Excel, let's say you get a cell formatted the way you want and then decide you'd like to do that too. format other cells exactly the same way, one trick you can try is to select the cell that has the format you want and then go to the home tab, home ribbon in the clipboard group, you can click on this format painter and then click whatever you click. below will take on exactly the same format as the active cell, so I'm going to click here on james. james is now centered and bold.

If I click on freddy, it doesn't work because the format painter is now not selected if you want. being able to click multiple times and apply the same formatting, all you have to do is again select the cell that has the formatting you want and then double click on the formatting painter and now you can click, click, just continue doing click and add the new one. When formatting cells, you can also apply the format to an entire range. I'm going to undo all of that with Ctrl Z. Another formatting trick you might want to know is that you can use what's called auto formatting, but you have to add first. here in quick access toolbar to do this just click here on customize quick access toolbar button and go down to more commands and then here you can switch from popular commands to all commands and then just navigate down until you find the automatic format.

There it is, so with it selected I can click Add and now it will be added to the Quick Access Toolbar. I click OK and here it is. To use the auto format tool, all I need to do is click somewhere in my data and then come up here and click auto format. I can explore the options for different formats, if I find one I like I can just click on it and click. okay, and that formatting is added to this spreadsheet, I'm going to undo it, so those are some of the most important and most common formatting options that we have in Microsoft Excel in this next segment of Excel for beginners, the complete course at let's go To see how to create some basic charts in Excel, there are a few different ways to create charts, let's look at one of the easiest and to do this, the first thing I want to do is make sure that you can see the important data so that you can I'm going to go down here to the zoom slider and I'll click and slide it to the left and then click and drag to highlight the data that's important to me.

In this case, I don't really need the tax rate nor do I need the totals and average, etc. down here just all that data, hold down alt and hit f1 and look what Excel did. Tried my best to create a chart that makes some sense because I selected All Data Excel is trying to find a way to display it all in one chart and it's a little difficult to do, but you can see at the bottom here. I have a list of names, employee IDs and then the bars are displayed every hour. salary, hours worked, taxable income taxes payable and all of this is color coordinated, if I want, I can click on the chart and then go up here to the chart layout and I could change the type of chart, so So instead of this type of stacked column chart I could change to a clustered column, click OK, let's see how it looks and if I don't like it, I can go back to the chart layout, change the chart type and we could try a pie chart or some other graph because I selected all the data.

It's going to be very difficult to graph everything at once. It should really only graph a very specific amount of data, but I wanted you to see how to quickly add a graph based on the data in your sheet. Once you have your chart, you can go up here to the chart layout and you can add some chart elements, for example, I could add a chart title, I could put it on top of the chart if I wanted to and then I could just triple click on the text there to select it and just title this summary chart.

You could also add other chart elements like axis titles, a horizontal axis title and you could also add a main vertical axis title, like I said, this chart is too much, there's too much information in one place, but in many cases you . I will be creating a simple spreadsheet and holding down Alt and tapping F1 will produce a graph which you will find very useful. If you want to learn more about charting in Microsoft Excel, check out my many other videos that focus specifically on creating charts in Excel in this The next and final Microsoft for Beginners segment, the full course, I'll show you the printing options and the publishing options that you have in Microsoft Excel, so let's say with the data that is in this spreadsheet and with this very confusing table that I have made.

Let's say this spreadsheet is ready to be printed, distributed and published to the target audience and the first question I need to ask myself is if the data in this spreadsheet actually fits on a printed page, it may not help with that. go to the file tab and go down to print and that will give you a print preview here on the right and it looks like my data fits pretty well on this page if I scroll down I can see that page 2 is just more rows of data Page 3 is part of my summary box, so it actually worked pretty well, but if you remember I entered the word hello here in the top right corner, I'm going to go back to Excel, add that back in, and now I'm going to go to the file.

Print and now take a look at the results. I have page one, but if I scroll down, there's page two and then there's page three with Hello there and with my chart, so if I want to make sure that this column is also included with the rest. of the data, there are some changes I need to make. I need to prepare this spreadsheet for printing. One way to fix this problem is to go to the page layout tab and in the page setup group I can click on orientation and change the orientation of the spreadsheet from portrait to landscape in many cases that will solve your printing problems in Microsoft Excel, so now when I go to print files all my data fits horizontally on one sheet except my chart, if I scroll down to page two that's what it looks like. like we have page three and page 4 is part of a chart so I still have the chart problem but at least the data here fits horizontally on one page without having to go to another horizontal page on the right something else What you can try when working with the print options in Microsoft Excel is to go to the View tab and switch from the normal workbook view to the page break preview.

When you click, it will show you where your pages are, so this is page one, page two, page three and it shows you these blue dashed lines that you can click and drag, so I want to try moving the jump from page so that it is here on the far right beyond my crazy graph. Let's now go back to normal view and I'll do the file. print and let's take a look. I used to have three or four pages of data to print. Now look, I only have two pages. This is page one and the graph fits the data very well when I click this button to go to page two. there's page two, so this is the best print preview I've seen so far.

I just need to decide how many copies I want to print, let's say two. I need to make sure I have selected the correct printer. I don't actually have a printer set to print to PDF, but usually you would click here and select your actual printer from the list. There are a few settings to think about. I could print just the active sheets, which is what I've been working on. in that they are active or I could print the entire workbook, the other option is to print a selection so if I go back to the spreadsheet I can click and drag, maybe I could skip the employee id and just get this selection and then go to file print. and change from printing active sheets to printing selection and this is what it will look like now.

I think I'll go back to printing active sheets if you want, you can choose to print just page one or if you had a longer spreadsheet you could print pages. five to seven, whatever you want to do there, you can adjust the orientation if you want the page size, etc., there's a really cool option here at the bottom, custom scale, if you want, you can click on this and choose the adjustment sheet on one page. It will shrink the printout to fit on one page, so I click on that and now the entire spreadsheet only fits on one page, so that's an option, just keep in mind that if you have a lot of data, it will be small.

It will be difficult to read. Well, at this point I'm going to click Print. Now remember that I don't have a printer, so it will print to a PDF. This is a good option whether you have a printer or not, if you want to convert what you want. look here in a pdf that you can email to people, you can post it on the web, you can include it as an attachment in an email to someone. This is a very good option, so I'll click Print and it asks me where I want. To save this pdf, I'll save it to my downloads folder, give it a file name, and click save now.

If I look in my downloads folder, here it is. I can open it and I have this beautiful pdf that is hard to read, these are the basics of printing documents in Microsoft Excel and you can use Save as PDF to publish your document to the web if you want other ways to publish your document, including making Click this share button at the top right. I want to upload my document to OneDrive. Once I've done that, a copy of the document will be online and I can easily share, send and publish it. If you want to learn more about OneDrive, watch one of my Microsoft OneDrive tutorials.

I also have the ability to attach this workbook as an Excel workbook or as a PDF, so those are some good options for sharing and then also here in the File tab if I choose to save, as that's another way to save to OneDrive, but also look what I can do. here where it says Excel workbook, I can click on this arrow to change the file type. I've been working in Excel and this is an Excel workbook, but I could save it as another type of Excel workbook, for example a macro-enabled workbook. You will learn more about this as you watch more of my videos.

You can also save your workbook as an html web page, which is a cool option. You can save it as an Excel template. You can save it as a csv file, which is a powerful tool. option and once again we have an option to save it as a PDF, so it is important to be aware of this drop down menu and the ability it has to save your bookfinished in other formats, so congratulations, at this point you have completed the Excel for Complete Course for Beginners, at this point you have everything you need to know to use Excel effectively.

Yes, there is more you can learn, but you have all the basics you need to be successful if you want a copy of this workbook I have. created, look at the description below the video, if you want you can download it and just go to sheet number two and then follow my video by watching it again and doing each step one at a time and then you can compare it to what I did. on sheet 1. If you're ready to take the next step in your Excel journey, I recommend checking out my beginner's guide to Microsoft Excel;

You may have already seen it, but if you haven't, it's important that you watch that video because it's a brief overview of some of the basics that you've seen here, but also with additional new content in the future. I'll also be creating a video called Excel for intermediate users of the full course, so watch that video to further expand your learning. Also, if you're interested in Microsoft Word and PowerPoint, I'll be adding full course videos for both great tools, so I hope you'll join me on this journey and I also want you to know that I have dozens. and dozens of other Excel videos that delve into every important aspect of Microsoft Excel so you can learn everything about charts.

You can learn everything about the format painter. You can learn about many of the features of Microsoft Excel. So also watch my individual Excel video tutorials thanks for watching. I hope you found this tutorial useful. If you did, please like and subscribe. When you do, click the bell and you will be notified when I post another video if you want to support my channel consider clicking the thank you button below the video or you can support me through my Patreon account or by purchasing products from the channel and you will see More information about those options in the description below the video.