How to Find Adjacent Blank Cells in an Excel Row with Formulas
Question details
The user needs a method to determine whether a specific row contains two or more consecutive blank cells, regardless of the contents of the non-blank cells.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Cleaning or analyzing datasets where rows missing consecutive data points need to be flagged for review.
- Observed behavior
- The goal is to output a specific indicator, such as 'Yes' or 'No', to flag rows that have adjacent empty cells.
Ensure your data range is contiguous and note the exact starting and ending columns for the row you want to check (for example, B2 to J2).
Use an Array Formula with FREQUENCY and IF
Use a combined array formula to calculate the sequence length of blank cells and output 'Yes' if multiple consecutive blanks are detected.
This method uses an array formula to calculate the frequency of empty cells grouped by the positions of non-empty cells. If the maximum frequency of these groupings is greater than 1, it indicates the presence of consecutive blank cells.
Click on an empty cell where you want the result to appear (e.g., cell K2 next to your data row).
Type or paste the following formula into the formula bar: =IF(MAX(FREQUENCY(IF(B2:J2="",COLUMN(B2:J2)),IF(B2:J2<>"",COLUMN(B2:J2))))>1,"Yes","No"). Adjust the range B2:J2 to match your actual data.
If you are using Microsoft 365 or WPS Spreadsheet, simply press Enter. If you are using an older version of Excel, you must press Ctrl + Shift + Enter to apply it as an array formula.
Click the small square at the bottom-right corner of the cell (fill handle) and drag it down to apply the formula to the remaining rows.
Easily Manage Complex Array Calculations in WPS Office
WPS Spreadsheet fully supports advanced array formulas, including FREQUENCY and IF combinations, making it simple to identify adjacent blank cells. It provides a lightweight, highly compatible environment for all your advanced data analysis tasks.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing the rows you want to analyze.
- 2. Apply the formula: Select the result cell and paste the MAX/FREQUENCY formula to check for blanks.
- 3. Confirm and drag: Press Enter (or Ctrl+Shift+Enter) and drag the fill handle to apply the check across all your data rows.
Frequently Asked Questions
How do I change the formula to detect three or more adjacent blank cells?
To detect three consecutive blank cells instead of two, modify the logic condition at the end of the formula. Change the '>1' to '>2' (e.g., ...>2,"Yes","No").
Why does the formula return an error message when I press Enter?
In older versions of Excel that do not support dynamic arrays, combining IF and COLUMN functions inside FREQUENCY requires array processing. You must press Ctrl+Shift+Enter simultaneously instead of just Enter.
Can I use Conditional Formatting to highlight the rows instead?
Yes, you can use a modified version of this formula within the Conditional Formatting rules. Select your rows, go to Conditional Formatting > New Rule > Use a formula, and paste the logic portion (without the IF statement) to apply a background color.
