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.
Do you have a long list of the Excel sheets in one file? How do you find what you need quickly? Moving around spreadsheets and making coloured tabs might help you.
Moving worksheets around
When we want to place the worksheet in a different place it is simple as drag&drop or using the context menu.

The tabs can be moved to the end or start of the list, as well as put after or before selected sheet.
Preparation
In this paragraph, we are going to 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 = "ImportExcelHowTo007.xlsx" Remove-Item $excelFiles -ErrorAction SilentlyContinue
Next, we will create a workbook with multiple worksheets. The data is irrelevant in this case as we are focusing on worksheets. We will add worksheets in a loop.
# workbook with multiple sheets for ($i = 1 ; $i -le 5 ; $i++){ $i | Export-Excel -WorksheetName HowToSheet$i -Path $excelFiles -MoveToStart }
Moving worksheets with PowerShell
In this part, we will work on an existing workbook that already has worksheets. If you want to learn how to add new worksheets using the Add-Worksheet have a look at this blog post.
In case you haven’t noticed in the script above we used -MoveToStart switch, that means all the new worksheets were added at the beginning. The list looks like that now:
HowToSheeet5
HowToSheeet4
HowToSheeet3
HowToSheeet2
HowToSheeet1
That’s one way to place the worksheets but it, since we are piping data in it, is not ideal for the existing worksheets as it will overwrite them.
However, when we use the method below we can “save” the worksheet in a new location without damaging the data.
# move existing worksheet to the start/end without overwriting the data Export-Excel -WorksheetName HowToSheet1 -Path $excelFiles -MoveToStart Export-Excel -WorksheetName HowToSheet5 -Path $excelFiles -MoveToEnd
As a result, the list looks like below:
HowToSheeet1
HowToSheeet4
HowToSheeet3
HowToSheeet2
HowToSheeet5
After that, let’s shuffle some worksheets again:
# move existing worksheet after/before other worksheets without overwriting the data Export-Excel -WorksheetName HowToSheet2 -Path $excelFiles -MoveAfter HowToSheet1 Export-Excel -WorksheetName HowToSheet4 -Path $excelFiles -MoveBefore HowToSheet2
The result will be:
HowToSheeet1
HowToSheeet4
HowToSheeet2
HowToSheeet3
HowToSheeet5
Finally, what if we want to sort the tabs by the name? For that, we are going to use Get-ExcelSheetInfo to get the names of the worksheets.
# sort tabs by name in ascending order $sheets = Get-ExcelSheetInfo -Path $excelFiles | Sort-Object Name foreach ($sheet in $sheets.Name) { Export-Excel -WorksheetName $sheet -Path $excelFiles -MoveToEnd }
That’s how it is going to look now:
HowToSheeet1
HowToSheeet2
HowToSheeet3
HowToSheeet4
HowToSheeet5
And of course, after we are done playing, we can revert to the original order:
# sort tabs by name in descending order $sheets = Get-ExcelSheetInfo -Path $excelFiles | Sort-Object Name foreach ($sheet in $sheets.Name) { Export-Excel -WorksheetName $sheet -Path $excelFiles -MoveToEnd }
Bonus: changing tab colours
In addition to the main topic, I want to mention about one more thing. Recently there was a question about changing the tab colour on modules GitHub repo. For example, within Excel, we do it by right-clicking on a tab and selecting Tab Color. The module itself does not have any function to do that, but we can hack a little bit by changing the TabColor property directly.
For instance, any color from the list or use RGB values.
# open the excel package $excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel # get the worksheets $excel = $excelPackage.Workbook.Worksheets # set colors to each worksheet tab $excel['HowToSheet1'].TabColor = 'Green' $excel['HowToSheet2'].TabColor = 'Yellow' $excel['HowToSheet3'].TabColor = 'Red' $excel['HowToSheet4'].TabColor = 'Blue' $excel['HowToSheet5'].TabColor = [System.Drawing.Color]::FromArgb(201,255,172) # save changes and open the file Close-ExcelPackage -ExcelPackage $excelPackage -Show

Summary
In conclusion, this week’s exercise was to move spreadsheets around and add them some colours.
Thank you,
Mikey
Permalink
Permalink