How to highlight rows when cell value changes in Excel?
If there is a list of repeated values in your worksheet, and you need to highlight the rows based on column A which cell value changes as following screenshot shown. In fact, you can quickly and easily finish this job by using the Conditional Formatting feature.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
To highlight the rows which value is different from above value based on a column, you can apply a simple formula mixed with the Conditional Formatting.
1. Select your data range that you want to use, if your data has headers, exclude them.
2. Then click Home > Conditional Formatting > New Rule, see screenshot:
3. In the New Formatting Rule dialog, click Use a formula to determine which cells to format, and then enter this formula =$A3<>$A2 (A3, A2 in the column which you want to identify the cell value is different from the above value.)into the Format values where this formula is true text box, see screenshot:
4. And then click Format button to open the Format Cells dialog, under the Fill tab, choose one color you like, see screenshot:
5. Then click OK > OK to close the dialogs, and the rows have been highlighted which cell value changes based on column A.
Note: Conditional Formatting tool is a dynamic function, if you change values in column A or insert new row between the data, the formatting will be adjusted as well.
Tip: If your data has no headers, please enter this formula =$A2<>$A1 into the Format values where this formula is true text box