Excel Assignment 5
Various Excel Tasks

---

Schedule | Syllabus | Printable Version
Minor modifications may be made to this assignment, based on questions in class

Scenario: Before you go on your trip to New Mexico, you find that you have a list of tasks to do on your existing spreadsheets. You will also create a table that will show how your vehicle payments are applied to interest and principle and how the payment period will change if you pay more than the minimum each month.

Excel Skills in this Assignment

• Line chart - page 436
• Importing text into a spreadsheet - pages 548 - 551
• Exporting a spreadsheet as Tab Delimited text - page 551
• Conditional formatting - pages 551 - 560
• Use Concatenation() - pages 622 - 623
• Use the Subtotal() - pages 632 - 632
• URLs in your spreadsheet - not in text
• Amortization table - pages 651 - 661
• Creating drop down lists to control data entry - pages 716 - 718
• Using the Date functions
• Using functions inside of other functions.

Specific Instructions

1. Someone else in your office needs the subscriber list that you cleaned up, but their software can't open an Excel file. They have asked for the file as "tab delimited text." Save your Client List worksheet as tab delimited text and attach it to your email as a text file. Be sure to save this file because you will need it later for the Access assignments. 10 points
2. Create a drop down list for your Client List that will allow only entry of valid state and province codes. Add a user friendly error message. Remember that you are telling your data entry folks that they must use valid two letter state and Canadian province codes. 10 points
3. You need to find out how many subscribers you have in each state. Copy your Client List into a new worksheet called Subtotals. Use the Subtotal function to count how many people are in each state in the Client List worksheet. Be sure to sort the list by state first! 10 points
4. Use Conditional formatting to highlight the "Count" cells with a background color. 10 points
   How to use Conditional Formatting to format whole rows
5. Use Crtl clicking to choose all the count and value cells. Put these values in a line chart where the Legend is Number of Subscribers. Note: the way our spreadsheet is set up, Excel can only take 14 items. Your chart only needs to have 14 data points. 10 points
6. In your Itinerary, use the CONCATENATE function to combine the information about the people you will visit in your itinerary into one cell each. 5 points
7. Set the URL for your Google map to be a hyperlink. The cell should have the words: Itinerary Map, but it should link to your Google map. This is not covered in the text. 5 points
8. Choose one vehicle from Assignment 3, with payments less than the yearly mileage costs.
9. Create a new worksheet named Vehicle Payments. Format the sheet, as we have discussed in class. 5 points
10. Set up the payment information at the top: car info, principle, interest, number of periods, first payment date, etc. 5 points
11. Create an amortization table that will show your payment dates; the amount of your payment that is interest; the amount of your payment that is principle and the balance. You can follow the basic steps from your text on pages 655 - 661. Not all the instructions are applicable to our exercise. You should be in class for the specifics. 30 points
12. Send your file to cs172homework@thecomputergal.com with the subject: your last name and Excel Assignment 5. Ex. McDougall Excel Assignment 4.