Excel Tips

Below are some of my favorite functions and keyboard shortcuts in Excel, A-Z. (I like Excel a lot, so there are many!) Browse below or (fittingly) download them in an Excel workbook complete with an interactive box to generate a random tip.

Tip
ABS – Returns the absolute value of a number
ADDRESS – Returns a reference as text to a single cell in a worksheet
Alt+= – AutoSum creates sum formula instantly
Alt+1 – Shortcut for the first button in the Quick Access Toolbar
Alt+AC – Clear all Filters on the sheet or Table
Alt+Down Arrow – Make the Filter drop-down menu appear, or make a validation list appear.
Alt+Down Arrow+E – Jump to the Search box in the Filter drop-down menu
Alt+F11 – Open VBA Editor Window
Alt+F5 – Refresh Pivot Table
Alt+HFS – Font Size drop-down
Alt+HOI – AutoFit Column Width
Alt+JTA – Rename Table
Alt+WFF – Apply or Remove Freeze Panes
AND – Returns TRUE if all its arguments are TRUE
Arrow Keys – Select cells
AVEDEV – Returns the average of the absolute deviations of data points from their mean
AVERAGE – Returns the average of its arguments
AVERAGEA – Returns the average of its arguments and includes evaluation of text and logical values
AVERAGEIF – Returns the average for the cells specified by a given criterion
AVERAGEIFS – Returns the average for the cells specified by multiple criteria
BETADIST – Returns the cumulative beta probability density function
BETAINV – Returns the inverse of the cumulative beta probability density function
BINOMDIST – Returns the individual term binomial distribution probability
CEILING – Rounds a number to the nearest integer or to the nearest multiple of significance
CELL – Returns information about the formatting, location, or contents of a cell
CHAR – Returns the character specified by the code number
CHIDIST – Returns the one-tailed probability of the chi-squared distribution
CHIINV – Returns the inverse of the one-tailed probability of the chi-squared distribution
CHITEST – Returns the test for independence
CHOOSE – Chooses a value from a list of values
CLEAN – Removes all nonprintable characters from text
CODE – Returns a numeric code for the first character in a text string
COLUMN – Returns the column number of a reference
COLUMNS – Returns the number of columns in a reference
COMBIN – Returns the number of combinations for a given number of objects
CONCATENATE – Joins several text items into one text item
CONFIDENCE – Returns the confidence interval for a population mean
CORREL – Returns the correlation coefficient between two data sets
COS – Returns the cosine of a number
COSH – Returns the hyperbolic cosine of a number
COUNT – Counts how many numbers are in the list of arguments
COUNTA – Counts how many values are in the list of arguments
COUNTBLANK – Counts the number of blank cells in the argument range
COUNTIF – Counts the number of cells that meet the criteria you specify in the argument
COUNTIFS – Counts the number of cells that meet multiple criteria
COVAR – Returns covariance, the average of the products of paired deviations
CRITBINOM – Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
Ctrl+-(minus key) – Delete column(s) or row(s) when entire column/row is selected.
Ctrl++(plus key) – Insert column(s) or row(s) when entire column/row is selected.
Ctrl+1 – Open Format Cells window
Ctrl+A – Select current range, entire table, entire pivot table, etc.
Ctrl+Arrow Keys – Selects the last used cell in the current range
Ctrl+C – Copy
Ctrl+D – Copy formula or value across to all selected columns.
Ctrl+End – Selects the last used cell in the worksheet
Ctrl+F – Open the Find Window
Ctrl+F3 – Open the Name Manager
Ctrl+H – Open the Find and Replace Window
Ctrl+Home – Selects the first cell in the worksheet
Ctrl+N – New Workbook
Ctrl+O – Open Workbook
Ctrl+Page Up/Down – Selects the previous or next sheet in the workbook
Ctrl+R – Copy formula or value down to all selected rows.
Ctrl+S – Save
Ctrl+Shift+5 – Apply percentage format to selected cells.
Ctrl+Shift+Arrow Keys – Selects the range of cells from the current selection to last used cell in the range
Ctrl+Shift+End – Select range from current cell to last used cell
Ctrl+Shift+Home – Select range of cells from current cell to top-left corner
Ctrl+Shift+L – Apply or Remove Filters
Ctrl+Shift+V – Paste Values
Ctrl+Space Bar – Selects the entire column
Ctrl+T – Create an Excel Table
Ctrl+V – Paste
Ctrl+Z – Undo
DATE – Returns the serial number of a particular date
DATEVALUE – Converts a date in the form of text to a serial number
DAY – Converts a serial number to a day of the month
DAYS360 – Calculates the number of days between two dates, based on a 360-day year
DEGREES – Converts radians to degrees
DEVSQ – Returns the sum of squares of deviations
DOLLAR – Converts a number to text, using currency format
EDATE – Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH – Returns the serial number of the last day of the month before or after a specified number of months
Escape – Exit Formula Edit mode without saving changes
EVEN – Rounds a number up to the nearest even integer
EXACT – Checks to see whether two text values are identical
EXP – Returns e raised to the power of a given number
EXPONDIST – Returns the exponential distribution
F12 – Save As
F2 – Open Formula Bar for in-cell formula editing
F4 – Repeats the last action
F4 in Formula Edit mode – Anchor cell references in a formula
F5 or Ctrl+G – Open Go To Window
FACT – Returns the factorial of a number
FALSE – Returns the logical value FALSE
FDIST – Returns the F probability distribution
FIND – Finds one text value within another (case sensitive)
FINV – Returns the inverse of the F probability distribution
FISHER – Returns the Fisher transformation
FISHERINV – Returns the inverse of the Fisher transformation
FIXED – Formats a number as text with a fixed number of decimals
FLOOR – Rounds a number down, toward 0
FORECAST – Returns a value along a linear trend
FREQUENCY – Returns a frequency distribution as a vertical array
FTEST – Returns the result of an F-Test
FV – Returns the future value of an investment
GAMMADIST – Returns the gamma distribution
GAMMAINV – Returns the inverse of the gamma cumulative distribution
GAMMALN – Returns the natural logarithm of the gamma function, G(x)
GCD – Returns the greatest common divisor
GEOMEAN – Returns the geometric mean
GETPIVOTDATA – Returns data stored in a PivotTable
GROWTH – Returns values along an exponential trend
HARMEAN – Returns the harmonic mean
HLOOKUP – Searches for a value in the top column of a table and then returns a value in the same column from a row you specify in the table
HOUR – Converts a serial number to an hour
HYPERLINK – Creates a shortcut that opens a document on your hard drive, a server, or the Internet
HYPGEOMDIST – Returns the hypergeometric distribution
IF – Specifies a logical test to perform
IFERROR – Returns a different result if the first argument evaluates to an error
INDEX – Uses an index to choose a value from a reference or array
INDIRECT – Returns a reference indicated by a text value
INT – Rounds a number down to the nearest integer
INTERCEPT – Returns the intercept of the linear regression line
INTRATE – Returns the interest rate for a fully invested security
IPMT – Returns the interest payment for an investment for a given period
IRR – Returns the internal rate of return for a series of cash flows
ISBLANK – Returns TRUE if the value is blank
ISERR – Returns TRUE if the value is any error value except #N/A
ISERROR – Returns TRUE if the value is any error value
ISEVEN – Returns TRUE if the number is even
ISLOGICAL – Returns TRUE if the value is a logical value
ISNA – Returns TRUE if the value is the #N/A error value
ISNONTEXT – Returns TRUE if the value is not text
ISNUMBER – Returns TRUE if the value is a number
ISODD – Returns TRUE if the number is odd
ISPMT – Returns the interest associated with a specific loan payment
ISREF – Returns TRUE if the value is a reference
ISTEXT – Returns TRUE if the value is text
KURT – Returns the kurtosis of a data set
LARGE – Returns the kth largest value in a data set
LEFT – Returns the leftmost characters from a text value
LEN – Returns the number of characters in a text string
LINEST – Returns the parameters of a linear trend
LN – Returns the natural logarithm of a number
LOG – Returns the logarithm of a number to a specified base
LOG10 – Returns the base-10 logarithm of a number
LOGEST – Returns the parameters of an exponential trend
LOGINV – Returns the inverse of the lognormal distribution
LOGNORMDIST – Returns the cumulative lognormal distribution
LOOKUP – Returns a value either from a one-row or one-column range or from an array
LOWER – Converts text to lowercase
MATCH – Returns the relative position of an item in an array
MAX – Returns the maximum value in a list of arguments, ignoring logical values and text
MAXA – Returns the maximum value in a list of arguments, including logical values and text
MDURATION – Returns the Macauley modified duration for a security with an assumed par value of $100
MEDIAN – Returns the median of the given numbers
MID – Returns a specific number of characters from a text string, starting at the position you specify
MIN – Returns the minimum value in a list of arguments, ignoring logical values and text
MINA – Returns the minimum value in a list of arguments, including logical values and text
MINUTE – Converts a serial number to a minute
MIRR – Returns the internal rate of return where positive and negative cash flows are financed at different rates
MOD – Returns the remainder from division (modulo)
MODE – Returns the most common value in a data set
MONTH – Converts a serial number to a month
MROUND – Returns a number rounded to the desired multiple
MULTINOMIAL – Returns the multinomial of a set of numbers
N – Returns a value converted to a number
NA – Returns the error value #N/A
NEGBINOMDIST – Returns the negative binomial distribution
NETWORKDAYS – Returns the number of whole workdays between two dates
NOMINAL – Returns the annual nominal interest rate
NORMDIST – Returns the normal cumulative distribution
NORMINV – Returns the inverse of the normal cumulative distribution
NORMSDIST – Returns the standard normal cumulative distribution
NORMSINV – Returns the inverse of the standard normal cumulative distribution
NOT – Reverses the logic of its argument
NOW – Returns the serial number of the current date and time
NPER – Returns the number of periods for an investment
NPV – Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
ODD – Rounds a number up to the nearest odd integer
OFFSET – Returns a reference offset from a given reference
OR – Returns TRUE if any argument is TRUE
PEARSON – Returns the Pearson product moment correlation coefficient
PERCENTILE – Returns the kth percentile of values in a range
PERCENTRANK – Returns the percentage rank of a value in a data set
PI – Returns the value of pi
PMT – Returns the periodic payment for an annuity
POWER – Returns the result of a number raised to a power
PPMT – Returns the payment on the principal for an investment for a given period
PRODUCT – Multiplies its arguments
PROPER – Capitalizes the first letter in each word of a text value
PV – Returns the present value of an investment
QUARTILE – Returns the quartile of a data set
QUOTIENT – Returns the integer portion of a division
RADIANS – Converts degrees to radians
RAND – Returns a random number between 0 and 1
RANDBETWEEN – Returns a random number between the numbers that you specify
RANK – Returns the rank of a number in a list of numbers
RATE – Returns the interest rate per period of an annuity
REPLACE – Replaces characters within text
REPT – Repeats text a given number of times
RIGHT – Returns the rightmost characters from a text value
ROMAN – Converts an Arabic numeral to Roman, as text
ROUND – Rounds a number to a specified number of digits
ROUNDDOWN – Rounds a number down, toward 0
ROUNDUP – Rounds a number up, away from 0
ROW – Returns the row number of a reference
ROWS – Returns the number of rows in a reference
SEARCH – Finds one text value within another (not case sensitive)
SECOND – Converts a serial number to a second
SERIESSUM – Returns the sum of a power series based on the formula
Shift+Alt+Left Arrow – Ungroup rows or columns
Shift+Alt+Right Arrow – Group rows or columns
Shift+F11  – Insert new worksheet
Shift+F2 – Insert Comment
Shift+Space Bar – Selects the entire row
SIGN – Returns the sign of a number
SIN – Returns the sine of the given angle
SKEW – Returns the skewness of a distribution
SLOPE – Returns the slope of the linear regression line
SMALL – Returns the kth smallest value in a data set
SQRT – Returns a positive square root
SQRTPI – Returns the square root of pi
STANDARDIZE – Returns a normalized value
STDEV – Estimates standard deviation based on a sample, ignoring text and logical values
STDEVA – Estimates standard deviation based on a sample, including text and logical values
STDEVP – Calculates standard deviation based on the entire population, ignoring text and logical values
STDEVPA – Calculates standard deviation based on the entire population, including text and logical values
STEYX – Returns the standard error of the predicted y-value for each x in the regression
SUBSTITUTE – Substitutes new text for old text in a text string
SUBTOTAL – Returns a subtotal in a list or database
SUM – Adds its arguments
SUMIF – Adds the cells specified by a given criteria
SUMIFS – Adds the cells specified by a multiple criteria
SUMPRODUCT – Returns the sum of the products of corresponding array components
SUMSQ – Returns the sum of the squares of the arguments
T – Returns the text referred to by value
TDIST – Returns the student’s t-distribution
TEXT – Formats a number and converts it to text
TIME – Returns the serial number of a particular time
TIMEVALUE – Converts a time in the form of text to a serial number
TODAY – Returns the serial number of today’s date
TRANSPOSE – Returns the transpose of an array
TREND – Returns values along a linear trend
TRIM – Removes excess spaces from text
TRIMMEAN – Returns the mean of the interior of a data set
TRUE – Returns the logical value TRUE
TRUNC – Truncates a number (you specify the precision of the truncation)
TTEST – Returns the probability associated with a student’s t-Test
TYPE – Returns a number indicating the data type of a value
UPPER – Converts text to uppercase
VALUE – Converts a text argument to a number
VAR – Estimates variance based on a sample, ignoring logical values and text
VARA – Estimates variance based on a sample, including logical values and text
VARP – Calculates variance based on the entire population, ignoring logical values and text
VARPA – Calculates variance based on the entire population, including logical values and text
VLOOKUP – Searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table
WEEKDAY – Converts a serial number to a day of the week
WEEKNUM – Returns the week number in the year
WORKDAY – Returns the serial number of the date before or after a specified number of workdays
XIRR – Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
XNPV – Returns the net present value for a schedule of cash flows that is not necessarily periodic
YEAR – Converts a serial number to a year
YEARFRAC – Returns the year fraction representing the number of whole days between start_date and end_date
YIELD – Returns the yield on a security that pays periodic interest
ZTEST – Returns the two-tailed P-value of a z-test