In the Microsoft Excel spreadsheet program, you can carry out calculations on data values. If you have a series of characters, you can use Excel functions to translate them into other characters, creating a simple cipher. Excel offers a range of different functions you could use for ciphers, such as moving characters along the alphabet. By first translating your characters into their ASCII representation, you can carry out a numeric calculation on them, then translate them back into characters for communicating your encoded text.

Prepare your characters. In an Excel worksheet, place your characters in consecutive rows, all in the same column, for example listing characters A to Z both upper and lower case into the A column. In the first row of the B column, translate the character in the first row within column A to its ASCII number as follows: \=CODE(A1)

  • In the Microsoft Excel spreadsheet program, you can carry out calculations on data values.
  • In an Excel worksheet, place your characters in consecutive rows, all in the same column, for example listing characters A to Z both upper and lower case into the A column.

Press Enter to see the result. Select the "B1" cell again and copy it. Paste it into the remaining rows in column B by selecting all of them and pasting the copied formula. You should see the ASCII value for each character listed in column B. The upper case letter "A" should be represented by 65, with lower case "a" represented by 97 and so on.

  • Press Enter to see the result.
  • You should see the ASCII value for each character listed in column B.
  • The upper case letter "A" should be represented by 65, with lower case "a" represented by 97 and so on.

Add to the value of your character. A common cipher involves rotating characters along the alphabet. For example, the letter "a" could become "f" by rotating five places. In the first row of column C, implement this by adding a value of 5 to the value in column B: \=B1+5

Press enter, then copy and paste the formula throughout the rows in column C as with the ASCII coding in column B.

Accommodate the bounds of the alphabet. Before you can translate your number back into a character, you need to accommodate either end of the alphabet if you have used a rotation cipher. For example, once it reaches the end of the alphabet you will need your cipher to move back to the start, so the letter "v" will become "a" if you are rotating by five characters. In Excel, this requires a conditional statement, in column D: \=IF(OR(C1>122, (AND(C1>90, C1<97))), C1-26, C1)

This test will determine what appears in column D. If the value in column C is either greater than 122 or between 90 and 97 exclusive, the code deducts 26 from it, otherwise it simply lists the value from column C as it is. This is to accommodate the fact that upper case Z character is ASCII 90, lower case "a" is 97 and lower case "z" is 122. Paste the formula throughout column D.

Translate your number back to a character. You can carry out any additional ciphering processes you choose at this point, adding a column for each step. To translate your number back to a character, use the following code in the first row of Column E or whatever your final column is, altering the D to suit the column you are at: \=CHAR(D1)

Column E should now contain the encoded version of the character listed in each row of Column A, with "f" for "a", "a" for "v" and so on.

TIP

Your cipher can involve multiple steps, each added to its own column as you require.

WARNING

If you implement a cipher for encoding text, you may also need to translate it into a deciphering function, perhaps in another spreadsheet.