Office hacks: the wonderful use of conditional formatting highlighting query results

When querying through drop-down lists, highlighting the query results in the data source will make the query results clearer. To achieve such an effect, it actually does not require much complicated setup or design, and the clever use of the combination of conditional formatting and CELL functions can solve the problem.

First, the establishment of the query menu. In cell H1, enter “query product code”, select cell H2, select the “Data” tab, click “Data Validation”, in the pop-up window “Settings” tab, “Allow” at the choice of “series”, select the data area A2: A8, OK, in cell H2 to create a drop-down menu (Figure 1).

1822A-GLCX-1

Next, select the data area, switch to the “Start” tab, click “Conditional Formatting → New Rule”, in the pop-up window, select “Use the formula to determine the cells to be formatted “, in the “format for the value of this formula” at the input “= cell (“contents”)=$A1″, click “Format “, in the pop-up window to set up a good font color, style and cell fill color, etc. (Figure 2).

1822A-GLCX-2

Tip: cell (“contents”) indicates the contents of the cell.

After the settings are complete, click on cell H2, select the menu in the drop-down menu, at this time, in the data area according to the menu to select the corresponding record, it will highlight the corresponding entry (Figure 3).

1822A-GLCX-3

Application extension.

Using “Data Validation”, enter “Product Code” at A11, create a drop-down list at A12, source A2:A8, create a drop-down list at B11, source B1:E1; enter at B12 “=VLOOKUP($A$12,$A$1:$E$8,MATCH($B$11,A1:E1,0),0)”, so that you can find the corresponding value in the data source according to the selection result at A12 and B11. Select the data area, create a new rule, in the pop-up window, select “use the formula to determine the cells to be formatted”, in the “format for the value of this formula” at the input “= ($A1=$A$12) + ( A$1=$B$11)”, click “Format” in the pop-up window to set the font color, style and cell fill color, etc.. In this way, according to the selection at A12, B11, in the data source for precise positioning and highlighting (Figure 4).

1822A-GLCX-4

Leave a Comment