Excel: Use conditional formatting and ignore hidden rows
Conditional formatting can be used to give us visual cues for when a value in a column changes.
But the rules we set up under normal circumstances might not work so well if we start filtering the data.
The issue is that most of the time, formulas comparing one row with another don't pay attention to whether a row is hidden or not.
A big help in this situation is the SUBTOTAL function applied to a single row:
=IF(SUBTOTAL(103, [@Job])=1,[@Job],"")
If the current row is hidden, then the sub-total using COUNTA while ignoring hidden rows (subtotal 103) will be 0. In that case, this formula returns an empty string. If the row is not hidden, it returns the job number.
Since this is a helper column, we can call it "HiddenJob" (we intend to hide it).
When we combine this with last-to-first XLOOKUP, we can find the first non-hidden job number above the current row and check if it's different to the job number on the current row.
We can then use the result of that inequality to format the row.
By doing this, the formatting is applied appropriately even when the data are filtered.
Please watch the short video to see this in action and let me know if there's a simpler way to do this (I bet there is!) 😀