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.
The Microsoft Excel spreadsheet is very flexible, users can put whatever they want and it will accept it – even if it does not make much sense.
Data Validation in Excel
Luckily there is a feature called Data Validation (under Data > Data Tools).
The feature helps to control the values in the spreadsheet, so for example we do not end up with negative age.
Validation rules
The Microsoft Excel offers a handful of rules for the data validation:
- Any value – default with no restrictions
- Whole number and Decimal
- List
- Date and Time
- Text Length
- Custom – limited by the user’s imagination
Additionally we can decide weather the blank cells should be included or not.
Input Message
That settings will result in a note-like pop-up when the cell is selected. It won’t stop the user from adding invalid data though.
Error Alert
To prevent putting in the wrong data the cells need to be protected by error alert once the data is entered.
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 = "ImportExcelHowTo006.xlsx" Remove-Item $excelFiles -ErrorAction SilentlyContinue
In order to show the example we need some data, so let’s create a spreadsheet with some important data.
# create a file with some data $data = ConvertFrom-Csv -InputObject @" EmpID,Name,MiddleInitial,Email,Age,Hair,Planet 1,James Mary,A,[email protected],30,yes,Vogsphere 2,Patricia John,B,[email protected],20,blonde, Coruscant 3,Jennifer Robert,C,[email protected],10,dark,Krypton 4,Michael Linda,D,[email protected],40,no,Arda 5,Elizabeth William,E,[email protected],50,long,Melmac "@ $data | Export-Excel -WorksheetName HowToValidate -Path $excelFiles
In addition, the data validation can use a lookup table for the lists therefore we will add one more tab to our file.
# add lookup sheet with values for the drop-down list 'Arda','Coruscant','Krypton','Melmac','Vogsphere' | Export-Excel -WorksheetName Lookup -Path $excelFiles
Data Validation with Powershell
In this section, I am going to put all the validation rules using Add-ExcelDataValidationRule function in one script, so it’s easier to copy and paste. Each part has it’s own description too. All examples include the -ShowErrorMessage switch so the rules actually prevent from adding invalid data.
# open the package
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel
# validation rule - TextLength
# allows strings with the number of characters between 2 and 100
Add-ExcelDataValidationRule -Worksheet $excel -Range "B2:B100" -ValidationType TextLength -Operator between -Value 2 -Value2 100 -ShowErrorMessage
# validation rule - TextLength + Prompt one selecting the cells
# allows single character to be entered
# when the cell is selected the user will get a pop-up
Add-ExcelDataValidationRule -Worksheet $excel -Range "C2:C100" -ValidationType TextLength -Operator equal -Value 1 -ShowPromptMessage -PromptTitle Initial -PromptBody 'Use one letter' -ShowErrorMessage
# validation rule - Integer + error
# only integers over 0, i.e. positive will be accepted
Add-ExcelDataValidationRule -Worksheet $excel -Range "E2:E100" -ValidationType Integer -Operator greaterThan -Value 0 -ShowErrorMessage -ErrorStyle stop -ErrorTitle 'Negative age' -ErrorBody 'Be a little more positive, use a value > 0'
# validation rule - internal list
# use the list configured inside the validation rule
Add-ExcelDataValidationRule -Worksheet $excel -Range "F2:F100" -ValidationType List -ValueSet @('yes','no','somewhat') -ShowErrorMessage -ErrorStyle warning -ErrorTitle 'Pick from the list' -ErrorBody 'Pick on value from the list'
# validation rule - a list from the lookup table
# use the list from the lookup worksheet
Add-ExcelDataValidationRule -Worksheet $excel -Range "G2:G100" -ValidationType List -Formula 'Lookup!a$1:a$10' -ShowErrorMessage
# validation rule - custom formula - valid emails
# use formula to validate data
# some string magic from https://www.extendoffice.com/documents/excel/4303-excel-data-validation-email-address.html
Add-ExcelDataValidationRule -Worksheet $excel -Range "D2:D100" -ValidationType Custom -Formula '=ISNUMBER(MATCH("*@*.?*",D2,0)) ' -ShowErrorMessage
# validation rule - custom formula - avoid duplicates
# another example of the formula
Add-ExcelDataValidationRule -Worksheet $excel -Range "A2:A100" -ValidationType Custom -Formula 'COUNTIF(A$2:A$100,A2) = 1' -ShowErrorMessage
# save the package and open the spreadsheet
Close-ExcelPackage $excelPackage -Show
Data validation in Excel
After we run the script, we will see this raw Excel spreadsheet:
Before we go any deeper into the data validation rules, let’s have a look at one interesting feature (unfortunately I was not able to replicate it with PowerShell).
Circle Invalid Data
The feature is called Circle Invalid Data, and we can enable it from the same menu as the Data Validation.
When we select that our worksheet looks like a test marked by the teacher (at least that’s how it looked for me).
After that, all the data that do not match the rules defined in the previous section are marked by red circle:
- column A: duplicate values
- column C: string is too long
- column D: invalid email address
- column F: values that are not on the list
It is a very interesting feature if we are validating the existing data. Unfortunately, after we close and re-open the file the red circles are gone. Let’s move on to the Error Alerts.
Errors Alerts
The most common result would be the Error Alert – Stop to prevent from entering invalid data
We can use a generic one or customize both the title and the message. It won’t allow us to enter the data until it passes the check.
Another type of the Error Alert is the Warning. It will let the user know what’s wrong, but won’t prevent from entering the invalid data. Similarly, the Information type also allows the user to keep invalid data.
Summary
In conclusion, this week we’ve learned how to add the data validation to the spreadsheet using PowerShell function Add-ExcelDataValidationRule. We could also apply the validation rules to the existing data which shows where it is wrong.
Thank you,
Mikey
Permalink