"In a Flash" Video Tutorial
Excel 1. An Introduction to Spreadsheets

tutorials require headphones or speakers

Use the Video Tutorial to learn about
• Spreadsheet Basics
• Example: Hannah's Big Trip
• What If?
• Spreadsheet Uses

"In a Flash" Video Tutorial
Excel 2. Working with Cells and Cell Data

Practice
Turn to Microsoft Office Help for assistance as you experiment with software features.

Assignment to Submit for Grade

Work in Excel 2007 to complete the following steps following the example provided in the Video Tutorial.

1. Create a new file in Excel, and name it after your school network username. Make sure that in the "Save As Type" field, you select "Excel Workbook (*.xlsx)". This is Excel 2007 format.
2. [4 pts] Create your own personal budget, as was done in the "In a Flash" Video Tutorial (use the link above under "In a Flash" Video Tutorial to access the tutorial). Your personal budget should cover 12 months, rather than 6, beginning with the current month.
3. [3 pts] Include a title in cell A1 that includes your full name (i.e. Kim Novak's Budget).
4. [4 pts] Include at least 12 expense items, do not copy from the example, or other students -you can use fictitious or real data (don't include private information).
5. [4 pts] Fill in fictitious or real amounts spent on each item for the 12 month period. Try out the copying techniques shown in the tutorial.
6. [3 pts] Add a label and value in cells A2 and B2 that indicates your monthly income.
7. [2 pts] Change column widths so that all data is visible.

Save your work for future use in the remaining steps below.

"In a Flash" Video Tutorial
Excel 3. Working with Functions and Formulas

Practice
Turn to Microsoft Office Help for assistance as you experiment with software features.

Assignment to Submit for Grade

Work in Excel 2007 to complete the following steps following the example provided in the Video Tutorial.

8. [4 pts] Use the SUM function to display the SUM for each expense over 12 months, and for each month as was done in the "In a Flash" Video Tutorial. Insert "Item Total", and "Total" labels to identify these values as was done in the example.
9. [4 pts] Use a formula (equation) below each monthly column that indicates how much money is left over each month after paying expenses. Insert a "Leftover" label to identify these values as was done in the example.
10. [4 pts] Insert a label and formula at the bottom of your spreadsheet that shows your total earnings for the year (the sum of your 12 monthly paychecks).
11. [4 pts] Which expense (rent, groceries, cell phone, etc) did you spend most on over the 12 month period? Show this in your spreadsheet by inserting another label (call it Maximum Yearly Expense.) and function (MAX()) at the bottom of your spreadsheet that shows the maximum yearly expense. Your Maximum Expense Amnt should only show the highest expense amount and not what it was for. For example, if your largest yearly expense was $3000 for rent , your spreadsheet will show Maximum Yearly Expense $3000.00.
12. [4 pts] What was the average monthly total over the 12 month period? Show this in your spreadsheet by inserting another label (call it Average Monthly Total.) and function (AVERAGE()) at the bottom of your spreadsheet that shows the average total amount spent each month. For example, if you spent $100 each month for the first 6 months and $200 each month for the second 6 months, the Average Monthly Total would display $150.
13. [5 pts] Insert a label and formula (equation) at the bottom of your spreadsheet that shows how much money you have left over at the end of the year.
14. [5 pts] Use the IF() function to display a message based on the amount of money you have left over at the end of the 12 months. Feel free to makeup your own goal. In the example it was a trip to Paris. Yours should be something different. Insert a label identifying the goal.
15. [3 pts] Change column widths so that all data is visible.
16. [4 pts] Open Sheet2 in the workbook by clicking its tab, and type "Leftovers" in cell A2. Then use a formula that displays the amount you have left over (see 13 above) in cell B2 of the new worksheet. Tip: To reference another cell, simply enter = and the cell name, for example =Q24. To reference cells in another sheet use =Sheetname!cellname for example =Sheet1!Q24. Search Excel Help on keywords "about cell and range references" for additional information.
17. [3 pts] Change the name of Sheet1 to the current year, and change the name of Sheet2 to next year.

Save your work for use in the remaining steps.

"In a Flash" Video Tutorial
Excel 3. Working with Functions and Formulas

Practice
Turn to Microsoft Office Help for assistance as you experiment with software features.

Assignment to Submit for Grade

Work in Excel 2007 to complete the following steps following the example provided in the Video Tutorial.

18. [2 pts] Change all dollar amounts to currency format.
19. [2 pts] Sort your expense items along with their associated values alphabetically as was done in the "In a Flash" Video Tutorial.
20. [2 pts] Use Merge and Center to center the title of the spreadsheet above all budget data.
21. [2 pts] Change the title to a large, bold, font --feel free to take artistic liberties with the font type.
22. [2 pts] Change all labels in the spreadsheet to bold.
23. [2 pts] Change all cell contents, except the title, to a 12 point font of your choice.
24. [2 pts] Change column widths so that all data is visible.
25. [3 pts] Use cell fill color, and cell borders to give your spreadsheet an attractive appearance (use colors and style of your own choice). Both fills and borders must be used in at least one area of the spreadsheet.
26. [3 pts] Use conditional formatting on the cell that contains your yearly leftover amount (see step 13) to automatically change the cell fill color to red whenever the number in the cell is negative.
27. Save your work for use in the remaining steps.

"In a Flash" Video Tutorial
Excel 5. Working With Graphics

Practice
Turn to Microsoft Office Help for assistance as you experiment with software features.

Assignment to Submit for Grade

Work in Excel 2007 to complete the following steps following the example provided in the Video Tutorial.

28. [10 pts] Select the cells containing all of your expense labels, and all of the expense totals - as was done in the "In a Flash" Video Tutorial Use the Chart Wizard button on the toolbar to create a Pie chart. Select options of your choice to create an attractive chart that is easy to read. Insert the chart below your budget data.
29. [4 pts] Insert a clip art image of something appropriate next to the chart.
30. [2 pts] Set the Print Area for the spreadsheet to include the cells containing data, the chart, and clip art image --everything from cell A1 through the bottom right corner of your clip art.
31. Use print preview to view your work.
32. [2 pts] Change the page orientation to Landscape. Note that your spreadsheets may require more than one page.
33. [2 pts] Type your full name in the footer of the spreadsheet along with the date you submit your work for a grade.
34. Save your work and submit it to your Instructor for grading prior to the due date listed on the course agenda.