training review report sample

in this section and in the few other sectionscoming we are going to learn how spreadsheet software helps you analyze data and make businessdecisions. we will become familiar with the different elements of a spreadsheet, and learnyour way around the excel program. so the question is: what is microsoft excel and whatis spreadsheet software? microsoft excel is the electronic spreadsheet program that comeswith microsoft office suite. an electronic spreadsheet is an application that you useto perform numeric calculations, and to analyze and present numeric data. one advantage ofa spreadsheet over pencil and paper is the calculations are updated automatically andyou can change this without having to recalculate them. so some of the abilities and what youcan do with excel as you will see later in

these sessions are entering data accurately,recalculating your data, performing what is called what if analysis. so what if i soldtwice as many units, what would be the profit, and then how to change the apperance of theinformation via charts and use the different functions and formulas in excel. so let'sget started here. basically, like i said, microsoft excel, is part of the office suite,and to get to it you go under programs, and then you go under microsoft office, and thenyou will pick microsoft excel. as soon as you open microsoft excel, you will be presentedwith these similar windows just like you are used with microsoft word, this is the officeribbon. and i am not going to go through all these settings here and all the things here.you have the home tab with the most common

stuff, common functions within this application,then you go under insert where you can, additional components, formatting and the page layout.and then formulas. this is kind of different from what you may be used with in microsoftword, and other applications, because it deals with calculations. and then you have the datatab as well, getting data from the other systems, and other sources. and review, view and add-ins.so it is pretty much similar to using the office ribbon that is the concept at thispoint which is similar to using the office ribbon in microsoft word and other officeapplications. then what you have right below the ribbon is the function toolbar where youenter the different functions, and formulas where they are displayed and then right belowthis is where the worksheet view is. and i

will cover this in a few seconds. now noticein the bottom here, there are three different sheets that are by default as you enter orcreate a new spreadsheet in excel. think of those like pages in a notebook and you cando calculations in the first sheet and link these calculations with other items in thesecond one, third one and you can create new worksheets by simply clicking on insert worksheetcomponent right here. so notice i inserted a worksheet 4. notice you can rename the worksheet.let's say you are dealing with different months of the year and these are just some of thebasic concepts here. just right click, choose rename and then you could call this one january.so you can get the idea here adding other sheets. and as far as the number of rows andcolumns, you basically can insert a million

rows and 16 thousand columns. so what aresome of the other components here? notice that as you open any of these worksheets,by the way, this is mispelled here, notice you have these numbers and it keeps on going,all the way to 1 million, and then you have those columns, that are a, b, c, d and soon, so those can go up to 16 thousand columns. so you have the rows and the columns here.now wherever you click here, in the worksheet, that is referred to the as the active cell.and the active cell has an address. so the address for this cell what i have clickedon right now it will be where the column meets with the row. so this would be b3. this nextone would be b6. that would be f6. notice it displays it right here. so the conceptso far is that you have the rows and the columns,

and each one of them has an address. now inthese cells you can add different types of data. you can insert and notice the type datathat you can insert:general number, general type of data, no specific, a number, currency,it can be accounting format numbers, it could be a date, or a percentage fraction, or text.so basically, here you can put in text, numbers, percentages, and formulas, and we will coverformulas shortly as well. all formulas in excel start with the = sign. that is an exampleof a formula and we will get into that shortly. that is it in getting started briefly withexcel, the different components of the interface, and the different worksheets here. the actualfile when you save it, that is referred to a spreadsheet made up of the different workbooks.next we will go into the actual entering of

the data, doing some of the basic calculations welcome back. in this section i am going tocover some of the basic functions in using excel. those will be some of the functionssuch as adding a bunch of numbers, finding the maximum number, the average, countinga bunch of numbers, and subtracting and multiplying and so on. some of these basic arithmeticconcepts that you have learned, come in very handy in here in excel, and we will use themconstantly as you plan on using excel. so let's get started. basically, you would say,where are the functions, how many of them are there. if you click here on the tab formulas,and insert functions, you will see a listing of all the functions, if you choose the dropdown here. and there are hundreds and hundreds

of these functions. we will not cover anywhereclose to all all them, but just some of the basic stuff here in excel because excel isendless in what you can learn and do with it. for example the functions here that weare talking about in using earlier, it is listed somewhere in here. and there it is,for example, sum it adds all the numbers in a range. you can add those numbers, for example,number 1, comma, number 2, comma and number three but if you have a long range then youwould use the colon to determine the starting point and the end point and everthing in between.so that is pretty much the stuff. the idea here is that there are hundreds of them andto understand them better you can click on under the insert function and thenalso you click on help on this function, and

then microsoft will bring up the help menuor the help guides on how to use this function with examples and things of that nature. keepin mind as well that all of these functions work the same accroos any other types of spreadsheetsoftware out there. there it gives you an example. so let's go back, now lets say thatwe want to find the maximum number, the highest number in this range. remember that all formulasstart with the = sign, so we type = and then we want to get the maximum number, so youwould say, how do i know the function for maximum is. you can click on either insertfunction here on formulas or just take a guess and just do max, for example. and notice excelgives you a listing of close matches to it. so now we will choose max, and it tells youthat it returns the largest value. so we choose

max here and then we want the range, to bethese guys over here in this range and then hit enter. so we could have typed c5:c13 andnotice the highest number here is 500. if i change one of these numbers to 788 and hitenter, notice this will be updated automatically.all of these references as well to it. for example,this number here got changed, and that and also the total here. now if i wanted to findthe minimum, the lowest number, then you do the same thing. so you do = and then you tryfor example minimum, min, and notice the first one recommended by microsoft is min. so actuallyyou have to remember them, it works the same in openoffice and other applications as well.so we choose min, notice it is doing the parenthesis and we select the range we want to find thelowest number in this range and hit enter,

and the lowest number is number 13. now youwould say, what is the average here, so for the average you would do the = sign, and thenyou type average or you start typing part of the average. notice it is the whole word.it gives you, the average or the arithmetic mean of its arguments of the numbers thatare included in the range, for example this would be the range right here. and we hitenter, and that is the average. now what about counting them? so you do the = sign, count.by the way, for any of those instead of doing them manually you could go to the home tab,so let's go back here, under the home tab, and there is this section, right here underthe editing option, and we choose the drop down here, and then you choose count numbers,that is another function. but i would like

to have you use this manually first to understandthe concept behind it because these menus could change. so if we click on count numbershere, notice it is going to try to count these numbers right here. but we are not interestedin these, we are interested in those from 5:13, c5 through : c13. so we select the rangeand we hit enter. notice there are 9 numbers included in there. so instead of you countingthese manually, you count them using a formula. so now the question is how can i have these,instead of me spending all afernoon entering these formulas for the other ranges you couldhave hundreds of those, how can i replicate these numbers so that they actually, how cani replicate those formulas so that what i have in here it does the same on the othercolumns? so the way to do that if you remember

before when we did this we just dragged itto the right. so we could simply do this first one. notice this formula up here, we coulddrag it to the right, and it works. now by the way, anytime you drag this stuff to theright, you need to verify that the correct stuff is being calculated in the formulas.so if you click on the formula bar up here notice it selects, it tells you in blue whatis being selected. so that is one of the ways. so you can clickon it, drag it to the right. now another way to do this easier is to select the whole rangehere of a bunch of stuff and then click on the bottom right and drag it to the right.and now notice it does it for all the records in the selected area, or in that range thatwe are picking. so what is happening is as

you drag it to the right notice the firstone is c5:c13. when we click on the next one, it is d5 through d13, so it is shifting oneover in a sequence very similar to the days of the week that we tried earlier and themonths of the year and so on. so that is relative references. so that issome of the basic functions here that we learned so far as to add a bunch of numbers, to findthe total of a bunch of numbers or the sum of them, to find the highest number minimumnumber, the lowest number, the average, and counting. it is just five functions so far.now a lot of times in business you also do other mathematical calculations such as addition,subtracting numbers, multiplying a bunch of numbers, and then dividing. so, addition inexcel is represented usually by using the

sum function, or the + sign. so you couldhave a couple numbers, and you can use the + sign between these two references. i wouldrecommend that you use the sum function for that just like we did over here.subtraction is usually just the minus sign on the keyboard. multiplication is the asteriskshift and and number 8 in a pc. and then dividing (division) is by using a slash. so in ourcase here we have another example. let's say that we have these employees. this is theirpay, they have to pay different deductions: deduction number 1, deductions number 2. sonow we want to calculate the total of those deductions. so we have deduction one and deduction2 and that will give us the total. what we do here is that we press the equal= sign, and then we type sum and then just

pick these two cells. hit enter. and it is$289. now in this case you would say why would inot do the plus + sign here. the reason you would not want to do the plus sign here isbecause at some point you might insert a new column and that new column that you insertis going to be part of your new range. so now what you do is, notice the warning here.it says"the formula in this cell refers to a range that has additional numbers to it.so it is saying, hey you might have forgotten to include this one over here. but we arenot interested in that because we are doing something else at this point. we just wantto have those deductions. so now if you want to add all these deductions together, allyou do is to use the autofill feature. actually,

that is not adding them together it is replicatingthat same formula into the other cells. so again i drag it down. and notice thosehave been represented. those guys that are making 9 thousand dollars a month he has topay 693 dollars in deductions. now if we wanted to figure out what his net pay is what wewould do here is that we would need to basically take his gross pay minus the deductions. sowhat we do in this case is equal sign and then we choose the payment minus the deductionsand then we hit enter. so again what i did was equal sign, the first number, first valuetotal pay minus the deductions. then we can replicate this using the autofill feature.so the net pay for bill is instead of getting nine thousand dollars he is actually gettingonly eight thousand three hundred dollars.

so what we would do is that we want to figureout what is the annual income for any of those employees. so what we do is, let's say wewant to figure out the net pay. usually it is the gross pay that you would calculatebut let's say for now we want to include the annual one. so the annual would be the monthtimes twelve. so the way you put that in a formula is the equal sign this number (netpay) times 12. usually it is not recommended that you encode in a formula actual numbers,however this is for the months of the year and it is a round number and most likely notgoing to change but usually keep in mind that you to reference something similar to to annualand then in another cell here you say this is going to be 12. so what you would do inthis case is instead of of 12, you would change

that to be this value l20 and i still getthe same value. and if i hit enter, i still get the same value. so now let's drag thisdown using the autofill feature and i will actually explain this later why this did notwork. so let's go back and i will explain this on the absolute references in a littlebit. so now it works. the reason is because we are using an absolute reference and arereferring to this value here and i will explain that shortly. so the other thing that you can do here islet's go back to the values the way they work. instead of using the reference we used just12 for simplicity and in not being confused. so here is g12 the annual net income g23*12and we drag this down and that is the net

income for everyone. now let's get rid ofthese. and let's say that we wanted to divide this per week. so pay per week. so that wecan learn about division. remember this is the total for the annual, so we want to dividethat for each week. a year has 52 weeks, so what we do here is, we press the equal signthe number, divided, so the annual income divided by 52 and then we hit enter. and thesame, we can drag this down and then the last one, bill ellen since he is making 90 thousanddollars, or a 100 thousand dollars a year he is making 2 thousand dollars per month,actually per week here. so that is the division. now keep in mind for multiplication like wedid here, and division and subtraction, notice there is no function that you need to includebecause for these other guys here, for these

other references here, remember we had toinclude the actual function, the max and so on. for subtraction, multiplication and dividingyou do not need to use those. again, keep in mind that subtraction is the minus sign,multiplication the *, and dividing is the slash. welcome back to learning about excel and someof the concepts in excel. in this session i am going cover some of thebasic components and doing some of the basic calculations in excel. what i have here isa predefined worksheet, a spreadsheet with a bunch of worksheets. so notice i have onetab called basic calculations, data filtering, and all types of other stuff. so what i havein these cells is that i have different types

of data, the data here is basically eithertext, or numbers, and it is just a matter of typing something here in these cells. ok,so at this point, what we are going to do is we are going to figure out how to add thosenumbers up using a formula. the benefit of using excel is the use of formulas. so hereis one thing to keep in mind as you are dealing with spreadsheets. it does not matter whatapplication you are using, they all use the same concept here. so usually all formulasin excel start with the equal sign. that is how the computer knows that what you are enteringin there is a formula a calculation of some sort. then there is the function and we willlearn about the functions shortly. for example sum, it would be the = sign, open parethesisand then the starting point for your range,

it would be b1 all the way to b10 and thenclose parenthesis. so you have the equal sign, the function, and then the starting pointin your range, colon and then the end point in your range. this is a key concept and itis very important that we remember this in our study of excel.so now let's go back to excel here. so let's say that we want to add all these numbers.there are a couple ways of doing this. one of them is by using the autosum function righthere, but i want to show you how you can calculate and enter this calculation by using the formula.so like we said earlier, it starts with the equal sign, then we start with the startingpoint, actually the next thing that we add here is sum, and then open parenthesis andthen we type c5 colon c13. so from the start

of c5 and end on c13. then we close parenthesisand then we hit enter. so the total of all of these numbers is 1386 at this point. noticethat is the formula that we are using. so that was one way to enter the formula. nowanother way to enter the formula is by using the equal sign, the function, and then openparenthesis and then just drag the mouse over the range in order to select the numbers fromthe starting point to the end and then let the mouse go. then hit enter. and notice weget the same values. now one other cool thing here with excel is that, and this is wherethe power of excel comes in, that you can change one of these values from one hundredto two hundred and notice what will happen as soon as i hit enter. it will change thetotal automatically.

so the next thing what you can do here isthat of course you can use the automated calculation from the toolbar under the editing tab andthen in there for example we want to add all of these numbers, we can click on autosumor just click on the drop down here and then choose sum and now excel will try to figureout as to what you want to calculate. however, you have to be very careful as to what theprogram is selecting because sometimes you may not want just that way but you may wanta different range. so that is why i wanted to show you the other method first, the manualway. so hit enter here, and this will be calculated coming to 1972. the manual way which is whati actually recommend to get you used to this is =, function, sum and then select the range.even the one that is blank make sure that

you select it, because at some point you mightadd a number in there and then it will give you the total here once we hit enter. if iadded something in that blank one, because i had it in the range, it's going to calculatecorrectly. so that is a couple ways of entering the formulas to add a bunch of numbers up.so again, =, sum, notice if you do not put the parenthesis, nothing happens. so =, sumopen parenthesis and select the range. so you would say, why can't i use this drop downfrom here. the reason for that is because in the next version of excel, that might notbe available or in open offfice that might not be available or in google docs, if youare using the sheets in google docs, that may not be available the same way on the buttonthere, so you need to know the logical way

of how to do this. the other reason is ifi want to put the total for this column back here, if i click on the autosum, the computerdoes not know it is not picking the right range here. notice it is picking these numbers.so what you can do is you can select the range, with the mouse and then hit enter notice wehave now the total back here. so the idea is that you can reference other cells. againyou would not put each number manually here like 300 and 500, always put the referencefor that number. c5 the address because you can change the numbers at any point and thenthese values will be updated automatically .now now at this point let's learn another trick here. so let's say, let's say that iwanted to put the totals for these other numbers

here. there is a feature called autofill here,so instead of me having to enter the formulas for each one of these, what you can do ishold the mouse on the bottom right and when it changes to a little plus sign, not thethick plus sign, this kind, then you drag it to the right and that will give you thetotals appropriately and the way it works is that you can use this from left to rightor from top and down. for example if i wanted to get the total here, let's say i have thefirst formula here, in this case the formula would be =, let's say i want to get the totalfor training for the month, i would have the = sign, the function sum, and then the startingrange, and the end range. so c3 through e5. hit enter so it is 800. instead of me enteringthese values or these formulas, in every one

of these cells, you may have 100 cells or200 or more, what you can do is you can utilize the autofill feature. so all you do is youclick on the cell that has the formula already in it and then you drag it down after youclick on the bottom right when the mouse changes to just a little plus sign and then drag itdown and then let it go. so notice all those numbers now add to 832. so that is the concepthere is using the autofill feature. the autofill feature comes in very handy for anything thatis in a sequence. for example, if you want, let's say to type the days of the week andyou want to fill these days in a sequence, instead of you having to type them manuallywhat you can do is just type the first day of the week and then drag it down. i guessthis should have been sunday the first day

of the week so let's do sunday and then dragit down. if you wanted to do months in a year, so you type one and then drag it down. andyou get the idea here. you can do it for numbers, you can do it for, actually for numbers youhave to be cautious here if you drag this down by default it it will leave it with thecurrent number. notice there is a drop down here under the autofill options and you canchoose auto fill and then choose fill series. because it is seeing these numbers and itis giving you a chance to leave them alone the way they are or just replicate or go tothe next number up. you can choose fill series and then it will do what you were probablylooking for do do to start with. you can also use the autofill feature by typinglet's say things in a sequence, for example

1 and 4. let's say that you want to have 3in between two numbers. what you do is that you select that range, for example 1 and 4those two cells and then you drag this down together. notice i had selected both of themand now notice it is 1, 4, 7, 10, 13 and so on. so we have 3 in-between those two numbersthat we started with. and it gives us a sequence of it. so that is basically the autofill feature.so let me get rid of those quickly here. and let's get back to some of the formatttingor some of different components here on the formatting of the text.as far as the formatting of the text, it is actually pretty easy, just like in microsoftword, you will select the text and make it larger or smaller, just like in word. youcan also select a whole bunch of cells, notice

here, notice here you can select to the left,and it is going to indent the whole range here, and go ahead and change so that it mergesall those cells and it centers that component. so the concept that we are learning here isactually merge and center. now that we merged and centered it, you canmake this font larger and customize it the way you want it. notice there are also stylesthat you can use for those cells under the formatting here just like in word there aredifferent styles and you can use for example heading 1, title or something like that. sodepending how you want it and what you want the style here. as far as formatting of thenumbers, you can select these numbers, the range here, and then format them in some littlequick access toolbar type of thing here regarding

numbers or different commonly used tools oryou can click on the dropdown and let's say you want to format that as currency. so youclick on currency here and it is going to put in the dollar signs with the decimal pointsas well. as far as further formatting here, keep in mind that like i said earlier thereis also the style formatting that would be the easiest to utilize and you can pick anyof those or you can format things manually by you selecting a range of cells and thenchanging here the background color to what you may be interested in.i will show you more a little more on conditional formatting and that kind of stuff shortlyhere in one of these videos. welcome back. in this session we are goingto learn a little bit about references and

the different types of references. there are4 types of references. most of the references that you use out there are what is calledthe relative references. if you notice here, we have sum(c5:c13) and if we drag this over,it replicated it, it went from d5 to d13. and then e5 to e13 and so on. so it is movingfrom one spot to the other in a relative way. so if you remember when we did the annualnet income earlier we had a problem with one of the references like the number of monthshere via a formula. so for example, if we go here and we use the autofill feature, noticeto calculate the annual income for these employees, notice it works, it gives us the correct values.however, if we go and delete this and like i mentioned earlier, it is best to use referencesfor calculating stuff. so let's say we want

to get the net income. the net pay for example,for a month, for the first employee here is this value. and if we wanted to calculateit based on a predefined value, for example the number of months for annual, so let'ssay 12 here, that times 12 months income times 12 and enter, notice the first calculationis correct. the reason is because we just entered it manually. we could do the samething here for the second one: equal, the monthly times the number and that will work.however, if you go and drag this down that will not work. and it will give you zeros.notice what is happening on the one that it is giving you a zero. it is multiplying g25times this blank number here. so it is going one down and one down from our original place.then we go to the next one. notice it's going

further down, 3 down from the original placebecause it used to start here, one two three so we start here, one two three. an now weare in the fourth spot. so that is why we are getting the zeros. what we are doing hereis that these references, they are called relative references. so for example: thisone right here its reference or address is l20 and that is not locked. if you were tomake it an absolute reference meaning, stay put and locked down what that will do is orthe way you can do this as an absolute reference referering to the number of months is by puttingdollar signs here. put $ sign in front of it of the l for the column and the row andthen hit enter here and it will the same number. however, when we use the autofill featurenotice these calculations will be correct.

you would say why would i want to use a pointof reference here. well, the idea here is that you would say well if i want to hirethis guy for 6 months or these guys for 6 months then what would be their annual salary?so you could simply change this value to 6 and then all these numbers will be updatedautomatically. so that is the advantage. instead of you having to go to the formula and tinkeringwith the number and doing it manually for each one of them, you can have a single pointof reference we change that to 11 months or whatever and that is updated automatically.and that is the beauty of excel. here technically we can put sum or total and you put the sumof all of these and then at any point you can know let's say you want to hire them for18 months what your expenses are going to

be. here is another example if we click inthis other tab and notice let's say you have a budget of 20 thousand dollars. let's saythere is going to be a decrease in your budget by 5% on each item. now you have trainingfor example and training was 4 thousand dollars. now the 5% you have to give up. calculatehow much you have to give up on the difference is here that you have to give up. so whatyou do is that you put here the equal sign, you take this number training times the percentagethat you have to give up and hit enter. so you have to give up 200 dollars. now noticewhat happens if i drag this down i get very large numbers. on the second cell here ond7 i do not get anything but on the other ones you will notice in a moment that youwill get very large values and by the way,

anytime you see these number signs that meansthat the column is not wide enough. all you have to do is go here to the top and moveit to the right or another trick here is to doublcliick between the columns and it willmake it exactly fit the widest point. the question is why on travel i had only 2 thousdanddollars but now on travel i have 10 million dollars difference. the reason for that isbecause notice what is being calculated. it is calculating c11 right here * c9. becausewhat is happening is that as it is moving down when we did the autofill as the autofillwas moving down it shifted one down automatically. so if we go here, c7*c5. so 100 x 0 is 0.the next one it got 8 thousand * 4 thousand is 32 million and so on.