Over the last few months, I have found myself interested in cryptography. Cryptography is the art of writing and solving clues. Often solving clues involves decrypting some sort of hidden message. Encrypting and decrypting messages is a lot of fun.
A cipher is a series of steps taken to encrypt or decrypt a message that you might want to hide. One of the earliest known ciphers is Caesar Cipher. Julius Caesar used this to hide military messages. It’s a basic form of encryption where the letters were simply shifted by 3 back down the alphabet. Now a day’s early school kids crack codes like this with ease. However, it has lead to more secure encryptions such as Vigenère cipher and ROT13.
Excel is no match for most programming languages and many ciphers require multiple iterations and even processing power. However, I wanted to see if I could create a Shift Caesar Cipher in Excel without the use of VBA (before I go on and try some more complicated ones), that could both encrypt and decrypt string text with ease.
A Google search did turn up some good results, however, these were rather old and did not make use of Excel Power Tool functionalities. So, to modernize the resources out there, I have created a Ceaser Cipher using Excel and Excels Power Query in Office 365. In this model, you can both encrypt and decrypt a text string using an alphabet shift.
You can download the Excel model at the end of this post.
To encrypt text with a shift Ceaser cipher in Excel, we must first complete the blue input cells. I have set the input cells up as tables as they will be used to load data into power query and drive formulas.
The first table is Text, in which we enter the text you wish to encrypt.
The second table is where you define the number of letters to shift the text. On the decryptions side, this set up to shift forward or move up the alphabet. On the encryptions side, it is set to shift back down the alphabet.
Once you have completed the inputs, to encrypt your text, select Refresh ALL twice from the data ribbon.
The output table will populate with the encrypted text and the check table will output a reverse so you can ensure you have done the right thing.
On the worksheet Encrypt workings, we have the workings to encrypt the text by shifting the letters by the specified number. The encryptions side will shift the letter to the left, or down the alphabet and the decryptions side will shift the letter to the right, or up the alphabet.
In columns A:B we have our reference table. Down the rows of column A is each letter of the alphabet. Column B uses the OFFSET function to create a restructured alphabet based on the shift. In this example, we have 5 of a shift so f becomes a and so forth.
In column E we have a table returned by Power Query. we created this table by loading the Text table from the inputs into power query on refresh. It then splits the text by character and transposed it from a column to a row. Each character of text is now in a cell going down a column. This table is loaded into Excel.
Column G, Return, then carries out a VLOOKUP, referencing the Alphabet table, returning the shifted letters. This VLOOKUP is combined with other functions to account for spaces, punctuations, and errors. This same table is the also loaded to Power Query (on the second refresh). In power query, the transformations transpose the text from rows to columns and then merges all the columns together. The transformed table is then loaded back into Excel as the output table.
The model also has a built in check. Columns J & K reverse the shift used on the alphabet. This is then used to create the check column (column m) with the use of VLOOKUPS. This table is then loaded into power query where it is transposed and the columns combined to return the check output table on the main sheet.
The table with the text is first loaded to power query when we press refresh. This returns a table in the workings sheet that drives the next table, which is also loaded into power query. Therefore, we will need to refresh 4 times to account for the iterations.
There is also a limit of to plain or ciphertext of 90 characters in this model. To increase this limited, you will need to edit the queries to account for the merge of the additional new columns.
Decrypt the following piece of ciphertext. Comment below with the hidden message
extkg tgw xtkg hyyxk : 50% vtla wblvhngm hg tgr mktbgbgz vhnklxl yhk hger 10 lmxxf
(you may have to try multiple shifts before you find the correct answer)
If you have any questions, comments or more efficient steps, please do drop me a comment below. It might also be worth a reward!!!!!
Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox with Earn and Learn activities.SIGN UP NOW
Now there is value in Learning with The Excel Club and our Learn and Earn STEEM activities.
We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.
