I have a worksheet where I am attempting to have a row highlight if a specific cell in the same row is not blank. Is there a way to apply that rule to the entire worksheet without formatting each individual row?
2 Answers
Have you tried checking Conditioal formatting. It is available in both MS Excel and Google sheets and most likely any other spreadsheet editor you may be working with.
1. MS Excel
Highlight one row, for instance the first row of your data
Then keep in mind the cell in the column that you are checking whether it's blank. In this case we'll use the second column (B). So the cell is B1
Open Conditional Formatting from the Home Tab
Click on New Rule
Then click on Use a formula to determine which cells to format
So the rule you will apply here will check whether that cell is empty and format the entire row, otherwise the entire row will not be highlighted. The formula is:
=$B1=""
Which basically checks whether that cell is empty.
After that, click on Format, still on that popup window to choose your custom formatting, such as border and fill then the formatting will be applied to that row.
Now to make it affect the entire excel file, just select the current(first) row with your cursor and (copy) CTRL + C. Then select your whole excel file or within the range of your data and right click, then instead of Pasting the data, paste only the formatting. And the conditional formatting will be applied to the entire sheet.
Is there a way to apply that rule to the entire worksheet without formatting each individual row
Maybe you could apply a formula to all rows.
One worksheet has a maximum of 1048576 rows, the range can be set to be =$1:$1048576.
