Encryption and Decryption Using the Vigenere Cipher

The Vigenere Table has been entered in cells A2 thru Z27 of the Lab3 spreadsheet.

Encryption

1. Enter the encryption/decryption key (up to 7 letters) beginning in cell AB3 of the spreadsheet.

2. Enter the plaintext message (up to 7 characters) beginning in cell AB8 of the spreadsheet.

3. In cells A30 thru B55, create a table to translate each letter from the key to a column number (A=1, B=2, etc.).

4.Calculate the ciphertext values. Enter a formula in Cell AB10, that will look up the plaintext character in column 1 of the Vigenere table, and find its corresponding ciphertext character in the column number calculated in Step 3. Drag the formula across to get the remaining ciphertext values.

Decryption

5.Write a formula that uses the translation table in cells A30 thru B55 to calculate a translation factor for each letter of the key. (Note: this number starts at 1 not 0, as we have used in all class examples; this is because we used the number to identify the correct column to use to encrypt.) Enter the formula in cells AB20 to AH20.

6.Use translation table in cells AC30 to AD55 to convert each step 6 result from a numeric to a letter, using A=0, B=1,etc. to find the plaintext. Since the Vigenere Tableau was created by adding a translation factor to each character value, the decryption can be achieved by subtracting the translation factor, MOD 26.

VIGENERE TABLE

ENCRYPTION

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

ENTER THE KEY (up to 7 letters) in CELLS AB3 thru AH3:

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

a

r

i

l

e

y

t

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

a

b

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

a

b

c

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

a

b

c

d

ENTER THE PLAINTEXT (UP TO 7 LETTERS) in CELLS AB8 thru AH8:

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

a

b

c

d

e

PLAINTEXT:

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

a

b

c

d

e

f

h

e

l

l

o

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

a

b

c

d

e

f

g

CIPHERTEXT:

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

a

b

c

d

e

f

g

h

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

a

b

c

d

e

f

g

h

i

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

a

b

c

d

e

f

g

h

i

j

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

a

b

c

d

e

f

g

h

i

j

k

m

n

o

p

q

r

s

t

u

v

w

x

y

z

a

b

c

d

e

f

g

h

i

j

k

l

DECRYPTION

n

o

p

q

r

s

t

u

v

w

x

y

z

a

b

c

d

e

f

g

h

i

j

k

l

m

ENTER THE CIPHERTEXT (UP TO 7 LETTERS) IN CELLS AB17 THRU AH17

o

p

q

r

s

t

u

v

w

x

y

z

a

b

c

d

e

f

g

h

i

j

k

l

m

n

CIPHERTEXT

p

q

r

s

t

u

v

w

x

y

z

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

y

m

w

p

m

q

r

s

t

u

v

w

x

y

z

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

r

s

t

u

v

w

x

y

z

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

Translation Factor

s

t

u

v

w

x

y

z

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

t

u

v

w

x

y

z

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

u

v

w

x

y

z

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

PLAINTEXT

v

w

x

y

z

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

w

x

y

z

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

x

y

z

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

y

z

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

z

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

Translate Key Letter to a Column Number

Translation Table

a

1

0

a

b

2

1

b

c

3

2

c

d

4

3

d

e

5

4

e

f

6

5

f

g

7

6

g

h

8

7

h

i

9

8

i

j

10

9

j

k

11

10

k

l

12

11

l

m

13

12

m

n

14

13

n

o

15

14

o

p

16

15

p

q

17

16

q

r

18

17

r

s

19

18

s

t

20

19

t

u

21

20

u

v

22

21

v

w

23

22

w

x

24

23

x

y

25

24

y

z

26

25

z