"Excel for Beginners - The Complete Course" - Transcript | YTScribe

[Music] this is excel for beginners the complete course it doesn't matter how little experience you have in excel i promise you that as you watch this video and follow along that you will learn everything you need to know to get started using excel effectively don't 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're going to look at how to create workbooks in excel and how to save them and also we'll learn the

basics of what i call the anatomy of a spreadsheet and also the layout in microsoft excel let's get started so whenever you start up excel it should take you first to a screen similar to this one you may see a list of recently used spreadsheets but you'll also have a way to create a new blank workbook and i'm going to click here just to show that there will often also be templates that you can click to open up and use you can also search and there's a button here to more templates but i'm just going

to click blank workbook to open up a brand new completely blank workbook in microsoft excel and before we create anything in this workbook let's talk about the anatomy of a spreadsheet when you're working in microsoft excel you will always have at least one sheet you can see here in the lower left it says sheet1 but it's possible to have multiple sheets and all of those sheets collectively together are called a workbook right now my workbook just has the 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 gets selected we also have rows in excel this is row 3 this is row 9 this is row 10. so every column has a column letter and every row has a row number now the intersection of a column and a row is what we call a cell so for example this cell here is the intersection of column e and row 10. and an excel workbook can contain over 17 billion cells when you click on a cell that

becomes the active cell now one of the most powerful things about the 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 that's how you say it f8 what cell is this this is cell l4 and this is cell b7 now because of this fact that you can describe every single cell in microsoft excel there's a lot of exciting possibilities that come about because of that and you'll see that as you begin using excel more the next part of the

anatomy of a spreadsheet is range a range is a collection of cells that are generally grouped together so i've clicked and dragged to select a group of cells in excel this is a range and we can also describe every range in microsoft excel once again using the column letters and the row numbers the way you do this is you start in the upper left corner of the range and you describe that cell so d4 through and then you go to the lower right corner and describe that cell j14 so the description of this range its

reference basically is d4 through j14 and in excel the way you indicate through is with a colon so d4 through j14 this is very important and will become more and more useful and important as you keep using excel so that is the anatomy of a spreadsheet we have columns we have rows we have cells the cell that you've selected is the active cell and we have ranges and all of this is done on a sheet or spreadsheet or it's also called worksheet you can add more worksheets by clicking this plus sign now i have two

sheets now i have three sheets and the collection of all of these sheets together is what we call a workbook now in addition to the anatomy of the spreadsheet it's also important to understand the layout that we have to work with in microsoft excel as you use excel you'll notice that there are tabs across the top of the screen generally you'll start on the home tab but we also have the insert tab page layout tab data tab etc whenever you click 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 the formulas tab i get the formulas ribbon if i click the view tab the ribbon completely changes now i have the view ribbon and each ribbon is divided up into groups you can see the groups are separated by lines so i have a charts group i have a tours group a comments group a tables group etc now not all of the options can fit in such a small group for example my charts group is not big enough to hold all of the options so some of

the groups have what i call a launch button or sometimes it's called a dialog launcher but i'll just call it a launch button not all of the groups have these launch buttons but if you click on a launch button it'll open up with even more options than could normally fit in the space provided on the ribbon in addition to the tabs the ribbons and the groups and the launch buttons we also have some other layout features that you really need to know over here on the right we have a scroll bar that you can use

to move down your spreadsheet and up the spreadsheet we also have a horizontal scroll bar underneath that we have a zoom slider if i slide this to the right i zoom in on the spreadsheet if i slide it to the left i zoom out on the spreadsheet doing this does not change the data at all it just zooms in or out i have some view buttons here in the lower right corner i can go to page break preview or the page layout view but most excel users spend most of their time in normal view here

in the upper left we have what's called the name box whenever you click on a cell you can look in the name box to see the description of that cell or the name for that cell and later you'll learn that this name box can do even more for you here at the top of my excel layout i have the quick access toolbar and i have customized this yours may look different but this gives me quick access to some of the most commonly used features in microsoft excel my save button an undo button autosum and more

here's my title for this spreadsheet over here i have my close button it looks like an x if i click that the spreadsheet will close and then finally here i have a very special bar called the formula bar in many cases this is where you'll go to enter formulas into microsoft excel so now that you know about the anatomy of a spreadsheet and also the layout in microsoft excel you're completely ready to begin learning to use excel at this point i'm going to click file and save but because this is my first time saving this

document it's having me do a save as i need to decide where to save this excel workbook so i'm going to click on browse and i'm just going to save it in my documents and i'll just write a name for it excel for beginners complete course and save it's 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've been asked to create a payroll spreadsheet for the employees here in column a i'd like to have the

employee id numbers in column b the employee names and then some information about their wages the hours worked how much they are owed etc and anytime in excel when you want to enter data into a cell it's important to first select that cell there's a saying that i heard once that i use over and over which is select to effect if i want to affect this spreadsheet if i want to affect the data in the spreadsheet first i have to select something so i want to effect a1 so i'll select it just by clicking on

it once i can now affect it by typing so let's say the employee id number for employee 1 is simply 1. i type the number 1 and that data appears in the cell but it's not truly entered in the cell until i tap enter or return on the keyboard now the data is inside that cell notice when i tapped enter the active cell moved down one and that's great because that's where i want to type the next number two enter three enter four enter and i could just continue to do this down the spreadsheet but

that is kind of a repetitive task and excel has a built-in feature to make it so it's not so repetitive there's something called the fill handle or the auto fill handle in microsoft excel and the way it works is that it's been set up to identify patterns and then to 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 a1

through a4 the pattern should be clear now i'm going to zoom in a little bit so you can see this a little better but if you look in the lower right corner of this range that i've selected you'll see a little green square that's what we call the auto fill handle or the fill handle if i put my mouse pointer directly on that little green square notice that the mouse pointer becomes a black plus sign now if i click and hold the click i can drag down the spreadsheet i'm going to go all the way

to 70 and then i can release the mouse button and look what it does excel automatically extended the pattern that i showed it next i'm going to click on cell b1 because i want to affect that cell and i'll type the name of the first employee and then i'll tap enter and again the active cell moves down the spreadsheet i can type the next employee name and i'm just going to use first names so that this goes faster and the third name now you'll notice i misspelled one of the names this 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 on a cell and clicking in a cell when you click on a cell once the whole cell is selected so if i click on jams and i try to fix this so that it says james the first letter i type will erase whatever the contents of that cell already is so that's what happens when you click on a cell and type it erases everything that's already there well what if i've

got five or six words in this cell and only one word is misspelled i don't want to have to retype all of it so instead of clicking on the cell i'm going to double click on the cell and that gets me inside the cell now you can see my flashing cursor and i can use the arrow keys to move that cursor to exactly the right place and then i can type and fix the misspelled word then i can tap enter on the keyboard and that correction is entered into the cell now as you're entering data

into excel every time you tap enter the active cell should move down but what if you need to move up you can hold the shift key and tap enter and that should move you up the spreadsheet give me a few seconds to finish entering names and then i'll resume now that i've finished my list of names i'm going to click here on cell b1 because i need to show you 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 key so if i tap tab i move to the right now i'm on cell c1 and i can type in the hourly wage of julia if i need to move to the left you can probably guess what keys i need to press shift tab moves me to the left so enter is down shift enter is up tab is to the right shift tab is to the left so i'm going to tab over to cell d1 and i'll put in the number of hours julia has worked in this month 160. i'll tab over the next

thing we need to learn about entering cell values in excel is how to clear and copy cell contents so we've already looked at how to edit them by double clicking but what if you just need to copy the contents of one cell to another cell you can click on a cell or even a range if you prefer and then hold the ctrl key and tap c to copy now there are other ways to do this you could go to the home tab and you could click this button here to copy the contents of cell c1

in this case it's also possible to right click copy but in most cases simply using ctrl c to copy and ctrl v to paste is going to be your fastest way to copy paste so i could just keep pasting in the number 15 in each of these cells but remember we have a tool called the auto fill handle and it's great to avoid the repetitive entering of data so now with just cell c5 selected what is the pattern that the autofill handle is going to see the only pattern i'm showing it is one number if

i click on the autofill handle and pull down the spreadsheet all the way down to the bottom it's going to extend that pattern of just the number 15 and it basically copies that same number all the way down the page so that's a shortcut that we can use but what if this number isn't accurate for all of these employees maybe amelia is making more than 15 dollars an hour i could click on that cell and just overwrite it with another number tap enter and that's a very fast way to do it but there are times

when you need to not only delete the information that's there by either overwriting it or tapping the delete key or backspace key on the keyboard but sometimes you need to completely clear everything that's in that cell not just the data but maybe also the formatting and other information you can do that a couple of different ways one is to right click on the cell and choose clear contents but one of my favorite ways is to select the cell or range of cells and then go up here to the home tab on the home ribbon in

the editing group we have this button and if you click the arrow next to the button you can clear all or just clear the formats contents hyperlinks etc i'll click clear all and everything is completely wiped clean out of that range i'll hold ctrl and tap z to undo that but it's important that you know about that feature now as i'm building this spreadsheet i'm realizing it's going to be hard for me to remember exactly what each of these columns of data represents 15 what 16 what 160 what i should have put in column titles

or headings so let's look at how to insert new rows and columns into excel if i need another blank row above row number one what can i do well all i have to do is right click on the 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 and choose insert and i get a column to the left of column a i can also insert columns in between 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 rows i'm going to undo all of that to get back to this point where i just have a new blank row above my data and i'll click here on cell a1 and i'll call this employee id i'll use tab to move over to cell b1 and i'll type name i'll use tab to go over to c1 and i'll type hourly wage and i'll tap tab to move over to d1 and i'll type hours worked i'll tab over to e1 and type taxable income at

this point i'll tap enter on the keyboard now i understand a lot better what all of the data in these columns is about now you probably noticed that not all of my data in row number one actually fits inside the columns for example in c1 hourly wage doesn't really fit in the space i have provided for it so let's look at how to fix that i could click and hold the click between the letter c and the letter d column c and d and then i could drag that column to make it so the text

fits and i could do the same with column a column d column e but let me show you a faster way if i click and drag across from a all the way to e actually clicking on the column letters and then if i double click between any two of these column letters let's say between b and c i'll double click what happens is all of the columns are resized to the perfect width so that the data that's inside those columns will fit that looks a lot better now as i'm entering data in excel from time

to time i may need a little help with that for example let's say that the standard hourly wage increases from fifteen dollars an hour to fifteen dollars and fifty cents an hour how could i quickly update all of this information one way to do that 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'll find find and select you may see the text or you may just have a magnifying glass if you click on the arrow next to that

notice that there's an option for find and there's also replace i'm going to select replace and this lets me find what let's say 15 and replace it with 15.5 and i'll select replace all all done we made 70 replacements i click ok and close you'll notice that all of the 15s have been turned into 15.5 now there is one problem with that notice employee id number 15 and so i can just update that manually so that is a great time saver if you want to keep your hands on the keyboard more instead of using the

mouse to go over here and click on find and replace you could hold ctrl on the keyboard and tap h and that brings up the find and replace option generally speaking anytime you can keep your hands on the keyboard and not use the mouse you'll be able to work more efficiently in excel one other trick for entering cell values and data into microsoft excel that you'll definitely want to know is how to use the autocorrect features so if i go here to file and choose more i can go to options and here in my excel

options i could go down here to proofing and here you'll see autocorrect options change how excel corrects and formats text as you type if i click this button it lets me adjust some of these settings so for example right now if i type a day it will automatically capitalize the first letter of that day and there's some other settings here that you can change but down here you'll notice that there's a replace blank with blank and there's already some examples here of common misspelled words and how they'll be automatically corrected so let's say i need

to type the name of my youtube channel quite often instead of typing the beautiful name technology for teachers and students over and over and over what i could do is i could just type t4 tas and then i'll tab over and this will make it so that 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 click ok let's try it out i'll click ok again so now if i click on a cell and type t4 tas tap enter

it's automatically auto corrected 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 i've now updated some of these hourly wages and i've put in all of the hours worked for each employee one last thing i want to show you about entering values and managing the data that you put into excel is i want to show you how to move the contents of cells we've already looked at how to copy paste by clicking on a cell

or range ctrl c to copy ctrl v to paste you could also do a cut paste ctrl x to cut ctrl v to paste and that essentially moves the data from one cell to another but another way to do the same thing is just to click on a cell and then put your mouse on the very edge of that cell any of the edges you'll see that the mouse pointer becomes cross with arrows coming out the ends at that point you can click and hold the click and then drag the contents of that cell anywhere

you want it to go and then release 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 the click and then drag the data where i want it to be release the mouse button and the data has been moved again ctrl z to put it back with that we're now ready for the third segment of excel for beginners complete course in this

segment we're going to focus on formulas i would like to calculate the total taxable income for each employee and for that i need to multiply each employee's wage by how many hours they worked so i could do that in my head or i could use the calculator and then just put in the information here but that is exactly one of the things that excel does best excel is a spreadsheet tool yes but it's also a calculator it has built in pretty much all of the calculator functions and operators that you would need so here on

cell e2 i'm going to create my first formula in excel whenever you're creating a formula you need to start by typing equals when i first started using excel this was confusing to me until i thought about algebra in my algebra 1 class that i took i remember learning about variables and there would be formulas like x equals and then it would be y plus 10 divided by 3 or whatever that's basically what we're doing here so whenever i type equals before i type it i think e2 and then i type equals so cell e2 equals

whatever comes next and here i could just type in 13.9 and then i could put in multiplied by or times and in excel we use an asterisk for 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 in the numbers you can use plus signs minus signs asterisks for multiply forward slash for divide and then just tap enter and that formula will be executed but there's another way to do the

same thing and it's a better way again i need to start by clicking on the cell and then typing equals but this time instead of typing in the numbers i'm going to type in the cell references if you remember in excel we can describe every cell in this spreadsheet we can name it basically and remember we do have this name box in the upper left corner to help us with that so equals and i'm going to name or describe this cell so that would be c2 so i'll just type in c2 asterisk for multiplied by

d2 and i'll type in d2 now you'll notice as i typed in those cell references excel highlighted them in different colors so i can see exactly what i'm about to do now i'll tap enter on the keyboard and i get the same result but this time it's a little better the reason why it's better is because this formula now is dynamic if i change the contents of one of these cells let's say it's a mistake julia doesn't really have a wage of 13.9 it's more like 14.25 i can tap enter and look at the taxable

income it's going to change so this is a superior way to create a formula in excel there is one other method that i want you to be aware of i could type equals and then instead of typing the cell references c3 asterisk d3 i could just click on the cell so c3 asterisk and then click on d3 tap enter and my formula produces results now it looks like the contents of cell e2 and cell e3 are simple numbers it looks like the contents of e2 is 2251.5 and the contents of e3 2480. but that's not

actually true if i double click on e2 you can see that the contents of that cell is just a formula what about e3 double-click the contents of that cell is a formula so we see the results of the formula but what actually is in the cell is a formula and let's talk about the type of formula that we have in both cases these are relative cell references what that means is when i say c3 multiplied by d3 what excel is interpreting that as being is just to multiply whatever is in two cells to the left

multiply that by whatever is in one cell to the left so watch what happens now when i use the auto fill handle if you remember the autofill handle lets me 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 autofill handle and i could click and drag all the way down the spreadsheet like i have in the past but i want you to see a shortcut if you have a table basically of data that's all together instead of clicking and dragging

on the autofill handle it is faster just to double click on the autofill handle and it automatically extends down the page so look at that because i use the autofill handle i don't have to keep creating formula after formula after formula my formula was copied and extended down the spreadsheet because i used the autofill handle now how come that worked the reason it worked is because of what i said a minute ago excel is interpreting these cell references as being relative not absolute so c3 excel just interprets that as two cells to the left d3

one cell to the left multiply the two together you get a result so down here when i copied that formula down using the autofill handle it just adjusted those cell references it's still two cells to the left multiplied by one cell to the left it's no longer bothering with c3 and d3 it's moved on to c4 times d4 what about down here double click c7 multiplied by d7 the reason this works is because these are relative cell references now what if i wanted to calculate the amount of taxes to be paid now i understand this

isn't how it's normally done but let's pretend and let's say the tax rate is seven percent and i'm going to enter this as a decimal .07 and i tap enter let's use a formula now to calculate the taxes to be paid i'll click here on cell f2 and in my head i'll say to myself cell f2 and then i type equals the taxable income of julia in this case multiplied by the tax rate h1 i'll tap enter on the keyboard and you can see the results this worked beautifully but what happens when i use the

autofill handle and double-click to extend that down the page it 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 is looking one cell to the left and multiplying it by two cells to the right and one above so when i copied that formula down the spreadsheet it continued that pattern it's looking one cell to the left two cells to the right and one up and because these cells are empty it's like multiplying by zero so i'm going to fix this formula by

clicking on cell f2 and in excel anytime 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 it's easier and better in most cases to just select the cell and then go up here to what's called the formula bar this is a safer easier better place to edit and work with your excel formulas so right now these are relative cell references i want to change the h1 reference to become an

absolute cell reference the way i do that is by putting a dollar sign in front of the h and a dollar sign in front of the one and then tap enter on the keyboard and what those dollar signs do is they force excel whenever this formula is copied or used to always refer exactly to column h and to row one so that part of the formula no matter how far down the spreadsheet i copy it it will remain fixed on cell h1 let's try it now i'll double click on the autofill handle it copies down

the page let's see if it's working it looks like it is if i double click on any of these formulas you can see they're all referring back to cell h1 and that's where the tax rate is kept so that's a very important concept to understand the difference between relative cell references like this one and absolute cell references like this one and cell references could be partially relative and partially absolute i could have left the dollar sign off the h and just kept the dollar sign on the one there are two more things you need to

know about using formulas before we move on the first is that from time to time you may make a mistake in your formulas for example if i tried dividing the contents of cell e2 by zero that's not possible it's an error i tap enter on the keyboard and i'll get an error message error messages look something like this often with hashtags or other symbols sometimes you'll be warned that what you're entering may be an error for example if i do it this way i get a warning do i want to accept their correction yes or

no and so if you see things like this that's okay this is excel trying to warn me that i've created something that is producing an error and in most cases i'll go to the formula bar to fix 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 all the way down 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 owed and i'm not going to put in any spaces there i'll tap enter on the keyboard and now that range has its own special name if i forget what i named that range i can always click here on this drop down arrow and it tells me taxes owed now why would that be important you'll learn that later but let me give you a quick preview in my formula instead of referring just to a cell like e3 i could refer to a named cell or a named range

so i could type in taxes owed and then continue my formula so taxes owed plus one tap enter and it's added one to each of those cells now that's not a great example like i said you'll learn later later on in this video and in other videos i've created how you can use those named ranges in some exciting ways in this next segment of excel for beginners the complete course we're going to focus on functions and many users of excel constantly confuse formulas and functions you'll even find those mistakes in other videos on youtube but

there's a definite difference between the term function and formula in excel and the first function that i want to teach you is sum let's say i want to add up all of the hours worked and all of the taxable income to be paid to the employees i could just browse down the sheet here to the bottom of the data column d has the hours worked and i'll click here in d72 and type equals again thinking in my head d72 equals and then i'll use my first function sum now as soon as i start typing a

word in that cell microsoft excel searches its database of functions and tries to find the function that i might be looking for and there it is it's sum so excel is suggesting to me that that might be the one to use it tells me what it does it adds all of the numbers in a range of cells perfect that's exactly what i want but it's possible that i might want this some if or some ifs some product there's all of these different functions that deal with sums and some of these are amazing you definitely need

to watch my other tutorials on some if and some ifs sum product is also great but in this case all i want to do is add up all the numbers in a range of cells so equals sum and when you use a function in excel after typing the function you put in a left parenthesis as soon as i do that excel gives me a pop-up with some suggestions it's trying to help me and guide me in writing a good formula using this sum function so excel is expecting to have a number and then a comma

and another number so i could put in a number and then another number to be added together let's say five comma six and then i should put in my right parenthesis although it's not necessary and then i could tap enter and it adds those two together but in this case i don't wanna add five and six i want to add this entire column so how do i describe this entire range of numbers that i want to add up well if you remember we can describe a range by using the top left corner and then the

colon and then the lower right corner in this case it's all just in one column so d2 and then i'll go back down here so d2 and then the colon so through i want to sum d2 through d71 i should put in a right parenthesis but i don't have to i'll tap enter on the keyboard there's the grand total hours worked by the employees let's look at another way to do the same thing instead of typing the cell references i could just say this cell and type equals sum left parenthesis and then i could have

clicked and dragged to select the entire range all of the numbers that i want to add up and then back down in my formula i could have tapped enter and gotten the same results now because the sum function is so common in microsoft excel they've added what's called the autosum function so instead of even typing that formula at all i can just select the cell beneath the data that i want to sum up and then i simply go here to the home tab home ribbon in the editing group this symbol stands for autosum so i'll

click that and excel automatically figured out what i want to add up d2 through d71 tap enter and it's done so in many cases autosum is the fastest best way to do that at this point i probably should type something here like totals colon and maybe i could click on it and go to the home tab font group and make it bold maybe i go to the alignment group and change it to be aligned right and now i could do the same formula autosum for taxable income or instead of redoing the formula i'll just click

on d72 and i'll use the autofill handle to scoot that over and the formula adjusted because these are relative cell references they don't have dollar signs when i auto filled it over to the right those cell references adjusted and it worked beautifully in addition to the sum function there are some other functions that are pretty common and important for example we can calculate the average let's do that with cell d73 selected i'll type equals this cell equals average and you can see that i do get similar helps as i did with sum excel explains what

this is it also can tell me about these other average functions but i'll stick with the generic average now that i've put in a function i need to put the left parenthesis and i'll put in my range d2 through d71 tap enter on the keyboard there is the average number of hours worked by this group of employees in the last month i can autofill that to 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 to be paid let's look

at how named ranges work with formulas and functions so here on f72 i'm going to say to myself f72 equals the sum of and i'll put in a left parenthesis it's looking for a number i'm just going to type in taxes owed and then i'll put in my right parenthesis and notice excel recognizes taxes owed oh you want to add up all of the taxes owed and excel knows what that means because i named that range so now when i tap enter it just automatically sums up everything in the taxes owed range i can do

the same thing with average so f72 equals average left parenthesis taxes owed tap enter and it figures it out let's look at three other important and common functions in microsoft excel we're going to calculate the highest hours worked the lowest and then also we're going to count the number of employees so here i'll type highest lowest and number of employees okay how am i going to figure this out if i want to know what the highest amount of hours worked was out of all of these employees and i also want to know the highest taxable

income of all of these employees there's got to be a good way to do that in excel that's easier than just scanning and looking for the highest number fortunately there is there's a function called max so in my head i'll think d74 equals max left parenthesis and then i just need to describe the range of cells to look in i want to find the highest number the max number in this range d2 through d71 i should put in my right parenthesis but i don't have to tap enter and the highest number of hours worked in

this month by this group of employees is 208. i can autofill that over to the right and i'll just go to both columns now to the right and now we know the highest taxable income amount and the highest amount of taxes due let's do the same with lowest i think to myself cell d75 equals instead of 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 it ignores logical values and text so equals min left parenthesis d2 through

d71 tap enter and there's the lowest amount of hours worked i use the autofill handle to extend it over to these other columns of data our last common function that i'm going to show at this point in the video is count i want to count the total number of employees now you might be saying don't we already have that yes we do it's right 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 equal

to count and there are different types of counts count a countif countifs definitely watch my other videos on those different functions but for now just count and you can see what it does it counts the number of cells in a range that contain numbers so left parenthesis once again d2 through d71 right parenthesis tap enter and it counts up 70 different cells in that range that have numbers if i wanted to i could autofill this over but it's just going to give me 70 again so those are the five or six most commonly used functions

in microsoft excel if you want to learn more functions in excel check out my other videos i have lots and lots of different functions that i show in those other tutorials and there's more to come in the future in this next segment we're going to focus on how to modify the formatting in the spreadsheet including how to format numbers and text we'll also look at formatting cells rows and columns and a couple of tricks that will help you do your formatting more effectively we're going to format some of these numbers notice that it's hard to

tell the difference between money dollars in this case taxable income and just regular hours worked they all just look like numbers so let's change the formatting 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 general formatting that's the default but if i click on this arrow i can change this to be currency now you can clearly tell the difference between

the data in column c and the data in column d now there are times when instead of using currency as the number format you might want to use accounting let's look at the difference when i click on accounting i still get the dollar sign i still get commas and decimals for the cents but the dollar sign is separated from the numbers i think it just 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

same with this one here i'll switch that also to accounting now there are other number formats that you should look into including dates and times percentages fractions and more watch my other tutorials to learn more about the different number formats what about text formats i could just leave my text as general format or i could go down here and select text and in excel by default when you have text entered in a cell it will align to the left of the cell so all of this text is aligned to the left if excel interprets what

you type as numbers generally speaking it will align to the right in the cell so you can see all of these numbers are aligned to the right these names are aligned to the left but you can change some of that if you want using the alignment options and also by changing the number or text formats there are also some more formats you can click here to see what those are you can set up some custom formats there's different time and date formats that are worth checking out i'm going to cancel and let's look at some

other ways that 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 bolded i think that's a good idea in this case because i'd really like to set apart that row so that people can tell it's not really part of the actual data these are column titles or labels so having selected row one i'm gonna click on the home tab home ribbon in the font group i'll just click on this bold

symbol now that entire row is bolded and if i were to go to let's say cell i1 and if i type something there you can see that it comes in in bold formatted just like the rest of this row other ways that i could format rows include changing the alignment of the cells in a row so here on the home tab home ribbon in the alignment group i can click this button to center each cell in row 1 within its column if i wanted to i could change the background color for the row and there

are many other format options for rows and columns in excel if you'd like to learn more about those please watch my many tutorials that cover formatting in excel next i want to show you a shortcut you can use when you're trying to format columns rows cells etc in microsoft excel let's say you get a cell formatted just the way you want and then later you decide you would like to also format other cells in exactly the same way one trick that you can try is select the cell that has the formatting you want and then

go to the home tab home ribbon in the clipboard group you can click this format painter with that clicked whatever you click on next will take on the same exact formatting as the active cell so i'm going to click here on james james is now centered and bolded now if i click on freddy it doesn't work because the format painter now is deselected if you want to be 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 format painter and now you can click click click just continue to click and add the new formatting to the cells you can also apply the formatting to a whole range i'm going to undo all of that with ctrl z one other formatting trick that you might want to know is you can use what's called auto format but first you need to add it here to the quick access toolbar to do this just click up here on the 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 browse down until you find auto 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 go up here and click auto format i can browse through the options for the different formats if i find one that i like i can just click on it click ok and that format

is added to this spreadsheet i'm going to undo that 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 we're going to look at how to create some basic charts in excel there are a few different ways to create charts let's look at one of the very easiest and to do this the first thing i want to do is make sure i can see the important data so i'm going to go down here to the zoom

slider and i'll click and slide that to the left and then i'm going to click and drag to highlight the data that's important to me in this case i don't really need the tax rate i also don't need the totals and the average etc here below just all of that data there hold the alt key and tap f1 and look what excel did it did its very best to create a chart that makes some sense because i selected all of the data excel is trying to figure out a way to show all of it in

one chart and it's kind of hard to do this but you can see across the bottom here i have a list of names employee ids and then the bars show hourly wage hours worked taxable income taxes to be paid and this is all color coordinated if i want to i can click on the chart and then go up here to chart design and i could change the chart type so instead of this stacked column chart type i could switch to a clustered column click ok let's see how that looks and if i don't like that

i can go back to chart design change chart type and we could try a pie chart or some other chart because i selected all of the data this is going to be very difficult to chart all of it all at once i really should chart only a very specific amount of data but i wanted you to see how to quickly add a chart based on the data in your sheet once you have your chart you can go up here to chart design and you can add some chart elements for example i could add a chart

title i could put it above the chart if i want to and then i could just triple click on the text there to select it and i'll just title this summary chart i could also add other chart elements like axis titles a horizontal axis title and i could also add a primary vertical axis title like i said this chart is too much it's too much information in one place but in many cases you'll be creating a simple spreadsheet and holding alt and tapping f1 will produce a chart that's very useful to you if you'd like

to learn more about charts in microsoft excel please watch my many other videos that focus specifically on creating charts in excel in this next and final segment of microsoft for beginners the complete course i will show you the print options and the publishing options that you have in microsoft excel so let's say with the data that's in this spreadsheet and with this very confusing chart that i've made let's say this spreadsheet is ready to be printed to be handed out and published for its intended audience and the first question that i need to ask myself

is will the data in this spreadsheet actually fit on a printed page it may not to help with that you can go to the file tab and go down to print and that will give you a print preview here at 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 chart so that actually worked out pretty well but if you remember i had entered the word hello here in the

upper right i'm gonna go back into excel add that back in and now i'll go to file print and now take a look at the results i have page one but if i go down there's page two and then there's page three with hello sitting there and with my chart so if i want to make sure that this column also is included with the rest of the data there's some changes i need to make i need to prepare this spreadsheet to be printed one way i could fix this is to go to the page layout

tab and in the page setup group i could click on orientation and switch 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 file print look all of my data fits horizontally on one sheet except for my chart if i go down to page two that's what it looks like we've got page three and page 4 is part of a chart so i still have the problem of the chart but at least the data here is fitting horizontally on

a page without having to go to another horizontal page to the right another thing you can try when working with printing options in microsoft excel is you can go to the view tab and switch from normal workbook view to page break preview when you click that it will show you where your pages are so this is page one page two page three and it gives you these blue dashed lines that you can click on and drag so i want to try to move the page break to be here to the far right side beyond my

crazy chart let's switch back now to the normal view and i'll do file print and let's take a look i used to have three pages or four pages worth of data to print now look i only have two pages this is page one and the chart fits very nicely with the data when i click this button to go to page two there's page two so this is the best print preview that i've seen so far at this point i just need to decide how many copies i want to print let's say two i need to

make sure i've selected the correct printer i don't actually have a printer set up so it's going to print to pdf but generally you would click here and select your actual printer from the list there are some settings to think about i could print only the active sheets so sheets that i've been working on that are active or you could print the entire workbook the other option is to print a selection so if i go back to the spreadsheet i could click and drag i could leave out employee id maybe and just get this selection

and then go to file print and switch from print active sheets to print selection and that's what it will look like now i think i'll go back to print active sheets if you want you can choose to print only page one or if i had a longer spreadsheet i could print pages five through seven whatever you want to do there you can adjust the orientation if you want the page size etc there is a very interesting option here at the bottom custom scaling if you want you can click on this and you could choose fit

sheet on one page it will shrink the print out so it fits on one page so i click that and now the entire spreadsheet fits only on one page so that is an option just keep in mind if you have a lot of data it's going to be tiny it'll be hard to read okay at this point i'm going to click print now remember i have no printer so it's going to be printing to a pdf and this is a good option whether you have a printer or not if you want to turn what you

see here into a pdf that you could email to people you could publish it to the web you could include it as an attachment in an email to someone this is a really good option so i'll click print and it asks me where do i want to save this pdf i'll save it to my downloads folder and i'll give it a file name and click save now if i look in my downloads folder here it is i can open it up and i have this beautiful pdf that's tough to read so those are the basics

of printing documents in microsoft excel and you can use the save as pdf to publish your document to the web if you'd like other ways you can publish your document include clicking this share button in the upper right i might 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 it and send it and publish it if you want to learn about onedrive please watch one of my tutorials about microsoft onedrive we also have the ability to attach this workbook as an

excel workbook or as a pdf so those are some good share options and then also here on the file tab if i choose 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 this arrow to change the kind of file that this is 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'll learn more about that as you watch more of my videos

you could also save your workbook as an html web page which is an interesting option you could save it as an excel template you could save it as a csv file which is a powerful option and once again we do have an option to save it as a pdf so it's important to be aware of this drop down and the ability that you have to save your finished workbook in other formats so congratulations at this point you have completed the excel for beginners complete course at this point you have everything that you need to know

to use excel effectively yes there's more that you could learn but you have all of the basics that you need to be successful if you want a copy of this workbook that i've created look in the description below the video if you want you could download it and just go to sheet number two and then follow along with my video watching it again and doing each step one at a time and then you could compare it to what i did on sheet 1. if you're ready to take your next step in your excel journey i

recommend that you next watch my beginner's guide to microsoft excel you may have already seen that but if you haven't it's important to watch that video because it is a nice short overview of some of the basics that you've seen here but also with some additional new content in the future i also will create a video called excel for intermediate users the full course so watch for that video to extend your learning even further and if you're interested in microsoft word and powerpoint i will be adding full course videos for both of those great tools

as well so i look forward to you joining me on this journey and then also i want you to know that i have dozens and dozens of other excel videos that are deep dives into each of the important aspects of microsoft excel so you can learn all about charts you can learn all about the format painter you can learn about many of the functions in microsoft excel so please also watch my individual excel video tutorials thanks for watching i hope you found this tutorial to be helpful if you did please like follow and subscribe and

when you do click the bell and you'll be notified when i post another video if you'd like to support my channel consider clicking the thanks button below the video or you can support me through my patreon account or by buying channel merch and you'll see more information about those options in the description below the video