Introduction: Make Logic Gates in Excel

By
About: I am an electronics engineer as well as a hobbyist in many other areas. My Instructables are based on engineering and electronics. I also like to do DIY projects involving other disciplines such as audio.

Making all 7 basic logic gates in Excel is not very difficult. If you understand the functions in Excel, then this project would be fairly simple, if you don't, no worries it won't take long to get used to.

Excel already created a few logic gates for us but it does not include all 7 and we want to make it ourselves anyway.

The project does not take long and once done, you can create many circuits digitally in Excel.

Step 1: What You Need

You do not need much for this project.

  • Computer

  • Excel (I recommend Excel but similar ones should also be fine)

  • Basic knowledge on how logic gates function

Step 2: Set Up Excel and Format

First start Excel (The version should not matter much, but I used Excel 2016), then open a new "Blank Workbook".

Then make the format you see in the picture above (Due to the shape of the image, you will have to click on it to view it properly, this applies to the following images). If you can't copy the format, then read the following:

Make column B & C one digit wide, merge Row1 A,B & C.

Then type the text in.

Step 3: AND Gate

The AND gate is the simplest one, this is because you can get the output by just multiplying the inputs.

0 x 0 = 0, 0 x 1 = 0, 1 x 0 = 0, 1 x 1 = 1

This product of the equation are the same as the outputs of the gate.

Copy the formula and test it by giving it the inputs (only in binary though).

Remember every time you create a new gate, copy the layout so nothing overlaps.

Step 4: OR Gate

The OR gate is more complicated, it requires a 'If' statement. 'If' statement work like this: =If(logic_test, [value if true], [value if false]). The logic test we are using is: Input1 + Input2 > 0, the true value is 1, otherwise the value is 0. This is because only if both inputs are false will the output become false, and since 0 + 0 = 0, anything set of input including a 1 would be bigger in value (sums value). So if the sum of both inputs is bigger than 0 then its out put is True or 1.

Step 5: NAND Gate

The NAND gate is just like the OR gate, it requires an 'If' statement and the logic behind it is similar. The gate will only give a False output if both inputs are True. So if we multiply both inputs, any sum smaller than 1 is going to be True because the 'If' statement is: input1 x input2 < 1, 1, 0. If that was confusing then this chart might help:

0 x 0 = 0, 0 < 1 so True = 1

0 x 1 = 0, 0 < 1 so True = 1

1 x 0 = 0, 0 < 1 so True = 1

1 x 1 = 1, 1 = 1 so False = 0

Step 6: NOR Gate

The NOR gate also uses an 'If' statement, the statement for this gate is: Input1 + Input2 < 1, 1, 0. This is because the gate only gives a True output of both inputs are false. Since we add both inputs together, any set of input including a 1 would be bigger than two 0s. Then the True and False statement shows that if any sum smaller than 1, show 1 otherwise show 0.

0 + 0 = 0, 0 < 1 so True = 1

0 + 1 = 1, 1 = 1 so False = 0

1 + 0 = 1, 1 = 1 so False = 0

1 + 1 = 2, 2 > 1 so False = 0

Step 7: XOR Gate

This is quite similar to the NOR gate, but instead of using a greater or less than symbol, we use an equals sign because the gate will only give a True output of it has mixed inputs, so if we add both inputs together, mixed inputs will always give a 1 so we use the statement: Input1 + Input2 = 1, 1, 0.

0 + 0 = 0, 0 ≠ 1 so False = 0

0 + 1 = 1, 1 = 1 so True = 1

1 + 0 = 1, 1 = 1 so True = 1

1 + 1 = 2, 2 ≠ 1 so False = 0

Step 8: XNOR Gate

The XNOR gate is fairly simple, it is basically the opposite of the XOR gate, this means that the logic test is the opposite as well. This gate only gives a True output if both inputs are the same number, in other words any mixed set of inputs is False. The logic test for the XOR gate is: Input1 + Input2 = 1, but the logic test for the XNOR gate is: Input1 + Input2 ≠ 1. (<> is ≠ in Excel formulas).

0 + 0 = 0, 0 ≠ 1 so True = 1

0 + 1 = 1, 1 = 1 so False = 0

1 + 0 = 1, 1 = 1 so False = 0

1 + 1 = 2, 2 ≠ 1 so True = 1

Step 9: NOT Gate

The NOT gate is a simple gate but its 'If' statement is just the same as others. It only has one input so you may want to change your format. The gate just inverts its input so the formula is not that hard, the logic test is: if input is 0, and True statement is: display 1 otherwise display 0.

0 = 0, so True = 1

1 ≠ 0, so False = 0

Step 10: Digital Logic Circuit

Once you have created all the logic gates, you can use them to make logic circuits in Excel. But the current format is too big so you can try the new format (image above).

Make two columns one digit wide, merge top two cells to create output display, bottom two cells are inputs.

When typing in the formula, type the formula of the gate you want in the output display place.

Step 11: Troubleshoot

If at any stage a logic gate does not function correctly, make sure that you formula is typed in correctly and that the inputs are linked correctly to the formula.

If you are certain everything is correct, then I might have made a mistake writing this instructable, if so, please tell me in the comments so I can correct.