When you’re working with a lot of data within a large spreadsheet, you know that organization is key. If you can’t comfortably read and analyze the information, your spreadsheet is probably not fulfilling its purpose. This is why learning how to sort data in Excel is so pivotal to reorganizing data and making your spreadsheet configuration much more manageable.
So, in today’s guide, we are going to cover everything you need to know about sorting in Excel, including the step-by-step process for executing different sorting parameters.
The Sort Function
In essence, the sort function in Excel lets you arrange the values or data points within a selected range based on specific parameters. It is most common to choose a specific column and then sort all of the values within that column so that there is a coherent order. For example, you may choose to order text values of a given column alphabetically or by numerical values from smallest to largest. However, these are not the only ways to sort portions of your data in Excel.
How To Sort Alphabetically
Sorting your data alphabetically is one of the most efficient ways to organize datasets that are mostly or entirely comprised of text values. For example, if you have a long list of products in one of your columns, you can rearrange the names from A to Z (or Z to A) so that you can quickly find specific items as needed.
Follow these steps to sort your data alphabetically in Excel:
- Choose a cell in the column that you want to sort alphabetically
- Navigate to the Data tab
- Select “Sort & Filter”
- Click on “A to Z” or “Z to A”
How To Sort By Color
Color-coding your spreadsheet can help make the information much easier to read. However, if you’ve edited your spreadsheet since adding the colors, you may need to sort your data based on color to ensure that it is still well-organized. It is important to note that you can sort by either cell color or font color.
Follow these steps to sort by color in Excel:
- Choose a cell within the column you want to sort by color
- Navigate to the Data tab
- Select “Sort & Filter”
- Find and select the column you want to sort in the sort dialog box
- Under “Sort On,” choose Cell Color or Font Color
- Under “Order,” choose the color you want to use to sort the data
- (Optional) Sort using more than one color by selecting “Add Level”
How To Sort By Number
Sorting by number is by far one of the most commonly-used sort functions in Excel. Since many spreadsheets are filled with rows and rows of numbers, sorting by color or letter is not always that useful. Fortunately, it is very easy to sort by numbers — either low to high or high to low.
Follow these steps to sort by number in Excel:
- Choose a cell within the column you want to sort by number
- Navigate to the Data tab
- Select “Sort & Filter”
- Click on the icon for Low to High or High to Low
How To Sort By Date
Sorting dates manually takes forever, and depending on the type of data you need to analyze, automatic date sorting could be extremely useful. For example, if you want to examine sales numbers for a particular period of time, you’ll need to organize your spreadsheet so that you can quickly find the time period in question. With the Excel sort function, you can sort by date or time, either from oldest to newest or newest to oldest.
Follow these steps to sort by date (or time) in Excel:
- Choose a cell within the column you want to sort by date or time
- Navigate to the Data tab
- Select “Sort & Filter”
- Click on the icon for Oldest to Newest or Newest to Oldest
How To Sort By Column
Sorting within one column is great, but what if you want to sort multiple columns at once? How does multi-column sorting even work? While it may seem complex at first glance, sorting using multiple sections and parameters is relatively straightforward. For example, if you have one column with Plant Species and one with Plant Classes, you could first sort Plant Species alphabetically and then sort by Plant Class. Excel will allow you to sort by up to 64 columns at a time, which is more than enough for most users.
Follow these steps to sort by column in Excel:
- Choose any cell within the range of columns you want to sort
- Navigate to the Data tab
- Select “Sort & Filter”
- In the sort dialog box, choose the first column you want to sort under the “Column” section (for best results, make sure your columns have headings)
- Under “Sort On,” choose the sorting method you want to use (alphabetical, number, color, etc.)
- Based on your sorting method, select how you want the data to be sorted (A to Z, smallest to largest, etc.)
- Select “Add Level” to add another column; repeat steps 2 through 6 as needed
- Continue until you’ve added all of the columns you want to sort
Dynamically Sort A Range
If you have data that is regularly updated, you’ll want to ensure that your sort function can automatically change accordingly. This way, you won’t have to manually adjust your sort settings every time you change the underlying data. Keep in mind that sorting data arrays dynamically is a bit more complicated and the exact formula will vary based on what kind of sorting you want.
That said, you can follow these steps to dynamically sort a range in Excel:
- Open the formula bar
- Enter this formula: =SORT(array, [sort_index], [sort_order], [by_col])
- SORT = the function you want to execute
- Array = the range you want to use
- Sort_index = the row or column to sort by
- Sort_order = the order in which values will be sorted
- By_col = a value indicating how the data should be compared
- Execute the formula and confirm that your data is updating correctly
Naturally, this is just one formula with which you can begin sorting your data dynamically. If you need additional help sorting data with dynamic formulas, feel free to reach out to the experts at Geeker for assistance!
Custom Sorting
Excel includes some of the most useful ways to sort data as built-in functions. However, you won’t always have datasets that conform to the standard sorting options. For instance, you may have unique parameters that don’t exist by default in Excel, like descriptive categories that can be attributed to numerical values (small, large, cold, hot, etc). Custom sorting ensures that, no matter what kind of data you have in your spreadsheet, you can define your own orders to sort it in a way that works for you.
Follow these steps to create custom sorting lists in Excel:
- Choose a column in your worksheet to define your sorting values
- Type each value in its own cell within the column (arrange these values in the order you want to sort them)
- Highlight all the cells containing your sorting values
- Go to File > Options > Advanced
- In the General section, select “Edit Custom Lists”
- Choose “New Entry” in the Custom Lists box
- Click “Import” to finalize
Need help from dependable Excel experts? Reach out to Geeker today for on-demand IT and software solutions!