Tasks to be completed in the assignment
Important Note: Do not submit files that were created during a lecture in the lab. You are supposed to submit files that you have created at home. The purpose is to have as much practice as possible. Use as many comments and examples as possible. Marks are based on the size, examples, and comments inside the submitted assignments.
1. Show the use of VLOOKUP for the following three uses: (4.5 marks)
1.1 Merge two dataset of your own choice (Examples can be merging share prices data with index on the basis of dates)
1.2 Make a data retrieval form (Examples can be Students data, where we retrieve students information based on students registration number)
1.3 Assign quota marks if students belong from different districts (Example: Make a list of students and in second column write their domiciles. Make another list where domiciles have different points, such as Peshawar has 10, Dir has 20, Bannu has 30, etc. Then use vlookup function to assign these scores to students in the first list)
2. Macro Exercise (5.5 Marks)
2.1 Make a macro that copies data from one sheet to another and then deletes the data on the first sheet. Give this macro a short-cut of Ctr + q
2.2 Make a macro that formats a data table in the following way: a. Header row has light grey color b. Header row has bold text c. Header row has upper and lower cell borders d. The last row on the table has bottom cell border
2.3 Search internet to find and use at least 10 useful macros, assign these macros to clickable buttons
3. Protection : (3 marks)
3.1 Use a separate Excel file, name it “Protected” and make this file password protection so that no one can open it without a password of 123
3.2 Show practice on data entry in one sheet and some formulas on another sheet, using the data from the first sheet. (Example: Account data on one sheet, and Ratios on another sheet)
3.3 Protect the formulas on the second sheet with a password 123, the formula cells should not be clickable
3.4 Make a sheet, name it secret. Hide that sheet and password protect it from unhiding.
4. IF Statements: (4 marks)
4.1 Use at least 5 different examples of IF and nested IF statements.
4.2 Show the use of OR functions with IF
4.3 Show the use of AND function with IF
5. Data Validation (3 Marks)
5.1 Show example of data validation using lists
5.2 Show examples of data validation using whole numbers
5.3 Show examples of data validation using text length
6. Import and Filtering (3 Marks)
6.1 Show practice on importing delimited text
6.2 show practices on filtering records (You may use merit list file from IMSciences website
6.3 Show practices on Pivot Tables
Please note: You can do all these in one excel file or in different files. If one excel file, then given proper names to excel sheets. If different excel files, then zip all files in one folder and upload.