The Ultimate Guide to 100+ Excel Formulas to Skyrocket Your Productivity

The Ultimate Guide to 100 Excel Formulas

Boost your Excel skills and productivity with these 100 essential formulas, each explained simply to help you work smarter and faster.

Basic Math Functions

• SUM – Adds a range of numbers.
• AVERAGE – Calculates the mean of a group of numbers.
• MIN – Finds the smallest number.
• MAX – Finds the largest number.
• PRODUCT – Multiplies a group of numbers.
• QUOTIENT – Returns the integer part of a division.
• MOD – Returns the remainder of a division.
• ROUND – Rounds a number to a specified number of digits.
• ROUNDUP – Rounds a number up, away from zero.
• ROUNDDOWN – Rounds a number down, toward zero.

Logical Functions

• IF – Returns one value if a condition is true and another if false.
• AND – Returns TRUE if all conditions are met.
• OR – Returns TRUE if at least one condition is met.
• NOT – Reverses the value of a logical argument.
• IFERROR – Returns a custom value if a formula causes an error.
• IFNA – Returns a custom value if a formula results in #N/A.
• SWITCH – Evaluates an expression against a list of values.
• CHOOSE – Selects a value from a list based on position.

Text Functions

• TEXT – Formats a number and converts it to text.
• CONCAT – Combines multiple values into one text string.
• TEXTJOIN – Joins text items using a delimiter.
• LEFT – Extracts characters from the start of text.
• RIGHT – Extracts characters from the end of text.
• MID – Extracts characters from the middle of text.
• LEN – Returns the length of a text string.
• TRIM – Removes extra spaces from text.
• UPPER – Converts text to uppercase.
• LOWER – Converts text to lowercase.
• PROPER – Capitalizes the first letter of each word.
• REPT – Repeats text a specified number of times.
• SUBSTITUTE – Replaces old text with new text.
• REPLACE – Replaces part of a text string.
• FIND – Finds the position of one text string within another.
• SEARCH – Like FIND, but case-insensitive.

Lookup & Reference Functions

• VLOOKUP – Searches for a value in the first column of a range.
• HLOOKUP – Searches for a value in the first row of a range.
• INDEX – Returns the value at a specific row and column.
• MATCH – Returns the relative position of an item.
• XLOOKUP – Searches a range and returns a matching value.
• LOOKUP – Searches for a value and returns a result from a range.
• OFFSET – Returns a reference offset from a starting cell.
• INDIRECT – Returns a cell reference from a text string.
• ROW – Returns the row number of a cell.
• COLUMN – Returns the column number of a cell.

Date & Time Functions

• TODAY – Returns the current date.
• NOW – Returns the current date and time.
• DATE – Creates a date from year, month, and day values.
• TIME – Creates a time from hour, minute, and second values.
• DAY – Returns the day of the month.
• MONTH – Returns the month number.
• YEAR – Returns the year.
• HOUR – Returns the hour of a time.
• MINUTE – Returns the minute of a time.
• SECOND – Returns the second of a time.
• WEEKDAY – Returns the day of the week.
• WEEKNUM – Returns the week number in the year.
• EDATE – Returns a date after adding months.
• EOMONTH – Returns the last day of a month.
• DATEDIF – Calculates the difference between two dates.
• NETWORKDAYS – Counts working days between dates.
• WORKDAY – Returns a workday date offset by a number of days.

Statistical Functions

• COUNT – Counts numeric values in a range.
• COUNTA – Counts non-empty cells.
• COUNTBLANK – Counts empty cells.
• COUNTIF – Counts cells meeting a condition.
• COUNTIFS – Counts cells meeting multiple conditions.
• AVERAGEIF – Calculates average for cells meeting a condition.
• AVERAGEIFS – Calculates average for cells meeting multiple conditions.
• MAXIFS – Returns max value that meets conditions.
• MINIFS – Returns min value that meets conditions.
• STDEV.S – Estimates sample standard deviation.
• STDEV.P – Calculates population standard deviation.
• VAR.S – Estimates sample variance.
• VAR.P – Calculates population variance.

Financial Functions

• PMT – Calculates loan payment amount.
• FV – Calculates future value of an investment.
• PV – Calculates present value of an investment.
• RATE – Calculates interest rate per period.
• NPER – Returns number of periods for an investment.
• NPV – Calculates net present value.
• IRR – Calculates internal rate of return.
• SLN – Calculates straight-line depreciation.
• SYD – Calculates sum-of-years digits depreciation.
• DB – Calculates declining balance depreciation.

Engineering and Math Functions

• ABS – Returns the absolute value of a number.
• INT – Rounds a number down to nearest integer.
• TRUNC – Truncates a number by removing decimals.
• CEILING – Rounds number up to nearest multiple.
• FLOOR – Rounds number down to nearest multiple.
• POWER – Raises a number to a power.
• SQRT – Returns the square root of a number.
• EXP – Returns e raised to a power.
• LN – Returns the natural logarithm of a number.
• LOG – Returns logarithm with a given base.
• RANDBETWEEN – Returns a random integer between two numbers.
• RAND – Returns a random decimal between 0 and 1.

Array & Dynamic Functions

• UNIQUE – Returns unique values from a range.
• FILTER – Filters data based on criteria.
• SORT – Sorts data ascending or descending.
• SEQUENCE – Generates a list of sequential numbers.

Master these 100 Excel formulas to increase your productivity and data management skills. Bookmark this guide for easy reference as you grow your Excel expertise.

Taimoor Butt