Sort and filter data

Sort Data

You can quickly sort the data in the Spreadsheet Editor using one of the following options:

Ascending is used to sort the data in ascending order - from A to Z alphabetically or from the smallest to the largest number for numerical data.
Descending is used to sort the data in descending order - from Z to A alphabetically or from the largest to the smallest for numerical data.

Note: the Sort options are accessible from both Home and Data tab.

To sort the data,

select a cell range you wish to sort (you can select a single cell in a range to sort the entire range),
click the Sort ascending
icon situated on the Home or Data tab of the top toolbar to sort the data in ascending order,
OR
click the Sort descending
icon situated on the Home or Data tab of the top toolbar to sort the data in descending order.

Note: if you select a single column/row within a cell range or a part of the column/row, you will be asked if you want to expand the selection to include adjacent cells or sort the selected data only.

You can also sort your data using the contextual menu options. Right-click the selected range of cells, select the Sort option from the menu and then select Ascending or Descending option from the submenu.

It's also possible to sort the data by color using the contextual menu:

right-click a cell containing the color by which you want to sort the data,
select the Sort option from the menu,
select the necessary option from the submenu:
- Selected Cell Color on top - to display the entries with the same cell background color on the top of the column,
- Selected Font Color on top - to display the entries with the same font color on the top of the column.

Filter Data

To display only the rows that meet certain criteria and hide other ones, make use of the Filter option.

Note: the Filter options are accessible from both Home and Data tab.

To enable a filter,

Select a cell range containing data to filter (you can select a single cell in a range to filter the entire range),
Click the Filter
icon situated at the Home or Data tab of the top toolbar.
The drop-down arrow will appear in the first cell of each column of the selected cell range. It means that the filter is enabled.

To apply a filter,

Click the drop-down arrow
. The Filter option list will open:

Note: you can adjust the size of the filter window by dragging its right border to the right or to the left to display the data as convenient as possible.
Adjust the filter parameters. You can proceed in one of the following ways: select the data to display, filter the data by certain criteria or filter data by color.
- Select the data to display
  Uncheck the boxes near the data you need to hide. For your convenience, all the data within the Filter option list are sorted in ascending order.
  
  The number of unique values in the filtered range is displayed to the right of each value within the filter window.
  
  Note: the {Blanks} check box corresponds to the empty cells. It is available if the selected cell range contains at least one empty cell.
  
  To facilitate the process, make use of the search field on the top. Enter your query, entirely or partially, in the field - the values that include these characters will be displayed in the list below. The following two options will be also available:
  - Select All Search Results - is checked by default. It allows selecting all the values that correspond to your query in the list.
  - Add current selection to filter - if you check this box, the selected values will not be hidden when you apply the filter.
  After you select all the necessary data, click the OK button in the Filter option list to apply the filter.
- Filter data by certain criteria
  Depending on the data in the selected column, you can choose either the Number filter or the Text filter option on the right side of the Filter options list, and then select one of the options from the submenu:
  - For the Number filter the following options are available: Equals..., Does not equal..., Greater than..., Greater than or equal to..., Less than..., Less than or equal to..., Between, Top 10, Above Average, Below Average, Custom Filter....
  - For the Text filter the following options are available: Equals..., Does not equal..., Begins with..., Does not begin with..., Ends with..., Does not end with..., Contains..., Does not contain..., Custom Filter....
  After you select one of the above options (apart from Top 10 and Above/Below Average), the Custom Filter window will open. The corresponding criterion will be selected in the upper drop-down list. Enter the necessary value in the field on the right.
  
  To add one more criterion, use the And radiobutton if you need the data to satisfy both criteria or click the Or radiobutton if either or both criteria can be satisfied. Then select the second criterion from the lower drop-down list and enter the necessary value on the right.
  
  Click OK to apply the filter.
  
  If you choose the Custom Filter... option from the Number/Text filter option list, the first criterion is not selected automatically, you can set it yourself.
  
  If you choose the Top 10 option from the Number filter option list, a new window will open:
  
  The first drop-down list allows choosing if you wish to display the highest (Top) or the lowest (Bottom) values. The second field allows specifying how many entries from the list or which percent of the overall entries number you want to display (you can enter a number from 1 to 500). The third drop-down list allows setting the units of measure: Item or Percent. Once the necessary parameters are set, click OK to apply the filter.
  
  If you choose the Above/Below Average option from the Number filter option list, the filter will be applied right now.
- Filter data by color
  If the cell range you want to filter contains some cells you have formatted changing their background or font color (manually or using predefined styles), you can use one of the following options:
  - Filter by cells color - to display only the entries with a certain cell background color and hide other ones,
  - Filter by font color - to display only the entries with a certain cell font color and hide other ones.
  When you select the necessary option, a palette that contains colors used in the selected cell range will open. Choose one of the colors to apply the filter.
The Filter button will appear in the first cell of the column. It means that the filter is applied. The number of filtered records will be displayed at the status bar (e.g. 25 of 80 records filtered).

Note: when the filter is applied, the rows that are filtered out cannot be modified when autofilling, formatting, deleting the visible contents. Such actions affect the visible rows only, the rows that are hidden by the filter remain unchanged. When copying and pasting the filtered data, only visible rows can be copied and pasted. This is not equivalent to manually hidden rows which are affected by all similar actions.

Sort filtered data

You can set the sorting order of the data you have enabled or applied filter for. Click the drop-down arrow or the Filter button and select one of the options in the Filter option list:

Sort Lowest to Highest - allows sorting the data in ascending order, displaying the lowest value on the top of the column,
Sort Highest to Lowest - allows sorting the data in descending order, displaying the highest value on the top of the column,
Sort by cells color - allows selecting one of the colors and displaying the entries with the same cell background color on the top of the column,
Sort by font color - allows selecting one of the colors and displaying the entries with the same font color on the top of the column.

The latter two options can be used if the cell range you want to sort contains some cells you have formatted changing their background or font color (manually or using predefined styles).

The sorting direction will be indicated by an arrow in the filter buttons.

if the data is sorted in ascending order, the drop-down arrow in the first cell of the column looks like this:
and the Filter button looks the following way:
.
if the data is sorted in descending order, the drop-down arrow in the first cell of the column looks like this:
and the Filter button looks the following way:
.

You can also quickly sort the data by color using the contextual menu options:

right-click a cell containing the color by which you want to sort the data,
select the Sort option from the menu,
select the necessary option from the submenu:
- Selected Cell Color on top - to display the entries with the same cell background color on the top of the column,
- Selected Font Color on top - to display the entries with the same font color on the top of the column.

Filter by the selected cell contents

You can also quickly filter your data by the selected cell contents using the contextual menu options. Right-click a cell, select the Filter option from the menu and then select one of the available options:

Filter by Selected cell's value - to display only the entries with the same value as the selected cell contains.
Filter by cell's color - to display only the entries with the same cell background color as the selected cell has.
Filter by font color - to display only the entries with the same cell font color as the selected cell has.

Format as Table Template

To facilitate your work with data, the Spreadsheet Editor allows you to apply a table template to a selected cell range automatically enabling the filter. To do that,

select a range of cells you need to format,
click the Format as table template
icon situated on the Home tab of the top toolbar.
select the required template in the gallery,
in the opened pop-up window check the cell range to be formatted as a table,
check the Title if you wish the table headers to be included in the selected cell range, otherwise the header row will be added at the top while the selected cell range will be moved one row down,
click the OK button to apply the selected template.

The template will be applied to the selected range of cells and you will be able to edit the table headers and apply the filter to work with your data. To learn more on working with formatted tables, please refer to this page.

Reapply Filter

If the filtered data has been changed, you can refresh the filter to display an up-to-date result:

click the Filter
button in the first cell of the column that contains the filtered data,
select the Reapply option in the opened Filter option list.

You can also right-click a cell within the column that contains the filtered data and select the Reapply option from the contextual menu.

Clear Filter

To clear the filter,

click the Filter
button in the first cell of the column that contains the filtered data,
select the Clear option in the opened Filter option list.

You can also proceed in the following way:

select the range of cells containing the filtered data,
click the Clear filter
icon situated on the Home or Data tab of the top toolbar.

The filter will remain enabled, but all the applied filter parameters will be removed, and the Filter buttons in the first cells of the columns will change into the drop-down arrows .

Remove Filter

To remove the filter,

select the range of cells containing the filtered data,
click the Filter
icon situated on the Home or Data tab of the top toolbar.

The filter will be disabled, and the drop-down arrows will disappear from the first cells of the columns.

Sort data by several columns/rows

To sort data by several columns/rows you can create several sorting levels using the Custom Sort function.

select a cell range you wish to sort (you can select a single cell to sort the entire range),
click the Custom Sort icon situated on the Data tab of the top toolbar,
the Sort window will appear. Sorting by columns is selected by default.
use the Levels sections to add new levels and to manage the added levels.
- New button to add a new level, select the second column / row you want to sort and specify other sorting parameters in the Then by field as described above. If necessary, add more levels in the same way,
- Copy button to copy the selected level and to duplicate all the existing settings,
- Options button to change the sorting orientation (i.e. sorting data by rows instead of columns). Click the button to open the Sort Options window:
  - check the My data has headers box, if necessary
  - choose the necessary Orientation: Sort top to bottom to sort data by columns or Sort left to right to sort data by rows,
  - click OK to apply the changes and close the window.
- Delete button to delete the selected level,
- Arrow buttons Move the level up / Move the level down to change the order of levels.
set the first sorting level in the Sort by field:
- in the Column / Row section, select the first column / row you want to sort,
- in the Sort on list choose one of the following options: Values, Cell color, or Font color,
- in the Order list, specify the necessary sorting order. The available options differ depending on the option chosen in the Sort on list:
  - if the Values option is selected, choose the Ascending / Descending option if the cell range contains numbers or A to Z / Z to A option if the cell range contains text values,
  - if the Cell color option is selected, choose the necessary cell color and select the Top / Below option for columns or Left / Right option for rows,
  - if the Font color option is selected, choose the necessary font color and select the Top / Below option for columns or Left / Right option for rows.
click OK to apply the changes and close the window.

The data will be sorted according to the specified sorting levels.

Return to previous page

Try now for free Try and make your decision No need to install anything
to see all the features in action

Articles with the tag:

Changelog