Excel Mastery: Crush Your Spreadsheets Like a Pro!

Data Manipulation in Excel

Part 4.1

Sorting and filtering data

Sorting and filtering data

Have you ever found yourself staring blankly at an Excel spreadsheet, wondering what it all means? Today we’re going to talk about sorting and filtering data, the dynamic duo of data manipulation in Excel.

Let’s start with sorting. Sorting is like having a magical sorting hat, but for data. Instead of assigning you to Gryffindor or Hufflepuff, Excel sorts your data based on specific criteria. Are you looking to organize your sales data by date, product, or revenue? Excel has got your back.

But what if you need to narrow down your data to specific parameters? Enter filtering. Filtering is like having a group of bouncers at a nightclub, but for data. You get to decide who makes it past the velvet rope and onto your spreadsheet guest list. Want to see all sales data from a specific month or only from a certain region? Filtering allows you to do just that. Want to sort your sales data by product and then filter to only show your top-selling items. It’s like having a mini-celebration right there in your spreadsheet.

Example:

Here’s a step-by-step guide to sorting

1. Open a new or existing Excel worksheet that contains figures you want to sort.
2. Highlight the entire range of cells that contain the data you want to sort.
3. Click on the “Data” tab in the top menu and select “Sort.”
4. In the “Sort” dialog box, select the column that contains the first level of sorting you want to apply. For example, if you want to sort by month, select the column that contains the month names.
5. Choose whether you want to sort in ascending or descending order.
6. Click the “OK” button to apply the sorting.

Real-life Example with Sample Data

Please use this data here as your source data for this demonstration. This data was randomly generated just for this course. If you are wondering how we got the formatting just right with the table, we used CelTools. CelTools has a function that will allow you to select a range of cells in Excel and have it convert over to an HTML formatted table that keeps all of the cell properties. Pretty cool for presentations if you ask me.

1. Open a new Excel worksheet and enter the sales figures for each month, region, and product/item in separate columns, like the example above (you can also just copy-and-paste the whole table as CelTools makes this preformatted just for that!).
2. Highlight the entire range of cells that contain the sales data (including the headers).
3. Click on the “Data” tab in the top menu and select “Sort.”
4. In the “Sort” dialog box, select “Month” as the first level of sorting.
5. Choose “Ascending” as the sort order for the first level.
6. Click the “Add Level” button to add a second level of sorting.
7. In the “Sort” dialog box, select “Region” as the second level of sorting.
8. Choose “Ascending” as the sort order for the second level.
9. Click the “OK” button to apply the sorting.

The sorted data should now look like this:

As you can see, the data is now sorted by sales and region in descending order. You can add more levels of sorting as needed to further refine the data.

More advance sorting

How can you sort data based on highest selling products? Well, let’s do this and just use the same data as before.

Here is a step-by-step guide on how to sort sales data by product and then filter to only show top-selling items:

1. Highlight the entire range of cells that contain the sales data (including the headers).
2. Click on the “Data” tab in the top menu and select “Sort.”
3. In the “Sort” dialog box, select “Product” as the first level of sorting.
4. Choose “Ascending” or “Descending” as the sort order for the first level depending on whether you want to sort by highest or lowest sales figures for each product.
5. Click the “OK” button to apply the sorting.
6. Once the data is sorted, click on the filter icon in the header row of the “Sales” column.
7. In the filter drop-down menu, select “Top 10” (or any other number you want to display).
8. In the “Top 10 AutoFilter” dialog box, select “Top” in the drop-down menu and enter the number of top-selling items you want to display. In this example I pick 4.
9. Click the “OK” button to apply the filter.

The filtered and sorted data should now display only the top 4 (or your chosen number) of best-selling products, as follows:

Note that the data is now sorted by product in ascending or descending order and filtered to show only the top-selling items. You can adjust the filter to show more or fewer items, or remove the filter altogether to view all the sales data.

Conclusion and a bit extra takeaway

Sorting and filtering don’t just apply to boring old numerical data. You can also sort and filter text and even color-coded cells. Feeling extra adventurous? Combine sorting and filtering with other Excel functions like conditional formatting and pivot tables for the ultimate data manipulation experience. Go ahead, give it a try and add some pizzazz to your spreadsheet game. Your data will thank you for it.