Encryption in Excel: 3 Possible Methods

Method 1 – Encrypt an Excel File Using Encrypt with Password Feature

  • Go to File>> Info>> Protect Workbook>> Encrypt with Password.

2- selecting Encrypt with Password option from protect workbook section

  • Insert a password in the Encrypt Document dialog box and click OK.

3- entering password in the Encrypt Document dialog box to encrypt the Excel file

  • Re-enter the password in the Confirm Password dialog box and click OK.

4- re-entering password in the Confirm Password dialog box to encrypt the Excel file

  • 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.

5- entering password to open encrypted workbook in Excel

We can encrypt our Excel workbook with a password.

Method 2 – Apply VBA Macros to Encrypt Cell Contents

  • Press ALT + F11 on your keyboard to open Visual Basic.
  • 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

6- VBA code to encrypt cell contents

This code declares the function scramble as a String. And we also set a Hidden_Password in the function.
  • 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
End Sub
We declare the range as r. In our dataset, the encrypted data starts from row 5. The code will begin to encrypt in row 5. We want to set the encryption for the Password column. We set column number 4. We transform it with the scramble sub-function we declared in the previous code.
  • Press F5 to run the entire code and your work will be done.

7- final output after running the VBA code to encrypt cell contents in an Excel file

We can apply VBA macro encrypt cell contents.

Method 3 – Create Encryption Function with VBA to Protect Data

  • Press ALT + F11 on your keyboard to open Visual Basic Editor.
  • 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)
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

8- VBA code to create encryption function to protect data

  • Press F5 to run the entire code.
  • An Excel Encryption dialog box will appear.
  • Select the desired cell range that you want to encrypt and press OK.

9- selecting cell range to encrypt data using created encryption function

  • A dialog box will appear.
  • Enter the password there and press OK.

10- entering password to encrypt data using created encryption function

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

11- entering 1 to encrypt data using created encryption function

  • The data in the Card Number column got encrypted.

12- final output image of encrypted data after running the created encryption function with VBA



Encryption Alternative: Masking Cell Contents with Excel Functions

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

13- selection format cells option to mask cell contents

  • The Format Cells dialog box appears.
  • Choose Custom and put ;;;** as Type.
  • Click OK.

14- choosing custom in the format cells dialog box

  • We masked our data, but it is showing in the Formula Box (see the image). It is not secured yet.

15- masked data showing in the formula box

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

16- selecting protect sheet option from the Review tab

  • The Protect Sheet window appears.
  • Enter the password.
  • Uncheck all the boxes except the Edit Objects and Edit Scenarios boxes.
  • Click Ok.

17- unchecking options from the protect sheet window

  • Re-enter the password to confirm and click OK.

18- re-entering the password to confirm to mask the data

  • We locked the dataset since the Formula Box shows nothing for the masked data.

19- masking cell contents as the alternative of encryption


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.

Download Practice Workbook

You can download the practice workbook from here:

The password for the file is- 12345.

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!
Nujat Tasnim
Nujat Tasnim

Nujat Tasnim, BSc in Computer Science and Engineering from American International University-Bangladesh, has a year-long tenure with ExcelDemy. Initially a Content Developer at SOFTEKO, she transitioned to the Software Team as Junior Software Quality Assurance Engineer. Currently serving as a Junior SQA Engineer at SOFTEKO, she excels in SDLC, STLC, and requirement analysis and possesses profound insights into OOP-related software development processes. Nujat is also proficient in design software like Photoshop and Illustrator, contributing diverse skills to her... Read Full Bio

1 Comment
  1. there is a bug in your code as listed on this page.
    once fixed your code worked as expected.

Leave a reply

Advanced Excel Exercises with Solutions PDF