Let’s begin with two essential data management techniques in Excel: sorting and filtering.
Excel provides several built-in tools to manage data efficiently, and sorting and filtering are among the most useful. The filter tool allows you to focus on specific values in a column, while the sort tool helps arrange data by date, numbers, alphabetically, and more. With your dataset ready, apply sorting and filtering to organise your data and gain a clearer understanding of it.
In summary, Excel offers a basic sort option such as Oldest to Newest or Newest to Oldest, which applies to one column at a time. For more flexibility, you can use the Custom Sort feature, which allows sorting based on a user-defined list or multiple criteria.
The default sorting options in Excel vary depending on the type of data in a column. For instance, a column like final_delivery_status contains text, so it can be sorted alphabetically in ascending or descending order. A column like dispatch_date contains dates, so the sorting options are from oldest to newest or vice versa. Excel also allows you to create custom lists and sort data according to that sequence.
In a previous demonstration, you observed sorting at two levels: first, by final_delivery_status using a custom list (e.g., delivered, returned, cancelled), and second, by dispatch_date.
Filters
You may already be familiar with basic filters in Excel. Now, let’s explore the Advanced Filter, which allows you to apply more complex criteria that simple filters cannot handle. Advanced filters provide better visibility of the applied conditions and even allow you to copy the filtered data to another sheet. In the next video, you will learn how to use the Advanced Filter with AND conditions.
As demonstrated earlier, when using the Advanced Filter in Excel, placing criteria side by side is treated as an AND condition. This means all conditions must be satisfied for a row to be included in the filtered results.
But what if you want to filter rows where at least one of the conditions is met? In that case, you can use an OR condition in the Advanced Filter.
In the next video, you will learn how to apply an Advanced Filter with an OR condition.
To summarise, when using the Advanced Filter in Excel, placing criteria side by side is considered an AND condition by default.
In the previous example, writing the criteria side by side extracted only the orders that satisfied all of the following conditions:
- The destination city is 'Delhi'.
- The origin city is 'Delhi'.
- The actual delivery date is before the promised delivery date.
- The final delivery status is 'Returned'.
- The product name contains 'TV'.
However, when two different values are written in a column, one below the other, Excel treats it as an OR condition.
Using the method described above, the criteria will extract orders that satisfy either of the following conditions:
- The destination city is Delhi, and the origin city is Bangalore.
- The destination city is Bangalore, and the origin city is Delhi.
Attempt the following questions to test your understanding of this concept.
Note: Use the Ecommerce_demo dataset to answer the questions.
Now, let’s move on to the next segment: Text Functions.