Homework 5   

Microsoft Excel - Sorting, Conditional Formatting, Filter with SUBTOTAL, VLOOKUP, Paste Special

*** 15-point assignment due by Thursday, February 9, 2011 at 9:00pm.


Start with the file: T:\teach\classes\for112\25Years.xlsx

1. Do a “Save As” and save a copy of the starter file to your Z: drive and name it Your_ONID_username-HW5.xlsx. For example, the instructor would name her file howellk-HW5.xlsx

2. (1 pt) Merge and center the title in Row 1 across columns A-J. Do the same for Row 2.

3. (1 pt) In the 25YearHarvestHistory worksheet, apply comma formatting to all the data. Make sure that the data doesn’t display any decimal place digits.

4. (2.5 pts) Apply conditional formatting, only two rules, as follows:

a. For all data values except the Totals column, apply the “Yellow Fill with Dark Yellow Text” formatting to any data values that are less than 150.
b. Apply the green data bars formatting to all data columns (including the Totals column).
c. Make the changes necessary so that the cells that are less than 150 and are formatted with the yellow text do not display a green data bar.

5. (1 pt) Let's say that the data in the “Other Private” column was not entered correctly. Those values should all be twice as high. Use one of the handy Paste Special operations to make that change.

6. (1 pt.) Copy just the Year and Totals columns (data cells only, not the entire column) from 25YearHarvestHistory and paste the cells on to Sheet 2 running across -- swap the rows and columns (transpose).

7. (1.5 pt) Sort the 25YearHarvestHistory data from smallest to largest based on the State column. BE SURE the Average row is not included in your sort.

8. (3 pts) VLOOKUP Function:
a. On the 25YearHarvestHistory worksheet, put the values 3,000, 5,000, and 7,000 in cells L5-L7 and type “Good”, “Bad”, and “Ugly” in cells M5-M7.
b. Create a named range for the L5:M7 range. Use HarvestLevel for the name.
c. In cell J4, type “Production Level”.
d. In cell J5, create a VLOOKUP function that looks up the total for the first data row in your named range and then returns the appropriate text for that year’s total. That means that values between 3,000 and 4,999 will return “Good”; values between 5,000 and 6,999 will return “Bad”; and values 7,000 or greater will return “Ugly”. Copy the function down column J to all the other cells. NOTE: when it comes to the fourth parameter of your VLOOKUP function, you don’t have an exact match.

9. (1 pt) On the Schedule worksheet, Freeze the first row column headers so they don't scroll off the screen as you scroll down.

10. (3 pts) a. Filter the data on the Schedule worksheet to display only those classes that meet in Peavy and have 25 Total Seats or less.  DON'T just use the checkboxes for either filter.
b. Create a SUBTOTAL formula in Row 215 at the bottom of the Total Seats column that adds up the total seats.    
      BE SURE all rows of data are included so your formula works with other filters !!
c. Create a SUBTOTAL formula in Row 215 at the bottom of the Room column that counts the number of courses.     
      BE SURE all rows of data are included so your formula works with other filters !!

10. Save all the changes to your modified Excel file and close Excel.
Attach your your_ONID_username-HW5.xlsx file to an e-mail you send to the instructor.
For the e-mail subject line, please use FOR112 HW5.