Excel Intermediate

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

MICROSOFT EXCEL 2003 – INTERMEDIATE HANDOUT **To effectively participate in this course, you should have first taken Microsoft Excel - Intro, Parts 1 & 2, or be familiar with the basics of Excel. This handout will give you general information and hands-on exercises covering Aligning text, Adding, Moving and Deleting Sheets, IF Formulas, External Reference Formulas, Naming Ranges, Creating Subtotals, Wrapping Text, Conditional Formatting, and Absolute References. Open the Excel File ‘Excel-PayrollInterClass.xls’. Be sure ‘Sheet1’ is selected.

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com Aligning text Excel will allow you to align cell contents in any direction. Follow the instructions below to align the contents of cell H4 vertically. 1. Select the cell H4. 2. From the Format menu, choose Cells. 3. From the Format Cells dialog box, click on the Alignment tab. 4. In the Orientation box, click on the red diamond next to the word ‘Text’, and drag it to the top diamond. (The Degrees box will say 90). Click OK. Your text will be displayed vertically instead of horizontally (See the example on the next page). This… Should look similar to this… 5. Click OK. Nami ng sheet s You may find that it’s easie r to remember the contents of a multiple sheet workbook if you give each sheet a new name, rather than accepting the Excel defaults – Sheet1, Sheet2, Sheet3, etc. 1. Double-click on the tab labeled Sheet1. The tab should now be highlighted. Type ByWeek and press Enter. The sheet has now been renamed. Adding Sheets Excel workbooks show three Sheets by default, but can contain many more. For those times when more are needed follow these instructions. 1. Click on the Sheet3 tab. 2. From the Insert menu, choose Worksheet, and click OK. A new sheet will appear to the left of Sheet 3. You can insert a new sheet anywhere; it will be inserted to the left of the active sheet. 3. Rename this sheet "Linking". Moving a sheet For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com You may find that rearranging sheets will help you organize your data. 1. Click and hold the left mouse button on the new "Linking" sheet. When the ‘Page Icon’ appears, drag the sheet to a spot between ByWeek and Sheet2. (A small, black triangle just above the sheet tabs indicates where the sheet will be inserted).

Aligning Text 1. Click the sheet tab labeled ByWeek. Select rows 4, 15, 26, & 37. (Select multiple rows by clicking on row 4, and while holding the Control key, select the other rows.) 2. From the Format menu, choose Cells. 3. From the Format Cells dialog box, click on the Alignment tab. 4. In the Degrees box, type 45. 5. Click OK. Note: You may resize the columns by selecting all then clicking between two of the columns (between A &B, etc.) Entering Data 1. On the sheet labeled ByWeek, enter the following information into the appropriate columns for the week February 28th. (B38 – C43)

Hours 42 40 39 43 35 41

Hourly Rate $15.00 $12.50 $11.75 $14.25 $16.00 $13.00

Using an IF Statement Excel’s IF function can make decisions based on whether a test condition is true or false. The “IF function” is useful when you want to display one result based on a true statement, and another result if the statement is false. An IF statement is written like the following: =IF (what you want tested, what to do if the result if true, what to do if the result if false) Commas separate each statement. Do not use spaces after the commas within the parenthesis. Use an IF statement to calculate the OT Hours and OT Rate for the week of February 28th. 1. In Cell D38, enter the IF statement: =IF(B38>40,B38-40,0) The result should be 2. For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com 2. In Cell E38, enter the IF statement: =IF(B38>40,C38*1.5,0) The result should be $22.50. 3. Select the cells D38 and E38. Use the fill handle to copy the formulas down through row 43. Using Other Formulas 1. For the week of February 28th, enter a formula to determine the Regular Pay, OT Pay, and Weekly Total. Remember the order of operations! …() then * / then +In cell F38, enter =(B38-D38)*C38 Result=$600 In cell G38, enter =D38*E38 Result=$45 In cell H38, enter =F38+G38 Result=$645 2. Copy the formulas down through row 43. Using External Reference Formulas If you would like to have information from one sheet included in calculations on another sheet, you can use an external reference formula. An external reference formula is a formula containing references to cells not located in the current sheet. When the information referenced is changed, the result of the formula is also changed. 1. Click the sheet tab labeled Linking. Here you will create external reference formulas to show weekly totals. 2. In cell A1 type Weekly Totals. 3. In cell A2 type Week of Feb 7. 4. Resize Columns A and B to fit their contents. 5. Click on the cell where you would like your result to show, cell B2. 6. In that cell type =SUM( be sure to include the left parenthesis. 7. Select the ByWeek sheet tab and the range that you would like to link, H5:H10. Press Enter. 8. Click on B2 again and take a moment to look at the formula in the formula bar. =SUM(ByWeek!H5:H10) This formula means that the information displayed in that cell is coming from the sheet named ByWeek, cells H5-H10. Note that the sheet name is followed by the exclamation point and then followed by the cell reference. 9. Continue for the 14th, the 21st, and the 28th. Naming Ranges A range is a rectangular group of cells. It can be one cell, a row, a column or several columns and rows. Naming a Range is useful if you have a large spreadsheet that extends beyond the visible area of the screen. It is a quick and easy way to get to information that would require time-consuming scrolling. You can also use range names in formulas. Note: When assigning a range name you may not use spaces, a dash, slashes or the dollar sign. These characters have special meaning in Excel. Try an underscore, or use capital letters to indicate a new word

To name a range: 1. Select Sheet3. 2. Select a range of cells that you would like to name, H6-H29. For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com 3. Click inside the Name Box located on the left side of the Formula Bar. 4. Type the text that you would like to name the cell or range of cells, MonthTotal with no space between ‘Month’ and ‘Total’. Press Enter.

Using the Range Name in a Formula: 1. Click on cell B3 and type Total. 2. Click cell C3 and type =sum( (or use the sum icon) 3. Next choose Insert on the Menu Bar then choose Name. 4. Next choose Paste and select MonthTotal. 5. Click OK and then press Enter. If MonthTotal is not in the list to paste, start over with #1of this step and make sure you do not put a space in the name. Sorting Data On Sheet3, sort the data in alphabetical order by First Name. 1. Click on cell A6. 2. From the Data menu, choose Sort. Sort by Name, in ascending order. Be sure the radio button for Header Row is selected. 3. Click OK. Creating Subtotals For subtotals to work correctly, you need to organize the data into labeled columns with no blank rows, and you also need to sort the data into the groups to be subtotaled. Sort the data according to what you want to subtotal. 1. Select cell A6. 2. Choose Data, Subtotals. 3. Select how to group the data for the subtotals by selecting the Name heading for the column in the At each change in: drop down list. The function is Sum. 4. Select data that you want calculated by selecting the Month Total check box in the Add Subtotal to: list box.

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

5. Click OK. Notice how the subtotals are automatically added to the worksheet. Click Between the 1, 2, and 3 on the left to see different views of the totals. 6. To remove subtotals, select Data, Subtotals, then click the Remove All button. Conditional Formatting Conditional formatting allows you to format cells depending on whether or not specific conditions in the cells are met. Use Conditional Formatting to make the cells in Column B red if the hours are greater than 40. 1. Select the cell or range of cells that you want to apply the conditional format to, B6-B29. 2. Choose Format, Conditional Formatting to display the Conditional Formatting dialog box. 3. Select the Cell Value Is option in the drop-down list at the very left of the dialog box. **The only difference between the cell or formula option is that when the formula option is used, Excel evaluates the formula to determine if the conditional formatting will be applied. The formula must be TRUE/FALSE.** 4. Select greater than from the drop down list of conditional operators located second from the left in the dialog box. 5. Enter 40 in the last dialog box. 6. Click the Format… button in the Conditional Formatting dialog box to set the formatting that you would like the computer to show if the condition is TRUE. On the Font tab, choose Red from the Color drop-down box. Click OK. Notice the preview area. 7. Click OK to accept the conditional formatting. Notice all hours greater than 40 now appear in red.

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com Conditional Formatting dialog box:

Close the ‘Payroll for Intermediate Class’ file – do not save your changes. Open the file named ‘Excel–InterExercise2’. We will use this file to practice using the MAX, MIN, AVERAGE and TODAY functions. 1. Insert 5 blank rows at the top of the worksheet: Select rows 1-5. From Insert menu, choose Rows. 2. In cell A1 type =today() The parenthesis are important even though there is nothing between them. Press Enter. If you wish to change the format of the date and time, from the Format menu, choose Cell. On the number tab, select date as the category and choose a format. Click OK to return to the worksheet. 3. In cell A2 type Average Student Pay Rate 4. In cell C2 use the AVERAGE function to find the average of all student pay rates. 5. Click the Insert Function button. Choose AVERAGE and click OK. 6. To select the range F6 through F379, type F6:F379 in the Number 1 box, or use the collapse dialog button and highlight the range. 7. Press Enter. Click OK to finish the formula. The result should be $6.86. As you select this large range it becomes clear that using the Range Name feature can be useful. MAX will give you the highest value in a range. MIN will give you the lowest value in a range. 1. You can use MAX and MIN in the same way you used AVERAGE above. Try it on your own and ask the instructor if you have questions or need assistance. 2. Complete the rest of the calculations for the sheet.

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com Freeze Panes Click on the Data tab. Since this is a large list you may want to Freeze Panes. This feature is available from the Window menu. If you have a large worksheet with horizontal or vertical headers, you may wish to continue to display those headers as you scroll through the cells. This can be done by ‘freezing’ those panes. 1. To freeze the top horizontal pane, select the row just below where you want the data to appear. –OR2. To freeze the left vertical pane, select the column to the right of where you want the data to appear. –OR3. To freeze both the upper and left panes, click the cell below and to the right of where you want the data to appear. 4. On the Window menu, click Freeze Panes.

The value of the sorting and calculating functions becomes clear when using a larger data file. Take some time to try different sorts. For example, sort by Last Name or by Hrs/Wk. Ask the instructor if you need any assistance. The benefit of the copy command (or fill) is also more obvious with a larger data file. Imagine entering the formula to calculate the Weekly Gross Pay 300 or more times! Click File and choose Close. You do not need to save any changes. Absolute Referencing Using dollar signs, you can tell Excel to use the contents of a cell in an exact location when creating a formula. When the formula is copied, the reference to that cell will not change. We will use an absolute reference in the next exercise to calculate a 5% raise for 1999. We will also use the pointing method of entering a formula. 1. Open Excel-AbsoluteReference.xls

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

2. Compare the cell contents in C4 to H4. C4 is a label and H4 is a value. 3. Click in Cell D5 and type =, click cell B5, type *, click cell C5. The formula should read =B5*C5. Press Enter. 4. Click in Cell D5 again and use the fill handle to drag and copy the formula down to D19. As the formula is copied the cell addresses will change relative to the new location. The formula we enter in column H will be different. We will refer to the 5% value that has been entered in one cell -- H4. Therefore, when the formula is copied we will need to make the reference to H4 absolute since this is the only cell we want to multiply by. 1. Click in cell H5 and type =, click cell G5, type *, click cell H4, then press F4 (the function key along the top of the keyboard). The formula should be =G5*$H$4. This will add two dollar signs to your formula, which makes the cell reference for H4 absolute. Press Enter to complete the formula. 2. Click H5 again and use the fill handle to drag and copy the formula down to H19. 3. Check the formulas in column H to verify that the reference to H4 did not change. NOTE: If you did this correctly, the totals will be the same for H20 as it is for D20. Now think about what you would need to do to determine a 7% raise in each scenario. On the relative side of the worksheet, 5% would need to be changed to 7% in several cells. On the absolute side, the value in only one cell would need to be changed. Try it: Change H4 to 7%. See how all the cells with a formula containing the absolute reference to H4 changed? Tip: If you forget that F4 is the key to press to make your cell reference absolute, the dollar signs can be typed in manually. Click File, choose Close. You do not need to save the changes. For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com

For useful Documents like this and Lots of more Educational and Technological Stuff... Visit... www.thecodexpert.com