How to Create Encryption Function in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

While we work in Excel, sometimes may want to encrypt data for security purposes. We can easily create encrypt function to encrypt the data in Excel. Here, we will show step-by-step procedures to create encryption function in Excel.

Create Encryption Function in Excel: Step-by-Step Procedure

In this section, we will discuss the stepwise procedures to create an encryption function in Excel. A simple piece of VBA code will do the job. Follow the steps given below.

STEP 1: Prepare Dataset for Encryption Function

  • Firstly, create a dataset in your worksheet. For demonstration, we have included a dataset having Salesman, Salary, State for a company. We will encrypt the data in Salary column.

excel encryption function

STEP 2: Open VBA Window in Excel

  • Next, go to the Developer tab and select Visual Basic to open the VBA window.
  • Alternatively, you can press Alt + F11 to open the window.

Open VBA Window in Excel

STEP 3: Type Code in VBA Module for Encryption Function

  • Afterward, select Insert > Module from the VBA window.

Type Code in VBA Module for Encryption Function

  • Consecutively, a Module window will show up.
  • Write the following code there.
Private Function iStrTPsd(ByVal Txt As String) As Long
Dim xVl As Long
Dim xCha As Long
Dim xSf1 As Long
Dim xSf2 As Long
Dim J As Integer
Dim xLn As Integer
xLn = Len(Txt)
For J = 1 To xLn
xCha = Asc(Mid$(Txt, J, 1))
xVl = xVl Xor (xCha * 2 ^ xSf1)
xVl = xVl Xor (xCha * 2 ^ xSf2)
xSf1 = (xSf1 + 7) Mod 19
xSf2 = (xSf2 + 13) Mod 23
Next J
iStrTPsd = xVl
End Function

Private Function iEncryption(ByVal Pd As String, ByVal InTx As String, Optional ByVal Encc As Boolean = True) As String
Dim xOfset As Long
Dim xLn As Integer
Dim J As Integer
Dim xCha As Integer
Dim xOutTx As String
xOfset = iStrTPsd(Pd)
Rnd -1
Randomize xOffset
xLn = Len(InTx)
For J = 1 To xLn
xCha = Asc(Mid$(InTx, J, 1))
If xCha >= 32 And xCha <= 126 Then
xCha = xCha - 32
xOfset = Int((96) * Rnd)
If Encc Then
xCha = ((xCha + xOfset) Mod 95)
xCha = ((xCha - xOfset) Mod 95)
If xCha < 0 Then xCha = xCha + 95
End If
xCha = xCha + 32
xOutTx = xOutTx & Chr$(xCha)
End If
Next J
iEncryption = xOutTx
End Function

Sub xEncryption_Range()
Dim xxRg As Range
Dim xxPsd As String
Dim xxTxt As String
Dim xxEnc As Boolean
Dim xxRet As Variant
Dim xxCell As Range
On Error Resume Next
xxTxt = ActiveWindow.RangeSelection.Address
Set xxRg = Application.InputBox("You need to select range:", "Excel Encryption", xxTxt, , , , , 8)
Set xxRg = Application.Intersect(xxRg, xxRg.Worksheet.UsedRange)
If xxRg Is Nothing Then Exit Sub
xxPsd = InputBox("Type your password:", "Excel Encryption")
If xxPsd = "" Then
MsgBox "Your password can't be empty", , "Excel Encryption"
Exit Sub
End If
xxRet = Application.InputBox("Insert 1 to encrypt cells or Insert 2 to decrypt cells", "Excel Encryption", , , , , , 1)
If TypeName(xxRet) = "xBoolean" Then Exit Sub
If xxRet > 0 Then
xxEnc = (xxRet Mod 2 = 1)
For Each xxCell In xxRg
If xxCell.Value <> "" Then
xxCell.Value = iEncryption(xxPsd, xxCell.Value, xxEnc)
End If
End If
End Sub

excel encryption function code

STEP 4: Run Code for Data Encryption

  • Further, press the Run button from the VBA window.

Run Code for Data Encryption

  • Instantly, a Macros dialog box will appear. Click on the Run button.

  • After that, an Excel Encryption dialog box will appear.
  • Select the desired range of cells whose data you want to encrypt. Press OK.

excel encryption function set range

  • Again, a dialog box will appear.
  • Write the password there. Press OK.

excel encryption function set password

  • Consecutively, another dialog box will appear.
  • Write 1 there for encrypting the data. Also, press OK.

Final Output

  • Finally, come back to the worksheet and see the data of the selected range has been encrypted.

excel encryption function Final Output

  • Further, for decrypting the data run the code again and follow the steps given previously.
  • Just write 2 in the last dialog box instead of 1.
  • Also, don’t forget to enter the same password for decryption like you gave for the encryption.

  • And you will see the encrypted data got decrypted again.

excel encryption function decrypt again

Read More: How to Decrypt Excel File Without Password

How to Encrypt Excel File with Password

In this section, we will discuss the stepwise procedures to encrypt an Excel file with password to open the file.

  • Firstly, go to the File tab from the Excel ribbon.

How to Encrypt Excel File with Password

  • Then, select Save a Copy option from the pane.

  • Afterward, select More options from the appeared window.

  • Further, select Tools > General Options from the next window.

  • Next, give a password in the Password to open section of General Options dialog box. Also, press OK.

  • After that, rewrite the password in the Confirm Password dialog box. Press OK.

  • Finally, save the file.
  • From the next time, you have to give the password to open the Excel file.

Read More: How to Encrypt Cell Contents in Excel

Download Practice Workbook

You can download the practice workbook from here.


In this article, we have shown stepwise easy procedures to encrypt data in Excel. If you have any queries or suggestions, please leave a comment.

Related Articles

Mehedi Hasan Shimul
Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF