How to Make Ciphers With Excel

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.

Step

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.

Step

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.

Step

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

Step

Enter the following function in cell "B3":

Step

\=LEN(B1)

Step

Enter the following function in cell "B4":

Step

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

Step

Enter the following function in cell "B5":

Step

\=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

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.