How to Sort in Excel by Number

If you are working with a large amount of data in Excel, sorting is a useful tool that can help you view and analyze the data more easily. While sorting by text or date is a common task, sorting by number requires a different approach. In this blog post, we will explain how to sort in Excel by number using either the Sort feature or the Custom List feature. Whether you are organizing financial data, ranking student test scores, or simply need to sort numerical values in a spreadsheet, this guide will provide you with the necessary steps to do it efficiently and accurately.

Why Sort by Number in Excel?

Sorting data is a vital part of analyzing and managing information in Excel. Often, we sort data based on text, date, or alphabetical order. However, sorting by number offers a unique advantage in dealing with numerical data. It allows us to see the highest and lowest values quickly or determine the correlation between different factors. For example, you may need to sort by sales figures to identify the top-performing products, the least profitable customers, or the highest sales numbers per region.

Sorting by Number: The Basics

Step 1: Select the Data

The first step in sorting data in Excel is to select the range of cells you want to sort. Make sure you include the entire range of cells that contain the data, including any headers or titles.

Step 2: Choose the Sort Command

You can sort your data in two ways in Excel: by using the Sort command or by using the Custom List feature. We will explain both methods, starting with the Sort command. To use this feature, go to the ‘Data’ tab and click on ‘Sort.’

Step 3: Select the Column to Sort

After selecting the ‘Sort’ command, a ‘Sort’ dialog box will appear. In this box, select the column that contains the numerical data you want to sort. You may also choose to sort by multiple columns.

Step 4: Specify the Sort Order

Now, you need to specify the sort order by choosing either ascending or descending order. Sorting in ascending order will display the lowest value first, while descending order will display the highest value first.

Step 5: Sort the Data

Finally, click on ‘OK’ to sort your data by number. Your data should now be sorted according to the values in the selected column.

Sorting by Number: Using Custom List Feature

Step 1: Create a Custom List

The Custom List feature is useful when you need to sort based on a specific order that is not numerical. For example, if you need to sort student test scores by grade level, you can create a custom list that contains the order of the grades – Freshman, Sophomore, Junior, and Senior.

To create a custom list, go to ‘File’ > ‘Options’ > ‘Advanced,’ and scroll down to ‘General.’ Click on ‘Edit Custom Lists.’ Enter the values in the order you want them to appear, separated by commas.

Step 2: Select the Data and Sort Command

Select the range of cells you want to sort and use the ‘Sort’ command as described above. In the ‘Sort’ dialog box, select the column that contains the grades in our example and choose ‘Custom List’ from the ‘Order’ options.

Step 3: Apply Custom List

Click on ‘Custom List’ and select the custom list you created in Step 1. Click on ‘OK’ to apply the custom list to your data. Your data should now be sorted according to the custom list order.

The Final Word

You have learned how to sort in Excel by number using both the Sort command and the Custom List feature. Sorting by number allows for a much quicker and more efficient analysis of data that contains numerical values. Excel provides a vast array of sorting options that can help you to tailor your data to meet your specific needs.

Now you can sort your financial data, student test scores, or any other numerical values you work with in Excel with confidence.

Sorting Tips for Complex Data Sets

When dealing with more complex data, sorting by number can become more challenging. In some cases, you may need to use multiple sorting criteria to sort your data accurately. For example, you may need to sort by year and then by sales figures to see how sales figures have changed year over year.

You can sort by multiple columns by selecting the column you want to sort first and then holding down the ‘Shift’ key and selecting the secondary column. After that, select a custom sort and specify the order of priority for the selected columns.

If you have a large dataset, you may also want to consider using filters to sort your data. For instance, you can filter based on specific conditions that match your criteria. This will allow you to see only the data that you want to analyze or manipulate.

The Pitfalls of Incorrect Sorting in Excel

Incorrect sorting can lead to significant problems when analyzing or organizing data. For instance, sorting by text instead of by number can cause results to be sorted alphabetically or in a way that differs from the intended results.

Users can avoid incorrect sorting by selecting the correct sorting criteria and double-checking all data cells before sorting. Misspelled words, errors in data entry, or hidden cells can all throw off sorting results, leading to incorrect conclusions or failed analysis.

Using Conditional Formatting with Sorting

Conditional formatting is a useful tool that can be used to highlight cells that meet certain criteria. When used alongside sorting in Excel, it can help you to better visualize your data and identify the most important elements quickly.

For instance, you can use conditional formatting to highlight the top 10% of sales figures or to color code values that exceed certain thresholds. You can also use it to highlight cells based on their proximity to the highest or lowest value, further improving data analysis.

Final Thoughts

Sorting by number in Excel is a simple and effective way to organize your data based on numerical values. Whether you’re analyzing financial data, grading student work, or conducting research, correctly sorting data ensures that you will get accurate and reliable results from your analysis.

By following the simple steps on how to sort by number in Excel and using custom list options, you can zero in on the data you need to do your job effectively and efficiently.

FAQ

Here are some frequently asked questions about sorting numeric data in Excel:

Can I sort by number in ascending and descending order at the same time?

No, you cannot sort by ascending and descending order at the same time. However, you can sort by multiple columns. To do that, select the first column you want to sort, then hold down the ‘Shift’ key and select the other columns you want to sort. When you click on ‘OK,’ the data will be sorted based on the order you selected.

Can I create a custom list for sorting by numbers?

No, custom lists are not suitable for sorting numeric data. Instead, you should use the ‘Sort’ feature in Excel.

How do I sort negative numbers in Excel?

Negative numbers are sorted like any other number in Excel. For instance, if you want to sort values in ascending order, Excel will treat -5 as a lesser value than 0. Therefore, -5 will appear first when you click on ‘Sort.’

Can I sort by number in Excel Online?

Yes, you can sort by number in Excel Online in the same way you would in the desktop version. However, not all features may be available in Excel Online, so users should check the available tools before beginning their analyses.

Why does Excel sort my column headers, and how do I avoid it?

Excel sorts the entire range of cells you select, including the column headers. If you don’t want headers to be sorted, make sure to exclude them from your selection, or copy them to a separate row. Alternatively, you can sort the data and then remove the sort from the headers by selecting the ‘Data’ tab then clicking ‘Sort’. In the ‘Sort & Filter Group’, click on ‘Custom Sort’ and uncheck the ‘Header Row’ checkbox.