DHTMLX Spreadsheet 5.0: Searching and Filtering Data, Merging Cells, Auto-Width, and More


Our whole team is thrilled to announce the release of DHTMLX Spreadsheet 5.0. This major update is primarily focused on expanding the list of capabilities for modifying the spreadsheet structure on the fly and managing tabular data with ease. For instance, the new version of our JavaScript spreadsheet library allows searching and filtering data, merging and splitting cells, automatically adjusting the column width of the table, inserting links into spreadsheet content, applying the strikethrough text formatting, and more. Almost all these highly anticipated features are available via API and UI.

Let us have a closer look at how the novelties delivered in v5.0 can be used by both web developers and end-users.

Download an evaluation version of DHTMLX Spreadsheet v5.0 >

Data Searching

Finding specific pieces of data in spreadsheets may be time-consuming if you don’t have a special search tool for this task. It is especially true for large tables with multiple sheets containing hundreds or even thousands of records. But you won’t have to worry about that when using the latest version of our JavaScript Spreadsheet since it comes with a handy search option.

End-users can perform this operation via a search bar, which is opened in two ways:

  • by clicking on any spreadsheet cell and pressing the Ctrl (Cmd) + F combination,
  • by going to Data -> Search in the menu section.
Check the sample

It should be noted that the search is performed only within the opened worksheet. All the results will be highlighted right in the grid and can be reviewed using search bar arrows or hotkeys Ctrl (Command) + G (previous) / Ctrl (Command) + Shift+ G (next). By default, all searches are case-insensitive.

To find certain information in the spreadsheet via API, you have to use the newly added search() method. It takes 3 optional parameters:

  • text — specifies a search value,
  • openSearch — if set to true, opens the search box and highlights the results that match the entered query (false by default),
  • sheetID — serves to identify the sheet where searching should be performed. If you do not set the value for this parameter, the search will be performed on the currently active sheet.

For example, you can find all income statistics for February in the corresponding sheet in the following way:

spreadsheet.search("feb", true, "Income"); 

There is also the new hideSearch() method that closes the search bar:


Data Filtering

One more significant improvement for effective working with large spreadsheets provided in v5.0 is the ability to filter data by certain criteria. This feature will help you to temporarily hide cells with excessive information and concentrate on currently relevant data for more productive analysis.

In the user interface, this feature can be brought into action by selecting one or several cells and doing one of the following:

  • clicking on the Filter button in the toolbar,
  • going to Data -> Filter in the menu section.

After that, selected cells or ranges of cells will be complemented with filter icons. Then it is possible to start filtering data by condition or by value.

When filters are no longer needed, end-users can remove them by clicking on the Filter button in the toolbar or on the corresponding option in the Data menu of the spreadsheet. As a result, all hidden records will become visible.

Here are visual examples that show step by step how to filter data both ways and clear filtering settings afterward:

  • Filtering by condition
Check the sample
  • Filtering by values
Check the sample

When talking about implementing data filtering via API, you should call the setFilter() method.

It enables you to set a cell or range of cells to be filtered and add certain rules that should be followed during this operation.

For instance, you can display cells in column C, where numeric values are not between 5 and 8, excluding 3.75 like in the example below:

spreadsheet.setFilter("C1",[{}, {},{condition: {factor: "inb", value: [5,8]}, exclude: [3.75]}]);

Now let us consider how to use the setFilter() method for specifying the filtering criteria for two columns using the following example:

spreadsheet.setFilter("C1:D20", [{condition: {factor: "ib", value: [5,8]}}, {exclude: [740]}]);

In this case, the first condition, namely “between 5 and 8”, is applied to column C, while the condition for excluding 740 works for column D.

To reset the filter, you need to call the setFilter() method, indicating only the first cell parameter or without specifying any parameters at all.

If necessary, you can get the criteria that are currently used for filtering spreadsheet data with the help of the getFilter() method.

Merging and Splitting Cells

When manipulating different kinds of data in spreadsheets, it may be required to quickly change the grid structure. That is why we decided to bring in the ability to merge cells and split them back in v5.0. By merging cells, you combine two or more adjacent cells into a single one. It can be very useful for creating headings and labels or adding extra space for large pieces of content, thereby making it more readable.

In v5.0, end-users can merge any number of cells vertically or horizontally by simply selecting them and clicking on the Merge button in the toolbar. Alternatively, this feature is also available in the Format section of the spreadsheet menu.

You should also use one of the mentioned options if it becomes necessary to split the merged cell.

Check the sample

On the coding side, this functionality is enabled with the mergeCells() method. All you need to do is just specify a range of cells that should be merged in the first parameter.


The same method is used for splitting the merged cells. It is done by adding the second parameter with true as a value.


There is the new merged property in the sheet object aimed at defining a range of cells for merging.

Column Auto Width

Another helpful cell formatting feature shipped with v5.0 is automatic column width. It will help to forget about the necessity to manually change the width of any column when the content in its cells varies greatly in length.

In spreadsheets built with DHTMLX, end-users now can activate the automatic adjustment of a column to fit the longest content by a double-click on the column’s resizer or the context (3 dots) menu as follows:

Check the sample

Programmatically, you will be able to use this feature by applying the fitColumn() method. It takes one required cell parameter in which the ID of the needed column should be specified.


Hyperlinks in Cell Content

Starting from v5.0, cell content in DHTMLX-based spreadsheets may contain hyperlinks. It is common to use hyperlinks in cells to direct end-users to online documents or resources that are relevant to a given spreadsheet.

In practice, end-users are provided with three ways of inserting hyperlinks into cells:

  • Insert link button in the toolbar
  • hotkey combination (Ctrl (Command) + K)
  • context menu of a cell
Check the sample

A cell with an embedded hyperlink will be complemented with a special popup, including three options for managing a link (copy, edit, remove).

Check the sample

In terms of coding, hyperlinks are inserted in a spreadsheet cell with the new insertLink() method. This method also allows adding a text (or numeric) value that will contain your hyperlink.

spreadsheet.insertLink("A2", {
text:"DHX Spreadsheet", href: "https://dhtmlx.com/docs/products/dhtmlxSpreadsheet/"

Any hyperlink can be removed by calling the insertLink() method with the cell ID.


These are the main features implemented in DHTMLX Spreadsheet 5.0.

Source: release article published on November 17, 2022, on the DHTMLX blog.



JavaScript UI Libraries — DHTMLX

Here we post news about our JavaScript UI libraries. In addition to this, we also share useful tips, news and articles about JavaScript.