How to filter data in Excel by color

In Microsoft Office Excel, starting from version 2007, it became possible to sort and filter the cells of a table array by color. This feature allows you to quickly navigate the table, increases its presentability and aesthetics. This article will cover the main ways to filter information in Excel by color.

Features of filtering by color

Before moving on to consider ways to filter data by color, it is necessary to analyze the benefits that such a procedure provides:

• Structuring and ordering information, which allows you to select the desired fragment of the plate and quickly find it in a large range of cells.
• Highlighted cells with important information can be further analyzed.
• Filtering by color highlights information that meets the specified criteria.

How to filter data by color using Excel’s built-in option

The algorithm for filtering information by color in an Excel table array is divided into the following steps:

1. Select the required range of cells with the left mouse button and move to the “Home” tab located in the top toolbar of the program.
2. In the area that appears in the Editing subsection, you need to find the “Sort and Filter” button and expand it by clicking on the arrow below.

3. In the menu that appears, click on the “Filter” line.

4. When the filter is added, small arrows will appear in the table column names. At this stage, the user needs to click LMB on any of the arrows.

5. After clicking on the arrow in the column name, a similar menu will be displayed, in which you need to click on the Filter by color line. An additional tab will open with two available functions: “Filter by cell color” and “Filter by font color”.

6. In the “Filter by cell color” section, select the shade by which you want to filter the source table by clicking on it with LMB.
7. Check result. After doing the above manipulations, only the cells with the previously specified color will remain in the table. The remaining elements will disappear, and the plate will be reduced.

You can manually filter data in an Excel array by removing rows and columns with unwanted colors. However, the user will have to spend additional time to complete this process.

If you select the desired shade in the “Filter by font color” section, then only the lines in which the font text is written in the selected color will remain in the table.

Pay attention! In Microsoft Office Excel, the filtering by color function has a significant drawback. The user can select only one shade, by which the table array will be filtered. It is not possible to specify multiple colors at once.

How to sort data by multiple colors in Excel

There are usually no problems with sorting by color in Excel. It is done in the same way:

1. By analogy with the previous paragraph, add a filter to the table array.
2. Click on the arrow that appears in the column name, and select “Sort by color” in the drop-down menu.

3. Specify the required sorting type, for example, select the desired shade in the “Sort by cell color” column.
4. After performing the previous manipulations, the rows of the table with the previously selected shade will be located in the first place in the array in order. You can also sort other colors.

Additional Information! You can also sort the data in the table using the “Custom sorting” function, adding several levels by color.

How to filter information in a table by color using a custom function

In order for Microsoft Office Excel to select a filter to display multiple colors in a table at once, you need to create an additional setting with a fill tint. According to the created shade, the data will be filtered in the future. A custom function in Excel is created according to the following instructions:

1. Go to the “Developer” section, which is located at the top of the main menu of the program.
2. In the tab area that opens, click on the “Visual Basic” button.
3. The editor built into the program will open, in which you will need to create a new module and write the code.

To apply the created function, you must:

1. Return to the Excel worksheet and create two new columns next to the original table. They can be called “Cell Color” and “Text Color” respectively.

2. Write the formula in the first column “= ColorFill()». The argument is enclosed in parentheses. You need to click on a cell with any color in the plate.

3. In the second column, indicate the same argument, but only with the function “=ColorFont()».

4. Stretch the resulting values ​​to the end of the table, extending the formula to the entire range. The received data is responsible for the color of each cell in the table.

5. Add a filter to the table array according to the above scheme. The data will be sorted by color.

Important! Sorting in Excel using a user-defined function is done in a similar way.

Conclusion

Thus, in MS Excel, you can quickly filter the original table array by the color of the cells using various methods. The main methods of filtering and sorting, which are recommended to use when performing the task, were discussed above.