158 useful answers · Page 3 of 7
Use formulas, clean imported data, combine tables, and understand calculation errors.
Search this topicJoin the text with CHAR(10), for example =A2&CHAR(10)&B2. Enable text wrapping and give the row enough height so both lines are visible. The line break is part of the resulting text, which matters when exporting it. It does not create a new worksheet row or split the content into separate cells.
Use =UNICHAR(9733) to display a star character from its Unicode number. Appearance can vary with the font and device, so check the result where readers will view it. Use a valid Unicode value rather than guessing. A decorative symbol should not be the only way you communicate an important status.
Use ENCODEURL on the parameter value, such as ="https://example.com/search?q="&ENCODEURL(A2). It encodes spaces and special characters so they do not break the query. Replace the example address with your actual destination. Encode the value rather than the entire assembled URL, since encoding URL separators would change its structure.
Use =SUMIF(A2:A100,"Books",B2:B100) when column A contains categories and column B contains amounts. It adds the amounts for matching rows. Keep both ranges aligned and check category spellings and unwanted spaces. This applies one condition; use SUMIFS when a row must satisfy several conditions before its amount is included.
Use =SUMIFS(D2:D100,B2:B100,"West",C2:C100,"Paid"). It sums amounts in D only where B is West and C is Paid. Notice that SUMIFS puts the sum range first, unlike SUMIF. Keep each range the same shape and exclude summary totals from the input so you do not double-count them.
Use =COUNTIF(B2:B100,"Complete") to count cells matching that status. COUNTIF is not case sensitive, so differently capitalized versions can count together. It does not count each occurrence of the word inside one cell as a separate record. Check for extra spaces or inconsistent labels when the count differs from your expectation.
Use =COUNTIFS(A2:A100,">="&D1,A2:A100,"<"&E1) when D1 is the inclusive start and E1 is the exclusive end. Use real date values in those cells. An exclusive end is useful for timestamps because it avoids missing times later on the final day. Set E1 to the following day if your intended range includes that whole day.
Use =COUNTUNIQUE(A2:A20) for a populated list. Repeated values count once, so this answers a different question from counting all records. Decide how blanks should be handled before using a large open-ended range. Also check spaces and inconsistent text, since values that look similar may still be distinct to the function.
Use =COUNT(B2:B100). It counts numeric values, including repeated values, while ignoring text in the range. Dates and times stored as numbers can count too, so it is not a test for ordinary whole numbers only. If imported amounts are stored as text, inspect and convert them before relying on the count.
Use =COUNTA(A2:A100) to count cells containing values, including text and numbers. Be aware that whitespace and formulas returning empty text can still count. If you need only visibly meaningful records, define that rule separately. A COUNTA total is not automatically the number of completed forms or valid entries.
Use =COUNTBLANK(B2:B100) to count empty cells and cells containing empty strings. A cell with a space is different from a genuinely empty entry. Keep the range limited to the records you are assessing, or unused rows will inflate the total. This is useful for checking missing fields before exporting a dataset.
Use =AVERAGE(B2:B20) for the arithmetic mean of the numeric values. Zeros are real values and affect the result, while text in the referenced range is ignored. Decide whether a zero means an actual measurement or missing data before calculating. A formatted blank and a genuine zero should not be treated interchangeably.
Use =AVERAGEIF(B2:B100,">0"). It includes positive values and excludes both zeros and negatives. That is appropriate only if it matches your intended rule; excluding zeros can make a result look artificially high when zero is a valid observation. If you want all nonzero values instead, use the criterion "<>0".
Use =AVERAGEIFS(D2:D100,B2:B100,"West",C2:C100,"Complete"). It averages the D values only for rows meeting both criteria. The average range comes first, and all criteria ranges must align with it. Check how many records qualify as well as the average, since a small subset may not represent the full dataset.
Use =MEDIAN(B2:B20) to find the center of the numeric dataset. With an even number of values, it averages the two middle values. This can describe a typical observation when a few extreme values distort the mean. It does not return the middle cell by position, so sorting the worksheet first is unnecessary.
Use =MODE(B2:B100) for the most commonly occurring numeric value. It is not a general method for finding the most common text label. Check for ties and for datasets with no repeats before presenting one result as a definitive winner. A frequency table is more informative when several values occur equally often.
Use =MIN(B2:B100) to return the lowest numeric value in the range. Review zeros and negative numbers because they may legitimately become the minimum. Text cells are not ordinary numeric observations. If you mean the smallest value for one group or only values above zero, add a suitable condition instead of using the whole range.
Use =MAX(B2:B100) to return the largest numeric value. It gives the value rather than the name or row associated with it. Check that your range excludes grand totals and includes all intended records. Use a lookup or filtered result if you also need the corresponding product, date, or person.
Use =MINIFS(C2:C100,A2:A100,"Tools") when A contains categories and C contains prices. Keep the ranges the same size. MINIFS returns zero when nothing meets the criteria, so a zero result is not proof that a free item exists. Check for matching records before displaying the value as an actual price.
Use =MAXIFS(C2:C100,A2:A100,"Blue") to return the largest C value among rows labeled Blue in A. Keep the ranges aligned. If no records match, the function returns zero, so also check whether the team exists. Returning the maximum score alone does not identify which person earned it.
Use =LARGE(B2:B100,2). It returns the second entry when numeric values are ranked from largest to smallest, including duplicates. If the top value appears twice, it can also be the second-highest result. Use a distinct-value step first only when your question is specifically about the second different value.
Use =SMALL(B2:B100,3) to return the third value in ascending numeric order. Repeated values occupy separate positions. Make sure the range contains at least three valid numeric observations, and decide whether zeros belong in the calculation. The function returns a value, not the worksheet row where it was found.
Use =IF(B2>=70,"Pass","Review") when B2 contains the score and 70 is your chosen threshold. The first text is returned when the condition is true, and the second when it is false. Handle missing scores separately if an empty record should remain unclassified rather than being treated as a completed assessment.
Use =IFS(B2>=90,"High",B2>=70,"Medium",TRUE,"Low") for numeric scores. Conditions are checked in order, and the first true condition wins, so put the highest threshold first. The final TRUE supplies a fallback. Validate missing or nonnumeric inputs separately; the formula's labels should reflect your own stated scoring rules.
Source checks for these answers: September 21, 2026. Product instructions and local requirements can vary.