How to Encrypt Cell Contents in Excel (2 Useful Methods)

Get FREE Advanced Excel Exercises with Solutions!

When you deal with sensitive data or some data you don’t want to reveal, you must encrypt the cell contents. Encryption is necessary for the protection of your valuable data. Otherwise, there is a risk that someone using the same worksheet may get access to the data. You can protect your Excel file with a password but when you want to encrypt a specific cell, you need to assign a VBA macro code to it. It could be a difficult task for you. But don’t worry. We are here to solve your problem. In this article, we will show you how to encrypt cell contents in Excel in a simple and straightforward manner. So, let’s get started.


Excel Encrypt Cell Contents: 2 Useful Methods

For the encryption method, we have taken a dataset of some Account Names, Card Numbers, and passwords. But as you know, Passwords are very sensitive data. We want to encrypt with the Password.

Dataset

Not to mention, we have used the Microsoft 365 version. You can use any other version at your convenience.


1. Applying VBA Macros to Encrypt Cell Contents

You may use VBA macro code to encrypt the content. All you need to do is specify a code and run the code. We created a button that allows you to encrypt or decrypt data with a single click. For writing the VBA code, you have to follow the below steps.

Steps:

  • Firstly, go to the Developer tab >> choose Visual Basic. If you don’t see it on the ribbon, then you need to enable the Developer tab.

Applying VBA Macros to Encrypt Cell Contents in excel

Now the Visual Basic Application dialog box appears. You can access it by pressing ALT + F11 on your keyboard.

  • From there, go to the Insert tab >> Module >> Module1.

Insert Module

  • In the General box of 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.

VBA code to encrypt cell contents in Excel

  • At this moment, we open a new module where we insert a new code.
Option Explicit
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 take the Sub as EncryptData. We declare the range as r. In our dataset, the encrypted data starts with row_number 5. So, the code will start to encrypt in row 5. We want to set the encryption in the 4th column. So, we set column number 4 in the Cells of the code. We transform it with the scramble sub-function we declared in the previous code. In the sub-function box, we put the Cells there.

Eventually, you get the data encrypted after running the code with the F5 key.

You can create a macro button in Excel utilizing the Form Controls in your worksheet. By doing this, you can easily encrypt and decrypt the data with the help of a single click on the button.

  • Consequently, after adding the button to the worksheet, right-click on the button and choose Assign Macro from the Context Menu.

Assign macro to encrypt cell contents in Excel

  • Sequentially, the Assign Macro dialog box appears. Select the Macro name as EncryptData.

Assign Macro window

Eventually, a button is created. Similarly, you can create a Decrypt button. See the below GIF for better visualization.

Excel Encrypts cell contents gif

Read More: How to Create Encryption Function in Excel


2. Using Encrypt/Decrypt Add-in for Cell Contents

You can get the add-in to encrypt or conceal cells. You can download the Spreadsheet Cell Conceal add-in from Microsoft AppSource. After getting the add-in for your Microsoft Excel file, you have to go to the CIMCON command in the Home tab and choose Encrypt/Decrypt.

Using Encrypt/Decrypt Add-in for Cell Contents in Excel

  • Consequently, the Cell Conceal window appears. Select the cell range that you want to encrypt. Move to Masked data and retain the Mask character (*).
  • Then, create a password and confirm it for the decryption of the cells.
  • Finally, hit the Encryption button.

Cell Conceal

Your data has been encrypted, as you can see in the image below.

Read More: How to Decrypt Excel File Without Password


Alternative of Encryption: Using Excel Functions to Mask Cell Contents

You can mask your contents as an alternative to the encryption process. The use of masks in the file hides the dataset that you don’t want to reveal. Follow the steps to do this.

Steps:

  • Firstly, select the entire column in which you want to put a mask.
  • Secondly, go to the Format Cells option.

Alternative of Encryption: Using Excel Functions to Mask Cell Contents

Note: you can open the Format Cells dialog box by pressing CTRL + 1 button.

  • Subsequently, the Format Cells dialog box appears. Choose Custom as the Category and put ;;;** as Type. Hit the OK button.

Format Cells

You have masked your data, but it is showing in the Formula Box (see the image).

  • At this moment, we lock the sheet. For doing this, go to the Review tab >> choose the Protect Sheet command.

Review tab

  • Consequently, the Protect Sheet window appears. Uncheck all the boxes except the Edit Objects and Edit Scenarios boxes.

  • Moreover, confirm the password and press OK.

Finally, you have locked the dataset. As you can see, Formula Box shows nothing for the masked data.


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Download Practice Workbook

Download the following practice workbook. It will help you understand the subject better.


Conclusion

That’s all about today’s session. And these are some easy methods to encrypt cell contents in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Thanks for your patience in reading this article.


Related Articles


<< Go Back to Encryption in Excel | Excel Protect | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo