How to hide Excel using PowerShell?

ImportExcel - PowerShell module

This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.

MS Excel offers many different functionalities and one of them is making things to disappear like hiding worksheets or columns and rows, even cells.

This week’s post is for your eyes only 😉

Preparation

Let’s prepare our environment now to make sure the file does not exist.

# set location for the files - I am using the temporary folder in my user profile
Set-Location $env:TEMP

# that is the location of our files
Invoke-Item $env:TEMP

# cleanup any previous files - this helps in case the example has been already ran
$excelFiles = "ImportExcelHowTo005.xlsx"
Remove-Item $excelFiles -ErrorAction SilentlyContinue

As an example, we will use a workbook with a couple of worksheets. We need something first in order to make it disappear, don’t we?

# create a file with a couple of worksheets and open it
1..10 | Export-Excel -Path $excelFiles -WorksheetName HowToProtect
1..100 | Export-Excel -Path $excelFiles -WorksheetName HowToHideWorksheet -Show

Hiding worksheets with PowerShell

There are basically three states of the worksheets visibility in Excel:

  • Visible – which is default and allows users to see the worksheets on the tab at the bottom
  • Hidden – that can be accessed directly from the worksheet, but it can be also made unhidden from there
  • VeryHidden – at the first glance the user might not be aware there are any hidden worksheets unless they go into the Code editor

Let’s see how it works. First, let’s hide a worksheet, in the Excel, it looks like that

In PowerShell, we need to change the Hidden property of the worksheet to “Hidden”.

# open Excel file with PowerShell
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# get the worksheets
$excel = $excelPackage.Workbook.Worksheets['HowToHide']

# hide the worksheet
$excel.Hidden = 'Hidden'

# save and open the file
Close-ExcelPackage $excelPackage -Show

The spreadsheet is no longer visible in the workbook. Now, if you need to unhide the worksheet, in Excel just right-click on the other tabs and select Unhide.

The window will pop up with the hidden worksheets to pick:

In PowerShell, simply change the Hidden property to “Visible”.

# open Excel file with PowerShell
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# get the worksheets
$excel = $excelPackage.Workbook.Worksheets['HowToHide']

# unhide the worksheet
$excel.Hidden = 'Visible'

# save and open the file
Close-ExcelPackage $excelPackage -Show

The last bit is to hide a worksheet very deep, so we cannot unhide it. Using the VBA editor change the property’s value to “VeryHidden”.

The same applies to the PowerShell method:

# open Excel file with PowerShell
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# get the worksheets
$excel = $excelPackage.Workbook.Worksheets['HowToHide']

# hide the worksheet that cannot be seen from the workbook
$excel.Hidden = 'VeryHidden'

# save and open the file
Close-ExcelPackage $excelPackage -Show

Hiding rows and columns with PowerShell

Besides hiding the whole worksheet we can hide selected columns or rows. The concept is very similar, however, there are only two states: Visible and Hidden, therefore we can see something that is hidden on the worksheet itself.

In MS Excel pick a victim (column(s) / row(s) ) , right-click and select Hide. In PowerShell we have couple options:

# open Excel file with PowerShell
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# get the worksheet
$excel = $excelPackage.Workbook.Worksheets['HowToProtect']

# using dedicated functions hide columns or rows
# columns need to be provided as numbers, A = 1, B = 2, etc
Set-ExcelColumn -ExcelPackage $excelPackage -Worksheetname HowToProtect -Column 10 -Hide
Set-ExcelRow -ExcelPackage $excelPackage -Worksheetname HowToProtect -Row 10 -Hide

# use range function to do the same
Set-ExcelRange -Worksheet $excel -Range $excel.column(5) -Hidden 
Set-ExcelRange -Worksheet $excel -Range $excel.row(11) -Hidden 

Close-ExcelPackage -ExcelPackage $excelPackage -Show

As we can see, rows 10-11 are hidden, as well as columns 5 (E) and 10 (J).

In order to unhide those poor rows and columns, set the Hide/Hidden switch to $false:

# open Excel file with PowerShell
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# get the worksheet
$excel = $excelPackage.Workbook.Worksheets['HowToProtect']

# using dedicated functions hide columns or rows
# columns need to be provided as numbers, A = 1, B = 2, etc
Set-ExcelColumn -ExcelPackage $excelPackage -Worksheetname HowToProtect -Column 10 -Hide:$false 
Set-ExcelRow -ExcelPackage $excelPackage -Worksheetname HowToProtect -Row 10 -Hide:$false 

# use range function to do the same
Set-ExcelRange -Worksheet $excel -Range $excel.column(5) -Hidden:$false 
Set-ExcelRange -Worksheet $excel -Range $excel.row(11) -Hidden:$false

Close-ExcelPackage -ExcelPackage $excelPackage -Show

Hide Excel cells with PowerShell

Technically, they are still visible, unless we use the method above to hide the whole column and row. Excel allows hiding the formula, keeping the value visible. Select the cells you want to hide, right-click and select Format Cells. The note below says the worksheet needs to be protected in order to hide the cells.

Using PowerShell, we just need to change the Cells.Style.Hidden value to $true and protect the worksheet using Set-WorksheetProtection.

# open Excel file with PowerShell
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# get the worksheets
$excel = $excelPackage.Workbook.Worksheets['HowToProtect']

# hide formulas cells
$excel.Cells["A4:A8"].Style.Hidden = $true

# protect the workbook
Set-WorksheetProtection -Worksheet $excel -IsProtected 

# save and open
Close-ExcelPackage -ExcelPackage $excelPackage -Show

When we look at the worksheet now, the cells have value, however, the formula field is empty:

Summary

Today you have learned how to hide things in Excel using PowerShell. In the future, I will show you how to protect the workbook.

Thank you,
Mikey

Leave a Reply

%d bloggers like this: