Excel Your Spreadsheets
Quick: how do you add up a row of numbers in Excel? Or how many times a particular value or number appears in a column? Sure, you might not think it's all that important to excel at Excel, but at least the next time a job post asks for it, you won't need to lie—or, ahem, rely on ChatGPT—to organize your work spreadsheets. Plus, Excel isn't just for data analysts and accountants; the program can come in handy for sorting your personal finances, budgets, and even hobbies (like creating a collection tracker of all your Pokémon cards, because why not?). So, if you're ready to become an Excel wizard, be sure you memorize these 20 key formulas.
1. SUM
The SUM function adds numbers from individual cells or an entire range. For example, =SUM(B2:B10) calculates the total of every value between cells B2 and B10. It’s more efficient than typing each cell reference separately, especially when you’re working with long lists of expenses, sales, or quantities.
2. AVERAGE
Use AVERAGE when you need to calculate the arithmetic mean of a group of numbers. A formula such as =AVERAGE(C2:C20) adds the values in the selected range and divides the total by the number of numeric entries. This function is useful for reviewing test scores, monthly performance, customer ratings, and other data sets where a central value matters.
3. MIN
The MIN function returns the smallest number within a selected range. Entering =MIN(D2:D50), for instance, identifies the lowest value without requiring you to sort the data first. It can help you find the lowest price, shortest completion time, lowest temperature, or weakest performance result.
4. MAX
When you need the highest value in a data set, the MAX function provides it immediately. The formula =MAX(E2:E100) searches the specified cells and returns the largest number it finds. You might use it to identify the top sales figure, highest score, largest transaction, or peak inventory level.
5. COUNT
COUNT tells you how many cells in a range contain numbers. If you enter =COUNT(A2:A30), Excel ignores text and blank cells while counting numeric entries. This makes the function helpful when you want to confirm how many recorded values appear in a column.
6. COUNTA
Unlike COUNT, the COUNTA function includes cells containing text, numbers, dates, logical values, and most other nonblank entries. A formula such as =COUNTA(A2:A30) shows how many cells in the range contain any type of data. It’s particularly useful for counting names, completed records, submitted responses, or populated rows.
7. IF
The IF function checks whether a condition is true and returns one result if it is and another if it isn’t. For example, =IF(B2>=70,"Pass","Fail") displays “Pass” when the value in B2 is at least 70 and “Fail” when it falls below that level. Learning this function allows you to create spreadsheets that respond automatically to different values.
8. AND
AND determines whether multiple conditions are all true at the same time. You could use =AND(B2>=70,C2="Complete") to confirm that someone has both earned a passing score and completed a required task. The function returns TRUE only when every included condition is satisfied.
9. OR
The OR function checks several conditions but only requires one of them to be true. A formula such as =OR(D2="Urgent",E2<TODAY()) can flag a record when it’s marked urgent or when its deadline has passed. This gives you more flexibility when several different circumstances should produce the same result.
10. SUMIF
SUMIF adds values only when they meet a specific condition. For instance, =SUMIF(A2:A100,"West",B2:B100) totals the numbers in column B whenever the corresponding cell in column A contains “West.” It’s a practical choice for calculating totals by department, region, category, employee, or product type.
11. SUMIFS
When one condition isn’t enough, SUMIFS lets you calculate a total using several criteria. The formula =SUMIFS(C2:C100,A2:A100,"West",B2:B100,"Online") adds values from column C only for rows that match both “West” and “Online.” This function is especially valuable when you’re analyzing detailed records with multiple categories.
12. COUNTIF
The COUNTIF function counts how many cells meet a stated condition. Enter =COUNTIF(B2:B50,"Approved") to see how many cells in the range contain the word “Approved.” You can also use comparison operators, such as =COUNTIF(C2:C50,">100"), to count values above or below a certain number.
13. COUNTIFS
COUNTIFS works like COUNTIF, but it evaluates more than one requirement. For example, =COUNTIFS(A2:A100,"East",B2:B100,">500") counts rows where the region is East and the value exceeds 500. It’s useful when you need precise counts from a large table without filtering the records manually.
14. XLOOKUP
XLOOKUP searches for a value in one range and returns related information from another. A formula such as =XLOOKUP(A2,F2:F100,G2:G100,"Not Found") searches column F for the value in A2 and returns the matching entry from column G. It’s flexible, readable, and often easier to maintain than older lookup formulas.
15. VLOOKUP
Although XLOOKUP is available in newer versions of Excel, VLOOKUP remains common in existing workbooks. The formula =VLOOKUP(A2,F2:H100,3,FALSE) searches for the value in A2 within the first column of the selected table and returns a result from its third column. The final FALSE requests an exact match, which is usually the safest option for IDs, names, and product codes.
16. INDEX and MATCH
INDEX and MATCH can be combined to create a flexible lookup formula. One example is =INDEX(C2:C100,MATCH(F2,A2:A100,0)), which finds the value in F2 within column A and returns the corresponding entry from column C. This combination can look to the left or right of the search column, which gives it an advantage over traditional VLOOKUP.
17. CONCAT
The CONCAT function joins text from multiple cells into one result. You could enter =CONCAT(A2," ",B2) to combine a first name and last name with a space between them. It’s useful when building full names, addresses, labels, reference codes, or other combined text fields.
18. LEFT, RIGHT, and MID
These three functions extract selected characters from a text string. LEFT starts from the beginning, RIGHT starts from the end, and MID pulls characters from a specified position within the text. For example, =LEFT(A2,3) returns the first three characters from cell A2, which can help separate prefixes, area codes, or identifying segments.
19. TRIM
Extra spaces can interfere with sorting, matching, and lookup formulas, particularly when data has been imported from another source. The TRIM function removes unnecessary spaces while leaving single spaces between words. Using =TRIM(A2) can clean a text entry and make it more consistent with the rest of your spreadsheet.
20. TODAY
The TODAY function inserts the current date and updates automatically whenever the workbook recalculates. Since it doesn’t require an argument, you can simply enter =TODAY() into a cell. It’s helpful for calculating deadlines, tracking how many days have passed, identifying overdue items, and creating reports that always display the present date.
