## You are learning Sorting and Filtering in MS Excel

How to filter for top or bottom N items in Excel?

There are two main approaches to filtering for top or bottom N items in Excel:

Method 1: Using LARGE/SMALL function with FILTER (Excel 365 or Excel with dynamic array functionality)

1. Identify your data range: Determine the range of cells containing the values you want to filter (e.g., A1:A10).

2. Choose top or bottom: Decide whether you want the top N largest values or the bottom N smallest values.

3. Define N: Specify the number of top/bottom items you want to filter (e.g., 3 for top 3).

4. Formula for top N: In an empty cell (e.g., B1), enter the formula:

`=FILTER(A1:A10, LARGE(A1:A10, ROWS($1:1)))`

- Replace `A1:A10` with your actual data range.

- This formula uses LARGE to identify the top N positions in the data (largest values to smallest).

- ROWS($1:1) creates a single-element array of 1, which is used as the k-th position argument for LARGE.

5. Formula for bottom N (modify LARGE):

Replace `LARGE` with `SMALL` in the formula above to get the bottom N smallest values.

`=FILTER(A1:A10, SMALL(A1:A10, ROWS($1:1)))`

6. Drag the formula down: Drag the formula in cell B1 down to the desired number of rows (e.g., B2:B4 for top 3). This will populate the top/bottom N values based on your criteria.

Method 2: Using Advanced Filter (For all Excel versions)

1. Copy your data: Copy your data range (including headers) to a new location (e.g., E1:F11).

2. Insert a criteria range: Below your copied data, leave a row empty for your criteria. In the next row (e.g., G2), enter the header "Rank".

3. Formula for Rank (top N): In the Rank cell (G2), enter the formula:

`=LARGE($A$1:$A$10, ROWS($G$2:G2))`

- Replace `$A$1:$A$10` with your original data range.

- This formula assigns a rank (from 1 being highest) to each value in your data.

4. Formula for Rank (bottom N):

Replace `LARGE` with `SMALL` in the formula above to get ranks for the bottom N smallest values.

5. Copy Rank formula down: Drag the formula in cell G2 down to match the number of rows in your data (e.g., G3:G11).

6. Sort by Rank: Select your entire copied data range (including the Rank column). Go to the "Data" tab and click "Sort".

7. Sort by Rank criteria:

- Sort by the "Rank" column (e.g., column G) first.

- Choose "Descending" order for top N and "Ascending" for bottom N.

- Optionally, sort by your original data column (e.g., column A) in a second level (ascending/descending as needed).

8. Keep top/bottom N rows: Select the desired number of top/bottom rows (e.g., top 3 rows).

9. Copy and paste values: Right-click and choose "Copy" only values. Paste them back to your original location (overwriting if needed).

Both methods achieve the same result. Choose the one that best suits your Excel version and workflow.