Google Sheets offers a wide range of functions that can simplify your data management tasks. One of the most powerful combinations is the SORT and FILTER Functions in Google Sheets. If you’re looking to analyze and display your data in a clean, organized manner, these two functions can be incredibly helpful. In this blog post, we’ll walk you through an example that uses both of these functions to extract and sort data.
Example: SORT and FILTER Functions Sales Data
We will work with a sales dataset that includes two columns: Sales Agent and Sales. In this example, we want to identify the Top 5 Sales Agents and the Bottom 5 Sales Agents based on the sales figures. Here’s the data we’re working with:
Applying the SORT and FILTER Functions
In this case, we’ll use a combination of SORT and FILTER to extract the data. We will apply these functions to get the Top 5 Sales Agents and the Bottom 5 Sales Agents based on the sales figures.
Here’s how it works:
Formula to Get the Top 5 Sales Agents:
=SORT(FILTER(A4:B20, B4:B20 >= LARGE(B4:B20, 5)), 2, -1)
Let’s break this down:
- FILTER(A4, B4>= LARGE(B4, 5)): This part of the formula filters the sales agents with sales figures equal to or greater than the 5th largest value.
- SORT(…, 2, -1): The SORT function sorts the filtered data in descending order based on the second column (sales figures), where -1 represents descending order.
Formula to Get the Bottom 5 Sales Agents:
=SORT(FILTER(A4:B20, B4:B20 <= SMALL(B4:B20, 5)), 2, -1)
Here’s the breakdown:
- FILTER(A4, B4<= SMALL(B4, 5)): This filters the sales agents with sales figures equal to or less than the 5th smallest value.
- SORT(…, 2, -1): As before, the SORT function sorts the filtered data in descending order based on the sales figures.
The Results
After applying the formulas, here are the Top 5 Sales Agents and the Bottom 5 Sales Agents:
Understanding the Formula
- SORT: This function allows you to arrange data in either ascending or descending order. You can specify the column you want to sort by and whether you want it sorted in ascending or descending order.
- FILTER: This function filters your dataset based on the criteria you provide. In this case, the criteria were based on the sales figures.
- LARGE/SMALL: These functions are used to get the top or bottom N values from a range. In our example, we used LARGE to extract the top 5 sales and SMALL to extract the bottom 5 sales.
Why Use SORT and FILTER?
These functions are essential tools for anyone working with large datasets in Google Sheets. Here’s why:
- 💡 Time-Saving: Automatically sort and filter data based on specific criteria without manually rearranging the dataset.
- 📊 Accurate: Extract data dynamically, ensuring that your sorted and filtered data stays updated as the underlying dataset changes.
- 🚀 Efficient: Especially helpful when you need to focus on top performers or underperformers in your data, as we did with sales agents.
Conclusion
By combining SORT and FILTER functions, you can easily manage and analyze your data in Google Sheets. In this example, we successfully extracted and sorted the Top 5 and Bottom 5 sales agents from a dataset, showcasing how powerful these functions can be when used together.
Visit our YouTube channel to learn step-by-step video tutorials
Youtube.com/@NeotechNavigators