7th grade Business & Computer Science

1

Excel Project 2 – Formulas, Functions, and Formatting 1.

Open a blank Excel spreadsheet

2. Save the spreadsheet as Project 2 Formulas, Functions, and Formatting on your network drive 3. In cell A1, type Silver Dollars Stock Club and then press the down arrow 4. Type Portfolio Summary in cell A2 5. In cell A3, type Stock and then press the right arrow key 6. In cell B3, type Symbol and then press the right arrow key 7. In cell C3, type Date, then press ATL + ENTER. Type Acquired and then press the right arrow key 8. In cell D3, type Shares and then press the right arrow key 9. In cell E3, type Initial, then press ATL + ENTER, then type Price, then press ATL + ENTER, and type Per Share. Press the right arrow key 10. In cell F3, type Initial then press ATL + ENTER, then type Cost. Press the right arrow key. 11. In cell G3, type Current, press ATL + ENTER, type Price, press ATL + ENTER, type Per Share. Press the right arrow key. 12. In cell H3, type Current, press ATL + ENTER, then type Value. Press the right arrow key. 13. In cell I3, type Gain/Loss. Press the right arrow key. 14. In cell J3, type Percent, then press ATL + ENTER. Type Gain/Loss Check your spreadsheet with the image below.

7th grade Business & Computer Science

2

15. Click cell A4, type Apple Computers, then press the right arrow key 16. Type AAPL in cell B4, then press the right arrow key 17. Type 12/1/14 in cell C4, then press the right arrow key 18. Type 440 in cell D4, then press the right arrow key 19. Type 64.59 in cell E4 20. Click cell G4 21. Type 82.99 in cell G4 22. Then click cell A5. Save your changes. 23. Using the table below, enter the data into your spreadsheet in rows 5 through 8 Stock

Symbol

Date Acquired

Apple Computers AT&T Comcast Google Home Depot

AAPL T CMCSA GOOG HD

12/1/2014 12/16/2013 12/11/2012 12/2/2014 11/14/2014

MORE ON NEXT PAGE – SCROLL DOWN

Shares Initial Initial Price Cost Per Share 440 64.59 870 28.71 380 33.62 920 390.32 770 34.54

Current Price Per Share 82.99 27.99 41.39 492.55 31.72

7th grade Business & Computer Science

3

24. Check your spreadsheet with the image below. Make any corrections and save changes.

25. Click cell A9 and type Totals. Then press the down arrow key. 26. Type Average in cell A10. Then press the down arrow key. 27. Type Highest in cell A11. Then press the down arrow key. 28. Type Lowest in cell A12. Then press the down arrow key. 29. Save your changes. ENTERING FORMULAS 30. In cell F4, type =d4*e4 (This will display the formula in the formula bar. This formulas means multiplication will take place). 31. In cell H4, type = then click cell D4, type *, then click cell G4. Press Enter 32. Click cell I4 to select it 33. Type = then click cell H4, type – then click F4. Press Enter. (This formulas means subtraction will take place).

7th grade Business & Computer Science

4

34. Click cell J4 35. Type = then click cell I4, type /, then click cell F4. Press Enter. (This formulas means division will take place). 36. Save your changes. 37. Click cell F4. Use your fill handle to copy the formula to the range F5 through F8.

7th grade Business & Computer Science

5

7th grade Business & Computer Science 38. Use your fill handle for columns H, I, and J to copy the formulas through row 8. Check your spreadsheet with the image below. Save changes.

39. Click cell F9 40. Use the AutoSum button on the Home Ribbon to display the total for this column 41. Use the AutoSum button to display the totals for columns, H and I. 42. In J9, type =I9/F9 then press Enter. Check your spreadsheet with the image below. Save changes.

6

7th grade Business & Computer Science

7

USING AVERAGE, MAX, and MIN FUNCTIONS 43. Click cell D10 44. Type =av in the cell to display the Formula AutoComplete list 45. Point to the AVERAGE function name 46. Double click the AVERAGE function name 47. Select the range D4:D8 to insert the range as the argument to the AVERAGE function. Check the image below for help.

7th grade Business & Computer Science

48. Click the Enter box to compute the average for the range D4:D8 49. Click cell D11 50. Type =max(d4:d8) and then press Enter 51. Click cell D12 52. Type =min(d4:d8) and then press Enter 53. Save changes.

8

7th grade Business & Computer Science

9

54. Select D10 through D12. Using your fill handle, copy the Average, Max, and Min functions to the range E10 through J12. See the images below.

7th grade Business & Computer Science

55. Save changes 56. Select the range A1:J1 and click the Merge and Center button on the Home Ribbon 57. Select the range A2:J1 and click the Merge and Center button on the Home Ribbon 58. Select cells A1 and A2. From the Cell Styles menu, choose Title. 59. Change the font size in A2 to 14. Save changes. 60. Select cells A1 and A2. Click the Fill Color button on the Home Ribbon.

10

7th grade Business & Computer Science

61. Choose Blue, Accent 1, Lighter 80% from the menu

62. Select the range E4:I4 then click the Accounting Number Format on the Home Ribbon. 63. Select the range E8:I8 then click the Accounting Number Format on the Home Ribbon. 64. Select the range E5:I7 then click the Comma Style button on the Home Ribbon. Save changes.

11

7th grade Business & Computer Science

12

65. Select the range J4:J12 then click the Percent Style button on the Home Ribbon. Then click the Increase Decimal button on the Home Ribbon TWO TIMES.

You should see two decimal places in Column J. 66. Place your cursor between Column A and B until you see this symbol 67. Double click between the columns to automatically resize the column. Save changes. 68. Place you name in cell A14 69. Print your spreadsheet Normal View and Formula View (If you forget how to set up and print, click HERE for instructions!) 70. Staple and turn in for grading

7th grade Business & Computer Science

Sample of completed Project 2.

13

7th grade Business & Computer Science

EXCEL PROJECT 2 LAB 2-1 Follow the instructions below to create the spreadsheet, Aficionado Guitar Parts. 1.

Using the image below, set up and format your spreadsheet so that it looks similar.

2.

Save the spreadsheet as Lab 2-1

3. In F4, enter the formula =0.0325*(B4-D4-C4) then press Enter. 4. In G4, enter the formula =B4+E4-C4-D4+F4 then press Enter. 5. Using your Fill Handle, copy the formula in F4 to the range F5:F10. 6. Using your Fill Handle, copy the formula in G4 to the range G5:G10. 7. Save changes. 8. In row 11, use a function to find the total of columns B through G. 9. Using appropriate functions, find the highest, lowest, and average figures for each column. Place these functions in the range B12:G14. 10. Format the spreadsheet so that it looks like the sample. 11. Type your name in A16, save, print, staple, and turn in for grading.

14

7th grade Business & Computer Science

Completed Spreadsheet

15