Excel - Encryption using Excel ( Great Trick )

Many more great Excel tutorials linked below:

Be sure to watch my other Excel tutorial videos on my channel, including more advanced techniques
and many useful and practical ones. Be sure to Subscribe and Comment.

[mmemarks]

I was looking for an easy way to generate cipher puzzles for elementary school students. This trick was genius. Thank you!

[SharonHarding]

Fabulous! You saved me a tonne of time. Thank you for providing such simple directions.

[TalentsphereCanada]

We extended the range in the table to include lower and uppercase and numeric digits 0-9. the made our range 58. ie C6-C66 and so on... We then used a mix on Vlookup and index/match to allow for case sensitivity, combined with a quick check for "@" and "." found in emails.

here is our first formula for encryption: {=IF(OR(E6="@", E6="."), E6, VLOOKUP(INDEX($C$6:$C$66, MATCH(TRUE, EXACT($B$6:$B$66, E6), 0)), $A$6:$B$66, 2, FALSE))}

note { } which you get by hitting Ctrl, Shift Enter after entering formula.

and now the one for decryption: {=IF(OR(E11="@", E11="."), E11, VLOOKUP(INDEX($A$6:$A$66, MATCH(TRUE, EXACT($B$6:$B$66, E11), 0)), $AD$6:$AE$66, 2, FALSE))}

Have fun

[davidh]

How do you encode the translation factor?

[Jawface]

OH MY GOD I searched for encryption in excel and it came up with this and I saw your comment and I was like OH MY GOD

[rossdude]

Have you not realised that with a translation factor of 0, then X, Y and Z have the same translated numeric as A, B and C...

[mario-t]

what is formula for column C ?? @2.54 how get C28=0 with factor 3 ?

[SANGERA]

Unfortunately, your formula for working out the translated numeric doesn't take into account when the number is bigger than 25, it will only work for a translation factor of 3 because you wrote 0, 1, 2 in the bottom three cells. You need to use an if statement to check if its bigger than 25 and then taking 26 from the value if it is. For example =IF(A5+$B$2>25, A5+$B$2-26, A5+$B$2)

[leebishop]

This would have been helpful if you actually explained anything in the video and explained what you did beforehand in the other tables before you got to the vlookup

[rdjalayer]

I am not sure I fully understand your question

[oupigs]

Is there a why to convert a letter into a symbol, Like "A" means "Ю" or "B" means "θθ"

[nglyo]

Maybe I understood it wrong, but how is this useful? If someone knows the translation factor they can just work out the ciphertext back into the original word quite easily with brute force (as per the example in video). Even if they only have access to the ciphertext, they just need to try out 25 different entries to get the correct word... only way to be useful is it uses a different ciphertext for each character, since then its strength is increased exponentially the longer the word is (unless that was the aim of this, just to show how its done).

[muchinore]

The instructor might have a bad temper. The way he talks is quite intimidating...

[paulFm]

Wow this was overly complicated and only works with lower case letters. I used some of your format but made one that uses upper case, lower case, numbers, and symbols. it's also way simpler and only uses one field with two columns. if anyone needs a copy, let me know and ill email it