Excel Project!

D131

Week4ProjectTipsHyperlinkReplacement.pdf

Home >Computer Science homework help >Excel Project!

Page 2 of 58

Computer and Internet Literacy

2 Week 4 Project Tips

Week 4 Project

Week 4 Project Tips

Below are tips to help you start your Week 4 Project.

The tips show you, step by step, how to create a spreadsheet in Microsoft Office Excel. This includes creating a table, creating a header, entering labels and numerical data, using formulas, and applying some basic formatting.

The tips also show you how to create a chart from your data.

The tips do not use the same data as your project, so will need to follow carefully the instructions in your project to use the correct data, but the organization of the data is the same as in your project.

Also, the formatting of the spreadsheet is as shown in the instructions for the project, but you are encouraged to create a spreadsheet and a chart with your own original presentation.

Now, let’s see how to tackle your Week 4 Project.

Page 3 of 58

Computer and Internet Literacy

3 Week 4 Project Tips

Week 4 Project

Step 1. Open Excel and click on Blank workbook to create a new workbook.

Page 4 of 58

Computer and Internet Literacy

4 Week 4 Project Tips

Week 4 Project

Step 2. Double click on the sheet name; the default name Sheet1 is highlighted as shown.

Page 5 of 58

Computer and Internet Literacy

5 Week 4 Project Tips

Week 4 Project

Step 3. Type the new name on the name tag for the worksheet. Here, the name is changed to Budget. Then type the header text across, as shown, and the item names for the budget in column A. No formatting is applied yet.

Page 6 of 58

Computer and Internet Literacy

6 Week 4 Project Tips

Week 4 Project

Step 4. Double-click on the right-most boundary of column A, as indicated with the red arrow in the screen capture below. That has the effect of widening column A, such that its content will fit nicely within column A’s boundaries, and will not encroach on the space of the columns to the right.

Page 7 of 58

Computer and Internet Literacy

7 Week 4 Project Tips

Week 4 Project

Step 5. Now, enter the numerical data per the instructions of the Week 4 Project. Note that the data below are different from what is specified in your project instructions. So, make sure to follow your project instructions; do not use the numerical values shown below.

Page 8 of 58

Computer and Internet Literacy

8 Week 4 Project Tips

Week 4 Project

Step 6. In cell B5, corresponding to Monthly Income for January, type =SUM(B3,B4). This is the sum formula applied to the values in cells B3 and B4. Then hit <Enter>.

Page 9 of 58

Computer and Internet Literacy

9 Week 4 Project Tips

Week 4 Project

Step 7. As a result, the value in B5 appears as the sum of the value in B2 + value in B3. Then, select cell B5, right-click and select Copy.

Page 10 of 58

Computer and Internet Literacy

10 Week 4 Project Tips

Week 4 Project

Step 8. Select and highlight cells C5 and D5, right-click and select the first Paste icon of the Paste Options.

Page 11 of 58

Computer and Internet Literacy

11 Week 4 Project Tips

Week 4 Project

Step 9. As a result of the last step, Excel places sum formulas in cells C5 and D5, corresponding to the formula in B5, but applied to cells in columns C and D respectively. This is a shortcut rather than retyping the sum formulas manually in C5 and D5. Note how now the value in C5 is equal to the sum of values in C3 and C4, and the value in D5 is equal to the sum of values in D3 and D4, as required.

Page 12 of 58

Computer and Internet Literacy

12 Week 4 Project Tips

Week 4 Project

Step 10. Now, let’s see another technique to apply the Sum formula. Select cell B19, corresponding to Monthly Expenses for January. Then, under the Home tab, under group Editing, click on AutoSum and select Sum.

Page 13 of 58

Computer and Internet Literacy

13 Week 4 Project Tips

Week 4 Project

Step 11. Excel selects the column of values above your selected cell and applies the SUM formula =SUM(B8:B18) in cell B19.

Page 14 of 58

Computer and Internet Literacy

14 Week 4 Project Tips

Week 4 Project

Step 12. When you hit <Enter> cell B19 displays the numerical sum of values from B8 through B18 as expected.

Page 15 of 58

Computer and Internet Literacy

15 Week 4 Project Tips

Week 4 Project

Step 13. Right-click on cell B19 and select Copy.

Page 16 of 58

Computer and Internet Literacy

16 Week 4 Project Tips

Week 4 Project

Step 14. Then select and highlight cells C19 and D19, right-click and select the first of the Paste Options.

Page 17 of 58

Computer and Internet Literacy

17 Week 4 Project Tips

Week 4 Project

Step 15. As a result, Excel places the formulas =SUM(C8:C18) and =SUM(D8:D18) in C19 and D19 respectively.

Page 18 of 58

Computer and Internet Literacy

18 Week 4 Project Tips

Week 4 Project

Step 16. In cell B21, type the formula =B5. This is to make the value in B21 equal to whatever value is in B5. So, if B5 changes later, B21 will match the value in B5 automatically.

Page 19 of 58

Computer and Internet Literacy

19 Week 4 Project Tips

Week 4 Project

Step 17. Right-click on cell B21 and select Copy.

Page 20 of 58

Computer and Internet Literacy

20 Week 4 Project Tips

Week 4 Project

Step 18. Select and highlight cells C21 and D21, right-click and select the first of the Paste Options.

Page 21 of 58

Computer and Internet Literacy

21 Week 4 Project Tips

Week 4 Project

Step 19. Let’s do the same with the Monthly Expenses. In cell B22, type =B19. This is to make the value in cell B22 always match the value in cell B19.

Page 22 of 58

Computer and Internet Literacy

22 Week 4 Project Tips

Week 4 Project

Step 20. Select cell B22, right-click and select Copy.

Page 23 of 58

Computer and Internet Literacy

23 Week 4 Project Tips

Week 4 Project

Step 21. Select cells C22 and D22, right-click and select the first option of the Paste Options.

Page 24 of 58

Computer and Internet Literacy

24 Week 4 Project Tips

Week 4 Project

Step 22. Now, let’s work on the Net revenue or loss. In cell B23, type the formula =B21-B22 as shown and hit <Enter>.

Page 25 of 58

Computer and Internet Literacy

25 Week 4 Project Tips

Week 4 Project

Step 23. As a result, the Net value of Monthly Income minus Monthly Expenses for the month of January is displayed in cell B23.

Page 26 of 58

Computer and Internet Literacy

26 Week 4 Project Tips

Week 4 Project

Step 24. Right-click on cell B23, and select Copy.

Page 27 of 58

Computer and Internet Literacy

27 Week 4 Project Tips

Week 4 Project

Step 25. Select and highlight cells C23 and D23, right-click and select the first Paste option of Paste Options as shown.

Page 28 of 58

Computer and Internet Literacy

28 Week 4 Project Tips

Week 4 Project

Step 26. Now, the data in your spreadsheet is complete. Next, let’s apply some formatting to the sheet Budget.

Page 29 of 58

Computer and Internet Literacy

29 Week 4 Project Tips

Week 4 Project

Step 27. Highlight the cells between B3 and D5 as shown, and under the Home tab, under group Number, click on the Accounting Number Format option as shown.

Page 30 of 58

Computer and Internet Literacy

30 Week 4 Project Tips

Week 4 Project

Step 28. As a result, the cells for Monthly Income for January, February, and March are displayed in Accounting Number Format, with $ sign left-justified, and the dollar amount right-justified. Also, a comma is automatically displayed as a thousands separator.

Page 31 of 58

Computer and Internet Literacy

31 Week 4 Project Tips

Week 4 Project

Step 29. Apply the Accounting Number Format to all the numerical cells as described above.

Page 32 of 58

Computer and Internet Literacy

32 Week 4 Project Tips

Week 4 Project

Step 30. Now, since the dollar amounts appear a little crowded, let’s adjust the columns width. Select columns B, C, and D as shown. Make sure to select the entire columns by placing the cursor of your mouse on the letter B, holding the left key of your mouse, and sliding the mouse to the right such that you cover and highlight the 3 columns as shown.

Page 33 of 58

Computer and Internet Literacy

33 Week 4 Project Tips

Week 4 Project

Step 31. Right-click and select Column Width…

Page 34 of 58

Computer and Internet Literacy

34 Week 4 Project Tips

Week 4 Project

Step 32. A Column Width form appears. Enter the desired value in the box. Here the value 12 is chosen.

Page 35 of 58

Computer and Internet Literacy

35 Week 4 Project Tips

Week 4 Project

Step 33. As a result, all 3 columns B, C, and D widen as expected, and the currencies appear less crowded than before. Notice how the $ sign is still left-justified according to the Accounting Number Format.

Page 36 of 58

Computer and Internet Literacy

36 Week 4 Project Tips

Week 4 Project

Step 34. Next, select and highlight the header in row 2 as shown.

Page 37 of 58

Computer and Internet Literacy

37 Week 4 Project Tips

Week 4 Project

Step 35. Click on the Center option under the Alignment group in the Home tab, then click on the Bold option under the Font group.

Page 38 of 58

Computer and Internet Literacy

38 Week 4 Project Tips

Week 4 Project

Step 36. Do the same with the other header in row 7.

Page 39 of 58

Computer and Internet Literacy

39 Week 4 Project Tips

Week 4 Project

Step 37. Also, select Monthly Income and apply the Bold option, and repeat with the next occurrence of Monthly Income. Do the same with Monthly Expenses, and Net.

Page 40 of 58

Computer and Internet Literacy

40 Week 4 Project Tips

Week 4 Project

Step 38. Now, let’s apply more formatting to the Budget sheet. Select and highlight the box of cells between A3 and D4 and, under the Home tab, in the Font group, click on the Borders icon to open the Borders drop-down menu, and select Thick Outside Borders.

Page 41 of 58

Computer and Internet Literacy

41 Week 4 Project Tips

Week 4 Project

Step 39. Repeat the previous step with the box of cells between cells A8 and D18 as shown.

Page 42 of 58

Computer and Internet Literacy

42 Week 4 Project Tips

Week 4 Project

Step 40. As a result, you have now created some basic formatting to your Budget sheet.

Page 43 of 58

Computer and Internet Literacy

43 Week 4 Project Tips

Week 4 Project

Step 41. Finally, select cells B22 through D22 as shown. Again, from the Font group, click on the Borders icon to open the drop-down menu of Borders options, and select Thick Bottom Border.

Page 44 of 58

Computer and Internet Literacy

44 Week 4 Project Tips

Week 4 Project

Step 42. Now your budget sheet is complete and fully formatted.

Keep in mind that the formatting here is simply and example of what can be done to make your Budget sheet look professional. You are encouraged to experiment with different formatting effects when you create your own Budget sheet, but be careful not to overdo it.

Page 45 of 58

Computer and Internet Literacy

45 Week 4 Project Tips

Week 4 Project

Step 43. Next, you need to open a new sheet for your chart. Click on the + sign for New Sheet at the bottom of your Excel window as shown.

Page 46 of 58

Computer and Internet Literacy

46 Week 4 Project Tips

Week 4 Project

Step 44. As you did earlier with the Budget sheet, double-click on the name tag of the new sheet and type your new name. Here, we typed the name Chart.

Page 47 of 58

Computer and Internet Literacy

47 Week 4 Project Tips

Week 4 Project

Step 45. Select your entire table as shown, then under tab Insert, click on the lower-right corner of the group Charts, to display All Charts as shown. As an example, select the third chart from the top under the Recommended Charts folder. Note here that if you only want to chart the expenses, then, only select and highlight the part of the table with expenses; below that’s the box between cells A7 and D19. The advantage of not including the income data is that your chart will show the expenses with better precision in dollar amounts.

Page 48 of 58

Computer and Internet Literacy

48 Week 4 Project Tips

Week 4 Project

Step 46. The chart is displayed in sheet Budget as shown. Next, you need to cut it and paste it on your new sheet Chart.

Page 49 of 58

Computer and Internet Literacy

49 Week 4 Project Tips

Week 4 Project

Step 47. Select the chart, right-click and select Cut.

Page 50 of 58

Computer and Internet Literacy

50 Week 4 Project Tips

Week 4 Project

Step 48. Click on the sheet Chart name tag to open it and paste the chart on that sheet. When you paste the chart, make sure to select the option Keep Source Formatting, not Picture!

Page 51 of 58

Computer and Internet Literacy

51 Week 4 Project Tips

Week 4 Project

Step 49. Experiment with different design options for your chart. To do so, select your chart, and the contextual tab set Chart Tools appears at the top. Click on the Design tab. From there, try different Chart Styles. You may also manually move the legend around and edit and format any text in your chart. Below, the title of the chart is edited to Monthly Budget.

Page 52 of 58

Computer and Internet Literacy

52 Week 4 Project Tips

Week 4 Project

Step 50. In the example here, Style 6 is selected from the Chart Styles options.

Page 53 of 58

Computer and Internet Literacy

53 Week 4 Project Tips

Week 4 Project

Step 51. The chart below is enlarged via the Format tab under the contextual tab set Chart Tools. To edit the text, for example in the legend, select the text box as shown, and from the Font group under the Home tab, select the desired Font Size. Here the value 12 is selected.

Page 54 of 58

Computer and Internet Literacy

54 Week 4 Project Tips

Week 4 Project

Step 52. Finally, open a new sheet, as was described earlier, and rename it with a name of your choice. Here its is named Analysis. Then, in Budget sheet, select the entire data space on that sheet. To do so, place your cursor in the upper-left corner of the data space as shown, and click on the little triangle icon there, as pointed by the red arrow.

Page 55 of 58

Computer and Internet Literacy

55 Week 4 Project Tips

Week 4 Project

Step 53. Right-click and select Copy.

Page 56 of 58

Computer and Internet Literacy

56 Week 4 Project Tips

Week 4 Project

Step 54. Go to sheet Analysis, select the entire data space as in the previous step, right-click and select the first Paste option of Paste Options.

Page 57 of 58

Computer and Internet Literacy

57 Week 4 Project Tips

Week 4 Project

Step 55. Now you have copied the same data, including all the formulas, and with the exact same formatting, from sheet Budget to sheet Analysis.

Page 58 of 58

Computer and Internet Literacy

58 Week 4 Project Tips

Week 4 Project

Step 56. Finally, experiment with this spreadsheet by changing some of the data, but don’t modify the cells where you have formulas! Below, values for Jane’s March income, and March Rent/Utilities Expenses are changed. Notice that the values in total Monthly Incomes for March (in cell D5), Monthly Expenses for March (cell D19), Monthly Expenses (cell D22), and Net for March (cell D23), get updated automatically from the formulas in those cells.

Again, remember that the example here does not use the same data as your project. Therefore, you need to follow carefully the instructions in your project to use the correct data. Also, you are encouraged to create your workbook with your own original presentation.

Week 4 Project Tips