Important Microsoft Excel Worksheet Functions

Presented below are some useful Microsoft Excel worksheet functions that are constantly used in our Microsoft Excel training classes.

EMAGENIT has been providing Microsoft Excel training and consulting on worksheet functions since 1998. Listed below are some useful Microsoft Excel worksheet functions that we have been repeatedly asked about over the years in our Excel classes. The table below gives a brief description of each function. How to use the worksheet functions in real life situations and the syntax behind writing them in formulas is discussed on their individual pages.

Important Microsoft Excel Worksheet Functions

Important Microsoft Excel Worksheet Functions

Important Microsoft Excel Worksheet Functions

Business, Engineering and Science

Presented below are some useful Microsoft Excel worksheet functions that are constantly used in our Microsoft Excel training classes.

Overview

EMAGENIT has been providing Microsoft Excel training on worksheet functions since 1998.

Listed below are some useful Microsoft Excel worksheet functions that we have been repeatedly asked about over the years in our Excel classes.

The table below gives a brief description of each function. How to use the worksheet functions in real life situations and the syntax behind writing them in formulas is discussed on their individual pages.

Microsoft Excel Worksheet Function List

Function Worksheet Function Description
ADDRESS Function This function returns a cell reference in text form like "A1" based on a specified row and column position that you give it. Great to use in formulas that have to change cell references or range references during calculation due to shifting data.
AND Function This function allows multiple criteria to be evaluated at once. Returns TRUE if all of its logical conditions evaluate as TRUE. Returns FALSE if one of its conditions return FALSE. A key function when developing thinking worksheets (logic).
AVERAGE Function Calculates the average (arithmetic mean) of the values you give it.
CLEAN Function Removes non-printable characters from a piece of text.  Great to use for data cleaning especially in combination with functions that must look up text.
COLUMN Function Returns the column number of a cell or range of cells.
COUNT Function This function counts how many cells have numbers in them. Great for figuring out things like the total number of sales based on cells with numbers in them or how many data points do you have.
COUNTA Function This function counts how many cells have a value in them. Great for determining things like how many sales were made or how many rows are in a table.
COUNTBLANK Function This function counts how many cells have blanks in them. Great for determining things like the number of cells missing information, sales not completed (no values),...etc.
COUNTIF Function Counts the number of cells within a designated range that meet a given condition.
DATE Function This function takes a month, day and year number and converts it into a number representing the Microsoft Excel date-time code.
DATEVALUE Function This function takes a piece of text in the form of a date and converts it into a number representing the Microsoft Excel date-time code.
HLOOKUP Function Searches for a value in the first row of your data and returns a value in the column it finds the value. Great for building pricing tables, looking up values in a table and for merging table information together.
HOUR, MINUTE, SECOND Function The HOUR, MINUTE and SECOND functions are designed to take an Excel serial date/time number and convert it to an hour, minute or second number representation.
IF Function This function is used to make TRUE/FALSE decisions in worksheet formulas and return one of two values as a result. These functions are what let your worksheet think for itself.
INDEX Function Returns a value from a range based on row and column. Just like VLOOKUP but also allows entire columns and rows to be returned from your data. It also can look in any column or row in a table, not just the first.
INDIRECT Function Takes a piece of text you give it and flips that text to a usable cell or range reference. Great for developing formulas that can determine what part of a worksheet range to use like C2:C13 and build that range on the fly during calculation.
INFO Function This function returns information about the current operating system. Useful for displaying the file path or the current calculation state of Microsoft Excel.
IS Functions These functions check errors in formulas and what type of value is typed in a worksheet cell. They return TRUE or FALSE. These functions are commonly nested within the IF function.
LEFT Function Extracts n number of characters from the left-hand side of a piece of text starting with character 1 and moving forward. Great to use for data cleaning or data mining.
LEN Function Counts the number of characters in a piece of text including spaces. Great to use for data cleaning or data mining.
MATCH Function Returns the index of an item (its position) found in a column or row, great for finding headers or names which shift in position in a table. Use with the INDEX function to create a more powerful lookup formula than VLOOKUP alone.
MAX Function This function determines the MAX value out of a group of values.
MID Function Extracts n number of characters from a piece of text at a specified starting character. Great to use for data cleaning or data mining.
MIN Function This function determines the MIN value out of a group of values.
MONTH, DAY and YEAR Function These functions are designed to take an Excel serial date number and convert it to a month, day or year number representation.
OFFSET Function This function returns a cell reference or range reference that is offset from a specified cell. Great for use when finding values dynamically on the worksheet.
OR Function This function allows multiple criteria to be evaluated at once. Returns TRUE if one of its logical conditions evaluate as TRUE. Returns FALSE if all of its logical conditions return FALSE. A key function when developing thinking worksheets (logic).
RIGHT Function Extracts n number of characters from the right-hand side of a piece of text starting at the end character and moving backwards. Great to use for data cleaning or data mining.
ROW Function Returns the row number of a cell or range of cells.
SEARCH Function Returns the position of a set of characters within a piece of text. Good to use with MID, SUBSTITUTE,...etc. Great to use for data cleaning.
SUBSTITUTE Function This function replaces text found in a piece of text. Great to use for data cleaning.
SUBTOTAL Function The SUBTOTAL function allows you to ignore hidden rows or columns and do tasks like count, sum, etc... Great for use with the AUTOFILTER to obtain totals of filtered lists.
SUM Function Adds all the numbers in a range of cells. Great for totaling columns of data.
SUMIF Function Adds the cells specified by a given criteria.
TEXT Function This function formats a specified value as a date, time, currency...etc. It is like using the Number tab on the Format Cell dialog box. Very useful getting the month name or day name from a date.
TIME Function This function takes a hour, minute and second number and converts it into a decimal number representing the Microsoft Excel date-time code.
TIMEVALUE Function This function takes a text value that represents a time and converts it into a decimal number representing the Microsoft Excel date-time code.
TODAY and NOW Function This function returns today's date or date/time as Excel serial date numbers.
TRIM Function Trims the leading and training edge spaces from a piece of text. It also removes extra spacing between words leaving only a single space. Great to use for data cleaning especially in combination with functions that must look up text.
VALUE Function Takes a piece of text that represents a number and flips it to number.
VLOOKUP Function Searches for a value in the first column of your data and returns a value in the same row from another column in your table. Great for building pricing tables, looking up values in a table and for merging table information together.
LinkedIn
Facebook
Twitter
Information
Need Help? Please call us at 1.866.924.6244

Copyright © 2002-2017

EMAGENIT All Rights Reserved