The core way to build a truth table in Excel is to use logical functions like AND, OR, NOT, and IF, combined with a grid of all possible input combinations. For example, to show the result of A AND B for two inputs, put 0 and 1 in cells A2 and B2, then enter =AND(A2=1,B2=1) in C2 and copy down.
Why Build a Truth Table in Excel?
Truth tables are a standard tool in logic, digital electronics, and programming to map every possible combination of inputs to a single output. Excel is a convenient place to build them because you can use formulas to compute outputs, then visually inspect or even use the table as a lookup reference. You might need one to verify a logical expression, teach Boolean logic, or test a decision rule before implementing it in a larger model.
Setting Up the Input Grid
Start by deciding how many inputs you have. For n inputs, you need 2n rows. For two inputs, that’s 4 rows; for three, 8 rows; for four, 16 rows. The fastest way to generate all combinations is to use the SEQUENCE function (Excel 365/2021).
For two inputs, put the following in A2 (assuming headers in row 1):
=MOD(INT((SEQUENCE(4)-1)/2),2)
This returns 0,0,1,1. In B2, enter:
=MOD(SEQUENCE(4)-1,2)
This returns 0,1,0,1. Together they list all four combinations. For three inputs, use 8 in the SEQUENCE and adjust the divisor: =MOD(INT((SEQUENCE(8)-1)/4),2) for the first input, =MOD(INT((SEQUENCE(8)-1)/2),2) for the second, and =MOD(SEQUENCE(8)-1,2) for the third.
If you prefer a manual approach, simply type the combinations yourself—for two inputs, that’s only four rows.
Writing the Logical Formulas
With inputs in columns A and B (rows 2–5), you can compute outputs in column C. For a basic AND truth table:
=AND(A2=1,B2=1)
This returns TRUE or FALSE. If you prefer 1 and 0, wrap it in IF:
=IF(AND(A2=1,B2=1),1,0)
For OR: =IF(OR(A2=1,B2=1),1,0). For NOT: =IF(NOT(A2=1),1,0). For XOR (exclusive OR), there is no built-in XOR function in older Excel, but you can use =IF(A2+B2=1,1,0) because XOR is true when exactly one input is 1.
You can also build more complex expressions, like (A AND B) OR (NOT C). With inputs in A, B, C, the formula would be:
=IF(OR(AND(A2=1,B2=1),NOT(C2=1)),1,0)
Using Conditional Formatting to Visualize
To make the table easier to read, you can apply conditional formatting. Select the output column (C2:C5), go to Home > Conditional Formatting > Highlight Cells Rules > Equal To, and set the value to 1 with a green fill. This will highlight all true results. For a more advanced look, use a formula-based rule: select the range, then Home > Conditional Formatting > New Rule > Use a formula, and enter =C2=1 (adjust to the first cell of your selection).
Common Problems and Fixes
- Formulas return #VALUE! because of text inputs: Ensure your input cells contain numbers (0 or 1), not text like “TRUE”. Use
--to convert text if needed. - SEQUENCE not available: If you are on Excel 2019 or earlier, use the manual entry method or fill down a pattern: type 0,0,1,1 in A2:A5 and 0,1,0,1 in B2:B5.
- Boolean vs. numeric output: Decide whether you want TRUE/FALSE or 1/0. Mixing them can cause lookup issues. Stick to one format.
- Copying formulas incorrectly: Use absolute references (e.g.,
$A$2) if you need to lock a cell, but for truth tables, relative references are usually fine.
Comparing with Other Tools
Excel is not the only place to build truth tables. Logic simulators like Logisim or online truth table generators can produce tables automatically, but they lack Excel’s flexibility for integrating with other data. Excel’s advantage is that you can combine truth tables with lookup functions, data validation, or even build a decision model that uses the table as a reference. For example, you could use INDEX and MATCH to retrieve an output based on input values stored elsewhere.
Cost and Accessibility
Excel is part of Microsoft 365, which requires a subscription or a one-time purchase. However, you can also use Excel for the web for free with a Microsoft account, though some functions like SEQUENCE are available there as well. If you need a free offline alternative, Google Sheets offers similar logical functions, but the syntax for generating combinations differs (you might use ARRAYFORMULA).
Frequently Asked Questions
How do I create a truth table for three inputs in Excel?
List all 8 combinations manually (000, 001, 010, 011, 100, 101, 110, 111) or use the SEQUENCE method described above. Then write your logical formula in the output column, referencing the three input cells for each row.
Can Excel handle more than 10 inputs in a truth table?
Technically yes, but 210 is 1,024 rows, which is manageable. However, with 20 inputs you’d have over a million rows, which is impractical. For many inputs, consider using a different approach like a decision tree or a lookup table with compressed logic.
Why does my formula return FALSE instead of 0?
Because AND and OR return Boolean values (TRUE/FALSE). If you want 1/0, wrap the formula in IF as shown earlier, or use the double unary operator: --(AND(A2=1,B2=1)).
Is there a built-in XOR function in Excel?
No, Excel does not have an XOR function. Use =IF(A2+B2=1,1,0) for two inputs, or for more inputs, use =MOD(A2+B2+C2,2) to get odd parity (which is XOR for multiple inputs).
Can I use truth tables to validate a formula in Excel?
Yes. Build the truth table for your logical expression, then compare the output column to expected results. This is a good way to catch errors in complex nested IFs or boolean logic.
Related
- Excel Power Query XML: How to Import & Transform
- Excel Power Query Web Connector: How to Import Data
- Excel Power Query Web Data: How to Import & Refresh
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calendar Math in Excel: Dates, Workdays, and FormulasCalendar math in Excel means using real date values and date functions to add or subtract days, find…
- Excel Specify Range with OFFSET: How-To & ExamplesTo specify a range with OFFSET in Excel, use the formula =OFFSET(reference, rows, cols, [height], [width]), which returns…
- Excel Length of Service in Years and Months: FormulasTo calculate completed length of service in years and remaining months, enter the start date in A2 and…
- Trim Excel Data: Remove Extra Spaces the Right WayTo remove extra spaces in Excel, enter =TRIM(A2) in a helper column, replacing A2 with the cell you…
- Add Workdays to a Date in Excel: Formula and ExamplesTo add workdays to a date in Excel, use =WORKDAY(A2,B2), where A2 contains the starting date and B2…
- Excel SUM and XLOOKUP: How to Add Lookup Results in ExcelTo add the values returned by XLOOKUP, wrap the lookup formula in SUM: with prices in B2:B5, matching…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.