Sorting and filtering data in Excel. Simple and custom sorting, how to set up and remove the filter, “smart tables”

Tables with a huge amount of numerical and textual information are often found in internal documents of large and small companies. Among the many lines, it is easy to lose sight of important information. Microsoft developers understand this, so Microsoft Excel has options for sorting and filtering data. It is difficult to understand them without prompts. Let’s try to understand how to properly configure sorting and filters in regular and pivot tables.

Normal (simple) sort

This option is called simple because it is easy to use even for beginners. As a result of sorting, information is automatically organized in the established order. For example, you can alphabetize the rows in a table or arrange numerical data from small to large.

Quick organization of data in a table is possible thanks to the “Sort and Filter” toolset. This button is located on the Home tab, on the right side of it. The sort options correspond to the format of the selected cells.

1. Let’s check out the tools in action. To do this, open an existing table with a large number of records, select a cell in a text format column, and open the sort and filter menu. Two options will appear. Imagine that you want to sort a list of names alphabetically. Click on the “Sort A to Z” button. Entries will be distributed in the specified order.

2. Let’s try to expand the table in the opposite direction – from the end to the beginning. Again, you need to open the sort menu, now select the “Sort from Z to A” function.

3. It is also possible to sort the numbers, but the options for this appear only after selecting a number format cell. Click on one of these cells and open “Sort and Filter”. New functions will appear in the menu – “Ascending” and vice versa. For dates, the sorting options “From old to new” and vice versa have been prepared.

Custom data sorting

Sometimes it is necessary to build the rows of a table in a certain order, which is set by the conditions of the problem. For example, there is a need to sort by several parameters, and not just one. In this case, you should pay attention to custom sorting in Excel.

1. Open the menu “Sort and filter” and select the item “Custom sorting” or “Custom sorting” (the name depends on the version of the program).

2. The sort window will appear on the screen. In the main part there is a line of three interactive selection elements. The first defines the column on which the entire table will be optimized. Next, the sorting principle is selected – by value, cell color, font color or icons. In the “Order” section, you need to set the way the data is sorted. If a column with text is selected, the last list will have options for the text format, the same for other cell formats.

Additional Information! If the table starts with a header, you need to check the box located in the upper right corner of the screen.

3. Let’s complicate the ordering of the data by clicking the “Add Level” button. Under the first line, the same second line will appear. It allows you to select additional sorting conditions. We fill it in the same way, but this time we will choose a different cell format.

Pay attention to the mark “Then by”. It shows that the conditions of the first row are prioritized for sorting the table.

4. If all fields are filled in correctly, click OK.

The settings can be changed by selecting one of the cells in the sorted range and opening the custom/custom sort box.

How to set up a filter in a table

Filters in Excel allow you to temporarily hide part of the information from the sheet and leave only the most necessary. Information is not lost forever – changing the settings will return it to the sheet. Let’s figure out how to filter spreadsheet rows.

1. Select a cell in the table header and open the “Sort and Filter” menu. Find the “Filter” item in it and click on it.

Attention! You can also access filtering through the “Data” tab. It has a large “Filter” button with a funnel.

2. Arrow buttons will appear in the table header, one for each column. With their help, the filters will be adjusted.
3. Click one of the buttons in the column by which we will filter the entire table. A menu will open with sorting and filtering settings corresponding to the data format in the cells. You can sort the information in the table by one of the values ​​in the cells. For example, let’s select one of the names and leave a check mark next to it. Next, you need to click the “OK” button. The table will contain rows corresponding to the selected value.

4. The resulting table can be additionally sorted by other data, for example, by date. Open the filtering menu in the column with dates and remove a few checkboxes. When the filter is set, click OK. The number of rows in the table will decrease again.

How to remove a filter in a table

It is not necessary to check the boxes back in the menu to restore the table to its original appearance. Let’s use the Microsoft Excel tools to undo the filtering results – there are two ways to do this.

1. Open the filter menu in the column where the settings were set. At the bottom of the window is the Clear Filter button. If you click on it, the table will return to its previous form. This should be done with all columns where the filter is applied.

2. Another method involves using the toolbar. Open the menu “Sort and filter” on the main tab and find the item “Clear”. It is active when a range of cells has been filtered. Click on this item, and all filter settings are erased.

3. Now let’s remove the filtering buttons from the header. Let’s open the sorting and filters menu – if the filter is enabled, there is a checkmark next to the corresponding item, in older versions it is highlighted in orange. You need to click on it and uncheck this box – the buttons will disappear from all header cells.

How to create a “smart table”

Connecting sorting and filtering options to a table can be combined with choosing a color theme for it. Such tables are called “smart”. Let’s figure out how to make a regular range of cells “smart”.

1. Select the entire table with the cursor.
2. Select “Format as Table” on the main tab.
3. The style menu will appear on the screen. You need to choose any of them – the choice will not affect the result.

4. A window for selecting coordinates will open. Since the table was originally selected, you can skip this step and click OK.

5. The style will be applied to the range of cells, and arrow buttons for setting filters will appear in the header.

There is another method to create a “smart” table:

1. Go to the “Insert” tab after selecting a range of cells and click the “Table” button.

2. We check the correctness of the coordinates in the window that appears and click “OK”.

3. A standard style will be applied to the table, and buttons for setting up filtering and sorting will appear in the column headers.

How to filter in Excel by columns

Excel tables are filtered by rows only. In the menu that appears after clicking on the button with an arrow in the column header, you cannot remove all the checkmarks, that is, you cannot hide the entire column. All information in a range of cells is important for filtering and sorting, so filtering one or more columns will not work.

Sort by multiple columns in Excel

When talking about sorting by multiple columns, it is implied that complicated settings are used to order the table, specifying two or more columns.

1. Select any cell in the table that you want to sort.
2. Find the “Sort” button on the “Data” tab and click on it.

3. Click the “Add Level” button in the menu of the window that opens to add an additional line for the conditions.
4. Press the “Options” button. In the window that appears, select the “Table Columns” item, then click on the “OK” button.

5. We fill out several forms for setting conditions and click the “OK” button. The columns of the table will be built in the specified order.

Important! The number of levels for sorting is limited only by the number of columns or rows in the table.

Autofilter

Automatic filtering of table rows is possible using the filter menu. This feature allows you to set more complex settings and create a unique filter. The set of automatic filters varies depending on the cell format. Text and number filters are applied.

1. You need to open the menu in the desired column and click on the item with one of these names. A list of filters that can be used for the table opens.

2. Select one of the filters, fill in the fields in the Custom AutoFilter window, and click OK. The settings will be applied to the range of cells.

Consider the “Custom filter” option. With its help, users can independently set the necessary filtering settings.

1. Open text or number filters again. At the end of the list is the item you want to click on.

2. Fill in the fields – you can select any type of filter that matches the format of the cells from the list and the values ​​that exist in the range of cells. After filling, click the “OK” button. If the filling was consistent, the settings will be applied automatically.

It is worth paying attention to the AND / OR items in the autofilter settings window. They determine how the settings will be applied – together or partially.

slices

Microsoft Excel allows you to attach interactive elements for sorting and filtering – slicers – to tables. After the release of the 2013 version, it became possible to connect slicers to regular tables, and not just to summary reports. Let’s see how to create and configure these options.

Slicing

1. We click on one of the cells of the table – the “Designer” tab will appear on the toolbar, which you need to open.

Pay attention! If your version of Microsoft Excel is older than 2013, it will not be possible to create a slice for a regular table. This feature only applies to PivotTable reports.

2. On the tab, you should find the “Insert Slice” button – click on it and go to the new window that opens.

3. Check all the fields that should be added to the slice, and click on the “OK” button.

Slicers look like dialog boxes with lists of buttons. The names of the items depend on which elements of the table were selected when creating the slicer. To filter the data, you need to click on the button in one of the lists. Filtering by multiple data ranges is possible by clicking the buttons in multiple slices.

Slice Formatting

Editing the appearance of slices and their interaction with other elements is possible with the help of special tools. Let’s try to change the color scheme.

1. Open the “Options” tab and find the “Slicer Styles” section. It contains themes for slices of different colors. We choose any of them – the color will not affect the efficiency of the element. Immediately after clicking on the style, the slicer will acquire the specified colors.

2. It is also possible to change the position of slices on the screen. Use the Move Forward and Move Back buttons in the Arrange section. Select one of the slices and press the button on the toolbar. Now, when moving around the screen, the slice will appear on top of all slices or fall under them.
3. Removing slices is a simple operation. Select the extra window and press the “Delete” key on your keyboard. The slicer disappears from the screen and stops filtering the data in the table.

Conclusion

If you carefully study the instructions for setting up sorting and filters, you can easily cope with the arrangement of lines in the right order or quickly temporarily remove unnecessary information from the sheet. The program is equipped with tools for working with regular and pivot tables. Sorting and filtering options are available for any purpose – simple and complex. More sophisticated tools allow you to get more accurate results.