# Hide Excel Columns Using PowerShell

## Problem

In this blog post, we will show you how to hide Excel columns using PowerShell.

As the context, our original columns will look as follows:

Then, after hiding `Email`

and `State`

columns, the columns will look as follows:

## Using Excel Com Object

After creating the object, we will open the Excel file to get workbook object we want to manipulate. Then, on current worksheet and within the range used, we will hide the column using `Hidden`

property of column object.

```
try {
# Open excel file
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\Scripts\Test.xlsx")
# Hide columns on current worksheet and within used range
$workbook.ActiveSheet.UsedRange.Columns(3).Hidden = $true
$workbook.ActiveSheet.UsedRange.Columns(4).Hidden = $true
# Save excel file
$workbook.Save()
}
finally {
# Close excel file
$workbook.Close()
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
}
```

We enclose the code above with `try-finally`

block because we want to make sure it will release the resource after modifying Excel that is performed at `finally`

block.

## Using ImportExcel Module

`ImportExcel`

is an external module that is built based on EPPlus which is a very well-known library to work with Excel spreadsheets in .NET.

Before using `ImportExcel`

module, we have to install it.

```
Install-Module -Name ImportExcel
```

And then following previous solution pattern, we have to create Excel object first before hiding the columns using `Hidden`

property.

```
try {
# Import the module
Import-Module ImportExcel
# Open excel file
$excel = Open-ExcelPackage -Path "C:\Scripts\Test.xlsx"
# Hide columns on active worksheet and within used range
$currentWorksheetIndex = $excel.Workbook.View.ActiveTab
$excel.Workbook.Worksheets[$currentWorksheetIndex + 1].Column(3).Hidden = $true
$excel.Workbook.Worksheets[$currentWorksheetIndex + 1].Column(4).Hidden = $true
}
finally {
# Close excel file
Close-ExcelPackage $excel
}
```

Similar to previous solution, we enclose the script with `try-finally`

block to avoid memory leak.

## Using PSExcel Module

`PSExcel`

is another module based on EPPlus library. You can find all the examples in github repository.

Before using this module, we have to install it.

```
Install-Module -Name PSExcel
```

In order to use the object, we must import the module first. Then, we create Excel object by specifying the path of our Excel file. This object will be used to get the columns on current worksheet where the column will be hidden using `Hidden`

property.

```
try {
# Import the module
Import-Module PSExcel
# Open excel file
$excel = New-Excel -Path 'C:\Scripts\Test.xlsx'
# Hide columns on active worksheet and within used range
$currentWorksheetIndex = $excel.Workbook.View.ActiveTab
$excel.Workbook.Worksheets[$currentWorksheetIndex + 1].Column(3).Hidden = $true
$excel.Workbook.Worksheets[$currentWorksheetIndex + 1].Column(4).Hidden = $true
# Save excel file
$excel | Save-Excel
}
finally {
$excel | Close-Excel
}
```

We also enclose the script with `try-finally`

block to avoid memory leak.

## Conclusion

To hide Excel columns using PowerShell, we can use excel module/libraries to manipulate excel.

We can use `Excel Com Object`

which is based on .NET Framework. We can also use PowerShell external modules like `PSExcel`

and `ImportExcel`

. `ImportExcel`

is an excellent module which is based on `EPPlus`

library, a popular C# libary for woking with Excel from .NET.

`PSExcel`

is the alternative of `ImportExcel`

module. `PSExcel`

is also based on `EPPlus`

but this module is no longer maintained as stated in its GitHub repository. If it doesn’t provide functionality that you need, you should contribute to the source code by yourself.