What are some common Excel formulas and how do you use them?

Posted by:

Kalpataru

|

On:

|

,

Here are some common Excel formulas and how to use them:

1. SUM

  • Purpose: Adds up a range of cells.
  • Formula: =SUM(A1:A10)
  • Usage: Adds all the numbers in cells A1 through A10.

2. AVERAGE

  • Purpose: Calculates the average of a range of cells.
  • Formula: =AVERAGE(A1:A10)
  • Usage: Finds the average value of the numbers in cells A1 through A10.

3. COUNT

  • Purpose: Counts the number of cells that contain numbers.
  • Formula: =COUNT(A1:A10)
  • Usage: Counts how many cells in the range A1 through A10 contain numeric data.

4. COUNTA

  • Purpose: Counts the number of non-empty cells.
  • Formula: =COUNTA(A1:A10)
  • Usage: Counts how many cells in the range A1 through A10 contain any data (numbers, text, etc.).

5. IF

  • Purpose: Returns one value if a condition is true and another if it is false.
  • Formula: =IF(A1>10, "Yes", "No")
  • Usage: Checks if the value in A1 is greater than 10. If true, it returns “Yes”; otherwise, it returns “No.”

6. VLOOKUP

  • Purpose: Looks for a value in the first column of a range and returns a value in the same row from another column.
  • Formula: =VLOOKUP(B1, A1:C10, 3, FALSE)
  • Usage: Searches for the value in B1 within the range A1and returns the value from the third column of the range.

7. HLOOKUP

  • Purpose: Similar to VLOOKUP but searches for the value in the first row and returns a value in the same column from another row.
  • Formula: =HLOOKUP(B1, A1:Z3, 2, FALSE)
  • Usage: Searches for the value in B1 within the range A1and returns the value from the second row of the range.

8. INDEX and MATCH

  • Purpose: A more flexible alternative to VLOOKUP/HLOOKUP. INDEX returns the value of a cell in a given range based on row and column numbers, and MATCH returns the position of a cell in a row or column.
  • Formula: =INDEX(A1:A10, MATCH(B1, B1:B10, 0))
  • Usage: Looks up the value in B1 within the range B1and returns the corresponding value from the range A1.

9. CONCATENATE (or CONCAT)

  • Purpose: Combines multiple text strings into one.
  • Formula: =CONCATENATE(A1, " ", B1)
  • Usage: Combines the text in cells A1 and B1 with a space in between.

10. TEXT

  • Purpose: Formats a number and converts it to text.
  • Formula: =TEXT(A1, "0.00")
  • Usage: Formats the number in A1 to two decimal places and converts it to text.

11. LEFT, RIGHT, MID

  • Purpose: Extracts a specified number of characters from a text string.
  • Formulas:
    • =LEFT(A1, 5) (Extracts the first 5 characters from A1)
    • =RIGHT(A1, 3) (Extracts the last 3 characters from A1)
    • =MID(A1, 2, 4) (Extracts 4 characters starting from the second character in A1)

12. TRIM

  • Purpose: Removes extra spaces from text except for single spaces between words.
  • Formula: =TRIM(A1)
  • Usage: Cleans up the text in A1 by removing extra spaces.

13. LEN

  • Purpose: Returns the number of characters in a text string.
  • Formula: =LEN(A1)
  • Usage: Counts the number of characters in the text in cell A1.

14. NOW

  • Purpose: Returns the current date and time.
  • Formula: =NOW()
  • Usage: Displays the current date and time.

15. TODAY

  • Purpose: Returns the current date.
  • Formula: =TODAY()
  • Usage: Displays today’s date.

These formulas are the foundation of many tasks in Excel, from basic arithmetic to complex data manipulation.

Posted by

Kalpataru

in

,