Get values of all rows in a particular column in openpyxl - Python
Last Updated :
26 Apr, 2025
In this article, we will explore how to get the values of all rows in a particular column in a spreadsheet using openpyxl in Python. We will start by discussing the basics of openpyxl and how to install and import it. Then, we will walk through for example, how to extract the values of a particular column from a spreadsheet and store them in a list.
To install openpyxl using pip, open a terminal window or command prompt and enter the following command:
pip install openpyxl
Raw Spreadsheet Data
For example, suppose you have a spreadsheet with the following data:
Name | Age | Gender |
John | 30 | Male |
Jane | 25 | Female |
Bob | 35 | Male |
Alice | 28 | Female |
Extracting the Values of a Particular Column
Now that you know how to read a spreadsheet and access the rows and cells in a sheet, you can extract the values of a particular column from the sheet. To do this, you can simply iterate over the rows in the sheet and check the value of the cell in the column you are interested in. You can then store the values in a list or other data structure for further processing.
Example 1
To extract the values of the "Name" column, you could use the following code:
Python3
# Import openpyxl
import openpyxl
# Open the spreadsheet
workbook = openpyxl.load_workbook("data.xlsx")
# Get the first sheet
sheet = workbook.worksheets[0]
# Create a list to store the values
names = []
# Iterate over the rows in the sheet
for row in sheet:
# Get the value of the first cell
# in the row (the "Name" cell)
name = row[0].value
# Add the value to the list
names.append(name)
# Print the list of names
print(names)
Output:
['Name', 'John', 'Jane', 'Bob', 'Alice']
Example 2
Note that the first row in the sheet contains the column names, so it is included in the list. If you want to exclude the column names from the list, you can add a check to skip the first row:
Python3
# Import openpyxl
import openpyxl
# Open the spreadsheet
workbook = openpyxl.load_workbook("data.xlsx")
# Get the first sheet
sheet = workbook.worksheets[0]
# Create a list to store the values
names = []
# Iterate through rows
for i, row in enumerate(sheet):
# Skip the first row (the row with the column names)
if i == 0:
continue
# Get the value of the first cell in the row
name = row[0].value
# Add the value to the list
names.append(name)
# Print the list of names
print(names)
Output:
['John', 'Jane', 'Bob', 'Alice']
Extracting the Values of a Particular Column Name
In the previous example, we extracted the values of a particular column by specifying the index of the column. However, in some cases, it may be more convenient to extract the values of a column by its name, rather than its index. To do this, you can use the iter_cols() method of the Sheet object to iterate over the columns in the sheet, and check the value of the first cell in each column to determine the column name.
Example 1
To extract the values of the "Name" column by its name, you could use the following code:
Python3
# Import openpyxl
import openpyxl
# Open the spreadsheet
workbook = openpyxl.load_workbook("data.xlsx")
# Get the first sheet
sheet = workbook.worksheets[0]
# Create a list to store the values
names = []
# Iterate through columns
for column in sheet.iter_cols():
# Get the value of the first cell in the
# column (the cell with the column name)
column_name = column[0].value
# Check if the column is the "Name" column
if column_name == "Age":
# Iterate over the cells in the column
for cell in column:
# Add the value of the cell to the list
names.append(cell.value)
# Print the list of names
print(names)
Output:
['Age', 30.0, 25.0, 35.0, 28.0]
Example 2
To exclude the column name from the list, you can add a check to skip the first cell:
Python3
# Import openpyxl
import openpyxl
# Open the spreadsheet
workbook = openpyxl.load_workbook("data.xlsx")
# Get the first sheet
sheet = workbook.sheets[0]
# Create a list to store the values
names = []
# Iterate over the columns in the sheet
for column in sheet.iter_cols():
# Get the value of the first cell in the column
# (the cell with the column name)
column_name = column[0].value
# Check if the column is the "Name" column
if column_name == "Age":
# Iterate over the cells in the column
for i, cell in enumerate(column):
# Skip the first cell (the cell with the column name)
if i == 0:
continue
# Add the value of the cell to the list
names.append(cell.value)
# Print the list of names
print(names)
Output:
[30.0, 25.0, 35.0, 28.0]
Similar Reads
Get a List of Particular Column Values in a Pandas DataFrame
In this article, you'll learn how to extract all values of a particular column from a Pandas DataFrame as a Python list. Get a List of a Particular Column Using tolist()tolist() method is a simple and effective way to convert a Pandas Series (column) into a Python list. Here's an example:Pythonimpor
2 min read
Get value of a particular cell in PySpark Dataframe
In this article, we are going to get the value of a particular cell in the pyspark dataframe. For this, we will use the collect() function to get the all rows in the dataframe. We can specify the index (cell positions) to the collect function Creating dataframe for demonstration: Python3 # importing
2 min read
How to Print Out All Rows of a MySQL Table in Python?
MySQL server is an open-source relational database management system which is a major support for web-based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. Â In order to access MySQL databases from a
2 min read
Python | Adjusting rows and columns of an excel file using openpyxl module
Prerequisites : Excel file using openpyxl writing | reading Set the height and width of the cells:Worksheet objects have row_dimensions and column_dimensions attributes that control row heights and column widths. A sheetâs row_dimensions and column_dimensions are dictionary-like values; row_dimensio
3 min read
Sum of two columns in openpyxl and result in a new column
Most of the developers especially developers who work in the field of data use Excel alongside other programming languages on a daily basis. Now Sometimes a situation might arise that they need to find the sum of two adjacent or non-adjacent columns and fill a separate column using the summation of
4 min read
Find the Frequency of a Particular Word in a Cell in an Excel Table in Python
In this article, we'll look at how to use Python to find the number of times a word appears in a cell of an Excel file. Before we begin with the steps of the solution, the following modules/libraries must be installed. We will use the following sample Excel file to determine the frequency of the inp
5 min read
How to extract a particular column from 1D array of tuples?
In this article, we will cover how to extract a particular column from a 1-D array of tuples in python. Example Input:  [(18.18,2.27,3.23),(36.43,34.24,6.6),(5.25,6.16,7.7),(7.37,28.8,8.9)] Output: [3.23, 6.6 , 7.7 , 8.9 ] Explanation: Extracting the 3rd column from 1D array of tuples. Method 1: Us
2 min read
How to Count the Number of Rows in a MySQL Table in Python?
MySQL server is an open-source relational database management system which is a major support for web-based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. In order to access MySQL databases from a
2 min read
Formatting Cells using openpyxl in Python
When it comes to managing Excel files programmatically, Python offers a powerful tool in the form of the openpyxl library. This library not only allows us to read and write Excel documents but also provides extensive support for cell formatting. From fonts and colors to alignment and borders, openpy
4 min read
Limited rows selection with given column in Pandas | Python
Methods in Pandas like iloc[], iat[] are generally used to select the data from a given dataframe. In this article, we will learn how to select the limited rows with given columns with the help of these methods. Example 1: Select two columns Python3 # Import pandas package import pandas as pd # Defi
2 min read