Excel formulas are useful when they express a repeatable operation on data rather than a one-off manual calculation. A beginner should learn how cell references behave, how ranges are expressed, how errors arise, and why a result may be mathematically valid but analytically misleading. The nine functions below cover totals, averages, counts, conditions, lookups, and text preparation. Each example uses synthetic worksheet data and can be recreated without a workbook download.
In formulas, a range such as B2:B20 includes both endpoint cells. A relative reference can change when copied; $B$2 is fixed, and $B2 fixes only the column. Most examples assume a modern desktop or Microsoft 365 version of Excel. Some functions, notably XLOOKUP and TEXTJOIN, are not available in every older edition, so verify the environment used by the intended audience.
1. SUM: add amounts in a range
Formula: =SUM(B2:B12) adds numerical values in eleven cells. It ignores blank cells and text that Excel does not interpret as numbers. If a column contains amounts imported as text, an unexpectedly low sum may reflect a data-type problem rather than a broken formula.
Use SUM for a straightforward total such as monthly expense or product quantity. Compare the result with a known control total before treating it as an approved financial figure. A filtered worksheet may require SUBTOTAL or another approach if the business question is supposed to exclude hidden rows; plain SUM does not automatically adopt every filter-based interpretation.
2. AVERAGE: compute a mean without hiding missing data
Formula: =AVERAGE(C2:C16) calculates the arithmetic mean of numeric cells. Empty cells are ignored, while zero values count. This distinction matters: a zero test score means something different from a score that was not recorded.
Averages can obscure variation, especially with outliers or highly skewed distributions. When presenting call-handling times, show the median or distribution where appropriate. A good analyst can explain what observations were included and why the statistic suits the decision.
3. COUNT: count numeric observations
Formula: =COUNT(D2:D30) returns how many cells contain numbers. It does not count text labels or blank entries. Dates stored as Excel serial values generally count as numeric data, while dates stored as text may not.
COUNT is useful for identifying how many valid numeric measurements exist in a dataset. It is not the same as counting customer records; if a customer ID is stored as text, COUNT may return zero even though every row has an ID. Choose the function that matches the meaning of a completed record.
4. COUNTA: count populated cells
Formula: =COUNTA(A2:A30) counts cells that are not empty, including text and many formula results. A cell containing a formula that returns an empty string can still be considered nonempty by COUNTA. That behavior can surprise people trying to count visible names.
Use COUNTA for a rough completeness check, then investigate whether whitespace, formula-produced blanks, or header rows distort the result. It is not a guarantee that a row contains a valid business record.
When reconciling a list, create a small check area containing a manually verified number of expected records, the COUNT or COUNTA result, and a note about excluded rows. Do not compare a formula result only against itself. Real quality control needs an independent reference, such as an export with documented row count or a signed-off statement from the source owner. If the data has a blank at the end of a copied range, a static formula may silently omit new entries; converting the data to an Excel table and using structured references may be more robust.
5. IF: return a result based on a test
Formula: =IF(E2>=70,"Pass","Review") returns one of two labels based on a numeric threshold. The statement can be copied down to other rows; its relative reference updates accordingly.
Define the threshold from an approved rule rather than choosing a convenient number. If a blank E2 cell is interpreted as zero, the formula may return Review for a missing result; that might be desired or might conceal incomplete data. A more explicit form is =IF(E2="","Missing",IF(E2>=70,"Pass","Review")) when distinguishing missing data is important.
6. SUMIF: total only matching rows
Formula: =SUMIF(A2:A20,"North",B2:B20) sums numbers in column B where the matching row in column A has the label North. Its criterion range and sum range should align on the same underlying records.
A sum can be incorrect if a category contains unexpected spaces or alternative spellings. Inspect the source values and document any normalization. Where more than one condition is required, learn SUMIFS rather than constructing elaborate nested expressions merely to avoid a new function.
7. COUNTIF: count records matching a condition
Formula: =COUNTIF(C2:C20,"Open") counts the cells matching the criterion Open. This is useful for a service desk queue, provided the status field uses controlled categories. COUNTIF can also accept criteria such as ">=10", but comparison rules and data types must be understood.
The result may be misleading if statuses are free text, for example Open, OPEN-REVIEW, and Ongoing being mixed. Data validation and a stable status dictionary are often more useful than making a complicated formula to compensate for inconsistent labels.
For conditional formulas, build an exception table rather than trying to hide every problematic result. A SUMIF formula can be correct while assigning two spellings of the same category to separate totals. Record normalization choices, keep a copy of raw imports where policy allows, and test at least one boundary case: an exact threshold, an empty string, a zero, and a negative value. A count that is unexpectedly small often signals inconsistent data types rather than a calculation error. Reporting the exception alongside the number is usually more useful than forcing the number to look complete.
8. XLOOKUP: retrieve a matching value safely
Formula: =XLOOKUP(G2,A2:A100,D2:D100,"Not found") searches for the value in G2 within A2:A100 and returns the corresponding value from column D. The explicit fallback avoids some cryptic errors and can help identify unmatched identifiers.
Before relying on a lookup, verify whether the key is unique and whether the expected match is exact. An employee ID as text may not match the same-looking value as a number. Duplicated keys can produce an unexpected match. XLOOKUP improves readability, but it cannot replace a data-quality rule.
9. TEXTJOIN: combine several pieces of text
Formula: =TEXTJOIN(" - ",TRUE,A2:C2) joins nonempty values from three cells with a separator. The TRUE argument ignores empty cells. This is helpful for building a readable category label, address component, or summary field.
Do not use concatenated labels as the only key for an important transaction when stable identifiers exist. When merging personal or confidential information, consider whether the resulting text is appropriate to expose to other workbook users. The older CONCATENATE function and the & operator still exist, but TEXTJOIN gives convenient delimiter handling where available.
A lookup exercise is more meaningful when it includes a duplicate key, a missing key, and one identifier with leading zeros. If text ID “00045” is transformed into number 45, a displayed match might appear plausible but could identify a different record. Preserve identifiers as text when their characters are semantically important. Before merging two lists, check uniqueness and perform a count of unmatched rows. Consider a separate reconciliation report for duplicates instead of accepting the first XLOOKUP result as definitive.
Finally, distinguish worksheet formatting from data meaning. Displaying a number as currency or percent changes its appearance, not necessarily the underlying value. For example, 0.15 formatted as 15% is different from a cell containing 15 formatted with a percent symbol. Verify the source scale before applying formulas. Document units, rounding rules, and whether dates are actual date values or strings; otherwise totals and comparisons can be systematically wrong while appearing professional.
For collaborative workbooks, use protected reference cells or clear input areas where appropriate, but do not treat sheet protection as an effective confidentiality boundary. Separate sensitive records from public summaries and use appropriate access control at the file-storage level. Test sharing using an account with consumer-level permissions, not only the workbook owner’s account.
Build reliable workbooks with these functions
Create a small practice table with names, regions, amounts, statuses, and a lookup ID. Add one deliberately blank value, one text-formatted number, and one duplicated identifier. Use each function and explain how it behaves when the data is imperfect. Then alter one source value and check which results should change. These exercises teach calculation plus validation.
When copying formulas, inspect references before filling an entire column. A fixed tax rate in $H$2 should remain constant while an amount in B2 should advance to B3 on the next row. Test the first, middle, and last records after a fill operation; a single missing dollar sign can make results drift systematically. Use clear input, calculation, and output sections, and label assumptions with units such as currency, minutes, or percentage points so another person can validate the work.
For larger imports and transformations, Power Query can be useful for reproducible cleaning before formulas operate. It is a separate tool, not a tenth formula. A dependable spreadsheet combines meaningful definitions, consistent input types, simple formulas, and checks that detect inaccurate results before anyone makes a decision from them.
