Reference no: EM133984177

Overview
This session will introduce you to some useful features of MS Excel 2016. You will use the file named
Session 2.xlsx to complete the tasks given in this document.
Review of Terminology
What is meant by the following?
Workbook
Worksheet
Row
Column
Cell
Cell Range
Label
How are cell ranges written?

Task 1
On the "Student List" worksheet, select the cells having data and convert them into a table. Apply "Table Style Dark 5" to the table and name it "Students". Remove filtering buttons from the table and add a row at the end that counts the number of students. Get AI-free online assignment help by the best tutors.

Task 2
On the "Classwork" worksheet, configure the table to make each alternate row filled with a different colour. Please ensure that any new rows added to the table will have the same formatting applied. Rename the table to be "Scores".

Task 3
Add the following at the end of existing data on "Classwork" worksheet:
UG-008989, Waseem, 20, 18, 23, 18, 25
UG-006767, Naseem, 18, 20, 15, 17, 25

Task 4
On "Practice" worksheet, define the cell range C2:C11 as "First_Ten". Define the cell range C12:C21 as "Next_Ten" and remaining cells with data in column C as "Remaining".

Task 5
Use "Go To..." feature to navigate to table named "Scores" and configure it to have same colour for all rows. Add a row at the end which averages scores in each column.

Task 6
Use the functions available in the "Editing" group of the Home tab to add a function which calculates total
score for each student. Update the value in cell H1 to "Total Scores".

Task 7
Convert the table "Students" to a normal range while retaining the formatting. Configure all rows with data to have a height of 25 points. Align data to be centred vertically in the cells.

Task 8
Change the colour of each worksheet tab to be different from each other.
Conclusion

Overview
This session will continue to cover features of MS Excel 2016. You will use the file named Session 5.xlsx
to complete the tasks given in this document.

Task 1
On "Practice" worksheet, add a formula in H2 to calculate the average score of the athlete using the scores in all three rounds. Extend the formula to calculate average scores of all other athletes.

Task 2
On "Practice" worksheet, type a function in C32 to calculate the average score of all athletes in Round 1. In cell D32, use the Insert Function option to calculate the average score of all athletes in Round 2. Extend the function to E32.
What is the difference between using plain formulas and functions?

Task 3
On "Theory" worksheet, use a function to populate cells in column H to calculate the total score of each athlete. In cell B32, add a function to calculate the total number of athletes.

Task 4
Let us explore some additional functions which are less common but very useful.

What is meant by case of letters? Possible options are Upper Case, Lower Case, Proper Case etc.
On "Student List" worksheet, add a function in cell D2 to change the case of name in cell B2 to proper case. Repeat the task for all students in column B.

Task 5
Replace the names in column B with those in proper case. Use a suitable Paste option after copying the data from column D. Once done, delete all data in column D. What are possible paste options? We will explore Paste options in detail later.

Task 6
On "Student List" worksheet, type "ID without Prefix" in cell C1 and "Email Address" in cell D1. Ensure that format of columns C and D match column B.
Hint: We can use a tool we have used earlier in Session 3.

Task 7
On "Student List" worksheet, add a function in cell C2 which removes the prefix of "UG-" from the value in cell A2. Repeat the task for all values in column A.

Task 8
On "Student List" worksheet, add a function in cell D2 which combines the value in cell C2 with "@gust.edu.kw" to generate email address for the student. Repeat the task for all values in column C.

Task 9
On "Student List" worksheet, add a function in cell F2 to generate a random number between 100 and 999. Repeat the task for the cell range F3:F19. Copy only the values from cell range F2:F19 to range E2:E19. Write "Password Suffix" in cell E1 and delete all values from column F.

Task 10
On "Student List" worksheet, type "Temporary Password" in cell F1 and then use a function to combine values in B2 and E2 to populate cell F2. Repeat the task for all values in columns B and E.