123 useful answers · Page 3 of 6
Work through formulas, formatting, sorting, and everyday data tasks.
Search this topicUse =UNIQUE(A2:A50,FALSE,TRUE). The final TRUE keeps only values occurring once in the source; repeated values are omitted entirely. With Apple, Apple and Pear, the result is Pear. By contrast, ordinary UNIQUE returns Apple and Pear. Confirm which meaning of unique your task requires before using the result to identify one-time entries.
For names in A and amounts in B, =SORT(A2:B50,2,-1) creates a separate result sorted by the second column, descending. The entire row moves together. Use this in Excel with dynamic arrays, outside the source range, and leave the spill area empty. Equal amounts do not establish a meaningful secondary order; add SORTBY if you need a tie-breaker.
Use =SORTBY(A2:C50,A2:A50,1,B2:B50,-1) when column A contains categories and B contains dates. It groups categories in ascending order, then orders dates newest first within each group. The sort ranges must have the same number of rows as the returned table. Confirm the date column contains true dates rather than date-shaped text before trusting the order.
In Microsoft 365 or Excel 2024, =TEXTBEFORE(A2,"-") returns the text before the first dash. For BOX-204-B, the result is BOX. If some codes have no dash, choose the desired fallback with the function's if_not_found argument or an explicit error check. Do not silently treat malformed codes as valid categories when the delimiter is required.
Use =TEXTAFTER(A2,"-",-1) in Microsoft 365 or Excel 2024. A negative occurrence number searches delimiters from the end, so BOX-204-B returns B. The last segment is not necessarily a file extension or product category unless your data follows that rule. Test codes containing no dash, one dash and several dashes before filling the formula down.
In Microsoft 365 or Excel 2024, use =TEXTSPLIT(A2,,CHAR(10)) for text separated by line-feed characters. The omitted second argument leaves the column delimiter empty; the third sets the row delimiter. Put the formula where rows below are free. Imported text may use different newline characters, so inspect a small sample if unwanted symbols or blank lines remain.
Use =TEXTSPLIT(A2,",",,TRUE) in Microsoft 365 or Excel 2024 to split comma-separated text and ignore empty pieces. For red,,blue, it returns red and blue in adjacent cells. This is useful for loose lists, but unsuitable when empty fields preserve column positions in structured records. Decide whether an empty value means missing data before dropping it.
Use =MID(A2,4,3) to take three characters starting at the fourth character. For AB-123-X, this returns 123 as text. MID counts from one, including spaces and punctuation. It works well for fixed-position formats; if the starting segment has variable length, locate a delimiter first instead. Keep the result as text when leading zeros are meaningful.
Use =LEN(A2) to count characters in a cell, including spaces and punctuation. It is useful for checking a fixed-length code or spotting unexpected trailing spaces. A visually short value may contain invisible characters. LEN is not a word count, and Unicode characters can behave differently in older compatibility modes, so test representative text when exact technical length matters.
Try =CLEAN(A2) in a helper column to remove the nonprinting characters that CLEAN supports. Compare the result with the original before replacing data. CLEAN does not remove every invisible Unicode character or fix every extra space, so a stubborn issue may need a targeted SUBSTITUTE or import cleanup. Preserve the original column while diagnosing where unwanted characters enter the data.
In a neighboring column, enter =UPPER(A2) and fill down. Review the results, then copy and paste values if you want fixed text instead of formulas linked to the original column. Uppercase formatting changes letters, not the underlying meaning or spelling. Avoid overwriting names or identifiers until you know capitalization is not significant for the system receiving them.
Use =CONCAT(A2:C2) to join the cell contents directly. If the cells contain AB, 12 and X, the result is AB12X. CONCAT does not automatically add spaces or punctuation, so use & to insert explicit separators or TEXTJOIN when separators should appear between many values. Check that blank components do not produce a misleading-looking identifier.
Use =FIND("A",B2) to locate an uppercase A within B2. The first character has position 1, and a missing match produces #VALUE!. FIND distinguishes uppercase from lowercase, unlike SEARCH. It is a position-finding tool rather than a replacement tool. Test a string containing both A and a if capitalization matters in your code format.
Use =ROUND(A2,2) or wrap the calculation, such as =ROUND(B2*C2,2). This changes the value used by later formulas. Merely showing two decimal places changes appearance while retaining additional precision underneath. For 12.345, ordinary ROUND gives 12.35. Decide whether your rules require rounding each row or rounding only the final total; those can produce different results.
Use =ROUND(A2,-2). A negative number of digits rounds to positions left of the decimal point: -1 means tens and -2 means hundreds. For 1,249, rounding to the nearest hundred gives 1,200; for 1,250, it gives 1,300. Keep an unrounded source column if later calculations need the original precision rather than presentation-ready figures.
Use =ROUNDDOWN(A2,0) to discard the fractional part toward zero. It turns 4.9 into 4 and -4.9 into -4. This differs from INT, which moves down the number line and would turn -4.9 into -5. Choose the rule deliberately, especially when negative adjustments appear in the same column as positive quantities.
For nonnegative values, use =MROUND(A2,5). It turns 12 into 10 and 13 into 15. MROUND expects the number and multiple to have compatible signs, so handle negative inputs deliberately. Values exactly halfway and decimal multiples deserve a spot check because floating-point representation can affect boundary cases. Keep the original values alongside the rounded results for comparison.
For a nonnegative quantity in A2 and a pack size of 6, =CEILING.MATH(A2,6) gives the next whole-pack quantity. A requirement of 14 becomes 18. Divide that result by 6 if you need the number of packs, which is 3. Confirm the pack size is positive and consider separately how returns or negative adjustments should be treated.
For a nonnegative stock count in A2 and packs of 6, =FLOOR.MATH(A2,6) gives the amount contained in complete packs. With 14 units, it returns 12, leaving 2 units outside a full pack. Divide by 6 for the pack count. This rounds toward a lower multiple; it is different from ordering enough packs to cover demand.
Use =ABS(B2-C2). If B2 is 18 and C2 is 25, the result is 7 rather than -7. This is useful when only the gap matters, but it removes the direction of change. Keep B2-C2 in a separate column if readers also need to know whether something increased or decreased.
INT rounds downward to the next integer, so =INT(-3.2) gives -4. Downward means lower on the number line, not closer to zero. If you want to discard the decimal portion toward zero, use a suitable truncation function such as ROUNDDOWN(number,0). Test positive and negative examples before using an integer conversion in a mixed-sign dataset.
Use =MOD(A2,B2), where A2 is the quantity and B2 is the divisor. With 17 units and a pack size of 5, the remainder is 2. A zero divisor causes an error. Negative inputs follow MOD's sign rules, so do not assume the result matches a programming language's remainder operator. For ordinary packing examples, keep quantity and pack size nonnegative and positive respectively.
Use =MOD(ROW(),2)=0 as a conditional-formatting formula for even worksheet rows. To alternate relative to a data block starting in row 2, use =MOD(ROW()-ROW($A$2),2)=0. Apply the rule to the intended range and verify its first two rows. The pattern follows row position; it does not permanently color a particular record when records move.
If values are in B2:B5 and their weights are in C2:C5, use =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). The weights do not have to total 100, but their total must not be zero. For values 80 and 100 with weights 1 and 3, the result is 95. Confirm that every value is paired with its intended weight.
Source checks for these answers: September 21, 2026. Product instructions and local requirements can vary.