As the chilling winds beat against the trees and windows, the writing is on the wall that the fall semester is rapidly winding down. As discussed in last class, after today, very little new material will be delivered in the remaining classes. Next class will be “virtual,” which means I won’t even be in New York State next Friday, so don’t show up to class. However, I do expect you sometime during the Thanksgiving vacation to get on the Internet and view this website for a remote learning assignment. I will upload next week’s assignment (hopefully) before Thanksgiving, and it will be due December 5th — no late submissions.
Please make the time before Thanksgiving to get caught up on all of the assignments. Over Thanksgiving break I will check everyone’s post page, and update the grade spreadsheet and post it with the virtual assignment.
Today is the most difficult class of the semester — I’m trying to finish up all of the material now, before December, and the skills we’re learning today, Excel formulas and Word Mail Merge, require following a bunch of steps correctly. Even if you haven’t taken a note in this class, I strongly recommend doing so today, otherwise you will likely be lost when doing next week’s remote assignment. That being said, this class will offer you skills that, regardless of your career path, you will find beneficial in whatever job you may have or will have in the future.
The skills you will learn today will make your life easier if you ever have to publish a bunch of letters or forms to a group of people, each with slightly different information, determined by each individual’s records. A form letter, paystubs, individualized assignments — all of these tasks that can take a day to complete if you create each one individually, can be reduced to an hour’s work if you know how to use Excel formulas and Word Mail Merge. You really can make yourself invaluable to your boss if you learn these skills.
Creating a Formula in Excel
One of the best functions in Excel is its ability to use formulas to automate repetitive calculations. Today, we’ll create a time sheet that can calculate your take home pay, simply by inputting your hours each day and your pay rate.
Let’s assume you have more than one source of income. So the first thing we need to do is list the different assignments, On a blank spreadsheet, copy/paste/type the following information into cell A1, etc.
Next we will create a drop down list, to make entering daily values easier. In cell D5, type ‘Date’. In cell E5, type ‘Assignment’. In F5, type ‘Pay Rate’. Make all of the text bold, 14 pt.
To make a drop down list, follow these steps.
- Move to the cell you want the drop down list (E6).
- Click on the Data menu, and click the Data Validation button.
- In the ‘Allow’ dialogbox, click on the ‘List’ Option.
- In the ‘Source’ box, click on the spreadsheet button to the right of the box.
- Using the mouse, select the words that will be in the list, in this case, the assignment types in cells A2 to A4.
- Click the spreadsheet button again, and click on the ‘OK’ button in the dialog box.
If you click on cell D6 now, you will be able to select the specific job you did that day without having to type it in.
Now we’re going to have Excel determine what the pay scale is for the job we selected. To do so will require using the VLOOKUP function.
- Move to the cell to the right of the dropdown list, F6, and click on the Formulas menu button. Click on the first button, ‘Insert Function’, type VLOOKUP in the search box, and click on the ‘Go’ button.
- In the list below the search box, double click on VLOOKUP.
- For the Lookup_value, click on the spreadsheet button to the right, and select the cell to the left where the dropdown list is (E6).
- For the Table_array, select the names you used to create the dropdown list and the rates in the next column (A2 to B4).
- Important! Type a $ in front of every letter and number, so the array values look like this: $A$2:$B$4
- For the Col_index_num, type a 2 in the box.
- For the Range_lookup, type FALSE in the box.
- Click on the ‘OK’ button
Now, whenever you select a job assignment, Excel will automatically place the corresponding pay rate in the cell to the right. One click, and two cells of information is created.
Doing the Calculations
Now we need to input our time starting and ending work on a given day. First, let’s create a header for ‘Start Time’ in cell G5, ‘End Time’ in cell H5, ‘Hours’ in cell I5, ‘Minutes’ in cell J5 and ‘Time’ in cell K5. Give the text the same formatting as the other headers.
Let’s input some sample data to work with. Type ‘8:00 AM’ in cell G6, and ‘5:00 PM’ in cell H6. Now we need to get Excel to figure out how many hours we worked, and, more importantly, how much we’re going to get paid. Let’s start with the time calculations.
In cell I5, we’re going to calculate how many hours we worked, using an Excel formula. All Excel formulas begin with an equals sign, so Excel knows what follows isn’t text, but a mathematical formula. Type in the following formula in I6,
What this formula means is that Excel will take whatever time is typed into cell H6 (5:00 PM) and cell G6 (8:00 AM) and take the difference, in hours between the two times.
Now type the following formula into J6,
This formula is almost the same, except, you guessed it, the formula calculated the remaining minutes.
In order to calculate wages, however, the time values must be in decimal format. To do so, type the following formula into K6,
Now we know how many hours we worked, in decimal format.
Finally, we can determine our earnings, First, type ‘Earnings’ in cell M5. Then, in cell M6, calculate your earnings by multiplying Pay Rate by Time. We earned $360!
Once we have one row/record working it is very easy in Excel to copy the functions/formulas into adjacent rows/records. Highlight cells D6 through M6. On the lower right hand corner of the selected cells you should see a little black square. When you roll over it with the mouse, the cursor will turn into a plus sign. Click and drag the cursor down 15 rows, and volia!
You now have an automated time sheet.
in Rows 7-10, type in the following information, and have Excel do the calculations.
Babysitting, 5:30 PM – 9:00 PM
Bookkeeping, 12:30 PM – 4:15 PM
Tutoring, 6:30 PM – 8 PM
Babysitting, 10:30 AM – 4:45 PM
Then ‘Autosum’ to get the total earning.
Although setting up an automated payroll spreadsheet takes time, once you have it up and running, all you have to do is click on the dropdown list, enter start and finish times, and Excel does everything else. If one of your job duties is payroll, I’m sure you see the value to making a spreadsheet like this one.
What we just did helps calculate an individual employee’s paycheck. But what if you have to submit pay stubs for every employee in the company, including the withheld tax, like the list below?
Again, you could type out each paystub, or you could let Excel and Word do the work for you. Here’s how to do it.
First, copy the above table into a black Excel spreadsheet.
Note that you could calculate all of the tax withheld numbers, and the Earnings values could be calculated from a spreadsheet like the one we just made with drop down lists and VLOOKUP formulas.
Very Important!!! : Highlight all of the numbers, click on the ‘Home’ menu, and format all of the numbers as text. If you don’t do this, all of the numbers will look crazy in Word. Then, SAVE the spreadsheet.
Now we have to do the Mail Merge part. This requires a bunch of steps, so take notes so you can remember how to do this on your own.
- Open Word. Click on the ‘Mailings’ menu, and click on the ‘Start Mail Merge’ button.
- Click on the ‘Step by Step Mail Merge Wizard. A new pane will open on the right side.
- Click ‘Next’ twice. You are now on Step 3 of 6.
- Click on the ‘Browse’ link. Open up the spreadsheet you just saved. Click on the ‘OK’ button twice.
- Click on the ‘Next’ button. You are now on Step 4 of 6.
- Click on the ‘More Items’ link. Double click on all of the fields until your Word document looks like this:
- Add some formatting (spaces between the fields, Enters between the taxes, etc.) so your document looks like this:
- Click on the ‘Next’ button. You are now on Step 5 of 6.
- At the top of the right pane are ‘Recipient’ back and forward buttons. Click on them to see how each paystub is generated.
- If you wanted to print out each paystub, you would click ‘Next’ one last time, and click on ‘Print.’ But don’t print them out!!!
- Important: If you want to save the document with the field codes intact so you can look at them in the future, click back to Step 4, and save. If you save in Step 5 or 6, all of the records will be save, and you won’t be able to edit the field codes.
Create a spreadsheet like the one we did today with the following time log:
- What are her total earnings for October?
- Suppose her tutoring rate was $55, her bookkeeping rate $37.50, and her babysitting rate $22.50. What would her earnings for be for October?
- Upload the spreadsheet, with your name on it to your post page.