Others

How to Filter SharePoint Lists to Exclude Zero Values

Rana Garcia Oct 9, 2026 868 views

Question details

The user needs to filter a SharePoint or Microsoft List calculated column to display all values except zero, ideally using a straightforward Select All function.

How to Filter SharePoint Lists for All Values Except Zero

Product: Microsoft SharePoint / Microsoft Lists
Device & OS: not provided
Scenario: Filtering multiple calculated columns in a list to exclude specific numerical values (like zero) without having to create multiple custom list views.
Observed behavior: Microsoft Lists lacks a built-in Select All checkbox for column filters. Users are forced to manually edit view conditions or export their list data to a spreadsheet application for efficient filtering.

Before you start

Verify that you have the necessary edit permissions to modify the current SharePoint list view. If you are dealing with multiple calculated columns, consider exporting your data to a spreadsheet program, as creating numerous custom SharePoint views can become difficult to manage.

Solution 1Recommended

Edit the Current List View Settings

Modify your SharePoint view settings to manually filter out zero values using a logical condition.

Since Microsoft Lists does not provide a 'Select All' checkbox filter like traditional spreadsheet software, you must manually adjust the view logic to hide rows containing a zero in your target calculated column.

Open the view editing menu

Navigate to your Microsoft List or SharePoint List. Click on your current view name in the top right corner (e.g., 'All Items') and select 'Edit current view' from the dropdown menu.

Locate the Filter section

Scroll down the settings page until you find the 'Filter' section. Ensure the option 'Show items only when the following is true' is selected.

Set the exclusion condition

In the first dropdown, select your calculated column. In the middle condition dropdown, choose 'is not equal to'. In the third text box, enter the number '0'.

Save the new view configuration

Scroll to the top or bottom of the page and click 'OK' to save your changes. Your list will now display all items except those containing a zero in that specific column.

Limitation with Multiple Columns: If you need to filter out zeros for multiple calculated columns separately, you will need to create individual views for each column, which can clutter your list environment.

Solution 2

Export Data to a Spreadsheet for Advanced Filtering

Export your SharePoint list to a spreadsheet application to utilize standard 'Select All' checkbox filtering capabilities.

Free Microsoft Office alternative

Easily Filter Complex List Data with WPS Spreadsheet

Since SharePoint Lists lacks advanced 'Select All' filter toggles for managing multiple columns, exporting your list to a spreadsheet is often the most efficient solution. WPS Office is a free, lightweight, and highly compatible alternative to Microsoft Office. It fully supports exported SharePoint CSV and Excel files, providing the familiar filter checkboxes you need to quickly exclude zero values across numerous columns without creating messy custom views in SharePoint.

1. Open the exported list file: Launch WPS Spreadsheet and open the CSV or Excel file you exported from your SharePoint List.
2. Enable the AutoFilter tool: Highlight the row containing your column headers, navigate to the 'Data' tab on the top ribbon, and click 'AutoFilter'.
3. Filter out zero values: Click the filter dropdown arrow on your calculated column, simply uncheck the box next to '0' (leaving all other values checked), and click OK to instantly filter your data.

Advanced data filtering with 'Select All' checkboxes, perfectly suited for excluding specific values like zeroSeamless compatibility with Microsoft Excel formats (.xlsx, .csv) exported directly from SharePoint ListsLightweight installation with a familiar, tabbed user interface that requires zero learning curveCompletely free alternative to costly Microsoft Office subscriptions

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't SharePoint Lists have a 'Select All' filter checkbox?

Microsoft Lists and SharePoint currently utilize a logic-based filtering system for views (e.g., 'is equal to', 'contains') rather than the specific value checkbox filtering system commonly found in dedicated spreadsheet applications like Excel or WPS Spreadsheet.

Can I filter out zero values on multiple calculated columns in SharePoint?

Yes, but it is complicated. You must either create complex nested conditions using 'AND/OR' logic in a single view's settings, or create completely separate list views for each column, which often becomes difficult for teams to manage.

How do I export my SharePoint list to filter it in a spreadsheet?

Navigate to your SharePoint list, click the 'Export' button in the top command bar, and choose 'Export to Excel' or 'Export to CSV'. You can then open this downloaded file in any standard spreadsheet software to use traditional checkbox filters.

Will my list data sync back to SharePoint if I filter it in a spreadsheet?

No. Exporting to a CSV or standard Excel file creates a static, one-way copy of your data for offline analysis. Changes or filters applied in the spreadsheet will not reflect back in your live SharePoint list unless you utilize advanced external data connections or Power Automate.

Office Quick Solutions