How To Convert Rows Into Columns In Excel Using Python

In today’s data-driven world, we often deal with large datasets containing a massive amount of information.

The need to organize, manipulate, and analyze this data is a must-have skill for any data analyst or professional working with data.

One such core manipulation functionality often involves converting rows into columns or vice versa in spreadsheet applications like Microsoft Excel.

This tutorial will guide you through the process of converting rows into columns in an Excel sheet using the Python programming language. We’ll be using the library openpyxl for this purpose, which makes it easy to work with Excel files in Python.

Step 1: Install openpyxl

To begin with, first, make sure that you have installed the required library – openpyxl. It is a widely-used library for reading and writing Excel files, specifically .xlsx files. To install this library, you can use the following pip command:

Step 2: Load the Excel Workbook

After installing the required library, we need to load the Excel workbook containing the data. If you don’t have an Excel workbook ready, you can create a simple one for this tutorial. Here’s an example:

 Name    Age    Country
 John    28     USA
 Alice   25     Canada
 Bob     23     UK

Once the Excel file is ready, load the workbook using the following code:

Make sure to replace “your_excel_file_path.xlsx” with the actual path to your Excel file.

Step 3: Read the Data from the Sheet

Now that we have the workbook loaded, we can read the data from it. To do this, first, get the active worksheet using the active method and then read the data:

Step 4: Convert Rows to Columns

Next, let’s convert the rows into columns which is also called transposing the data. This is a simple process where we use the zip function in Python to transpose our rows and columns:

Step 5: Create a New Sheet and Write Transposed Data

After transposing the data, we need to write it back to the Excel workbook. The first thing to do is to create a new sheet in the workbook:

Now we’ll write the transposed data to this new sheet:

Step 6: Save the Transposed Data to a New Excel File

Finally, save the updated workbook to a new file:

Your transposed data is now successfully saved to a new Excel file.

Full Code


In this tutorial, we learned how to convert rows into columns in an Excel sheet using the Python library openpyxl. This is a powerful and versatile tool for Excel file manipulation, and you can easily extend this example to perform more complex operations while reading/writing Excel files.