Hiding or unhiding rows and columns in Excel gives you precise control over the visibility of specific data within a worksheet. By hiding rows or columns, you can temporarily remove irrelevant information from view, reducing visual clutter and creating a cleaner workspace. This makes it easier to work with the data that truly matters and enhances your productivity. On the other hand, unhiding rows or columns allows you to restore visibility and regain access to previously hidden information whenever you need it. This is advantageous when you have hidden data that requires further review, modification, or analysis. In this article, we will explain how to hide or unhide rows and columns in Excel in Python using Spire.XLS for Python.

• Hide Specific Rows and Columns in Excel in Python
• Unhide Specific Hidden Rows and Columns in Excel in Python
• Hide Multiple Rows and Columns at Once in Excel in Python
• Unhide All Hidden Rows and Columns in Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Hide Specific Rows and Columns in Excel in Python

Spire.XLS for Python offers the Worksheet.HideRow(rowIndex) and Worksheet.HideColumn(columnIndex) methods to hide a specific row and column in an Excel worksheet. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
• Hide specific rows in the worksheet using Worksheet.HideRow(rowIndex) method.
• Hide Specific columns in the worksheet using Worksheet.HideColumn(columnIndex) method.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Hide the 3rd and the 7th rows
sheet.HideRow(3)
sheet.HideRow(7)

# Hide the 3rd and the 6th columns
sheet.HideColumn(3)
sheet.HideColumn(6)

# Save the result file
workbook.SaveToFile("HideRowsAndColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Unhide Specific Hidden Rows and Columns in Excel in Python

You can use the Worksheet.ShowRow(rowIndex) and Worksheet.ShowColumn(columnIndex) methods to unhide a specific hidden row and column. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
• Unhide specific hidden rows in the worksheet using Worksheet.ShowRow(rowIndex) method.
• Unhide specific hidden columns in the worksheet using Worksheet.ShowColumn(columnIndex) method.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("HideRowsAndColumns.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Unhide the 3rd and the 7th rows
sheet.ShowRow(3)
sheet.ShowRow(7)

# Unhide the 3rd and the 6th columns
sheet.ShowColumn(3)
sheet.ShowColumn(6)

# Save the result file
workbook.SaveToFile("ShowRowsAndColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Hide Multiple Rows and Columns at Once in Excel in Python

To hide multiple rows and columns at once, you can use the Worksheet.HideRows(startRowIndex, rowCount) and Worksheet.HideColumns(startColumnIndex, columnCount) methods. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
• Hide multiple rows in the worksheet using the Worksheet.HideRows(startRowIndex, rowCount) method.
• Hide multiple columns in the worksheet using Worksheet.HideColumns(startColumnIndex, columnCount) method.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Hide 3, 4 and 5 rows
sheet.HideRows(3, 3)

# Hide 5, 6 and 7 columns
sheet.HideColumns(5, 3)

# Save the result file
workbook.SaveToFile("HideMultipleRowsAndColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Unhide All Hidden Rows and Columns in Excel in Python

To unhide all hidden rows and columns, you first need to loop through the used rows and columns in the worksheet. Next, find the hidden rows and columns using Worksheet.GetRowIsHide(rowIndex) and Worksheet.GetColumnIsHide(columnIndex) methods, and then unhide them using Worksheet.ShowRow(rowIndex) and Worksheet.ShowColumn(columnIndex) methods. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
• Iterate through the used rows in the worksheet and find the hidden rows using Worksheet.GetRowIsHide(rowIndex) method.
• Unhide every hidden row using Worksheet.ShowRow(rowIndex) method.
• Iterate through the used columns in the worksheet and find the hidden columns using Worksheet.GetColumnIsHide(columnIndex) method.
• Unhide every hidden column using Worksheet.ShowColumn(columnIndex) method.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("HideMultipleRowsAndColumns.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Iterate through the used rows in the worksheet
for i in range(1, sheet.LastRow + 1):
    # Check if the current row is hidden
    if sheet.GetRowIsHide(i):
        # Unhide the hidden row
        sheet.ShowRow(i)

# Iterate through the used columns in the worksheet
for j in range(1, sheet.LastColumn + 1):
    # Check if the current column is hidden
    if sheet.GetColumnIsHide(j):
        # Unhide the hidden column
        sheet.ShowColumn(j)

# Save the result file
workbook.SaveToFile("ShowAllHiddenRowsAndColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.