Basic Excel formulas & functions with examples

This tutorial introduces fundamental Excel formulas and functions, providing examples and links to more detailed guides. Microsoft Excel's strength lies in its numerical capabilities, enabling swift calculations and problem-solving across various fields, from simple sums to complex financial modeling. This tutorial covers essential function usage and formula construction.

Understanding Excel Formulas and Functions

Before exploring specific formulas, let's clarify key terms:

• Formula: An expression calculating cell values. For instance, =A2 A3 A4 sums values in cells A2 through A4.
• Function: A pre-built formula performing specific calculations. =SUM(A2:A4) achieves the same sum using the SUM function.

The Function Library (Formulas tab) lists all available Excel functions. Excel's Formula Intellisense provides syntax and argument suggestions as you type.

Ten Essential Excel Functions

Here are ten crucial functions for Excel proficiency:

SUM

Adds numbers. Syntax: SUM(number1, [number2], ...)

• =SUM(B2:B6): Sums B2 to B6.
• =SUM(B2,B6): Sums B2 and B6.
• =SUM(B2:B6)/5: Sums B2 to B6 and divides by 5.
• Conditional sums use SUMIF: =SUMIF(A2:A6, D2, B2:B6) (sums B2:B6 if A2:A6 matches D2).

• AutoSum quickly sums columns or rows.

AVERAGE

Calculates the average. Syntax: AVERAGE(number1, [number2], ...)

• =AVERAGE(B2:B6): Averages B2 to B6.
• Conditional averages use AVERAGEIF: =AVERAGEIF(A2:A6, D3, B2:B6) (averages B2:B6 if A2:A6 matches D3).

MAX & MIN

Find the largest and smallest values.

• =MAX(B2:B6): Finds the maximum value in B2 to B6.
• =MIN(B2:B6): Finds the minimum value in B2 to B6.

COUNT & COUNTA

Count numeric cells and non-blank cells respectively.

• =COUNT(B:B): Counts numeric cells in column B.
• =COUNTA(B:B): Counts all non-blank cells in column B.

IF

Performs conditional logic. Syntax: IF(logical_test, [value_if_true], [value_if_false])

• =IF(C2"", "Yes", "No"): Returns "Yes" if C2 is not empty, "No" otherwise.

TRIM

Removes extra spaces from text. Syntax: TRIM(text)

• =TRIM(A1): Removes leading/trailing spaces from A1.

LEN

Counts characters in a text string. Syntax: LEN(text)

• =LEN(A2): Counts characters in A2 (including spaces).

AND & OR

Logical functions for multiple criteria.

• AND: Returns TRUE if all conditions are TRUE.
• OR: Returns TRUE if at least one condition is TRUE.

CONCATENATE

Combines text strings. Syntax: CONCATENATE(text1, [text2], ...)

• =CONCATENATE(A2, " ", B2): Combines A2 and B2 with a space.

TODAY & NOW

Return the current date and time.

• =TODAY(): Returns the current date.
• =NOW(): Returns the current date and time.

Best Practices for Excel Formulas

• Avoid quoting numbers: Treat numbers as numbers, not text strings.
• Don't format numbers within formulas: Format the cell's output, not the input value.
• Match parentheses: Ensure proper pairing for complex formulas.
• Copy formulas: Use the fill handle to avoid retyping.
• Preserve values: Copy and paste values to retain results without formulas.
• Automatic Calculation: Ensure "Automatic" calculation is enabled.

This comprehensive overview provides a solid foundation for using basic Excel formulas and functions. Remember to consult the linked resources for more in-depth information on each function.

The above is the detailed content of Basic Excel formulas & functions with examples. For more information, please follow other related articles on the PHP Chinese website!