Lab Test 1 MS Excel CS 101 PRACTICE #2

This is practice for the first Lab Test. The instructions on the lab test will be similar. You should practice these tasks until you can do them in roughly half an hour - you will have 50 minutes to do the actual test and zip it and submit it. You may use any on-line help or printed manuals you wish. You may not communicate with anyone during the test. NO TALKING IS ALLOWED during the actual test. (Of course during the practice, you can talk to your neighbor, your TA as desired.) If you get stuck on a task, you should not spend all your time on it. You can still get points for doing the other tasks.

See the first or third Practice Test for "File manipulation in Windows" practice.


Practice with Excel

When you are asked to write formulas in the problems below, make sure you use cell references, NOT just typing in numbers. That is, use =B5+C5 instead of =17+15, or even 32. Use the power of the spreadsheet! A formula like =B5+17 is ok if that is what the problem specifies (in other words, constants are ok to use, but not JUST constants).

  1. Make a set of folders starting from your Desktop so that the path is "\mytest\practice#2\". This will mean you have to create a folder within a folder. Use the practice#2 folder to store the files created below.
  2. Load in the spreadsheet from the link here, and save it with the name YourName1.xlsx in the folder just created. That is, its path is "\mytest\practice#2\YourName1.xlsx" on the Desktop. Then open it with MS Excel. Make sure you have the sheet open in Excel, not in a browser.
  3. Make columns A-F 17 columns wide.
  4. Fill in the column Area with formulas for all shapes. These formulas should figure the area of the particular shape. The area of these shapes is found by multiplying the base by the height. Practice copying the formulas instead of typing each one separately. Format this column so that it shows two decimal places.
  5. Fill in the column Weight with formulas for all shapes by multiplying the value in the Materials cell D10 by the area. * (difficult) Use absolute addresses for exactly one cell reference in all formulas in this column. Write the formula so that it can easily be copied down the column without having to be edited. You have to figure out which cell should be absolute reference.
  6. Put a label in cell C12 that says "Largest Shape".
  7. Use a function in a formula in cell D12 that reports the largest value in the Area column.
  8. Put a label in cell C13 that says "Lightest Shape".
  9. Use a function in a formula in cell D13 that reports the smallest value in the Weight column.
  10. Insert two rows at the very top of the sheet. Put your name into cell A1.
  11. Change the name of the Sheet1 worksheet (on the tab at the bottom) to "Geometry".
  12. Save the sheet as YourName1.xlsx.
  13. Do a "Save As" on the sheet as YourName2.xlsx (This is done so that your work up to this time is saved in a sheet, YourName1.xlsx, and the next steps will be saved in the next sheet, YourName2.xlsx. Each sheet is cumulative).
  14. Insert one row at the bottom of the list with the info: Parallelogram, 25, 30 and make sure the formulas are filled in in that row.
  15. Update the formulas for largest and smallest weights to include the new row.
  16. Sort the data for the shapes so that the names are alphabetical (ascending). All the data for a shape should stay with that shape's name. Don't move the headers (labels) at the tops of the columns.
  17. Center and merge the label "Materials Class 101" over the columns B through E, on row 3. Format the label to be 16 points high and Italics and Underlined.
  18. Put a formula using a function to show the current date in cell G5. It should be formatted to show only month, day and year.
  19. Freeze the sheet at cell B3. Show it is frozen by scrolling.
  20. Save the sheet as YourName2.xlsx.
  21. Create a new sheet and create the following errors:
  22. Save this file with the name YourName3.xlsx in the same folder as the other sheets. Close Excel.

These 3 files should be zipped along with the folders you created and submitted through the Canvas link. here. Make sure all filenames have the correct extension, .xlsx. They should not have names like "JohnSmith.xlsx.xlsx" or "JohnSmith..xlsx" or "JohnSmith.xlsx.docx"! Delete your folder and all your files from the Desktop.

The lines marked with * are the ones that most often cause people problems.