exel test

MIS 2325_04 Exam 3

1. Open the MIS 2325_04 Exam 3 workbook located on BlackBoard and save it on your desktop with the following filename: Firstname_Lastname_MIS2325_Exam 3 (Make sure it is still saved as a Macro Enabled Workbook)

2. Rename Sheet1 to Ranking

3. Make a copy of the Ranking worksheet (shortcut: ctrl+left mouse click on Ranking worksheet and drag new file to the right of Ranking worksheet)

4. Rename the copy to Ranking Table

5. Sort the list in the Ranking worksheet by 2003 Rank, smallest to largest

6. Correct the hyperlink for University of North Caroline at Chapel Hill to: http://www.unc.edu

7. Select the Ranking Table worksheet. Create a table in the range A1:J101

8. Name the table Ranking_table

9. Format the table to Table Style Medium 6

10. On the Data tab, sort the table by State A to Z and then by School A to Z

11. In the Student/Faculty Ratio column, apply a filter to select those less than 15

12. In the 4-year Grad. Rate column, apply a filter to select those greater than 50% (.5)

13. Select Total Sales worksheet

14. Use a 3-D reference to SUM each quarter’s sales from Uptown, Midtown and Westside worksheets (i.e. reference the same cell in multiple worksheets)

15. Group Total Sales, Uptown, Midtown & Westside worksheets

16. In Total Sales worksheet, SUM Q1 – Q4 sales

17. In Nametags worksheet, format the Print area to cells A6:E11

18. Input your first name in cell B1

19. Input your last name in cell B2

20. Input “UIW” in cell B3

21. Record a macro with the following steps and name it Transfer with Ctrl-q as the shortcut:

a. Select cell B1

b. Copy contents of B1

c. Paste contents of B1 to the merged cells beginning with B8

d. Select cell B2

e. Copy contents of B2

f. Paste contents of B2 to the merged cells beginning with D8

g. Select cell B3

h. Copy contents of B3

i. Paste contents of B3 to the merged cells beginning with B10

j. Select cell B1

k. Stop macro recording

22. Record a macro with the following steps and name it Clearform with Ctrl-i as the shortcut:

a. Select cells B1:B3 AND cells B8:E11 (hint: use Ctrl key to select multiple cells)

b. Clear contents of the selected cells

c. Select cell B1

d. Stop macro recording

23. Insert a macro button on the Nametags worksheet and assign the Transfer Macro to it

24. Label the button Transfer

25. Insert a macro button on the Nametags worksheet and assign the Clearform Macro to it

26. Label the button Clear

27. Select Protect Sheet for the Nametags worksheet

28. Save your workbook on the desktop

29. Upload your workbook to BlackBoard