When you have survey data in Google Sheets from Google Forms, third-party apps, or data entered by other users, you might want to find missing information. Highlighting rows where all cells have content helps you easily identify records with partially filled data.
If you have data in three columns in the range A1:C, you can use the following formula to highlight rows where all cells have content:
=COUNTA($A1:$C1)=COLUMNS($A$1:$C$1)The COUNTA function returns the number of cells with values, and the COLUMNS function returns the total number of cells in the range. If both values are equal, it means all cells in the row contain data.
This approach ensures you can focus on rows with incomplete information by highlighting rows that meet your criteria.
Example of Highlighting Rows Where All Cells Have Content in Google Sheets
- Select the range you want to highlight, for example, A1:C.
- Click Format > Conditional formatting.
- Under Format rules, select Custom formula is and enter the above formula.
- Under Formatting style, choose the cell color, font color, etc., that you want to apply to rows where all cells have content.
- Click Done.
If you want to highlight a different range, such as C10:Z100, you should adjust the formula to:
=COUNTA($C10:$Z10)=COLUMNS($C10:$Z10)Ensure that the reference matches the first row in the “Apply to range” field in the conditional formatting settings.
Additional Tips
Highlight Columns Where All Cells Have Content
If you want to highlight columns where all cells have content, use a different formula. For example, for the range A1:C10, you can use:
=COUNTA(A$1:A$10)=ROWS($A$1:$A$10)Here, the COUNTA function counts the filled cells in the column, and the ROWS function returns the total number of rows. If both values are equal, it means all cells in the column contain data.
Highlight an Entire Range Where All Cells Have Content
Sometimes, you may want to highlight an entire range, such as A1:C10, if every cell within the range is filled. For this, you can use:
=COUNTA($A$1:$C$10)=ROWS($A$1:$C$10)*COLUMNS($A$1:$C$10)In this formula, the COUNTA function returns the number of filled cells in the range, while the product of ROWS and COLUMNS gives the total number of cells. If both values are equal, it means all cells in the range contain data.
