JavaScript Data GridColumn filter

Filter data by values or by a set of conditions, using Handsontable's intuitive user interface or flexible API.

Overview

Filtering lets you quickly find the information that you're looking for, based on specific criteria. This makes data analysis easier and faster, especially with large data sets.

Handsontable's built-in filtering interface resembles Excel's, so it's intuitive even to non-technical users. And if you want to implement your own interface, you can easily filter data programmatically, using Handsontable's API.

You can filter data by value, or use the built-in conditions, which are different for each of the available column types.

Filtering demo

Click on one of the column menu buttons (▼) and play around with filtering by selecting values or conditions-based criteria.

After filtering, the column readjusts its width to the longest value displayed on screen. To disable this behavior, set fixed column widths.

    Enable filtering

    To enable the filtering interface for all columns, you need to do two things:

    1. Set the filters option to true.
    2. Enable the interface by setting the dropdownMenu option to true.

    Enabling the filters option without the interface can be useful if you plan to create your own custom interface for filtering by using the API.

    const configurationOptions = {
      // enable filtering
      filters: true,
      // enable the column menu
      dropdownMenu: true,
    };
    

    By default, the column menu presents the filtering interface along with other default items such as Insert column left. To display only the filtering interface, pass an array of filter items in the configuration.

      Enable filtering for individual columns

      You have control over which columns are filterable and for which columns the column menu is enabled. In the following demo, only the Brand column is filterable, while the other columns are not. However, the Model column still has the column menu available in case you want to have some useful items in the menu such as Clear column.

        import Handsontable from 'handsontable';
        import 'handsontable/dist/handsontable.full.min.css';
        
        const container = document.querySelector('#exampleEnableFilterInColumns')!;
        
        new Handsontable(container, {
          data: [
            {
              brand: 'Jetpulse',
              model: 'Racing Socks',
              price: 30,
              sellDate: 'Oct 11, 2023',
              sellTime: '01:23 AM',
              inStock: false,
            },
            {
              brand: 'Gigabox',
              model: 'HL Mountain Frame',
              price: 1890.9,
              sellDate: 'May 3, 2023',
              sellTime: '11:27 AM',
              inStock: false,
            },
            {
              brand: 'Camido',
              model: 'Cycling Cap',
              price: 130.1,
              sellDate: 'Mar 27, 2023',
              sellTime: '03:17 AM',
              inStock: true,
            },
            {
              brand: 'Chatterpoint',
              model: 'Road Tire Tube',
              price: 59,
              sellDate: 'Aug 28, 2023',
              sellTime: '08:01 AM',
              inStock: true,
            },
            {
              brand: 'Eidel',
              model: 'HL Road Tire',
              price: 279.99,
              sellDate: 'Oct 2, 2023',
              sellTime: '01:23 AM',
              inStock: true,
            },
          ],
          columns: [
            {
              title: 'Brand',
              type: 'text',
              data: 'brand',
            },
            {
              title: 'Model',
              type: 'text',
              data: 'model',
            },
            {
              title: 'Price',
              type: 'numeric',
              data: 'price',
              numericFormat: {
                pattern: '$ 0,0.00',
                culture: 'en-US',
              },
            },
            {
              title: 'Date',
              type: 'date',
              data: 'sellDate',
              dateFormat: 'MMM D, YYYY',
              correctFormat: true,
              className: 'htRight',
            },
            {
              title: 'Time',
              type: 'time',
              data: 'sellTime',
              timeFormat: 'hh:mm A',
              correctFormat: true,
              className: 'htRight',
            },
            {
              title: 'In stock',
              type: 'checkbox',
              data: 'inStock',
              className: 'htCenter',
            },
          ],
          // enable filtering for all columns
          filters: true,
          // enable the column menu for all columns
          // but display only the 'Filter by value' list and the 'OK' and 'Cancel' buttons
          dropdownMenu: {
            items: {
              filter_by_value: {
                // hide the 'Filter by value' list from all columns but the first one
                hidden() {
                  return this.getSelectedRangeLast()!.to.col > 0;
                },
              },
              filter_action_bar: {
                // hide the 'OK' and 'Cancel' buttons from all columns but the first one
                hidden() {
                  return this.getSelectedRangeLast()!.to.col > 0;
                },
              },
              clear_column: {
                // hide the 'Clear column' menu item from the first column
                hidden() {
                  return this.getSelectedRangeLast()!.to.col < 1;
                },
              },
            },
          },
          // `afterGetColHeader()` is a Handsontable hook
          // it's fired after Handsontable appends information about a column header to the table header
          afterGetColHeader(col, TH) {
            // remove the column menu button from the 'Brand', 'Price', and 'Date' columns
            if (col > 1) {
              const button = TH.querySelector('.changeType')!;
        
              if (!button) {
                return;
              }
        
              button.parentElement!.removeChild(button);
            }
          },
          height: 'auto',
          example: 'exampleEnableFilterInColumns',
          autoWrapRow: true,
          autoWrapCol: true,
          licenseKey: 'non-commercial-and-evaluation',
        } as Handsontable.GridSettings);

        Filter different types of data

        With its built-in cell types, Handsontable makes it easy to handle common data types like text, numbers, and dates by providing numerous configuration options. In addition, the filtering feature is designed to understand the underlying data and provides an adaptive interface that is tailored to each data type.

          The following table contains all available filter operators for each built-in data type.

          Cell type Available filter operators
          All cell types Default operators:

          None
          Is empty
          Is not empty
          Is equal to
          Is not equal to
          text
          time
          checkbox
          dropdown
          autocomplete
          password
          Default operators plus:

          Begins with
          Ends with
          Contains
          Does not contain
          numeric Default operators plus:

          Greater than
          Greater than or equal to
          Less than
          Less than or equal to
          Is between
          Is not between
          date Default operators plus:

          Before
          After
          Is between
          Tomorrow
          Today
          Yesterday

          Filter data on initialization

          You can filter data on Handsontable's initialization. This lets you apply pre-defined filters every time you launch your grid.

          To do this, you can use Handsontable's afterInit() hook, along with the API provided by the Filters plugin. For instance, the demo below demonstrates how you can start with a pre-applied filter to display only items priced less than $200.

            External quick filter

            Handsontable's quick filter feature lets you search for a particular phrase in a specific column. To accomplish this, use methods filters.addCondition() and filters.filter().

              Customize the filter button

              The default button that opens the column menu can be styled with CSS by modifying button.changeType and its ::before pseudoclass that contains an HTML entity displaying an arrow down icon.

                The column menu button is always visible, but if you want it to appear only when the mouse cursor is over the header, apply additional styling to th .relative:hover .changeType.

                  Change the width of the filter menu

                  If the text data in your columns is too long to fit in the filters container, you can adjust the column menu's width for better user experience. You can achieve this with by styling .htDropdownMenu table.htCore.

                  .handsontable .htDropdownMenu table.htCore {
                    width: 300px !important;
                  }
                  

                  Exclude rows from filtering

                  You can exclude any number of top or bottom rows from filtering.

                  In the following demo, the first and the last row are frozen, and filtering doesn't affect them.

                    Server-side filtering

                    You can decide to use Handsontable as an intuitive filtering interface, but perform the actual filtering on the server.

                    To help you with that, Handsontable's beforeFilter() hook allows you to:

                    • Gather information about the filters that the user wants to apply, to send it to the server.
                    • Disable filtering on the front end, so it doesn't interfere with filtering on the server.

                    In the following demo, click on one of the column menu buttons (▼) and play around with filtering by selecting values or conditions-based criteria. After you click OK, the ▼ button turns green to indicate filtering, but the data is not filtered. Instead, the information about the specified filters is logged to the console.

                      Control filtering programmatically

                      You can control filtering at the grid's runtime by using Handsontable's hooks and API methods. This allows you to enable or disable filtering based on specific conditions. For example, you may create a user interface outside of the grid to manage Handsontable's filtering behavior.

                      Enable or disable filtering programmatically

                      To enable or disable filtering programmatically, use the updateSettings() method.

                      handsontableInstance.updateSettings({
                        // enable filtering
                        filters: true,
                        // enable the column menu
                        dropdownMenu: true,
                      });
                      
                      handsontableInstance.updateSettings({
                        // disable filtering
                        filters: false,
                      });
                      

                      You can also enable or disable filtering for specific columns. For example, to enable filtering only for the first column:

                      handsontableInstance.updateSettings({
                        // enable filtering, for all columns
                        filters: true,
                        // enable the column menu, for all columns
                        // but display only the 'Filter by value' list and the 'OK' and 'Cancel' buttons
                        dropdownMenu: {
                          items: {
                            filter_by_value: {
                              // hide the 'Filter by value' list from all columns but the first one
                              hidden() {
                                return this.getSelectedRangeLast().to.col > 0;
                              },
                            },
                            filter_action_bar: {
                              // hide the 'OK' and 'Cancel' buttons from all columns but the first one
                              hidden() {
                                return this.getSelectedRangeLast().to.col > 0;
                              },
                            },
                          },
                        },
                      });
                      

                      Filter data programmatically

                      To filter data programmatically, use the Filters plugin's API. Remember to enable filtering first.

                      Mind that before you apply new filter conditions, you need to clear the previous ones with filters.clearConditions().

                        Import the filtering module

                        You can reduce the size of your bundle by importing and registering only the modules that you need.

                        To use filtering, you need only the following modules:

                        // import the base module
                        import Handsontable from 'handsontable/base';
                        
                        // import Handsontable's CSS
                        import 'handsontable/dist/handsontable.full.min.css';
                        
                        // import the filtering plugins
                        import { registerPlugin, Filters, DropdownMenu } from 'handsontable/plugins';
                        
                        // register the filtering plugins
                        registerPlugin(Filters);
                        registerPlugin(DropdownMenu);
                        

                        Known limitations

                        At the moment, filtering comes with the following limitations:

                        • There is no easy way to add custom filter operators to the user interface.
                        • The list of values that you can filter by is generated automatically and there's no supported way of modifying it.
                        Windows macOS Action Excel Sheets
                        Alt+A Option+A Clear all filters

                        API reference

                        For the list of options, methods, and Handsontable hooks related to filtering, see the following API reference pages:

                        Troubleshooting

                        Didn't find what you need? Try this: