Microsoft Excel 01 Payroll Part 1 - How to enter data and create formulas

welcome to assignment number one for Microsoft Excel in 2013 we're going to use Excel to create a spreadsheet for our business we're going to launch the program and start a blank workbook now since this is the first assignment that we're going to do with Microsoft Excel we need to go through some of the things that you see in any spreadsheet first of all the spreadsheet is designed in organized according to cells just like in the game battleship where you have columns that are letters and rows that are numbers you can identify cells such as this one by such as g6 to enter in information into a cell you simply click it and start typing and so I'm going to type employee payroll press enter and you notice that this cell even though it extends into column B really it is only in a1 now let's use Excel as a payroll we're going to keep track of the hours that our employees worked this week and we'll keep track of their wages so first of all we need to put some column headings in so I'm going to put in the title last name and then first name and then I'm pressing tab between these keys so that it moves the selection moves to the right hourly wage and then I'm going to put in the date let's say January 1st and press Enter over here I'm going to say that this is the hours worked and this is their total pay so I'll just put the word pay now you notice you can expand a column by clicking between the column headers like between D and E now you can see the whole word the next thing you need to do is you need to invent some names so you get to come up with about 20 names maybe 15 for your employees I'll type them in now now you can see that I've invented names last names and first names and now I'm going to give them an hourly wage each person makes about fifteen dollars or so so I'll type in some numbers here you notice when you type in numbers they are right justified when you type in the person's name in your spreadsheet that they are all left justified that's just a convention that Microsoft Excel uses so that you can identify letters versus numbers now one of the items that you will most commonly use in Excel is formatting these are all hourly wages I'm going to select this range with my mouse by clicking and dragging the first square is actually highlighted even though it's white the other ones are grey but what I want to do is change them into a currency so I'm going to click on the dollar sign up here and you notice that they all become dollar signs they all are the hourly wage for each employee now let's invent a number for each of the employees for how long they worked the average work week for a full-time person is about 40 hours so I'll create wages or I'm sorry I'll create the number of hours that each person worked in this column after giving each employee a number of hours we're going to start working with formulas now over here on this line the square in e4 I'm going to create a calculated number a calculated cell if a person gets $15.90 an hour and they work for 40 hours how much do they get paid well fortunately the calculations in Excel are quite easy to do on my keyboard I'm going to press the equal sign and you notice up here in this area called the formula bar we have an equal sign as well as the equal sign in the cell now I'm going to click on the cell that has the wage in it 1590 when I click there the letter C 4 is entered into the formula bar C 4 is the wage $15.90 now I want to multiply 1590 so I'm going to press the shift in the eighth key which is the multiply sign and then I'm going to click the 40 the dot the number of hours that this person worked so my formula is equals to C 4 times the number that's in cell d4 when I press ENTER it'll tell me that this person gets six hundred and thirty six dollars if you go and change some of these numbers such as if we added an hour here and I type 41 and press Enter the calculation is automatically updated when you double click on a cell that has a formula you will see that the colors correspond to the cells that you clicked so c4 is in blue which is this number and d4 is in red I'm going to press ENTER again and it shows the numbers again Microsoft Excel has a nice feature where you can copy and paste formulas if I right click on this cell and choose copy and then I click on the next cell and choose paste it'll automatically calculate the next line you can also highlight a range and choose right-click and paste it will fill down where all of those numbers are why is this one locally look like railroad tracks or a bunch of pound signs it's because the value is actually too large to be held in this in the cell so I need to make the column a little bit wider and now it becomes visible another way to fill down if you want to you can go to the corner of a cell where there's little green dot and if you click exactly on the corner and drag your mouse down it will also fill down the values in that column and so now I've quickly calculated the pay for every single employee one more thing we're going to do with this formula is we're going to add a few lines at the bottom I want to know who is the maximum press enter who is the minimum press enter what is the average pay and what is the total pay these numbers max min average in total are just labels but over here in this column I'm going to put in a corresponding formula Microsoft Excel has hundreds of formulas that are predefined here's one of them you type in the equal sign and then you type the word M a X you notice that these automatically are drop-down menus that show that this is one of the formulas that Excel knows about after typing max I use the open parenthesis which is the shift key and 9 and I'm going to drag my mouse across a range of cells till they come to the very top release the mouse button and press shift + 0 to do a closed parenthesis so what this will do it will give me the maximum of all of these cells the maximum person is getting $45 an hour and so you can see in this line here that Trent man is making 45 an hour now I'd like to know who the minimum is so I'm going to do the same process type equals sign mi n open parentheses and drag my mouse across our range and I will tell it what the minimum is after I press shift and 0 enter and the lowly person in my office is getting six dollars and ninety cents that looks like it's Paul Smith now what's the average employee well I can type in equals and then a V and you see there is a bunch of formulas for average I just want this most simple one I'm going to choose average I'm going to select the range closed parenthesis and press Enter the average employee makes $16.48 okay let's take this range here of three different formulas highlight it right click it with a mouse copy it now I want to click in the next column over right click and paste and now you see that we have the maximum the minimum and the average for the number of hours that are being worked now you notice that these values are listed as dollars they're actually not dollars there are a number of hours that the persons are working so I want to change these formattings back to general numbers up here in the ribbon where you will find the number formatting area there's a small arrow that if you click it will bring up a dialog box that shows all different types of formatting for numbers I'm going to select general and click OK now why does this number have so many decimal places in it it's because it's because we are computing an average it would be nice if we would just round these off - maybe the 10th the tenths place or the hundreds place so let's go to these zeros here and we're going to decrease the decimals that we're rounding to so to the nearest tenth is good enough for us so now we have the number of hours Mac some of minimum and average for our employees here in column D for column e we could also find the men the maximum the minimum and the average as well so let's highlight this group I'm going to select this bottom right corner and drag to the right and it fills to the right now and shows me that these are the maximum dollars that someone made the minimum dollars and the average once more these are dollars figures these are not hours so let's change these to the dollars format so I'm going to click up here on the dollar sign and now we have the average the maximum in the minimum in the salary for this set for this week so now we've created the maximum the minimum and the average for each of these columns column C D and E we haven't done anything yet with a total for the total let's find out what the total number of hours worked was and let's find out what the total number of wages that we've paid is so for the word sum we type equals S um and that will give us the total let's do Shift + 9 and then I'm going to select a range here I'm going to select all of the hours worked close parentheses and enter and you'll see that there were 695 hours worked in my shop now I'm going to fill this to the right and shows that I made a lot of more paid a lot of money for my wages this week I'm going to change this to a dollar sign format and it shows now eleven thousand five hundred and thirty-two dollars the last thing we should do is put our name at the top so maybe C one is a good place type in your name press ENTER save the spreadsheet print it and you're done with the assignment number one for Excel

Channel: Programming w/ Professor Sluiter

Views: 1,429,175

Keywords: Office, 2013, High, School, Computer, Classroom, Microsoft Excel (Software), How-to (Website Category), Microsoft Office (Software), Microsoft Corporation (Venture Funded Company)

Id: dEGVT1_yHKg

Length: 11min 49sec (709 seconds)

Published: Fri Jul 11 2014