Skip to Main Content

Data Cleaning with Excel

Through this guide you will learn tricks and tips to better clean and organize tabular data using Microsoft Excel.

Logical Operators

Another simple operation that can be applied to spreadsheet data in order to organize it quickly, or to hone in on specific slices of the data, is employment of logical operators or Booleans. The basic Boolean operators are AND, OR, and NOT. When these operators are applied to values in an Excel spreadsheet, they will return values of either TRUE or FALSE, as outlined below:

AND - returns a value of 'TRUE' if all specified conditions are true. If any or all conditions are false, then FALSE is returned.

OR - returns a value of 'TRUE' if at least one of all specified conditions is true.

NOT - returns a value of 'TRUE' if all specified conditions are not met.

To apply Boolean operators, create a new column next to the one on which the operators will run. Booleans follow the general Excel formula format: =AND, =OR, =NOT, followed by parentheses enclosing the cell number and the argument. The example below employs number-formatted cells, on which mathematical arguments will apply.

Screen Clipping showing how to add an AND Boolean, or, Logical Operator statement, to a field. This AND statement asks if the value in the cell to its left is greater than 50, and returns the value TRUE if it is and FALSE if it is not.

As with other Excel formulas, you can click in the bottom right corner of the formula cell and copy the formula down the entire column.

The next example shows a Boolean operation with multiple conditions, drawing from both the Weight and Height columns in the dataset.

Screen Clipping showing a compound Boolean statement, which queries two different cells to see if their values are greater than 50. A single AND statement encapsulates both queries, meaning that the value of TRUE can only be returned if both the specific conditions are TRUE.

After applying a Boolean argument to a column of data, you can sort and filter by whether the conditions you specified were met. The more complex your Boolean arguments, the more you can narrow in on very specific slices of data that meet very specific conditions.

For guidance on how to create more complex Boolean arguments, see Excel Boolean Logic: How to sift spreadsheet data using AND, OR, NOT, and XOR by JD Sartain.