Assignments OF Class 4: Data Sorting and Filtering in Excel

Rashmi Mishra
personRashmi Mishra
October 07, 2024

  

Assignments OF Class 4: 

Data Sorting and Filtering in Excel

Assignment 1: Sorting a Dataset

  1. Objective: Practice sorting data in ascending, descending, and custom orders.
  2. Instructions:
    • Open a dataset containing information about employees with the following columns: Employee ID, Name, Department, Hire Date, and Salary.
    • Sort the data by:
      • Employee Name in ascending order (A to Z).
      • Salary in descending order (highest to lowest).
      • Perform a custom sort where the data is sorted first by Department and then by Hire Date within each department.
    • Take a screenshot of each result and submit it with your explanation.

Assignment 2: Filtering Data with AutoFilter

  1. Objective: Apply AutoFilter to view specific subsets of data.
  2. Instructions:
    • Use the same dataset from Assignment 1.
    • Enable AutoFilter for the entire dataset.
    • Filter the data to display only employees in the "Marketing" department.
    • Further filter the "Marketing" department results to show only employees with a salary greater than $60,000.
    • Take screenshots of the filtered data and explain the steps taken.

Assignment 3: Custom Filters

  1. Objective: Practice using custom filters to display specific data.
  2. Instructions:
    • Open a dataset containing information about products in a store, with the following columns: Product ID, Name, Category, Price, and Stock Quantity.
    • Apply a custom filter to:
      • Show only products in the "Electronics" category.
      • Further filter the results to show products with a price between $100 and $500.
      • Filter products that are in stock (Stock Quantity > 0).
    • Take a screenshot of each result and describe the steps you used to achieve the filtering.

Assignment 4: Advanced Filtering with Criteria

  1. Objective: Use advanced filtering techniques with multiple conditions.
  2. Instructions:
    • Open a dataset containing sales data with the following columns: Order ID, Product, Salesperson, Region, Date, and Total Sales.
    • Create an advanced filter to:
      • Display only sales from the "North" region where the total sales are greater than $10,000.
      • Use a criteria range to filter records where the "Salesperson" is either "John" or "Emily," and the "Total Sales" is greater than $5,000.
    • Submit a screenshot of the results and describe how you set the criteria.

Assignment 5: Sort and Filter Combined Task

  1. Objective: Combine sorting and filtering to analyze data effectively.
  2. Instructions:
    • Open a dataset containing student records with the following columns: Student ID, Name, Major, GPA, and Graduation Year.
    • Sort the data by Graduation Year in ascending order.
    • Then, filter the data to display only students with a GPA greater than 3.5.
    • Sort the filtered data by GPA in descending order.
    • Submit a screenshot of the final sorted and filtered data and provide a step-by-step explanation of your actions.

Assignment 6: Creating a Custom Sort

  1. Objective: Understand how to apply custom sorts for specific criteria.
  2. Instructions:
    • Use a dataset containing project management data with the following columns: Project ID, Project Name, Priority (High, Medium, Low), Start Date, and Due Date.
    • Create a custom sort where projects are sorted first by Priority (High, Medium, Low) and then by Due Date in ascending order.
    • Submit a screenshot of the result and explain how you applied the custom sort.

Assignment 1: Sorting a Dataset

Objective: Practice sorting data in ascending, descending, and custom orders.

Dataset: Employee details with the following columns: Employee ID, Name, Department, Hire Date, and Salary.

Steps and Solutions:

  1. Sort by Employee Name in Ascending Order:
    • Step 1: Select the entire data range (including the headers).
    • Step 2: Go to the "Data" tab in the Excel ribbon.
    • Step 3: Click "Sort A to Z" (under the Sort & Filter group).
    • Result: The data will now be sorted alphabetically by Employee Name (A to Z).
  2. Sort by Salary in Descending Order:
    • Step 1: Select the data range.
    • Step 2: In the "Data" tab, click "Sort" (instead of using A to Z).
    • Step 3: In the "Sort" dialog box:
      • Choose "Salary" under the "Sort by" drop-down.
      • Select "Largest to Smallest" under the "Order" field.
    • Step 4: Click "OK."
    • Result: The data will be sorted by Salary, from highest to lowest.
  3. Custom Sort by Department and Hire Date:
    • Step 1: Select the data range.
    • Step 2: Go to "Data" → "Sort."
    • Step 3: In the "Sort" dialog box:
      • First, sort by Department in alphabetical order.
      • Then click "Add Level" and sort by Hire Date in ascending order (earliest first).
    • Step 4: Click "OK."
    • Result: The data will be grouped by Department, and within each department, sorted by the Hire Date.

Assignment 2: Filtering Data with AutoFilter

Objective: Apply AutoFilter to view specific subsets of data.

Steps and Solutions:

  1. Enable AutoFilter:
    • Step 1: Select the entire data range (including headers).
    • Step 2: Go to the "Data" tab and click "Filter."
    • Result: Drop-down arrows appear in each header cell.
  2. Filter by Marketing Department:
    • Step 1: Click the drop-down arrow in the "Department" column.
    • Step 2: Uncheck "Select All" and then check "Marketing."
    • Step 3: Click "OK."
    • Result: Only rows with "Marketing" in the Department column will be displayed.
  3. Further Filter by Salary > 60,000:
    • Step 1: Click the drop-down arrow in the "Salary" column.
    • Step 2: Select "Number Filters" → "Greater Than."
    • Step 3: Enter "60000" and click "OK."
    • Result: Only employees in the Marketing department with a salary greater than $60,000 will be displayed.

Assignment 3: Custom Filters

Objective: Practice using custom filters to display specific data.

Dataset: Product details with columns: Product ID, Name, Category, Price, and Stock Quantity.

Steps and Solutions:

  1. Filter by Electronics Category:
    • Step 1: Click the drop-down arrow in the "Category" column.
    • Step 2: Uncheck "Select All," then check "Electronics."
    • Step 3: Click "OK."
    • Result: Only products in the Electronics category are displayed.
  2. Filter by Price Between $100 and $500:
    • Step 1: Click the drop-down arrow in the "Price" column.
    • Step 2: Select "Number Filters" → "Between."
    • Step 3: Enter "100" and "500" in the dialog box, then click "OK."
    • Result: Only products with prices between $100 and $500 will be displayed.
  3. Filter by Stock Quantity > 0:
    • Step 1: Click the drop-down arrow in the "Stock Quantity" column.
    • Step 2: Select "Number Filters" → "Greater Than."
    • Step 3: Enter "0" and click "OK."
    • Result: Only products that are in stock will be displayed.

Assignment 4: Advanced Filtering with Criteria

Objective: Use advanced filtering techniques with multiple conditions.

Steps and Solutions:

  1. Create Criteria Range:
    • In empty cells (say H1

), set up the following:

      • In cell H1, write "Region"; in cell I1, write "Total Sales."
      • In cell H2, write "North"; in cell I2, write ">10000."
  1. Apply Advanced Filter:
    • Step 1: Select the original dataset range (Order ID, Product, etc.).
    • Step 2: Go to "Data" → "Advanced" (under the "Sort & Filter" group).
    • Step 3: In the "Advanced Filter" dialog box:
      • Select "Filter the list, in place."
      • For "Criteria range," select the criteria range (H1

).

      • Click "OK."
    • Result: Only orders from the North region with sales greater than $10,000 will be displayed.
  1. Advanced Filter for Salesperson and Total Sales:
    • In another criteria range (say K1

), write the following:

      • In K1, write "Salesperson"; in L1, write "Total Sales."
      • In K2, write "John" and in K3, write "Emily."
      • In L2

, write ">5000."

    • Step 1: Go to "Data" → "Advanced."
    • Step 2: In the dialog box:
      • Select "Filter the list, in place."
      • Set "Criteria range" to K1

.

      • Click "OK."
    • Result: Only records for John or Emily with sales over $5,000 will be displayed.

Assignment 5: Sort and Filter Combined Task

Objective: Combine sorting and filtering to analyze data effectively.

Steps and Solutions:

  1. Sort by Graduation Year in Ascending Order:
    • Step 1: Select the dataset.
    • Step 2: Go to "Data" → "Sort."
    • Step 3: In the dialog box, select "Graduation Year" under "Sort by" and choose "Oldest to Newest."
    • Result: The data is sorted by Graduation Year in ascending order.
  2. Filter by GPA Greater Than 3.5:
    • Step 1: Click the drop-down arrow in the "GPA" column.
    • Step 2: Select "Number Filters" → "Greater Than."
    • Step 3: Enter "3.5" and click "OK."
    • Result: Only students with a GPA greater than 3.5 will be displayed.
  3. Sort Filtered Data by GPA in Descending Order:
    • Step 1: With the filtered data still selected, go to "Data" → "Sort."
    • Step 2: Choose "GPA" and select "Largest to Smallest."
    • Result: The filtered data is now sorted by GPA, from highest to lowest.

Assignment 6: Creating a Custom Sort

Objective: Understand how to apply custom sorts for specific criteria.

Steps and Solutions:

  1. Custom Sort by Priority and Due Date:
    • Step 1: Select the project management dataset.
    • Step 2: Go to "Data" → "Sort."
    • Step 3: In the dialog box:
      • First, sort by "Priority."
      • Choose "Custom List" and define the order as High > Medium > Low.
      • Click "Add Level" and then sort by "Due Date" in ascending order.
    • Step 4: Click "OK."
    • Result: Projects are sorted by Priority (High first, then Medium, and then Low), and within each priority, sorted by Due Date.

 

Tags

You Might Like

Show more
Assignment
Share to other apps
Copy Post link