Find Empty Cells in Excel using Python

Hello friends, often you have seen empty cells in your Excel file. This results in erroneous conclusions. You can easily find these cells instead of doing it manually, which is time-consuming.

Find Empty Cells in Excel using Python

To work on an Excel file, you need to install pandas on your local system. You can check out my post on Create DataFrame from nested JSON in Python to learn how to install pandas on your system. Pandas require a dependency to be able to work on an Excel file. It’s mandatory for us to install the openpyxl module as well. You can follow our post on How to install openpyxl in Python to install openpyxl.

I have created a sample Excel file for explanation purposes. It has two columns Cust_ID and Cust_Name.

Read Excel File

I imported the pandas module as pd. Now That I need to read my Excel file, I’ve used the read_excel function and passed my Excel file’s name Cust_details.xlsx as an argument.

import pandas as pd

file = pd.read_excel('Cust_details.xlsx')
print(file)

Output :

   Cust_ID         Cust_Name
0   ID2008     Reyansh Mitra
1   ID9801    Richard Larson
2      NaN               NaN
3   ID6510               NaN
4      NaN               NaN
5   ID4509  Payal Chatterjee
6      NaN               NaN
7      NaN               NaN
8      NaN      Has Anderson
9      NaN               NaN
10     NaN               NaN
11  ID5409      Kejriwal Lal

Thus I was able to read my Excel file. NaN represents the empty cells.

Find Empty Cells using isna()

I’ve used the isna() function to find the empty cells. You need to add this attribute to your Python object that holds your Excel file’s value.

Code :

find_null = file.isna()
print(find_null)

Output :

    Cust_ID  Cust_Name
0     False      False
1     False      False
2      True       True
3     False       True
4      True       True
5     False      False
6      True       True
7      True       True
8      True      False
9      True       True
10     True       True
11    False      False

This isna() function returns a boolean value for every cell present in the Excel file. It returns True for empty cells and False for cells with values.

You can also find the sum of all the empty cells in a column just by using a sum() function on the find_null variable.

Code :

find_null_sum = find_null.sum()
print(find_null_sum)

Output :

Cust_ID      7
Cust_Name    7

Thus now you know how to read an Excel file using pandas, find the empty cells in Excel using Python values, and also retrieve their sum.