img img
The Menu of this blog is loading..........

Excel Assignment SyBCA Sem 4 | High Tech 7

High Tech 7

Excel Assignment SyBCA Sem 4


Excel Assignment - 1

1. Create a table with two columns (A & B) and 10 rows of numbers. In a new column, calculate the sum of each row using the + operator.

Steps:

  1. Enter numbers in Column A and Column B (10 rows).
  2. In Column C, type the formula to add A and B:
    • =A1+B1 (Press Enter).
  3. Drag the formula down for 10 rows to get the sum for each row.

2. Create a table with two columns: Original Price & Discount. Calculate the Final Price using subtraction.

Steps:

  1. In Column A, enter "Original Price" values.
  2. In Column B, enter "Discount" values.
  3. In Column C (Final Price), apply the formula:
    • =A1-B1 (Press Enter).
  4. Drag the formula down for all rows.

3. Enter Total Marks and Number of Subjects for students. Calculate the Average Marks using division.

Steps:

  1. In Column A, enter "Total Marks".
  2. In Column B, enter "Number of Subjects".
  3. In Column C (Average Marks), use the formula:
    • =A1/B1 (Press Enter).
  4. Drag the formula down for multiple students.

4. Enter a list of 10 numbers in a column, calculate the square of each number using multiplication.

Steps:

  1. Enter numbers in Column A.
  2. In Column B (Square), use the formula:
    • =A1*A1 (Press Enter).
  3. Drag the formula down for 10 rows.

5. Enter a list of 10 numbers in a column, calculate the square root.

Steps:

  1. Enter numbers in Column A.
  2. In Column B (Square Root), use the formula:
    • =SQRT(A1) (Press Enter).
  3. Drag the formula down for 10 rows.

6. Given Marks in 5 subjects, calculate Total Marks, Percentage & Grade using IF function.

Steps:

  1. Enter marks for 5 subjects in Columns A to E.
  2. In Column F (Total Marks), use the formula:
    • =SUM(A1:E1)
  3. In Column G (Percentage), use the formula:
    • =(F1/500)*100 (assuming each subject is out of 100).
  4. In Column H (Grade), use the IF function: 
    =IF(G1>=90,"A+",IF(G1>=80,"A",IF(G1>=70,"B",IF(G1>=60,"C",IF(G1>=50,"D","Fail")))))
  5. Drag all formulas down for multiple students.


Excel Assignment - 2 


1. Create a table with 10 products and their prices in two columns. Use the SUM function to calculate the total price for all products.

Steps:

  1. In Column A, enter product names (Product 1 to Product 10).
  2. In Column B, enter their respective prices.
  3. In Cell B11, use the SUM function:
    • =SUM(B1:B10)
  4. Press Enter to get the total price.

2. You have a list of sales amounts for 7 days. Use the MAX function to find the highest sales recorded.

Steps:

  1. In Column A, enter "Day 1" to "Day 7".
  2. In Column B, enter sales amounts for each day.
  3. In Cell B8, use the MAX function:
    • =MAX(B1:B7)
  4. Press Enter to get the highest sales amount.

3. Create a list of students and their marks. Use the IF function to display "Pass" if the student’s marks are greater than or equal to 50, otherwise show "Fail".

Steps:

  1. In Column A, enter student names.
  2. In Column B, enter their marks.
  3. In Column C (Result), use the IF function:
    • =IF(B1>=50,"Pass","Fail")
  4. Drag the formula down for all students.

4. In a table of employees, use the AND function to check if an employee’s salary is greater than 50,000 and they have been in the company for more than 5 years.

Steps:

  1. In Column A, enter employee names.
  2. In Column B, enter salaries.
  3. In Column C, enter years of experience.
  4. In Column D (Check), use the AND function:
    • =AND(B1>50000,C1>5)
  5. This will return TRUE if both conditions are met, otherwise FALSE.
  6. Drag the formula down for all employees.

5. Create a table with First Name and Last Name. Use the CONCATENATE function to join both names into a full name.

Steps:

  1. In Column A, enter first names.
  2. In Column B, enter last names.
  3. In Column C (Full Name), use the CONCATENATE function:
    • =CONCATENATE(A1," ",B1)
  4. Alternatively, use =A1&" "&B1.
  5. Drag the formula down for all names.

6. Given a date (in a cell), use the TEXT function to display it in the format "Day, Month Date, Year".

Steps:

  1. Enter a date in Cell A1 (e.g., 01/02/2024).
  2. In Cell B1, use the TEXT function:
    • =TEXT(A1,"dddd, mmmm dd, yyyy")
  3. Press Enter to display the formatted date (e.g., Thursday, February 01, 2024).

7. Create a list of names in mixed case. Use the UPPER function to convert all names to uppercase and the LOWER function to convert all names to lowercase.

Steps:

  1. Enter names in Column A (mixed case).
  2. In Column B (Uppercase), use the UPPER function:
    • =UPPER(A1)
  3. In Column C (Lowercase), use the LOWER function:
    • =LOWER(A1)
  4. Drag both formulas down for all names.

8. You have a list of product codes in the format "ABC123". Use the LEFT function to extract the first 3 characters (ABC) and the RIGHT function to extract the last 3 digits (123).

Steps:

  1. Enter product codes in Column A (e.g., ABC123).
  2. In Column B (First 3 Characters), use the LEFT function:
    • =LEFT(A1,3)
  3. In Column C (Last 3 Digits), use the RIGHT function:
    • =RIGHT(A1,3)
  4. Drag the formulas down for all product codes.


Excel Assignment - 3 


1. Create a table with 5 employees' names and their salaries. Change the font style of the employee names column to Arial, and set the font size to 12.

Steps:

  1. In Column A, enter employee names.
  2. In Column B, enter their salaries.
  3. Select all employee names (Column A).
  4. Go to the Home tab → In the Font section:
    • Select Arial as the font style.
    • Set Font Size to 12.

2. Create a list of sales data for 5 products. Format the sales amount column as Currency with two decimal places.

Steps:

  1. In Column A, enter product names.
  2. In Column B, enter sales amounts.
  3. Select Column B (Sales Amount) → Go to the Home tab.
  4. In the Number Format section, click on the drop-down and select Currency.
  5. Ensure that the number format has two decimal places.

3. Create a list of student names and their marks. Apply conditional formatting to highlight the marks greater than or equal to 50 in green and marks less than 50 in red.

Steps:

  1. In Column A, enter student names.
  2. In Column B, enter marks.
  3. Select Column B (Marks) → Go to the Home tab → Click Conditional Formatting.
  4. Click New Rule → Select Format cells based on values.
  5. Choose "Format only cells that contain", then:
    • For marks ≥ 50 → Set the condition as "Cell Value ≥ 50", click Format, choose a Green Fill, and press OK.
    • For marks < 50 → Set the condition as "Cell Value < 50", click Format, choose a Red Fill, and press OK.

4. Create a list of cities in your spreadsheet. Use conditional formatting to highlight any duplicate cities in the list.

Steps:

  1. Enter city names in Column A.
  2. Select Column A → Go to the Home tab → Click Conditional Formatting.
  3. Select Highlight Cell Rules → Click Duplicate Values.
  4. Choose a color (e.g., Light Red Fill) → Click OK.
  5. Now, all duplicate cities will be highlighted.

5. Create a list of student scores. Apply color scales to the scores, with green for high scores and red for low scores, to visually differentiate them.

Steps:

  1. In Column A, enter student names.
  2. In Column B, enter their scores.
  3. Select Column B (Scores) → Go to the Home tab → Click Conditional Formatting.
  4. Click Color Scales → Choose the Green-Yellow-Red scale (Green for highest, Red for lowest).
  5. Now, high scores will appear green, medium scores yellow, and low scores red.

6. Create a table with Employee Names and Departments. In the Department column, apply data validation to allow only the following entries from a dropdown list: HR, Sales, IT, Marketing.

Steps:

  1. In Column A, enter employee names.
  2. In Column B (Department), select all the cells where department names will be entered.
  3. Go to the Data tab → Click Data Validation.
  4. In the Allow box, select List.
  5. In the Source box, type: 
    HR, Sales, IT, Marketing
  6. Click OK.
  7. Now, only these values can be selected from the dropdown list.

7. Create a table with Product Names and Quantities. In the Quantity column, apply data validation to allow only whole numbers greater than or equal to 1.

Steps:

  1. In Column A, enter product names.
  2. In Column B (Quantity), select all the cells where quantities will be entered.
  3. Go to the Data tab → Click Data Validation.
  4. In the Allow box, select Whole Number.
  5. In the Minimum box, enter 1.
  6. Click OK.
  7. Now, users can only enter whole numbers that are 1 or greater.


Excel Assignment - 4 



1. Create a list of student names and their scores. Use a nested IF function to assign grades based on the following criteria:

  • 90 and above = "A"
  • 80 to 89 = "B"
  • 70 to 79 = "C"
  • Below 70 = "Fail"

Steps:

  1. In Column A, enter student names.
  2. In Column B, enter their scores.
  3. In Column C (Grade), use the following formula: 
    =IF(B1>=90,"A",IF(B1>=80,"B",IF(B1>=70,"C","Fail")))
  4. Press Enter and drag the formula down for all students.

2. Create a table with Product ID and Product Price. Use the VLOOKUP function to find the price of a product by using its Product ID.

Steps:

  1. In Column A, enter Product IDs.
  2. In Column B, enter Product Prices.
  3. In another cell (e.g., E1), enter the Product ID you want to look up.
  4. In Cell F1, use the VLOOKUP formula: 
    =VLOOKUP(E1,A:B,2,FALSE)
  5. Press Enter, and it will return the corresponding price for the entered Product ID.

3. Create a table with Employee Names in the first row and their respective Departments in the second row. Use the HLOOKUP function to find which department a specific employee is in.

Steps:

  1. In Row 1, enter employee names (e.g., A1, B1, C1, etc.).
  2. In Row 2, enter their departments.
  3. In Cell A4, enter the employee name you want to search for.
  4. In Cell B4, use the HLOOKUP formula: 
    =HLOOKUP(A4,A1:E2,2,FALSE)
  5. Press Enter, and it will return the corresponding department for the entered employee name.

4. Create a list of employee performance scores. Use the COUNTIF function to count how many employees have scored above 80.

Steps:

  1. In Column A, enter employee names.
  2. In Column B, enter performance scores.
  3. In Cell D1, use the COUNTIF function: 
    =COUNTIF(B1:B10,">80")
  4. Press Enter, and it will return the count of employees who scored above 80.

5. Create a table with Product Names and Sales Figures. Use the SUMIF function to calculate the total sales for a specific product.

Steps:

  1. In Column A, enter product names.
  2. In Column B, enter sales figures.
  3. In Cell D1, enter the product name you want to find total sales for.
  4. In Cell E1, use the SUMIF function: 
    =SUMIF(A1:A10,D1,B1:B10)
  5. Press Enter, and it will return the total sales for the specified product.

6. Create a list of test scores for 10 students. Use the MEDIAN function to find the middle value of the scores.

Steps:

  1. In Column A, enter student names.
  2. In Column B, enter test scores.
  3. In Cell D1, use the MEDIAN function: 
    =MEDIAN(B1:B10)
  4. Press Enter, and it will return the median score (the middle value when scores are arranged in order).






High Tech 7

Posted by High Tech 7