How To Check An Empty Cell In Excel Using Python

In this tutorial, we will learn how to check for empty cells in an Excel sheet using Python. This is a common task when working with spreadsheet data, as you often want to process only cells that contain actual data and skip over empty cells.

With the help of Python and the pandas library, this process can be simplified and automated.

Let’s use the following file:

Step 1: Install the Required Libraries

To get started, you’ll need to install the following libraries:

  • pandas : For data manipulation and analysis.
  • openpyxl : For reading and writing Excel files.

If you haven’t installed these libraries already, you can easily install them using pip:

1
pip install pandas openpyxl

Step 2: Import the Libraries and Read the Excel File

To begin, you should import the required libraries and read the Excel file. Let’s assume that we have an Excel file named example.xlsx. We’ll use the following code to read the Excel file into a pandas DataFrame:

1
2
3
4
import pandas as pd
 
file_name = "example.xlsx"
df = pd.read_excel(file_name, engine='openpyxl')

Step 3: Create a Function to Check for Empty Cells

Let’s create a function called is_empty_cell that takes a DataFrame and cell coordinates (row and column) as arguments. It then checks if the cell is empty or not, returning True for empty cells and False for non-empty cells:

1
2
3
4
def is_empty_cell(df: pd.DataFrame, row: int, col: int) -> bool:
    # Get the cell value
    cell_value = df.iat[row, col]
    return pd.isna(cell_value)

This function uses the iat property of the DataFrame to get the value of a cell at a specific row and column index, and then checks if it is a missing value using the isna function from pandas.

Step 4: Iterate Over the DataFrame and Check for Empty Cells

Now that we’ve created the is_empty_cell function, we can iterate over our DataFrame and check for empty cells. For example, let’s print out the row and column coordinates of all empty cells in our DataFrame:

1
2
3
4
5
6
nrows, ncols = df.shape
 
for row in range(nrows):
    for col in range(ncols):
        if is_empty_cell(df, row, col):
            print(f"Empty cell at row {row + 1}, column {col + 1}")

Note that we add 1 to the row and column indices to match the standard Excel cell references (i.e., starting from 1 instead of 0).

Full Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import pandas as pd
 
def is_empty_cell(df: pd.DataFrame, row: int, col: int) -> bool:
    cell_value = df.iat[row, col]
    return pd.isna(cell_value)
 
file_name = "example.xlsx"
df = pd.read_excel(file_name, engine='openpyxl')
 
nrows, ncols = df.shape
 
for row in range(nrows):
    for col in range(ncols):
        if is_empty_cell(df, row, col):
            print(f"Empty cell at row {row + 1}, column {col + 1}")

Output

To run the script above, make sure you have an example Excel file named example.xlsx with empty and filled cells. You will see an output similar to the following (assuming there are empty cells in your example Excel file):

Empty cell at row 2, column 1
Empty cell at row 3, column 2

These coordinates will help you identify where the empty cells are in your Excel sheet.

Conclusion

In this tutorial, you have learned how to check for empty cells in Excel using Python and Pandas. With this knowledge, you can now easily automate the process of identifying and handling empty cells in your Excel spreadsheets. This will help you save time and minimize errors when working with large datasets in Excel.