Scribd
Upload
14 views2 pages

Advanced+Excel+Exercises Module6

This document provides exercises for mastering advanced filtering, sorting, and dynamic arrays in Excel. It includes steps for extracting employees based on salary, sorting by multiple criteria, and utilizing dynamic array functions to generate unique lists and sort data. The exercises aim to enhance the user's ability to analyze and organize complex datasets effectively.

Uploaded by

jessthony09
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
14 views2 pages

Advanced+Excel+Exercises Module6

This document provides exercises for mastering advanced filtering, sorting, and dynamic arrays in Excel. It includes steps for extracting employees based on salary, sorting by multiple criteria, and utilizing dynamic array functions to generate unique lists and sort data. The exercises aim to enhance the user's ability to analyze and organize complex datasets effectively.

Uploaded by

jessthony09
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Advanced Excel: exercises

Module 6: Advanced Filtering, Sorting, and Dynamic Arrays

Exercise 18: Advanced Filtering and Sorting with Dynamic


Arrays
Objective: Learn how to use advanced filters, multi-level sorting, and dynamic array
formulas to analyse and organize complex datasets.

Part 1: Advanced Filtering

1.​ Extract Employees in Engineering Making More than $75,000​

○​ Select the range A1:D8 and go to Data > Advanced.


○​ In the Advanced Filter dialog box:
■​ Choose "Filter the list, in place."
■​ Set the criteria range to a separate area, such as F1:G2, and enter:

Department Salary
Engineering >75000

■​ Click OK to apply the filter.​

2.​ Result: Only Diana and Grace should appear.​

3.​ Copy Filtered Results to Another Location​

○​ Repeat the advanced filter, but this time, select "Copy to another location."
○​ Set the Copy to range as I1:L1, then click OK.

4.​ Result: Filtered data should appear in a new area.


Part 2: Sorting by Multiple Levels

1.​ Sort Employees by Department (A-Z), then Salary (Largest to Smallest)​

○​ Highlight the range A2:D8.


○​ Go to Data > Sort.
○​ Add the following sort levels:
■​ Column: Department, Order: A to Z.
■​ Column: Salary, Order: Largest to Smallest.
○​ Click OK.
2.​ Result: Employees should be grouped by department, with the highest salaries listed
first in each group.​

Part 3: Dynamic Arrays

1.​ Extract a List of Unique Departments


○​ In a blank cell (e.g., F1), enter:​
​
=UNIQUE(B2:B8)

○​ Press Enter.​

2.​ Result: A dynamic list of departments ("Marketing," "Engineering," "HR") will appear.​

3.​ Sort Employees by Hire Date


○​ In a blank cell (e.g., H1), enter:​
​
=SORT(A2:D8, 4, 1)
○​
○​ Press Enter.​

4.​ Result: The entire dataset will be sorted by hire date (oldest to newest).

You might also like