Top basic and advanced Excel formulas and shortcuts
Whether you love or hate using Excel, it is an extremely useful tool in a workplace or even home to easily calculate, analyse, audit and display data in a logical …
Excel Looking up values (VLOOKUP, INDEX, MATCH
VLOOKUP =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. …
Excel Basic Statistics (AVERAGE, MIN, MAX, MEDIAN, MODE)
AVERAGE =AVERAGE(number1, [number2], …) Returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contain numbers MIN =MIN(number1, [number2], …) Returns the …
Excel Logic / Conditional (IF, AND, OR, NOT, ISNA, ISERR, ISBLANK)
IF =IF(logical_test, [value_if_true], [value_if_false]) Checks whether a condition is met, and returns one value if TRUE and another value if FALSE AND =AND(logical1, [logical2], …) Checks whether all arguments are …
Excel Data Cleanup (TRIM, CONCATENATE, LEN, RIGHT, LEFT, MID)
TRIM =TRIM(text) Removes all space from a text string except for single spaces between words CONCATENATE =CONCATENATE(text1, [text2], …) Joins several text strings into one text string LEN =LEN(text) Returns …
Excel calculations on a filtered list (SUBTOTAL)
SUBTOTAL =SUBTOTAL(function_num, ref1, [ref2], …) Returns a subtotal in a list or database SUBTOTAL
Excel Rounding (ROUND, ROUNDUP, ROUNDDOWN)
ROUND =ROUND(number, num_digits) Rounds the number to a specified number of digits ROUNDUP =ROUNDUP(number, num_digits) Rounds a number up, away from zero ROUNDDOWN =ROUNDDOWN(number, num_digits) Rounds a number down, toward …
Excel Date Conversion (EOMONTH, DAY, MONTH, YEAR, DATE)
EOMONTH =EOMONTH(start_date, months) Returns the serial number of the last day of the month before or after a specified number of months and start of month DAY =DAY(serial_number) Returns the …
Excel Date Calculation (DAYS, NETWORKDAYS, NOW, TODAY)
DAYS =DAYS(end_date, start_date) Returns the number of days between two dates NETWORKDAYS =NETWORKDAYS(start_date, end_date, [holidays]) Returns the number of whole workdays between two dates NOW =NOW() Returns the current date …
Counting Cells (COUNT, COUNTIF, COUNTIFS)
COUNT =COUNT(value1, [value2], …) Counts the number of cells in a range that contains numbers COUNTIF =COUNTIF(range, criteria) Counts the number of cells within a range that meet the given …