How to Encode Data in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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

Read More: How to Decode Base64 in Excel

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 Apply UTF 8 Encoding on CSV File in Excel

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bishawajit Chakraborty
Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF