How to Sort in Excel

There are many powerful sort functions in Excel. But who hasn’t sorted a list and then messed up the data? When you’re sorting data alphabetically or by a custom order, every so often, you might make a mistake or simply want to change something. If you don’t have a backup of the original data, it’s often difficult to fix.

We’re going to walk through fixes to common sorting issues in Excel. We’re also going to show how you can sort data with another free tool, KNIME Analytics Platform, that won’t mess up your data.

Whether you stick with Excel or explore the alternative solution with KNIME, you’ll end up with a better way to sort your data.

One of the most common recommendations you find on Google for sorting data in Excel is to back up your data before you sort it. 

That’s because, with each sorting operation, you’re modifying the data itself. When you’re finished, you have your table of modified data, but no record of how you did it. So if you made a mistake, the data will be messed up, and you have to dig out the backup and start again. 

It’s a bit like cooking without a recipe. If you adjust the ingredients on the fly, you end up with a final dish but no record of how you made it. If something goes wrong, it’s hard to find and correct the mistake. 

With KNIME, you start with the recipe. You define your series of sorting operations and build a so-called “workflow” – the “recipe.” It could look like this:

You run your sorting process by sending the data through the workflow. The advantage is that the source data remains intact. And with the workflow, you have a repeatable process that you can use again and again.

Let’s get into fixing sorting issues in Excel and avoiding sorting issues in KNIME. If you want to try out KNIME, the workflow shown above is available to download here.

If headers aren’t properly selected, you end up sorting the data including the headers and the column names move out of place. This messes up the entire dataset.

To fix this in Excel:

1. Select your data range (including the headers)
2. Go to Data → Sort
3. In the Sort window, check the box that says “My data has headers”
4. Choose the column you want to sort by and click OK

Tip: Format headers differently (make them bold, add a background color) so if you forget to check the box, you’ll see them in your data automatically.

When I read my data into KNIME, it automatically recognizes the column headers. That’s because, in KNIME, column headers are treated as metadata, meaning they always remain separate from the actual data. You’ll never have to worry about accidentally sorting them into your dataset.

Accidentally sorting a single column instead of the entire table jumbles your data. For example, if you were to sort just the “Customer Name” column in a table that contains “Age,” “Gender,” “Address,” etc. then those columns will no longer correspond to the correct name. It happens when you select only the column by accident.

To fix this in Excel, you can either:

1. If only one column is selected, when you click Data → Sort, ensure “Expand the selection” is selected before confirming.
2. Use Undo (Ctrl + Z) immediately if sorting goes wrong.

Tip: Ctrl + A is your friend. Always select the entire table before sorting. Or convert your data into an Excel Table (Ctrl + T) to ensure sorting applies to the entire dataset automatically.

The Sorter node in KNIME applies sorting rules to the entire dataset at once. Think of it as having a converted Excel Table automatically. The advantage is that you don’t have to manually remember to convert your Excel file first or remember to press Ctrl + A to select all of your data before you start.

Below, you can see the Sorter window. Here I can apply as many different sort operations as I want. I can move each one up or down, depending on which one needs to be done first. I can delete them easily or go back and add more if I need to.

I can easily keep track of my changes to the data in the preview shown below my workflow.

If hidden rows (or columns) aren’t unhidden before sorting, they stay in place because Excel sorts the visible columns or rows but leaves anything that’s hidden unchanged. This causes inconsistencies.

To fix this in Excel:

• Select the entire sheet (Ctrl + A).
• Right-click any row number and select Unhide.
• Now sort your data as usual and reapply any filters as needed.

Tip: Use Filters instead of manually hiding rows. Sorting will work correctly in Excel when filtering is used.

KNIME processes all data in a table: There’s no concept of hidden rows disrupting sorting. Every row is considered unless explicitly filtered out beforehand. (Note that the Excel Reader node includes a setting to leave out hidden rows if you want to.)

Excel sometimes struggles when it has to sort by colors, icons, or custom lists and requires additional manual adjustments.

To sort by a custom list (e.g. low, medium, high):

1. Click Order → Custom List
2. Manually enter values in the correct order
3. Click OK to sort

In KNIME, the process for creating a custom list is similar to Excel. It actually took me a little longer, but the benefit is that the rule is transparent. 

I used the Rule Engine to create my custom list. I assembled the rule from the Column List and Function menus.

I can easily find this custom list in my workflow, and if I need to, I can remove it or change it by changing the settings in the Rule Engine window. 

When you’re moving a column to a different position in the table, e.g. to make your report easier to read or further analysis easier to follow, you might easily overwrite an existing column by accident. Moving columns can also break references in the formulas you’ve inserted.

How to avoid overwriting an existing column in Excel: Always insert a blank column before you move your data to avoid overwriting.

How to avoid breaking references in formulas in Excel:

• Convert to the normal range before moving (Table Design → Convert to Range).
• Reapply formatting after moving.

In KNIME, you can reorder columns to match a specific sequence in the window of the Column Resorter node. 

Excel and Google Sheets are probably the most used alternatives for project management, financial reporting, sales prospecting, and more. Oftentimes you feel you’re doing just fine with spreadsheets and they’re free. But it’s worth trying out alternatives. While you might initially need a bit longer to set up your workflow to sort your data, once it’s built, you can keep on using it over and over again. Just set your data to flow through it.

Download KNIME Analytics Platform (it’s open source and free to use) and try out the workflow shown in this article: How to sort data in an Excel spreadsheet.