Excel to Python Guide

Filter

Filter data based on certain criteria

Pivot Table

Group and summarize data

Sort

Sort data based on one or more columns

ABS

Find the absolute value of a number

AVERAGE

Calculate the mean value of a set of numbers

CEIL

Round a number up to the nearest integer

COUNT

Count number of cells that contain numbers

COUNTIF

Count the number of cells that match a condition

EXP, POWER

Calculate the e raised to the power of a given number

MAX

Find the maximum value in a set of numbers

MEDIAN

Calculate the median value of a set of numbers

MIN

Find the minimum value in a set of numbers

MOD

Calculate the remainder after a division

MODE

Calculate the mode value of a set of numbers

ROUND

Round a number to a specified number of decimals

SIGN

Determine whether numbers are positive, negative, or zero

SLOPE

Calculate the slope of a linear relationship between two variables

SQRT

Compute the non-negative square root of a number

SUM

Calculates the sum of numbers

SUMIF

Sum the values of numeric cells that match a condition

SUMPRODUCT

Calculates the sum of the products of corresponding numbers

VAR

Perform various operations on data sets

CLEAN

Remove non-printable characters from text

CONCAT

Join text together

FIND

Find the position of a substring in a string

LEFT

Extract characters from the left side of a string

LEN

Calculate the length of a string

LOWER

Converts a text string to lowercase.

MID

Extract a number of characters from the middle of a string

REPLACE, SUBSITUTE

Replaces characters in a string by location.

RIGHT

Extract characters from the right side of a string

TRIM

Remove whitespace from the start and end of string

UPPER

Convert all characters in a string to uppercase

DAY

Extract the day from a datetime

HOUR

Extract the hour from a datetime

MINUTE

Extract the minute from a datetime

MONTH

Extract the month from a datetime

SECOND

Extract the second from a datetime

TODAY

Retrieve the current date without the time component

YEAR

Extract the year from a datetime

IF

Use a conditional check to return one of two values.

IFERROR

Returns a value if the formula evaluates to an error

ISNA

Detect if a value is missing

HLOOKUP

Search for a value in the first row and return a value in the same column from a specified row

INDEX MATCH

Lookup a value in a table based on a given criteria and return the corresponding value from another column

VLOOKUP

Search for a value in one column and return a corresponding value from a different column

COUPNCD

Finds the next coupon date after the settlement date of a security

COUPNUM

Calculate the number of coupons payable between the settlement date and the maturity date of a security

IRR

Calculate the internal rate of return for a series of cash flows

NPV

Calculate the net present value of a series of cash flows

PRICE

Calculate the present value or price of a security based on its coupon payments, yield, and other parameters

OFFSET

Shift the placement of data within a dataframe

RANK

Returns the rank of a number in a list of numbers.

REVERSE

Reverse the order of data in pandas