 in this presentation we're going to enter the information from the payroll register to the earnings record for october which is our third month of payroll our first month of quarter four so we're going to scroll down this is the data we want now to do this we're going to put this data from this register over i'm scrolling to the right to the earnings record now remember what the difference is the earnings record is in order first by employee then by date to give the give the information by employee scrolling back to the left whereas the payroll register is in order by payroll period then by employee giving the data in essence by period rather than by employee so that's going to be our goal we just need to flip that information over so to do that we're going to first i want to freeze the pain so you may have frozen pains already from the last time so you may never have unfrozen the pain but uh just to practice this in case you have we're going to go we're going to go up top to a three we want to be on cell a four view tab windows group frozen pains select and unfreeze pains then i'm going to scroll down to the current period we want and it's going to be important to make sure that we pick up the information from the current period because it's common to you know get them mixed up because they all look very similar so in order to do that we're going to highlight or select should say select it's the proper terminology the current pay period and then once selected let go right click and let's make that green selecting green let's try to make it a lighter green this time it might be easier to see the numbers i'll make it this green okay so now we're going to enter that data into our records here so it's all formatted for us already we're going to scroll up to october so and we're just going to copy that data over so i'm going to do that with a formula in ab six we're just going to say equals i'm holding down the left arrow and i'm just looking for our green data so i'm going down green data where's the green data there it is anthony more we're picking up this m in b19 and enter so there it is scrolling up there's our m now i'm just going to copy that across and you'll just have relative references and copy it all across so i'm going to do that with a right click and copy and then i'm going to select all the cells in blue next to it and then we're going to right click on it and paste it just the formulas we don't want to paste total because it'll it'll see all the formulas or the formats are all not the same so we want to go to the formulas only and there we have that now we're just going to do the same thing for the rest and again why are we doing this again because we have to tell our employees one how much they earned this pay period and how much we took from them and and therefore what their net check is for this pay period and the cumulative year to date totals so this helps us to generate that information okay so then we're going to go down to the next employee and do the same thing so then we're going to be here and this is the employee cindy so we're an ab 13 i'm just gonna say equals go i'm just holding down the left arrow to find our green data on the payroll register green data for cindy there it is in b uh b20 and enter i'm going to scroll right scroll up and then we're just going to copy and paste the formula so we're going to right click on that m copy it select all the data to the right of it right click on it paste it formulas only so now we have her cumulative data up to the current time period then jill jack jill jackson so we're in ab 20 equals we're going to scroll left we're going to find our green data for jill jackson here uh is her s for single enter and then we're just going to copy and paste that data right click copied select the cell to the right highlight all the way over let go right click paste formulas only all right we get to do this one more time and then we have to stop because that's all we got so we're going to be over here for judy jones in ab 27 equals scroll into the left looking for the green data for judy jones scrolling up there it is green data judy so there's the m and enter and then we're just going to copy and paste that across once again right click and copy we're going to select the cells to the right of it and right click and paste formulas only okay so we're going to make these a little bit larger because judy's income is just too high for our the width of our cells there okay so now we can check our totals and just double check that everything's good so to do the lazy check instead of checking each number we can check total earnings and the net pay which should cap recap mainly everything to to you know should be a good check so we're going to go check that over from our earnings records over to our payroll register so again i'm going to make these cells a little bit larger so that we can see everything it's nice to see stuff so here's our total earnings 145 51 and our net check is uh this 83 uh 676 so is that what we had here all right now we could do a formula as well this might make it a little bit easier to check which means i want this number i'm going to say this number minus the net check over here which should if it's zero they're the same right so minus this number and then if it's zero it should be the same so that that formula is at 34 minus t 41 and if we did that for the total earnings which should be the same if the net checks the same you would think total earnings would be the same equals total earnings minus total earnings over here and that should be zero and then we say check zero check and and if you want to make them just for an excel that i'm going to make this a little wider if just for an excel tip if you want to make it a little fancier to to make the worksheet you know uh give you an indication that would be maybe a little easier to read you could go to these format i've been using these a bit if you go to the style uh tools and you go down and say i want my check field to um have rules and if it's equal to zero then we want it to be green so i'll say if it's equal to zero we want it to be green and then we can we can say our conditional formatting we can say if it's greater than let's say one we want it to be red and then one more time conditional formatting if it's less than one uh what happened there or less than negative one i should say negative one then we want it to be red and that'll make it pop a little bit if if especially if someone else is using it you can say hey it's turned red that's not good right so if they had two it would turn red if it was negative one it would turn uh red it should turn oh negative negative two let's say it turns red and then if it's zero then it should be good and then if you want to copy the formatting you could go to the um paint brush and use that same here so this should do the same thing so if it was two or negative two red so that's one way to format your um your check your check format cells and then the last thing we'll do to set up for the next time is to is to um ungreen this area so to do that i'm going to use the paint brush so i'm going to paint brush september i'm going to select the whole september area here and we're going to go back in the home tab paint brush it and then scroll back down and then highlight this whole area and remember the paint brush is not paint brushing just the color it's it's the whole format so you so you want to make sure you don't just do one cell or else it'll change all the format just not just the color all right so that is that