Swanand: Here is a cheat sheet of common Excel formulas with examples

Saturday, 14 October 2023

Here is a cheat sheet of common Excel formulas with examples:

Mathematical formulas

SUM: Adds a range of cells. Example: =SUM(A1:A10)
AVERAGE: Calculates the average of a range of cells. Example: =AVERAGE(A1:A10)
COUNT: Counts the number of cells in a range that contain numbers. Example: =COUNT(A1:A10)
MAX: Returns the largest value in a range of cells. Example: =MAX(A1:A10)
MIN: Returns the smallest value in a range of cells. Example: =MIN(A1:A10)

Logical formulas

IF: Returns a value if a condition is met, otherwise returns a different value. Example: =IF(A1>100, "Large", "Small")
AND: Returns TRUE if all conditions are met, otherwise returns FALSE. Example: =AND(A1>100, B1>50)
OR: Returns TRUE if any condition is met, otherwise returns FALSE. Example: =OR(A1>100, B1>50)
NOT: Reverses the truth value of a condition. Example: =NOT(A1>100)

Date and time formulas

TODAY: Returns the current date. Example: =TODAY()
NOW: Returns the current date and time. Example: =NOW()
DATE: Returns a date value from a specified year, month, and day. Example: =DATE(2023,10,14)
TIME: Returns a time value from a specified hour, minute, and second. Example: =TIME(12,00,00)

Other useful formulas

VLOOKUP: Looks up a value in a table and returns the corresponding value in another column. Example: =VLOOKUP(A1,B2:C10,2,FALSE)
COUNTIF: Counts the number of cells in a range that meet a specified condition. Example: =COUNTIF(A1:A10,">100")
SUMIF: Adds the values in a range of cells that meet a specified condition. Example: =SUMIF(A1:A10,">100",B1:B10)
CONCATENATE: Combines two or more text strings into one. Example: =CONCATENATE("Hello", " ", A1)

These are just a few of the many Excel formulas available. For a more comprehensive list, please see the Microsoft support website.

Swanand