如何检查行是否存在值及Excel单元格是否为空？

云原生阿岚

2026-5-27

Hey there! Let's break down practical solutions for your two Excel questions clearly:

1. How to check if a row contains any value

Depending on your specific need, here are two go-to approaches:

Check for any non-empty cell in the row
Use the COUNTA function, which counts all non-blank cells. For example, to check row 1 from column A to Z:
```
=COUNTA(A1:Z1) > 0
```
This returns TRUE if there’s at least one value in the row, and FALSE if the entire row is empty.
Check if the row contains a specific value
If you’re hunting for a particular value (like "Algebra" or a specific score), use COUNTIF with wildcards:
```
=COUNTIF(A1:Z1, "*your_target_value*") > 0
```
The asterisks (*) act as wildcards, so this will find the value even if it’s part of a longer text string.

2. How to check for empty grade cells in your Excel file

Assuming your course names are in column A and grades in column B (with headers in row 1), here are intuitive methods:

Quick TRUE/FALSE check with a formula
Add a helper column (say column C) and use the ISBLANK function. In cell C2, enter:
```
=ISBLANK(B2)
```
Drag this formula down to all rows. It will show TRUE for empty grade cells and FALSE for cells with values.
Visual highlighting with Conditional Formatting
This is perfect for instantly spotting missing grades:
1. Select all cells in the grades column (column B, starting from row 2).
2. Go to the Home tab → Conditional Formatting → New Rule.
3. Choose "Format only cells that contain" → under "Format only cells with", select "Blanks".
4. Pick a formatting style (like a yellow fill) and click OK. All empty grade cells will be highlighted automatically.
Filter to isolate rows with empty grades
To quickly view only rows missing grades:
1. Select your full data range (including headers).
2. Go to the Data tab → Filter.
3. Click the dropdown arrow in the grades column header → uncheck "Select All" → check "Blanks" → click OK. Only rows with empty grades will show up.

Automate with VBA (for large datasets)
If you have a huge file and want to auto-mark empty cells, use this simple script:

Sub MarkEmptyGrades()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    'Loop through each data row (starting from row 2, assuming row 1 is headers)
    For i = 2 To lastRow
        If IsEmpty(ws.Cells(i, "B")) Then
            ws.Cells(i, "B").Interior.Color = vbRed 'Mark empty cells red
        End If
    Next i
End Sub

To use this: Press Alt + F11 to open the VBA editor, insert a new module, paste the code, and run it.

内容的提问来源于stack exchange，提问作者user9175260