The sheet shown below uses a conditional format to highlight any value greater than 4. To set this rule, do the following:. Select the data range, A2:A7. On the Home tab, click Conditional Formatting in the Styles group.
![Filter by row color excel Filter by row color excel](/uploads/1/2/5/6/125615612/446910394.jpg)
Changing the cursor in Microsoft Excel is something that you can do using the ribbon toolbar. Change the cursor in Microsoft Excel with help from a longtime software expert and professional in.
![filter By Cell Color \ filter By Cell Color \](/uploads/1/2/5/6/125615612/668360537.png)
Choose Highlight Cells Rules and then choose Greater Than. In the resulting dialog, enter 4. At this point, you could choose Custom Format from the With control's dropdown. Using the default simplifies the example.
To filter by the conditional format, you'll need to add a filter to the data range as follows:. Select the data and header cell, A1:A7. On the Data tab, click Filter in the Sort & Filter group. Excel adds the filter automatically.
Before we experiment with the filtering, use any method you like to add a SUM function to A9. You need to skip a row so Excel won't interpret the function cell as data and filter it later.
Filters Now, let's filter the data by the conditional format and see what happens to the SUM function:. From the filter dropdown, select Filter By Color. In the resulting submenu, choose either cell or font color. Filtering by the conditional format was easy, but the SUM function doesn't reflect the data that you see. If you want a stable value that reflects the entire data set, you're done. If you want a function that evaluates only the visible data, you need to change the function.
Replace the SUM function as follows:. First, clear the filter by choosing Select All from the dropdown or clicking Clear on the Data tab (in the Sort & Filter group). Select A9 and replace the SUM function with the following function: =SUBTOTAL(9,A2:A7) After entering the SUBTOTAL function, reapply the filter.
This time, the function displays the sum of only the visible values. The function's first argument, 9, denotes the function's mathematical operation (sum) and SUBTOTAL ignores all values hidden by a filter. How convenient. Also read:. Related Topics.