A quick how-to for Excel explaining how to ‘grey out’ an entire row based of value of one cell – in this case each cell with an ‘x’ in column A. I often use this at work to track progress of various spreadsheets for different deployment projects.
I’ve made this ‘How-To’ using Excel on MacOS – some steps might be slightly different on Windows, but same principals and formulas apply.
First select any cell in row 2, A2 as an example.
![](https://blog.jsobczak.com/wp-content/uploads/2021/07/image-5-700x182.png)
Go to ‘Conditional Formatting‘->’New Rule‘
![](https://blog.jsobczak.com/wp-content/uploads/2021/07/image-6.png)
Under ‘Style:‘ select ‘Classic‘ and then ‘Use a formula to determine which cells to format‘
In the formula bar type ‘=$A2=”x”‘ – pay attention to dollar sign. It has to be in the right place for this to work.
![](https://blog.jsobczak.com/wp-content/uploads/2021/07/image-7.png)
Then under ‘Format with:‘ select ‘Customised Format…‘
You can customise it in any way you like. I like to keep it simple and change the colour of the text to grey. It makes rest of the cells stand out. You could add ‘Strikethrough’ effect or change size; or even style of the font on top of changing the colour.
![](https://blog.jsobczak.com/wp-content/uploads/2021/07/image-4.png)
Confirm your customisation options by pressing ‘OK‘ and then ‘OK‘ again on the ‘New Formatting Rule’ window.
![](https://blog.jsobczak.com/wp-content/uploads/2021/07/image-10-700x180.png)
You will notice that the new conditional formatting is applied only to the cell you had select. To apply this condition to all rows go to ‘Conditional Formatting‘->’Manage Rules‘ and locate the rule you have just created.
![](https://blog.jsobczak.com/wp-content/uploads/2021/07/image-11.png)
If you are unable to see it, change selection to show formatting for ‘This worksheet‘ instead of ‘Current Selection’.
![](https://blog.jsobczak.com/wp-content/uploads/2021/07/image-12-700x324.png)
In the field ‘Applies to‘ type ‘=$1:$10‘, or any number of rows you want to apply this condition to, then press ‘OK‘.
![](https://blog.jsobczak.com/wp-content/uploads/2021/07/image-13-700x323.png)
You will now see that all rows with an ‘x‘ in first column are greyed out to mark them as completed.
![](https://blog.jsobczak.com/wp-content/uploads/2021/07/image-14-700x186.png)