158 useful answers · Page 7 of 7
Use formulas, clean imported data, combine tables, and understand calculation errors.
Search this topicAdd =ISERROR(A2) in a helper column and fill it down alongside the results you want to review. TRUE flags an error such as division by zero, a missing reference, or a failed lookup. Keep the original column visible so you can diagnose the cause. ISERROR detects errors; it does not repair them or prove that every non-error answer is correct.
Use =ISNA(A2) to test specifically for the #N/A error. It returns FALSE for other error types, making it useful when a missing match needs a different response from a broken formula. Inspect the lookup formula and its match settings too: #N/A can reflect an incorrect lookup setup rather than proof that the item is absent from the source data.
Use =ISEVEN(A2) on a cell containing a whole number. It returns TRUE for values such as 0, 2 and 8, and FALSE for odd integers. You can use that result in an IF formula or a conditional-formatting rule. Make sure the input is the whole-number quantity you intended to classify rather than a date serial or a formatted decimal.
Select the range, open conditional formatting, and use a custom formula such as =ISODD(ROW()). Choose a fill color. This uses the sheet's physical row numbers, so row 1 is odd and row 2 is even regardless of where your table starts. If you want table-relative striping, adjust the row calculation to account for the table's header and starting position.
Use =ISFORMULA(B2) in a helper column and fill down beside a column that should contain formulas. FALSE identifies cells without a formula; investigate those against the intended design. Some constants may be legitimate, so do not automatically replace every flagged cell. TRUE only confirms that a formula exists, not that its references or calculation are correct.
Enter =FORMULATEXT(B2) in another cell to display B2's formula rather than its calculated answer. This is useful when documenting a calculation or comparing neighboring rows. Reference a cell that contains a formula, and allow enough column width to read it. If you supply a range, FORMULATEXT inspects only its top-left cell rather than listing every formula in the range.
Use =TYPE(A2) in another cell. Common results are 1 for a number, 2 for text, 4 for a logical value, and 16 for an error. This can reveal why a value that looks numeric behaves differently in another calculation. TYPE describes the result's type; it does not tell you whether the source cell contains a formula or a manually entered value.
N is not a general text-to-number parser. It preserves numeric values, turns TRUE into 1 and FALSE into 0, and returns 0 for ordinary text, including numeric text such as "123". Use VALUE when you intend to interpret a supported numeric string instead. Check first that the data represents a quantity, not an identifier whose leading zeros must survive.
Use =T(A2) in a helper column and fill down. Text is returned unchanged, while ordinary numeric and logical values become empty strings; source errors remain errors. This is useful when a later calculation should receive only text. It does not convert numbers to their written form, and an empty-string result is not the same as a truly empty cell.
Use =SUMX2MY2(A2:A10,B2:B10) for paired numeric ranges. For each position it calculates the first value squared minus the second value squared, then adds those differences. With a single pair of 5 and 3, the result is 25 minus 9, or 16. Keep the rows aligned. This is different from squaring the difference, which would give 4 for that pair.
Use =SUMX2PY2(A2:A10,B2:B10) with aligned numeric ranges. It squares each value separately and adds all the squares. A single pair of 3 and 4 contributes 9 plus 16, or 25. It does not square the sum of each pair: that would be 49 in this example. Check which calculation your problem requires before choosing the function.
Use =SUMXMY2(A2:A10,B2:B10) for two aligned numeric ranges. It subtracts each second-column value from its partner in the first column, squares that difference, and totals the results. A pair of 5 and 3 contributes 4. Keep each pair on the correct row and check missing data before interpreting the total as a measure of agreement between the columns.
Make a copy first, then select the complete table you want to clean. Choose Data > Data cleanup > Remove duplicates and select the columns that determine whether records match. Review the selected columns before confirming: matching names alone may not mean two orders are the same. Check the remaining rows afterward. Use UNIQUE instead if you want a separate formula-based list while preserving the source.
Select A2:A100, open Format > Conditional formatting, and choose Custom formula is. Use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1), choose a fill, and click Done. The fixed range checks the whole list while A2 follows each row. This flags every occurrence of a repeated nonblank value without deleting anything. COUNTIF ignores letter case; use another method if capitalization must distinguish records.
Source checks for these answers: September 21, 2026. Product instructions and local requirements can vary.