Creating Your Own Secure Messages– Part 1, by DaytonPrepper1

I am an experienced programmer with a lot of time spent in Excel everyday for my paying job. East Sierra Sage’s article on Cipher Security got me thinking again about One Time Pads and other secure message techniques. I really enjoy automating things with Excel’s powerful formulas and macros. So I set off to create a tool for a One Time Pad spreadsheet that would create the One Time Pads and also encode and decode the messages being sent.

How to Use a One Time Pad (OTP)

You will need a Shift Chart and an OTP Chart as the basic components for this type of message security. The Shift Chart is used to look up a character in your message and then offset or shift to a position in the Shift Chart using another character found in the OTP Chart. Your message will be encoded one character at a time.

To Encode Your Message

To encode your message, you take the first letter of your message and go down the left most column of your Shift Chart. When you find that letter in your Shift Chart, you will then find the next character available in your OTP Chart. To do that, go to the row of your OTP Chart and find the next unused character. If you have already used all of a row, just go down to the next available row. With your OTP character you find it by going across the top row of the Shift Chart. Use the character where the column and row intersect as your encoded letter. Cross off your OTP character in the OTP Chart, so that you will not ever use it again. Continue with that process until you have your entire message encoded.

To Decode a Received Message

To decode a received message, find your next OTP character in the OTP Chart and then find it by going across the top of your Shift Chart. Then look for the first received character in the message and go down that column until you find that character. Follow that row all the way to the left to see what the decoded message character is. Cross off your OTP and repeat that process until your message has been decoded.

Note: If you are new to this type of technique, it might be a good idea to print off the example picture in this article and then reread this section on how to use and OTP.

Creating the OTP Chart

Decide What Characters To Use

On a tab called OneTimePad, I allow the user to decide what characters they wish to use for the OTP. I decided to allow for 50 unique characters. The user can choose basically anything on the keyboard except an asterisk, a question mark, or a space. (Each of those characters is more difficult to program around.) These 50 characters are what will be used to create your OTP as well as the characters used in your encoded message.

Note About Use of High Numbered ASCII Codes

Advanced Note: You can even use some of the higher numbered ASCII codes to create something that is visibly harder to discern because they are characters or symbols that we aren’t used to looking at. (Examples of these are œ,ž,Ÿ,¡,¢,£,¤, and ¥.) If you are just getting started, I would suggest sticking with just uppercase letters, 0-9, and a few of the characters above the numbers on the keyboard, like in the example.

Create a 50×50 Grid of Randomly Created OTPs

Once the characters are selected, the user clicks a macro button to create a 50×50 grid of those randomly created characters using Excel’s random number generator. It is widely known that Excel’s random number isn’t so random, so I decided to take the characters that it generated and do some additional sorting to mix up the randomness even farther. I did this through sorting each of the rows and columns several times each. The result is 2,500 (50×50) randomly created OTPs.

Note: In the screen shot, you can see the Usable Letters at the top. The numbers above them are a count of how many times it appears in the 50×50 OTP Chart.

Creating the Shift Chart

Creating Column and Row Headers

On a tab called Shift, I have my Shift Chart setup to be another 50×50 grid. I start by creating column and row headers. The row headers are the actual characters that I want to type as my message (A-Z, 0-9, et cetera). The column headers are the same characters that I will be using in my OTP. Those 50 characters can also be A-Z, 0-9, et cetera, or they can be some of the ASCII characters that I referred to earlier.

Start Offsetting the Next Row

Once your headings are in place, copy your column headings down as your first row of data in your 50×50 grid. You will then want to start offsetting with the next row. By this, I mean that you will copy the row above by offsetting it one position to the right. Then, the first character that you didn’t copy is placed on the end of the row.





Follow that process until you complete your 50×50 grid Shift Chart.

Encoding and Decoding

On a tab called Convert, I have a cell to type in the message I want to encode. Below that I have a cell that will be filled in with the results of the encoding. Several cells below that, I have a cell to put in an encoded message that was received from your contact and below that a cell that will show the results after it is decoded.


So in this example, I have typed in in cell C4. After clicking the “Convert to Send” button, it is encoded to be B^EYD#_79/+OI^E8S36$. The macro does several things. Before encoding starts, it:

  • checks to make sure that the message only contains characters that are in the left column of my Shift Chart,
  • makes sure that there are no more than 140 characters in the message (we are not writing a book here after all), and then after encoding
  • copies the message into the next row available, starting in row 19 so that you can record the conversation

A received encoded message is copied into cell C8. It is decoded and the results placed into cell C9 as well as copied into the conversation area. The message I received was HI4KOEN$E)UA3D&E11&4Z&^), which decoded to “one of my favorite sites”.

Note about Use of Spaces

Programming Note: I do not allow spaces in the Shift or OTP charts, but I was expecting the user to use spaces between words as they typed their message. To allow for that, before any encoding takes place I substitute any space (“ ”) that I find in the message with an underscore (“_”). Likewise, after decoding has taken place, I substitute all underscores with spaces to show the original message.

How to Use

The Shift Chart and the OTP Chart are the key data pieces of this system. They can be created, printed, and used in a manual way without the aid of a computer. I created this spreadsheet, however, for more of a base station role that might have the aid of a computer.

Security Concerns

This now brings to mind many suggestions that I am sure we are all thinking about, like that the computer should be air gapped with no way for it to get an Internet connection and many other security concerns. I will allow you to figure those out or read the many suggestions that will come in from the comments section.

How to Get Message to Contact

So how do you get the message to your contact(s)? You could send them over the Internet with a separate computer via text message or email, via radio (though there can be no coded message over Ham radio until the SHTF), dead drop, Morse code, et cetera.

Extra Bells and Whistles

I have also created a way for multiple conversations to take place in the same spreadsheet. The potential issue is making sure to have an OTP for each person that you want/need to communicate with. So I will have one OTP Chart on its own tab for each group or person with whom I message. I have a drop down in cell A14 that allows me to select which contact that this conversation will be with and what OTP Chart will be used for encoding or decoding.

Watch Outs and Suggestions

  1. Use as few words as possible
  2. Do not try to use full sentences or necessarily proper sentence structure
  3. Do not use punctuation unless absolutely necessary.
  4. Messages have to be processed in the order they were sent and received.
  5. Do not create a new message until you have processed all received messages.

Pros & Cons


  • It is portable or could easily be used at a base station
  • It is simple to do with or without a computer
  • Very secure as long as the OTP Chart is secure and not reused


  • It is possible to corrupt the OTP, if messages are processed out of order.

I hope this inspires you to create something like this. I have really enjoyed creating this spreadsheet. I look forward to reading your comments. I am sure there will be suggestions for making it better and more secure.

In tomorrow’s Part 2, I will detail another encoding spreadsheet that I created after reading another article on SurvivalBlog several years ago.

See Also:

SurvivalBlog Writing Contest

This has been part one of a two part entry for Round 76 of the SurvivalBlog non-fiction writing contest. The nearly $11,000 worth of prizes for this round include:

First Prize:

  1. A $3000 gift certificate towards a Sol-Ark Solar Generator from Veteran owned Portable Solar LLC. The only EMP Hardened Solar Generator System available to the public.
  2. A Gunsite Academy Three Day Course Certificate. This can be used for any one, two, or three day course (a $1,095 value),
  3. A course certificate from onPoint Tactical for the prize winner’s choice of three-day civilian courses, excluding those restricted for military or government teams. Three day onPoint courses normally cost $795,
  4. DRD Tactical is providing a 5.56 NATO QD Billet upper. These have hammer forged, chrome-lined barrels and a hard case, to go with your own AR lower. It will allow any standard AR-type rifle to have a quick change barrel. This can be assembled in less than one minute without the use of any tools. It also provides a compact carry capability in a hard case or in 3-day pack (an $1,100 value),
  5. Two cases of Mountain House freeze-dried assorted entrees in #10 cans, courtesy of Ready Made Resources (a $350 value),
  6. A $250 gift certificate good for any product from Sunflower Ammo,
  7. Two cases of Meals, Ready to Eat (MREs), courtesy of (a $180 value), and
  8. American Gunsmithing Institute (AGI) is providing a $300 certificate good towards any of their DVD training courses.

Second Prize:

  1. A Model 175 Series Solar Generator provided by Quantum Harvest LLC (a $439 value),
  2. A Glock form factor SIRT laser training pistol and a SIRT AR-15/M4 Laser Training Bolt, courtesy of Next Level Training, which have a combined retail value of $589,
  3. A gift certificate for any two or three-day class from Max Velocity Tactical (a $600 value),
  4. A transferable certificate for a two-day Ultimate Bug Out Course from Florida Firearms Training (a $400 value),
  5. A Three-Day Deluxe Emergency Kit from Emergency Essentials (a $190 value),
  6. A $200 gift certificate good towards any books published by,
  7. RepackBox is providing a $300 gift certificate to their site.

Third Prize:

  1. A Royal Berkey water filter, courtesy of Directive 21 (a $275 value),
  2. A large handmade clothes drying rack, a washboard, and a Homesteading for Beginners DVD, all courtesy of The Homestead Store, with a combined value of $206,
  3. Expanded sets of both washable feminine pads and liners, donated by Naturally Cozy (a $185 retail value),
  4. Two Super Survival Pack seed collections, a $150 value, courtesy of Seed for Security, LLC,
  5. Mayflower Trading is donating a $200 gift certificate for homesteading appliances, and
  6. Two 1,000-foot spools of full mil-spec U.S.-made 750 paracord (in-stock colors only) from (a $240 value).

Round 76 ends on May 31st, so get busy writing and e-mail us your entry. Remember that there is a 1,500-word minimum, and that articles on practical “how to” skills for survival have an advantage in the judging.


  1. You lost any credibility you had right at the beginning when you ask the reader to use Excel (or any Microsoft product) for *anything* security related. (I’ve been a programmer & sysadmin for 30 years.)

    1. Not all versions of excel are bait big brother versions. With 30 years experience and with software licenses already purchased, what virtual machine with team viewer can you set to share with us please? I’ll need a Vip and VPN username and password please. The concept is sound it just needs a few issues ironed out. What spreadsheet tools would you recommend instead of Excel? What OS? What kernel?

      1. Teamviewer is a dead end in itself. Not secure at all by its design since it goes through their servers/control.

        I’d use LibreOffice and some flavor of Linux. I’ll stick by my original comment: All MS software is a dealbreaker.

  2. One of the oldest is the book method. It’s simple and ingenious, even children can use it. The drawback is similar to OTP’s. It was used during the American revolution. I hope Part 2 delves into that a little.

  3. This is a toy, not a serious tool: never, ever roll your own encryption! Do you know where and when Excel saves backup files? Do you know if it deletes them securely, or just removes the file handle? What if it’s running with low memory and swaps to disk? What about malicious programs that can grab the Excel OLE object and read exactly what is in it? In short, this just barely works at all, and doesn’t consider even the first steps towards security. It’s irresponsible to suggest to anyone that they could use this in the wild.

  4. ok how do I get a pad to you or somebody else that lives 500 miles away? Maybe somebody I do not know but have an urgent need to contact about dangerous information?

  5. You can use a large book. Everyone on your list should have an exact duplicate of your book (same edition, same printing, etc.). And OPSEC rules.

  6. This is a great article for the understanding of encryption/decryption methods. It has many uses within your teams for CW, Ameture Radio, paper messaging, etc. However, nothing is fool proof as I understand the Enigma Machine Code was broken, smoke signals from American Indians were broken, even the Uni Bomber Hideout was encoded (ratted out by his brother but it was still discovered).

    The best email is protonmail – the NSA can’t break that with your keys setup properly – they are in Switzerland and will never have their servers confiscated by the government (like in the USA).
    The best browser is TOR or Onion whilst utilizing VPN
    The best search engine is IXQUICK – very private an no previous searches stored.
    The best OS is Linix for your home computing needs.
    The best cell phone is Walmart throw away phones, changing up numbers every year, and paying the monthly reload service card with cash and then using Walmart’s LAN line to call in your reloaded card numbers (not your personal phone that they can tie that to this throw away).
    etc. etc. etc. Insert your own insights here….
    The best vehicle is pre-2014 and not having the black box [flight recorders] in them.

    It’s all about side stepping the authorities folks… nothing is foolproof.

  7. Never ever ever ever use a computer to create a one time pad. It’s impossible to completely erase the files used during the creation process, so a hostile party with access to your hard drive will be able to break your encryption.

    The other major error in this article is the use of a random number generator on a computer to create a one time pad. No matter how sophisticated such a program is, it doesn’t actually generate truly random numbers. Instead it runs a seed value through an algorithm to generate a huge list of digits that appear random to the untrained eye … but the same seed value will always generate the same string of digits. That’s easy for a hostile party to break.

    To get truly random digits, toss five 10-sided dice into a Tupperware container with a clear lid, shake it, and transcribe the digits five at a time onto a single sheet of paper. Write on a hard surface, and don’t write on the top sheet of paper attached to a pad, because otherwise you might leave faint impressions behind when you remove the sheet.

    Here’s a much better essay on the history, creation, and secure use of one time pads:

  8. DaytonPrepper1, thank you for writing this article. I realize that using a computer to generate anything that you want to be secure isn’t a good idea. However, for those of us who have never used OTP encryption to compose and decode messages, your article gives us a good starting point to learn how to use this method.

Comments are closed.