How to sort values ignore zeros in Excel?
When working with a dataset in Excel that contains both numbers and zeros, applying the standard sort function will typically move all zero values to the top of the list if sorting in ascending order. However, in many situations, you might want to sort your data while leaving zero values at the bottom, effectively ignoring them during the sorting process. For example, when analyzing sales amounts, production numbers, or test results, you may wish to focus on nonzero results and only review zero entries at the end. This can make reports clearer and more meaningful for further analysis.
Sort values ignore zeros with a helper column
Sort Values Ignoring Zeros with a Single Formula Column (Alternative Method)
Sort Values Ignoring Zeros with VBA Code (Alternative Solution)
Sort values ignore zeros with a helper column
To sort data while keeping zero values at the bottom, you can use a helper column with a specific formula. This approach is practical and works across all Excel versions, making it especially suitable when you have a list with zeros mixed among other values and want to sort the nonzero numbers together while grouping the zeros at the end.
1. In a blank cell adjacent to your values (for example, B2 next to original data in column A), enter the following formula:
=IF(A2=0,"",A2)This formula checks the value in cell A2. If the value is zero, the formula returns a blank cell (""); if it is not zero, it will return the original value. Use the fill handle to copy this formula down for all relevant rows in your data. This will result in your helper column displaying the nonzero values and leaving zeros as blank cells. The screenshot illustrates how the helper column appears after applying the formula:
Tip: Make sure you are referencing the correct column and cell range in your formula according to your actual worksheet setup to avoid errors.
2. After you have created the helper column, select all the cells in the helper column, then go to the Data tab and choose Sort Smallest to Largest. In the Sort Warning dialog box that appears, ensure you select Expand the selection so that the entire row of data is sorted along with your helper column values.
Tip: Expanding the selection is important; otherwise, only the helper column will be sorted, and your data will end up misaligned. Always confirm you are sorting the complete data range.
3. Click Sort. The data will now be sorted in ascending order, with all nonzero values at the top and zeros at the bottom, as desired. After sorting, you can delete or hide the helper column if it is no longer needed.
Note: If your data has formulas, copying and pasting as values before sorting can help prevent unexpected results.
A key advantage of this method is its simplicity and compatibility with all Excel versions. However, it does involve the addition of a helper column, which may not be ideal for users who wish to avoid extra columns. Additionally, if your dataset is frequently updated, you will need to ensure any new rows are included in your helper formula range.
Troubleshooting: If you find that zeros are not moving to the bottom as expected, double-check that the formula has been correctly entered and copied down for all relevant cells, and ensure the selection is expanded during the sorting process.
Tip. By default, Excel will sort blank cells to the bottom. If you need the blank cells (which represent the original zeros) to be sorted at the top, you can use Kutools for Excel’s Advanced Sort function and select the Blank cells in front option, as shown in the screenshot below. This feature allows for more flexible sorting scenarios, such as when you want to focus attention on missing or zero entries first. The tool offers a free full-feature trial for 30 days. Download for a trial here.
Sort Values Ignoring Zeros with a Single Formula Column (Alternative Method)
This method is useful when you want to extract and sort only nonzero values from a list, without adding extra steps or using built-in sort functions. The approach suits scenarios where you want to create a sorted list of nonzero values separately while leaving the original data unchanged.
1. Enter the following array formula in a new column (for example, C2):
=SMALL(IF($A$2:$A$11<>0,$A$2:$A$11),ROW(A1))2. Press Ctrl+Shift+Enter (for Excel 2019 and earlier) or just Enter (for Excel 365/Excel 2021). Then drag the fill handle down as far as needed. This formula returns the sorted nonzero values from the original data range in column A.
Explanation: IF($A$2:$A$11<>0,$A$2:$A$11) filters the nonzero values, while SMALL(...,ROW(A1)) returns sorted results in ascending order. Adjust the range $A$2:$A$11 to match your data, and ensure you have enough rows in column C to cover all nonzero values; otherwise, the formula will return errors in excess cells.
This approach is efficient and keeps the original list intact, but the sorted values are displayed in a new location only.
Note: The formula will first list the unique values and then proceed with sorting.
Sort Values Ignoring Zeros with VBA Code (Alternative Solution)
If you find yourself repeatedly sorting lists to ignore zeros, automating with VBA can save time. This solution is suitable for users comfortable with macros and who need to handle large or frequently updated lists. However, you should always back up your data before running any VBA script.
1. Go to Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications window. Click Insert > Module, then paste the following code into the Module:
Sub SortIgnoreZeros()
Dim rng As Range
Dim lastRow As Long
Dim ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set rng = Application.InputBox("Select the range to sort", xTitleId, "", Type:=8)
lastRow = rng.Rows.Count
' Move nonzeros to top
rng.Sort Key1:=rng, Order1:=xlAscending, Header:=xlNo
End Sub2 After entering the code, press 
to run the macro. A dialog will prompt you to select the range you wish to sort. The VBA script will sort the selected range in ascending order, placing zeros at the bottom. Make sure only the relevant column with values is selected; otherwise, rows may become misaligned.
This VBA approach is very efficient for repetitive tasks, but use caution with unsaved data and always verify the selection before running the macro.
In summary, sorting values while ignoring zeros in Excel can be achieved through several methods, each with distinct advantages. Selecting the optimal method depends on your task’s frequency, the size of your data, and whether you want to keep the original data unchanged. If you encounter any unexpected sorting results, always double-check your formula ranges, verify sorting expansion options, and consider keeping a backup of your worksheet. Exploring the built-in Excel features, supplemental formulas, VBA macros, or the enhanced sorting capabilities offered by Kutools can help you find the most suitable workflow for your needs.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in