Assignment 1

After watching the video introduction about navigation, cell addresses, formula bar, and the Excel interface, reflect on what you have learned and how it can help you in your future work with Excel. Your assignment should address the following:

1. Understanding the Interface: Describe the main components of the Excel interface covered in the video. How do these components contribute to your ability to work efficiently in Excel?
2. Cell Addresses and Navigation: Explain what cell addresses are and how they are used in Excel. Reflect on the strategies for navigating through a spreadsheet that were discussed in the video. How can these skills improve your workflow?
3. Using the Formula Bar: Discuss the importance of the formula bar in Excel. How does it help you when working with data and formulas?
4. Practical Application: Provide an example of how you can apply the concepts from the video to a real-world scenario or task. Describe the specific Excel functions or features you would use and why.

Your reflection should be approximately 500 words in length. Be sure to provide specific examples from the video and your own experiences to support your points. Aim to be clear and concise in your writing.

Instructions

1. Define the following terms in one or two lines: 30 Marks
   • Tabs
   • Ribbon
   • Groups
   • Name Box
   • Formula Bar
   • Status Bar
2. How cells can be formatted and how many formatting are there in the numbers group of the Home tab? 25 Marks
3. Define Data Bars in conditional formatting and their common uses. 15 Marks
4. Submit an Excel file of .xls or .xlsx extension in which you create daily income, expense, profit, and loss data with the sum function and every column formatted. Additionally, show that profit amount with data bars in the next column. 25 Marks

Instructions

1. Define the following purposes of basic formatting in a single paragraph: 50 Marks
   • How data formatting helps in Professional Looking of data?
   • How data formatting helps users to read it easily?
   • How data formatting helps users in Data Visualization?
   • How data formatting helps in highlighting key information?
   • How data formatting helps in time-saving for the user?
   
   

   10 Marks for every correctly briefed paragraph.

2. Submit an Excel file of .xls or .xlsx extension in which you created daily income, expense, profit, and loss data sheet file in your previous assignment with a new sheet having the same data with basic formatting, fonts, coloring, alignment, borders, and center across selection 45 Marks
   
   
   • 10 Marks for submitting xlsx or xls file.
   • 7 Marks for every applied formatting.

Instructions

1. Create a data set with columns: Name, D.O.B., Salary, Gender, Employer ID, and Country 05 Marks
   • Data set must have 30 rows.
2. Then Calculate with help of Excel functions:

   Calculation	Marks
   Sum of Salaries	12 Marks
   Total number of Employees	12 Marks
   Male Employees	12 Marks
   Female Employees	12 Marks
   Avg Salary	12 Marks
   Median Salary	12 Marks
   Maximum Salary	07 Marks
   Minimum Salary	07 Marks

Instructions

1. Describe two different uses of the Format Painter and how to apply it in two different ways? 20 Marks
2. Briefly describe five different uses of the fill handle. 25 Marks
3. Briefly describe three different uses of filters and how to apply them. 30 Marks
4. Submit the Excel file you created in the last assignment, with the age of each employee calculated using the DATEDIF() function, referencing the D.O.B. column. Apply a filter to the data using any option. 20 Marks

Instructions

1. Describe 11 different data formats in Excel in only one line each. 55 Marks
2. Create a duplicate sheet of your previously created data set and apply the following: 40 Marks
   • Currency format to the currency column
   • Date format as ‘dd-mmm-yyyy’
   • Insert two columns to the right side of the Name column with the column names ‘First Name’ and ‘Last Name’
   • Use Flash Fill to populate these columns with the first and last names respectively

Instructions

1. Describe concatenation in one paragraph and identify where concatenation is used in this video with examples. 20 Marks
2. Define three uses of Find and Replace in Excel with examples. 30 Marks
3. In your previously created data, replicate a sheet with the name of Assignment 7 and: 10 Marks
   • Find the age of employees with the help of the YEARFRAC() function. 15 Marks
   • Replace the first 25 employees' IDs starting with AU (e.g., E0001 to AU0001) using Find and Replace. 10 Marks
   • Replace the last five employees' IDs starting with CA (e.g., E0001 to CA0001) using Find and Replace. 10 Marks

Instructions

1. Describe any five uses of custom formatting with examples. 50 Marks
2. In your previously created data, replicate a sheet with the name of Assignment 8 and: 10 Marks
   • Insert a row or two above the data and give a heading that covers all columns. The heading should be center-aligned using any option. 10 Marks
   • Create a column for Phone Numbers and apply custom formatting for phone numbers so that when you enter only the number, the country code will appear for all Australian employees. 10 Marks
   • Apply custom formatting for the phone numbers of the last five employees according to their country with their dialing code. 15 Marks

Instructions

1. Describe any three benefits of working with multiple sheets. 30 Marks
2. Create a new workbook named ‘Assignment 9’ and: 10 Marks
   • Create 7 sheets in this workbook named them with days name from ‘Monday’ to ‘Sunday’ then apply method of working on multiple sheets as discussed in this lesson. 10 Marks
   • Create a sales data of employees working in a furniture store with columns, ‘S.No’, ‘Name’, ‘Total Sales Amount’ try using the same method of working in multiple sheets but with different sales amounts. 10 Marks
3. Create an 8th sheet with the name ‘Summary’ and Calculate: 35 Marks
   • Total weekly Sale.
   • Max sale of every day.
   • Min sale of every day.

Instructions

1. Create a new sheet in your previous assignment file. 10 Marks
2. Create a payroll for every employee in this sheet. 25 Marks
   • Columns should include Employee ID, Name, Hours Worked, Per Hour Rate, Pay
3. Calculate pay for every employee using the function explained in the lesson or any other method of your choice. 20 Marks
4. Keep in mind that time should be in HH:MM format and in a single cell. Convert this into a calculated integer value in the next column for ease of calculation. 40 Marks

Instructions

1. Describe any three uses of entering data in a cell using an apostrophe: 30 Marks
2. Define LEFT, RIGHT, and FIND function in one paragraph with examples: 30 Marks
3. In this assignment, you need to calculate the overtime of employees who worked more than 7 hours and 21 minutes in the same sheet of your last assignment. Then calculate the total salary adding overtime to the salary in a new column. 35 Marks

Instructions

1. Define relative reference and absolute reference with examples: 30 Marks
2. In your previously created payroll sheet:
   • Take relative reference from the employee sheet of Employee ID and Employee Name. 20 Marks
   • In an empty cell type Per Hour Rate and in the next cell type that rate. Now take absolute reference from this cell to calculate salary and overtime. 45 Marks

Instructions

1. Create a new worksheet with previous assignment named ‘Assignment 13’ and: 10 Marks
2. In an empty cell type Tax and in the next cell type value of tax then calculate Tax from everyone’s salary in the next column where you calculated salary but with help of absolute reference from Tax value cell. 40 Marks
3. In the next column calculate the net salary of every employee by deducting Tax from their salaries. 20 Marks
4. Calculate Min, Max, Average, and Median Salary in the same sheet. 25 Marks

Instructions

1. Describe LARGE(), SMALL(), INDEX(), and MATCH() functions with examples: 40 Marks
2. Differentiate between LARGE() vs MAX() and SMALL() vs MIN() functions: 20 Marks
3. Create a new worksheet with previous assignment named ‘Assignment 14’ and: 05 Marks
4. In the previous assignment you calculated every employee salary with the same per hour rate, but it could not be the case in most cases. Now you have to calculate with a different per hour rate for every employee. In the per hour rate column change per hour rate and calculate salary, tax, and net salary accordingly: 10 Marks
5. Calculate Top 5 Earners: 05 Marks
6. Calculate Least 5 Earners: 05 Marks
7. Match the salaries with the names of employees of every Top 5 and Least 5 Earners using INDEX and MATCH functions: 10 Marks

Instructions

1. Describe five different uses of Special Paste used in Excel: 20 Marks
2. Describe ROUND() and TRANSPOSE() functions with examples: 20 Marks
3. Create a new workbook with name ‘Class Record’ name sheet1 ‘Record’ and second sheet ‘Result Card’: 15 Marks
4. In Record sheet, name columns, S.No, Name, Roll No., Science, Maths, English, Social Studies, History and type 10 students record in these columns: 10 Marks
5. In Result Card sheet create result card of every student with the help of TRANSPOSE() function as shown in lesson 16: 10 Marks
6. Calculate in Result Card sheet, Total Marks, Marks Obtained, Percentage, and Grade (Calculate Grade using IFS() function): 10 Marks
7. Use relative and obsolete reference in Result Card sheet for student name, student roll no., subject names, subject marks: 10 Marks

Instructions

1. Explain Data Validation five different uses and examples: 20 Marks
2. Describe UNIQUE() and SUMIF(), SUMIFS() functions with examples: 15 Marks
3. Create a new workbook with name ‘Sales’, name sheet1 ‘Sales’ and name columns, Invoice No, Item, Category, Quantity, Amount and enter 15 records with 8 different invoices: 25 Marks
4. Now calculate Total amount of day sale, Total Quantity sold, then calculate Number of invoices generated that day, Quantity of different brand item sold, Amount generated per brand: 15 Marks
5. Create any two dimensional graph having Amount in its Y axis and Brand in its X axis (for this you need to calculate it first): 20 Marks

Instructions

1. In previous assignment you created a workbook with name ‘Sales’, in this assignment you need to data validation in this sheet to find out, amount received from different category and then brand for example in this case category ‘Shoes’ and brand ‘Hyderabadi’: 25 Marks
2. Insert a new column with sales record table and name it ‘Single Item’, then applying if condition find out which item sold in single quantity with showing Yes for single quantity sold and No for more: 25 Marks
3. Create a table in which calculate revenue generated by single item sold and revenue generated by more than single item sold by brand using SUMIFS function as shown in the lesson: 20 Marks
4. Apply Data formatting techniques to your calculated data so it looks visually clear and easy to understand: 25 Marks

Instructions

1. Describe EOMONTH(), CHAR(), ROW(), and RANDBETWEEN() functions with examples: 35 Marks
2. Create a new workbook with name ‘Assignment 18’: 10 Marks
3. In this sheet create a six months sales record with columns Product, January to June, and Total: 10 Marks
4. In this table enter 16 products manually and then you are not allowed to enter any value manually. You are assigned to use RANDBETWEEN Function for monthly sales and EOMONTH function for months and AUTOSUM for calculating sum of sales per month and total: 40 Marks

Instructions

1. Describe FILTER(), CHOOSE(), SORT(), and OFFSET() functions with examples: 35 Marks
2. From lesson 14 assignment in which you had created a list of employees with tax and salary calculated, create a new worksheet in it and name it ‘Assignment 19’: 10 Marks
3. With the help of this record you need to create sorted list from higher to lower salary with the help of functions discussed not with filter options: 10 Marks
4. Create a list of Top 5 Earners from this record but with the help of FILTER, CHOOSE, and LARGE function: 15 Marks
5. Create a list of Least 5 Earners from this record but with the help of FILTER, CHOOSE, and SMALL function: 15 Marks

Instructions

1. Describe ROUNDUP(), ROUNDDOWN(), and MONTH() functions with examples: 30 Marks
2. Create a sheet of per week sales record for the year of 2023 for the same store in which you created earlier, so it will be records you can create a record using RANDBETWEEN() function. Then in the next column create a ‘Quarter’ column in which you separate sales in four quarters of year according to weekly dates using INT, and MONTH function as shown in the lesson.: 20 Marks
3. In the next column again calculate four quarters of year with the help of ROUNDUP(), function.: 10 Marks
4. Now as you calculated four quarters of year, calculate sales per quarter according to brand using SUMIF() function.: 10 Marks
5. Create a chart of sales per quarter according to brand record which displays this record and visualize the sales in graphical interface organized with the sales per week and sales per quarter record.: 25 Marks

Instructions

1. Describe ROW(), COLUMN(), VLOOKUP() and HLOOKUP() functions with examples: 40 Marks
2. Create a sheet with table for Car Wash service rate list having columns Car Size, Small, Medium, Wagon, 4WD, SUV and Bus. With having random numbers as rate for wash and polish below these sizes. 30 Marks
3. Now use functions which are discussed in the lesson to convert this horizontal table in vertical table. You are not allowed to use TRANSPOSE() Function: 25 Marks

Instructions

1. Describe XLOOKUP() function with examples and write 3 benefits of using XLOOKUP() function instead of VLOOKUP() and HLOOKUP() functions: 25 Marks
2. Create a sheet having monthly sales record from January to June of 25 different electronic appliances. You can take help from Amazon for the list of Electronic Appliances, and use RANDBETWEEN() function for sales amount per month. 20 Marks
3. Create a dropdown list of products using data validation in your desired cell. 10 Marks
4. With the help of this dropdown list and using these functions find out sales for March and April of that particular product selected in dropdown list:
   • XLOOKUP()
   • VLOOKUP()
   • FILTER() and INDEX()
   • MATCH()
   40 Marks