In this article let us see how to count/find the number of blank and Non-Blank cells in an Excel table using Python

Along with the use of excel spreadsheets in nearly every aspect of daily life to perform calculations, analysis, and visualization of data and information, keeping track of the blank and non-blank cells in your excel table is essential. Counting all the cells can be difficult, thus python has a very short and simple code for this task which makes life easier.

**sampleExcelFile:**

#### Python xlrd Module:

The xlrd module can be used to get data from a spreadsheet. It can be used to read, write, or modify data. In addition, the user may have to navigate through several sheets and get data based on some criteria, or edit some rows and columns, among other things.

To extract data from a spreadsheet, use the xlrd module.

**NOTE:**

Support for reading xlsx sheets has been explicitly removed from xlrd module.

**Installation:**

Type the below command to install the xlrd module:

pip install xlrd

**Functions Used:**

**open_workbook():**It is used to open a workbook.**sheet_by_index():**This function is used to access/open the sheet with a specific index number.**nrows:**It is used to give the total number of rows.**ncols:**It is used to give the total number of rows.

## Python Program to Find the number of Blank and Non-Blank cells in Excel table

**Step#1: Importing the module**

Import xlrd module using the import keyword to work with the excel file.

# Import xlrd module using the import keyword import xlrd

**Step#2: Input the excel File**

Here we input an excel file on which the count of Non-empty/filled cells must be performed by giving the path of the excel file.

# Take a variable that stores the count of all empty cells in an excel sheet empty_cells=0 # Take another variable that stores the count of all Non-empty/filled cells in an excel sheet filled_cells=0 # Give the path of the excel file and store it in a variable filepath="sampleExcelFile.xlsx"

**Step#3: Creating a workbook and a worksheet**

Here we create a workbook for the given excel file using the open_workbook() function and also access the required worksheet using the sheet_by_index() method.

# Pass the above file path to the open_workbook() function of the xlrd module # to create a workbook object workbook=xlrd.open_workbook(filepath) # Create a first worksheet in the above workbook using the sheet_by_index() function worksheet=workbook.sheet_by_index(0)

**Step#4: Finding the number of blank and Non-blank cells **

Now, let us calculate the number of blank and non-blank cells using the below part of the code.

# Iterate through each row of the worksheet using the nrows attribute # The nrows attribute is used to extract the number of rows for excelrow in range (worksheet.nrows): # Iterate through each column of the worksheet using the ncols attribute # using the nested for loop # The ncols attribute is used to extract the number of columns for excelcolumn in range (worksheet.ncols) : # Check if the cell is a blank cell using the cell_value() function and # if conditional statement if (worksheet.cell_value(excelrow,excelcolumn)==""): # If it is true, then increment the count value of above empty_cells # by 1 empty_cells+=1 else : # Else in increment the count value of above filled_cells by 1 filled_cells+=1 # Print the count of empty cells in a given excel sheet print("The count of empty cells in a given excel sheet = ", empty_cells) # Print the count of Non-empty/filled cells in a given excel sheet print("The count of Non-empty/filled cells in a given excel sheet = ", filled_cells)

#### Complete Code

Here is the complete code for you.

**Approach:**

- Import xlrd module using the import keyword.
- Take a variable that stores the count of all empty cells in an excel sheet.
- Take another variable that stores the count of all Non-empty/filled cells in an excel sheet
- Give the path of the excel file and store it in a variable..
- Pass the above file path to the open_workbook() function of the xlrd module

to create a workbook object. - Create the first worksheet in the above workbook using the sheet_by_index() function.
- Iterate through each row of the worksheet using the nrows attribute. The
`nrows`

attribute is used to extract the number of rows - Iterate through each column of the worksheet using the ncols attribute using the nested for loop. The
`ncols`

attribute is used to extract the number of columns - Check if the cell is a blank cell using the cell_value() function and if conditional statement
- If it is true, then increment the count value of the above empty cells by 1.
- Else in increment the count value of above filled cells by 1.
- Print the count of empty cells in a given excel sheet.
- Print the count of Non-empty/filled cells in a given excel sheet.
- The Exit of the Program.

**Below is the implementation:**

# Import xlrd module using the import keyword import xlrd # Take a variable that stores the count of all empty cells in an excel sheet empty_cells=0 # Take another variable that stores the count of all Non-empty/filled cells in an excel sheet filled_cells=0 # Give the path of the excel file and store it in a variable filepath="sampleExcelFile.xlsx" # Pass the above file path to the open_workbook() function of the xlrd module # to create a workbook object workbook=xlrd.open_workbook(filepath) # Create a first worksheet in the above workbook using the sheet_by_index() function worksheet=workbook.sheet_by_index(0) # Iterate through each row of the worksheet using the nrows attribute # The nrows attribute is used to extract the number of rows for excelrow in range (worksheet.nrows): # Iterate through each column of the worksheet using the ncols attribute # using the nested for loop # The ncols attribute is used to extract the number of columns for excelcolumn in range (worksheet.ncols) : # Check if the cell is a blank cell using the cell_value() function and # if conditional statement if (worksheet.cell_value(excelrow,excelcolumn)==""): # If it is true, then increment the count value of above empty_cells # by 1 empty_cells+=1 else : # Else in increment the count value of above filled_cells by 1 filled_cells+=1 # Print the count of empty cells in a given excel sheet print("The count of empty cells in a given excel sheet = ", empty_cells) # Print the count of Non-empty/filled cells in a given excel sheet print("The count of Non-empty/filled cells in a given excel sheet = ", filled_cells)

**Output:**

The count of empty cells in a given excel sheet = 29 The count of Non-empty/filled cells in a given excel sheet = 101