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 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.
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.
Step 3
Enter the value "0" in column "B2."
Step 4
Enter the following function in cell "B3":
=LEN(B1)
Step 5
Enter the following function in cell "B4":
=IF(B1="",B2,IF(B4<B3,B4+1,B3))
Step 6
Enter the following function in cell "B5":
=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