Lab Test 1 Windows / MS Excel CS 101 PRACTICE #3

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 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.

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!

  1. Create on your Desktop a path that would look like "\practice\mytest\YourName\anotherone\". Use it to store the files created below. You will have to create folders nested inside of folders.
  2. Load in the spreadsheet from the link here, and save it with the name YourName1.xlsx in your folder on the Desktop. Then open it with MS Excel. Make sure you have the sheet open in Excel, not in a browser.
  3. Insert a row at the very top of the sheet. Put your name into cell A1.
  4. Widen or reduce all columns so they are just wide enough for the widest entry in them.
  5. Fill in the column labelled Miles per Gallon with formulas for all vehicles. These formulas should figure miles per gallon by subtracting Start Miles from End Miles and dividing by Gallons Used. Format this column so it has 2 decimal places.
  6. Fill in the column labelled Cost with formulas for each vehicle that calculates the cost of the gallons of gas used, by multiplying that value (Gallons Used) by the Price of a Gallon below. Use an absolute reference for the cell with the Price of a Gallon value. *(difficult) You must use absolute reference in these formulas for only one cell in each formula. Write the formula so that it can easily be copied down the column without having to be edited. Format this column as currency.
  7. In cell F10, put a formula that uses a function to find the total of the costs.
  8. Save the sheet as YourName1.xlsx.
  9. 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).
  10. Insert a row at the bottom of the list with the info:
    Jeep, 23839, 24182, 10.1
    and make sure the row is filled out with the correct formulas.
  11. Sort the vehicle data so that it is in descending order by Miles Per Gallon column. Do not move the labels at the tops of the columns. All the data to go with one vehicle should stay with that vehicle's name.
  12. Save the sheet as YourName2.xlsx.
  13. Do a "save as" with the name YourName3.xlsx.
  14. Put a label in cell G3 that says "Largest MPG".
  15. Put a formula in cell G4 that shows the largest value of the Miles per Gallon column.
  16. Put a label in cell G6 that says "Lowest MPG".
  17. Put a formula in cell G7 that shows the smallest value of the Miles per Gallon column.
  18. Put a label in cell G9 that says "Average MPG".
  19. Put a formula in cell G10 that shows the average value of the Miles per Gallon column.
  20. Freeze the sheet at cell D2. Show it is frozen by scrolling.
  21. Save the sheet as YourName3.xlsx.
  22. Create a new sheet and create the following errors:
  23. Save this file with the name YourName4.xlsx in the same folder as the other sheets. Do not close Excel yet.

Practice with File manipulations in Windows

  1. Make a copy of the file just saved, and call it "YourName5.xlsx", in the same folder. Copy it by click-and-dragging (drag-and-drop) the file icon. Hint: use the Ctrl key while you are dragging.
  2. Copy the two files (YourName4.xlsx and YourName5.xlsx) as a group. Put them in the same folder. Now delete the two copies you made.
  3. Minimize the Excel window. Now redisplay it at its original size.
  4. Move the Excel window so it is at the bottom of the screen. Move it so its title bar is at the very top of the screen.
  5. Resize the Excel window so it is as tall as the screen but not as wide. Now resize it back to its original shape. Close Excel.
  6. Now you should have five files, YourName1.xlsx, YourName2.xlsx, YourName3.xlsx, YourName4.xlsx and YourName5.xlsx. 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"!
  7. Create a zip file that contains the folder that contains all these files named YourName.zip. This zip file should be on the Desktop.
  8. Note: On the first lab test, the instructor will be allowed to help you if you have trouble with zipping or submitting. After that first lab test, you are expected to be able to use both programs without assistance. The instructor will not be able to help you during lab tests after the first one without a penalty.
  9. At this point you would use a browser to submit the file during the actual test. You can submit a file as many times as you wish. The last one submitted is the one that will be graded.
  10. Delete (erase, remove) your folder and all your files from the Desktop. Also empty the recycle bin.
  11. Using the Help system of Windows, find the help on "locating lost files". You may have to go to the Control Panel to find the Help and Support option.
  12. You have one browser window open with these instructions in it. Open another browser window and enter the URL of the class web page. Can you find your TA's name and what sections they teach? Switch back and forth between the browser windows.