How to Encode Data in Excel (with Easy Steps)

You have come to the right place if you’re looking for instructions on how to encode data in Excel. Despite this being a necessity, there is currently no encoding or decoding function in Excel or VBA. This article will demonstrate how to encode data in Excel.

How to Encode Data in Excel: Step-by-Step Procedures

In this article, we will demonstrate how to encode data in Excel by employing VBA code with some easy steps. So, to know how to encode data, you can follow the below steps accordingly.

Step 1: Making Data Set with Proper Parameters

In this section, we will create a data set with some names, such as a Text column and an empty column named Encode.

  • So, you can see our data set in the below image.

Making Data Set with Proper Parameters to Encode Data in Excel

Step 2: Using the Developer Tab

The Excel built-in Developer tab offers the tools required to use Visual Basic for Applications (VBA) to execute a macro. As a convention, the tab is disabled. To make it visible on the toolbar at the top of the Excel window, it has to first be activated in the Options section of the Menu bar.

  • We will open the Developer tab.
  • Then, select the Visual Basic command.

Using Developer Tab to Encode Data in Excel

Step 3: Creating VBA User-Defined Function

Here, we will create a new module to write the VBA code by opening the Visual Basic Applications window.

  • The Visual Basic window will open.
  • From the Insert option, we will choose the new Module to write the VBA code.

Creating New Module to Write VBA Code to Encode Data in Excel

  • Now, paste the following VBA code into the Module.
  • Then, save the following code.
Function Encoding(text$)
Dim i
With CreateObject("ADODB.Stream")
.Open: .Type = 2: .Charset = "utf-8"
.WriteText text: .Position = 0: .Type = 1: i = .Read
With CreateObject("Microsoft.XMLDOM").createElement("b64")
.DataType = "bin.base64": .nodeTypedValue = i
Encoding = Replace(Mid(.text, 5), vbLf, "")
End With
End With
End Function

Step 4: Encoding Data in Excel

VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate VBA code that makes it very easy to encode data in Excel.

  • Go to the worksheet and apply the following formula in cell B5.
  • Then, click Enter.

Employing VBA Code to Encode Data in Excel

  • You will see the encoded result for the first person.
  • Use the Fill Handle tool and drag it down from cell B5 cell to cell B11.

  • Finally, you will get all the results in the below image.

Read More: How to Check Encoding of Excel File

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


In this article, we’ve covered step-by-step procedures to encode data in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Related Articles

<< Go Back to Excel Encoding | Excel Files | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Bishawajit Chakraborty
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF