Encryption in Excel (All You Need to Know)

In this article, you will learn everything you need to know about encryption in Excel. Throughout this article, we will discuss some effective ways to encrypt a workbook in Excel. You can encrypt the whole workbook or some specific cell range in Excel. We will also show you how to create encryption functions in Excel easily.

When we work in Excel, we may want to encrypt data for security reasons. To protect our valuable data, encryption is necessary. Otherwise, someone else may be able to access the data. The Excel file can be encrypted with a password and a macro code can be assigned to encrypt a specific cell.

I hope you find this article informative and useful. So, let’s get into the main discussion.

Download Practice Workbook

You can download the practice workbook from here:

The password for the file is- 12345.

Performing Encryption.xlsm

Encrypting an Excel file: What Does It Mean?

Encrypting an Excel file means converting the contents of the file into a coded form that can only be accessed by individuals who possess the correct decryption key or password. Encryption ensures that the data within the file remains confidential and secure, even if unauthorized individuals gain access to the file.

It provides protection against unauthorized access, data breaches, and information leakage. It is especially important when dealing with sensitive or confidential data, such as financial information, personal details, or trade secrets.

How Does Encryption Work?

When we encrypt an Excel file, the encryption algorithm uses mathematical operations to scramble the data, making it unintelligible to anyone without the decryption key. Only those who have the correct key can decipher the encrypted data and view the file’s contents.

Encryption in Excel: 3 Possible Ways

1. Encrypt an Excel File Using Encrypt with Password Feature

In this method, we will encrypt an Excel file using the built-in encryption features provided by Microsoft Excel. To do so follow the below steps:

First, go to File>> Info>> Protect Workbook>> Encrypt with Password.

Now, insert a password in the Encrypt Document dialog box and click OK.

Then, re-enter the password in the Confirm Password dialog box and click OK.

Try opening your workbook again to see if the method worked.
A password prompt should automatically appear in Excel to enter the password to access data.
Enter password 12345 and click OK.

Thus, we can encrypt our Excel workbook with a password.

2. Apply VBA Macros to Encrypt Cell Contents

We can use VBA macro code to encrypt cell contents. All we need to do is specify a code and run the code. For writing the VBA code, you have to follow the below steps:

First, press ALT + F11 on your keyboard to open Visual Basic.
Then click Insert and select Module.
In Module 1 write down the below VBA code.

Option Explicit
Function SToByteArray(str As String) As Variant
    Dim A() As Byte
    A = str
    SToByteArray = A
End Function
Function ByteArrayToS(A() As Byte) As String
    Dim str As String
    str = A
    ByteArrayToS = str
End Function
Function scramble(str As String) As String
    Const Hidden_Password As String = "K*4HD%f#nwS%sdf032#gfl!HLKN*pq7"
    Dim stringA() As Byte, passwordA() As Byte
    stringA = SToByteArray(str)
    passwordA = SToByteArray(Hidden_Password)
    Dim upperLim As Long
    upperLim = UBound(stringA)
    ReDim scrambledA(0 To upperLim) As Byte
    Dim i As Long
    For i = LBound(stringA) To upperLim
        scrambledA(i) = stringA(i) Xor passwordA(i)
    Next i
    scramble = ByteArrayToS(scrambledA)
End Function

This code declares the function scramble as a String. And we also set a Hidden_Password in the function.

Now insert another module named Module 2 and insert the following code.

Sub EncryptData()
Dim r
r = 5
Do While Cells(r, 4) <> ""
Cells(r, 4) = scramble(Cells(r, 4))
r = r + 1
Loop
End Sub

Here, we declare the range as r. In our dataset, the encrypted data starts from row 5. So, the code will begin to encrypt in row 5. We want to set the encryption for the Password column. So, we set column number 4. We transform it with the scramble sub-function we declared in the previous code.

Now, press F5 to run the entire code and your work will be done.

Thus we can apply VBA macro encrypt cell contents.

3. Create Encryption Function with VBA to Protect Data

We can also easily create an encrypt function to encrypt the data in Excel. In this method, we will show step-by-step procedures to create an encryption function with VBA to encrypt data in Excel. Suppose we want to encrypt the data in the Card Number column. To do that let’s follow the below steps:

First, press ALT + F11 on your keyboard to open Visual Basic Editor.
Then click Insert and select Module.
In the new module, write down the below VBA code.

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)
Else
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
Next
End If
End Sub

Now, press F5 to run the entire code.
After that, an Excel Encryption dialog box will appear.
Select the desired cell range that you want to encrypt and press OK.

Again, a dialog box will appear.
Enter the password there and press OK.

Consecutively, another dialog box will appear where you need to enter 1 to encrypt the data.
Finally, press OK.

As a result, the data in the Card Number column got encrypted.

Thus we can create an encryption function in Excel and encrypt data as required.

Encryption Alternative: Masking Cell Contents with Excel Functions

We can hide our data instead of encrypting them. We can hide the information we don’t want to show with masks in the file. Follow the below steps to know the whole procedure:

First, select the entire column in which you want to put a mask. In our case, it is the Password column.
Then, right-click on your mouse and go to the Format Cells option.

As a result, the Format Cells dialog box appears.
Choose Custom and put ;;;** as Type.
Then click OK.

We can see that we have masked our data, but it is showing in the Formula Box (see the image). So, it is not secured yet.

To make it fully secured we need to lock the worksheet.
Go to the Review tab >> choose Protect Sheet.

Consequently, the Protect Sheet window appears.
Enter the password.
Then uncheck all the boxes except the Edit Objects and Edit Scenarios boxes.
Click Ok.

Re-enter the password to confirm and click OK.

Finally, we have locked the dataset since the Formula Box shows nothing for the masked data.

Key Takeaways from the Article

In this article, we have discussed how to encrypt data in Excel in a simple and straightforward manner.
First, we have discussed what it means to encrypt data and how encryption works in Excel.
Then we showed a step-by-step procedure of all methods to encrypt data in an Excel file.
We have also explained how to encrypt Excel workbooks using VBA macro.
Also discussed an alternative option of encryption in Excel.
Provide solutions to frequently asked questions by readers.

Conclusion

In this article, you will know everything about encryption in Excel. Don’t forget to download the practice file. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more.

Frequently Asked Questions

1. What are the 2 types of data encryption?

There are two forms of encryption in use today: symmetric encryption and asymmetric encryption. The term is derived from the usage of the same key for encryption and decryption.

2. Can I open an encrypted Excel file on a different computer?

Yes, you can open an encrypted Excel file on a different computer as long as you have the password to unlock it.

3. Which files need to be encrypted?

There are many times when we want to add an extra layer of security to our personal files. It’s especially important when we’re dealing with private or secret information, like money, personal information, or trade secrets.

Encryption in Excel: Knowledge Hub

<< Go Back to Excel Protect | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!