How to Encrypt Cell Content in Excel – 2 Methods

 

The dataset showcases Account Names, Card Numbers, and passwords.

To encrypt the Password:

Dataset

 

Method 1 – Applying VBA Macros to Encrypt Cell Content

Steps:

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

Applying VBA Macros to Encrypt Cell Contents in excel

The Visual Basic Application dialog box is displayed. You can also access it by pressing ALT + F11.

  • Go to the Insert tab >> Module >> Module1.

Insert Module

  • Enter the 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. It sets a Hidden_Password in the function.

VBA code to encrypt cell contents in Excel

  • Open a new module and enter this 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

The Sub is declared as EncryptData. The range is declared as r. In the dataset, the encrypted data starts with row_number 5. So, the code will start to encrypt in row 5. Set the encryption in the 4th column: set column number 4 in the Cells of the code. Transform it with the scramble sub-functionin the previous code. The sub-function box contains Cells.

Run the code by pressing F5.

You can create a macro button in Excel utilizing the Form Controls and encrypt and decrypt data with a click.

  • After adding the button to the worksheet, right-click it and choose Assign Macro in the Context Menu.

Assign macro to encrypt cell contents in Excel

  • Select the Macro name as EncryptData.

Assign Macro window

A button is created. Create a Decrypt button. Observe the GIF.

Excel Encrypts cell contents gif


Method 2 – Using the Encrypt/Decrypt Add-in for Cell Content

You can download the Spreadsheet Cell Conceal add-in from Microsoft AppSource.

Go to the CIMCON command in the Home tab and choose Encrypt/Decrypt.

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

  • In the Cell Conceal window, select the range to encrypt. Go to Masked data and maintain the Mask character (*).
  • Create a password and confirm it for decrypting cells.
  • Click Encryption.

Cell Conceal

Your data is encrypted:

Read More: How to Decrypt Excel File Without Password


Alternative to Encryption: Using Excel Functions to Mask Cell Content

Steps:

  • Select the entire column you want to mask.
  • Go to Format Cells.

Alternative of Encryption: Using Excel Functions to Mask Cell Contents

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

  • Choose Custom in Category and enter ;;;** in Type.
  • Click OK.

Format Cells

You have masked your data, but it is showing in the Formula Box:

  • Lock the sheet: go to the Review tab >> choose Protect Sheet.

Review tab

  • In the Protect Sheet window, uncheck all the boxes except Edit Objects and Edit Scenarios.

  • Confirm the password and click OK.

You have locked the dataset. The Formula Box does not show the masked data.


Practice Section

Practice here.

Practice Section


Download Practice Workbook

Download the practice workbook.


 

Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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