TechNikhil

Excel Cheat Sheet For Data Analysis

Mathematical Formulas: Statistical Analysis Formulas:
SUM: STDEV:
Definition: Adds up a range of numbers. Definition: Calculates the standard deviation of a sample.
Example: =SUM(A1:A10) Example: =STDEV(D2:D20)
Explanation: This formula calculates the sum of values in cells A1 through A10. Explanation: This formula computes the standard deviation of values in cells D2
AVERAGE: through D20.
Definition: Calculates the mean (average) of a range of numbers. VAR:
Example: =AVERAGE(B2:B20) Definition: Calculates the variance of a sample.
Explanation: This formula computes the average of values in cells B2 through Example: =VAR(E3:E15)
B20. Explanation: This formula calculates the variance of values in cells E3 through
COUNT: E15.
Definition: Counts the number of cells that contain numeric values in a range. CORREL:
Example: =COUNT(C1:C100) Definition: Calculates the correlation coefficient between two ranges of values.
Explanation: This formula counts the cells in the range C1 to C100 that contain Example: =CORREL(F2:F20, G2:G20)
numeric values. Explanation: This formula gives the correlation between values in columns F and
MAX: G.
Definition: Returns the maximum value in a range. COUNTIF:
Example: =MAX(D2:D15) Definition: Counts cells based on a single criterion.
Explanation: This formula finds the maximum value in the range D2 to D15. Example: =COUNTIF(H2:H50, “>50”)
MIN: Explanation: This formula counts cells in the range H2 to H50 that are greater
Definition: Returns the minimum value in a range. than 50.
Example: =MIN(E5:E20) COUNTIFS:
Explanation: This formula finds the minimum value in the range E5 to E20. Definition: Counts cells based on multiple criteria.
PRODUCT: Example: =COUNTIFS(A2:A100, “Category1”, B2:B100, “>100”)
Definition: Multiplies numbers in a range. Explanation: This formula counts cells in column A based on criteria in columns
Example: =PRODUCT(F2:F10) B and C.
Explanation: This formula multiplies values in the range F2 to F10.
ROUND:
Definition: Rounds a number to a specified number of digits. Logical Formulas:
Example: =ROUND(G3, 2)
Explanation: This formula rounds the value in cell G3 to two decimal places. IF:
SUMIFS: Definition: Performs conditional logic.
Definition: Adds up numbers based on multiple criteria. Example: =IF(A1>10, “Greater”, “Less or Equal”)
Example: =SUMIFS(A2:A100, B2:B100, “Category1”, C2:C100, “>100”) Explanation: This formula checks if the value in cell A1 is greater than 10 and
Explanation: This formula sums values in column A based on criteria in columns returns “Greater” if true, otherwise “Less or Equal”.
B and C. AND:
Definition: Returns TRUE if all arguments are true, and FALSE if any argument is

Text Functions:
false.
Example: =AND(A2>10, B2<20)
Explanation: This formula returns TRUE if both conditions in cells A2 and B2 are
true.
CONCATENATE:
OR:
Definition: Combines two or more strings into one.
Definition: Returns TRUE if any argument is true.
Example: =CONCATENATE(“First”, ” “, “Last”)
Example: =OR(C2=”Category1″, C2=”Category2″)
Explanation: This formula concatenates the strings “First”, a space, and “Last”
Explanation: This formula returns TRUE if the value in cell C2 is either
into a single text string.
“Category1” or “Category2”.
LEFT:
NOT:
Definition: Extracts a specified number of characters from the beginning of a
Definition: Returns TRUE if the argument is false and vice versa.
text string.
Example: =NOT(D2=”Incomplete”)
Example: =LEFT(E2, 3)
Explanation: This formula returns TRUE if the value in cell D2 is not equal to
Explanation: This formula extracts the first three characters from the text in cell
“Incomplete”.
E2.
RIGHT:
Definition: Extracts a specified number of characters from the end of a text
string.
Date and Time Functions:
Example: =RIGHT(F3, 4) TODAY:
Explanation: This formula extracts the last four characters from the text in cell Definition: Returns the current date.
F3. Example: =TODAY()
MID: Explanation: This formula returns the current date.
Definition: Extracts a specified number of characters from a text string, starting NOW:
at a specified position. Definition: Returns the current date and time.
Example: =MID(G4, 2, 3) Example: =NOW()
Explanation: This formula extracts three characters from the text in cell G4, Explanation: This formula returns the current date and time.
starting at the second character. DATEDIF:
LEN: Definition: Calculates the difference between two dates in years, months, or
Definition: Returns the number of characters in a text string. days.
Example: =LEN(H5) Example: =DATEDIF(L2, M2, “d”)
Explanation: This formula returns the number of characters in the text string in Explanation: This formula calculates the number of days between the dates in
cell H5. cells L2 and M2.
PROPER: EOMONTH:
Definition: Converts text to proper case (capitalizes the first letter of each Definition: Returns the last day of the month, a specified number of months
word). before or after a given date.
Example: =PROPER(I6) Example: =EOMONTH(N3, 2)
Explanation: This formula converts the text in cell I6 to proper case. Explanation: This formula returns the last day of the month that is two months
TRIM: after the date in cell N3.
Definition: Removes leading and trailing spaces from a text string.
Example: =TRIM(J7)
Explanation: This formula removes extra spaces from the text in cell J7. Lookup and Reference Formulas:
SUBSTITUTE:
Definition: Replaces occurrences of a specified substring with another VLOOKUP:
substring. Definition: Searches for a value in the first column of a table and returns a value
Example: =SUBSTITUTE(K8, “old”, “new”) in the same row from another column.
Explanation: This formula replaces all occurrences of “old” with “new” in the Example: =VLOOKUP(O2, P2:Q100, 2, FALSE)
text in cell K8. Explanation: This formula looks for the value in cell O2 in the first column of the
range P2:Q100 and returns the corresponding value from the second column.

Array Formulas: HLOOKUP:

Definition: Searches for a value in the first row of a table and returns a value in
the same column from another row.
SUMPRODUCT: Example: =HLOOKUP(R2, S1:U10, 3, FALSE)
Definition: Multiplies corresponding components in the given arrays and returns Explanation: This formula looks for the value in cell R2 in the first row of the
the sum of those products. range S1:U10 and returns the corresponding value from the third row.
Example: =SUMPRODUCT(X2:X10, Y2:Y10) INDEX:
Explanation: This formula multiplies each pair of values in the ranges X2:X10 and Definition: Returns the value of a cell in a specified row and column of a range.
Y2:Y10 and then sums up the products. Example: =INDEX(V2:Z10, 3, 4)
Array Formulas with Ctrl+Shift+Enter: Explanation: This formula returns the value in the cell at the intersection of the
Definition: Perform operations on arrays. third row and fourth column in the range V2:Z10.
Example: {=SUM(A1:A10*B1:B10)} MATCH:
Explanation: Array formulas are entered by pressing Ctrl+Shift+Enter. They can Definition: Searches for a specified value in a range and returns the relative
perform calculations on entire ranges of cells at once. position of that item.
Example: =MATCH(A2, A2:A100, 0)
Explanation: This formula searches for the value in cell A2 within the range

Information Functions: A2:A100 and returns its relative position.

INDIRECT:
Definition: Returns the reference specified by a text string.
ISNUMBER: Example: =INDIRECT(“Sheet2!A1”)
Definition: Checks if a value is a number. Explanation: This formula indirectly refers to the cell A1 in Sheet2.
Example: =ISNUMBER(A1)

Financial Functions:
Explanation: This formula returns TRUE if the value in cell A1 is a number;
otherwise, it returns FALSE.
ISTEXT:
Definition: Checks if a value is text. PV:
Example: =ISTEXT(B2) Definition: Calculates the present value of an investment.
Explanation: This formula returns TRUE if the value in cell B2 is text; otherwise, it Example: =PV(0.05, 10, 1000)
returns FALSE. Explanation: This formula calculates the present value of a $1,000 investment
ISBLANK: over 10 years with a 5% interest rate.
Definition: Checks if a cell is empty. FV:
Example: =ISBLANK(C3) Definition: Calculates the future value of an investment.
Explanation: This formula returns TRUE if the cell in C3 is empty; otherwise, it Example: =FV(0.03, 5, -500, 0, 1)
returns FALSE. Explanation: This formula calculates the future value of a $500 investment
IFERROR: with a 3% interest rate over 5 years.
Definition: Returns a value if a formula results in an error; otherwise, returns the

Database Functions:
result.
Example: =IFERROR(D2/E2, “Error in division”)
Explanation: This formula calculates D2/E2, and if an error occurs, it returns the
specified error message. DSUM:
Definition: Adds the numbers in a column of a list or database that meet
multiple criteria.
Example: =DSUM(A1:C100, “Sales”, D1:E2)
Explanation: This formula adds up the “Sales” column in the range A1:C100 that
meets the criteria specified in the range D1:E2.
DGET:
Definition: Extracts a single value from a list or database that matches the
specified conditions.
Example: =DGET(A1:C100, “Sales”, D1:E2)
Explanation: This formula retrieves a single value from the “Sales” column in
the range A1:C100 that meets the criteria specified in the range D1:E2.

Excel Cheat Sheet For Data

Analysis