Highlight Rows Where All Cells Have Content in Google Sheets

Published on

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

  1. Select the range you want to highlight, for example, A1:C.
  2. Click Format > Conditional formatting.
  3. Under Format rules, select Custom formula is and enter the above formula.
  4. Under Formatting style, choose the cell color, font color, etc., that you want to apply to rows where all cells have content.
  5. Click Done.
An example in Google Sheets showing highlighted rows where all cells in the row contain content

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.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.