TUHSD Computer Proficiency Test – SpreadsheetPractice #3Revised August 2011

You are to use Microsoft Excel 2010 to modify an existing spreadsheet data file. Each row of data in the file contains one record. You must use formulas and/or functions to perform the calculations. Text in quotes should be entered without the quotation marks. Follow the instructions below exactly and do not make any additional formatting changes. Complete the steps in the order presented and restart the exam if you encounter problems. You may use the built-in Excel help, but you may not use any outside sources (e.g., Web sites, other files, notes, etc.). Proofread your printouts carefully. Do not save the Excel file unless directed to do so by the instructor.

  1. Open the file called "SS-3-Data.xlsx" from the locationspecified by the instructor.
  1. Hide columns Bthrough E, J, K, R, T, and U.
  1. Delete columns G, H, and M.
  1. Insert two blank rows at the top of the spreadsheet.
  1. In row 1, use the merge cells feature to combine cells A1 through P1 into a single cell.
  1. In the newly formed cell A1, type your name and today's date. Set the cell’s horizontal alignment to the center.
  1. Starting in row 2, column A, type the following column titles (using bold and alluppercase letters) in this order from left to right: "LAST NAME", "STATE", "GENDER", "AGE", "OCCUPATION", "ANNUAL INCOME", "ANNUAL HOUSING COSTS ($)", "ANNUAL FOOD EXPENSES", and "NUMBER OF KIDS". (For now, it is okay if the titles overlap each other.)
  1. Between the "ANNUAL HOUSING COSTS ($)" and "ANNUAL FOOD EXPENSES" columns, insert a new column titled "ANNUAL HOUSING COSTS (%)" (using bold and alluppercase letters).
  1. For the column titles (row 2), turn on the wrap textfeature.
  1. Adjust the column widths so that no words in the titles are split over multiple lines, andnocolumn titlehas more than two words per line. If necessary, adjust the height of row 2. Make sure all the data in the remaining cells is fully visible.
  1. Set the horizontal alignment ofeach column titleto the center,and the vertical alignment ofeach column title to thetop.
  1. In the "ANNUAL HOUSING COSTS (%)" column, create a formula that calculates the percentage of each person’s income spent on housing each year ("ANNUAL HOUSING COSTS ($)" divided by "ANNUAL INCOME"). Calculate this value for all the records.
  1. Format the values in the "ANNUAL INCOME", "ANNUAL HOUSING COSTS ($)", and "ANNUAL FOOD EXPENSES" columns as currency(not accounting) with nodecimal placesand right aligned. Format thevalues in the "ANNUAL HOUSING COSTS (%)" column as percentages with twodecimal places and right aligned.
  1. In cell K44, type the row title "AVERAGE" (right align using bold and alluppercase letters).
  1. In row 44, use the AVERAGE function to calculate the average values for the "ANNUAL INCOME", "ANNUAL HOUSING COSTS ($)", "ANNUAL HOUSING COSTS (%)", and "ANNUAL FOOD EXPENSES" columns.
  1. Formatthe averages (row 44) as currency with twodecimal places(right align using bold). Then, change the format of the average for the "ANNUAL HOUSING COSTS (%)" column to a percentage with twodecimal places (right alignusingbold).
  1. Sort the spreadsheet records by "ANNUAL FOOD EXPENSES" in Largest to Smallest order.
  1. Filter the spreadsheet so that it shows only the records of females under the age of 50. (Rows 1, 2, 43, and 44 should remain visible and unchanged.)
  1. Setallvisible cells to Arialfont,size10. If necessary, adjust the column widths so that no title has more than two words per line.
  1. Print the filtered spreadsheet, in landscapeorientation, showing gridlinesand row and column headings. Before printing, if necessary, adjust the column widths to make the spreadsheet fit on one page with all the titles and data showing (as described in step 10). Do NOT changethe page margins, font size, or font face. Make sure the page scaling is set to 100%.
  1. Print the filtered spreadsheet, in landscape orientation, on one page, showing all formulas, gridlines,and row and column headings. After showing the formulas, adjust the column widths, and then the page scaling, so that the print size will be as LARGE as possible, and the titles, data, and formulas will all be fully visible. Do NOT manually adjust the page margins, font size, or font face. Ignore the formatting changes to the data. (If necessary, the titles may now have more than two words per line.)
  1. Create a two-dimensional clustered column chartthat uses the "LAST NAME", "ANNUAL INCOME", and "ANNUAL HOUSING COSTS ($)" columns from the filtered spreadsheet. Do NOT include the averages in the chart. Make sure only last names appear on the X-axis and the legend shows "ANNUAL INCOME" and "ANNUAL HOUSING COSTS ($)".
  • Move the chart to a new sheet.
  • Create a title above the chart that reads "Household Finances", and include your name as part of the title.
  • For the horizontal (X) axis of the chart, create a title below the axis that reads "Names".
  • For the vertical (Y) axis of the chart, create a rotated title that reads "Dollars".
  1. Print the column chart in landscape orientation. (Your completed exam should consist of threeprinted pages.)