How to Make Ciphers With Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

Excel's functions are flexible tools, and one way to use them is to make a simple substitution cipher. This isn't any kind of encryption that's secure enough for banking or company secrets, but it is a good programming exercise in Excel and a good way to showcase the power of Excel functions.

Advertisement

Step 1

Set Excel to use iterative calculations by clicking on the "File" tab, selecting "Options," then clicking "Formulas" and checking the option at the upper right of the dialog box that comes up that says "Permit iterative formulas." Set the maximum number of iterations to the longest message you'll be encrypting and decrypting -- 500 characters is a reasonable limit.

Advertisement

Video of the Day

Step 2

Enter the following values in cells "A1" through "A7," in order. They're labels to help you keep track of what the functions in column "B" do: "Text," "Start," "End," "Counter" and "Translate." They do not affect the functions in column "B," but do make it easier to put the right functions in the right space.

Advertisement

Step 3

Enter the value "0" in column "B2."

Step 4

Enter the following function in cell "B3":

=LEN(B1)

Advertisement

Step 5

Enter the following function in cell "B4":

Advertisement

=IF(B1="",B2,IF(B4<B3,B4+1,B3))

Step 6

Enter the following function in cell "B5":

Advertisement

=IF(B1="","",IF(B4<=B3,CONCATENATE(B5,CHAR(IF(CODE(MID(B1,B4,1))=32,32,MOD((CODE(MID(B1,$B$4,1))-IF(CODE(MID(B1,$B$4,1))<91,65,97))+13,26)+IF(CODE(MID(B1,$B$4,1))<91,65,97)))),B5))

Step 7

Type in the phrase you want to convert into a cipher in cell "B1" and press "Enter." The ciphered version appears in cell "B5." Typing in the ciphered text in cell "B1" and pressing "Enter" gives you the original text back.

Video of the Day

Advertisement

Advertisement

references