Skip to main content
It looks like you're using Internet Explorer 11 or older. This website works best with modern browsers such as the latest versions of Chrome, Firefox, Safari, and Edge. If you continue with this browser, you may see unexpected results.

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.

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.

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.