Home > Formulas > Topics

Excel Formulas and Functions

Use Excel formulas and functions to summarize data, return values from a lookup table, do mathmatical calculations, and much more!

Author: Debra Dalgleish

Excel Function Tutorials

These tutorials show how to use some of the most popular Excel functions.

To see full list of Excel functions, visit the Excel Functions List page.

1 -- How to Sum Cells - Start with the SUM function, then try SUMIFS and more!

2 -- Count All or Specific Cells - Do a simple count, or count based on criteria

3 -- How to Do a VLOOKUP - Find a lookup item in a table, such price for specific product

4 -- Lookup With Criteria - Use formulas to get values from a lookup table, based on multiple criteria

4 -- Combine Text & Numbers - Use formulas to combine values text and numbers from different cells

Excel Function Videos

Here's one of my most popular Excel function videos, and almost all of my Excel function tutorial pages include a video too.

This short video how to quickly split a combined date and time into separate date and time columns. You can use simple Excel formulas, or use a quick trick, with no formulas!

Excel Functions - Sample Files

Here are the download links for a couple of my latest Excel Function sample workbooks, and most of the Excel tutorial pages include a sample file too.

More Workbooks: For even more function sample files, go to the Excel Sample Files page, and scroll down to the Functions section.

Loan Payments -- Enter your loan information, Excel calculates the monthly payment amount, using the PMT function.
Excel File: Loan Payment Schedule (no macros)

Excel Function Categories

Compatibility Functions

These functions are used to work with older versions of Excel or other spreadsheet applications. They have been replaced by newer functions that provide better accuracy and functionality. You can still use them for backward compatibility, but it is recommended to use the newer functions instead.

Cube Functions

These functions are used to work with data stored in an Online Analytical Processing (OLAP) cube. They allow you to retrieve and analyze data from a multidimensional database.

Database Functions

These functions are used to perform calculations on data that is stored in a table format. They allow you to apply criteria to select specific rows and columns and perform various operations on them.

Date and Time Functions

These functions work with dates and times, such as calculating the difference between two dates, finding the current date or time, converting between different date formats, etc. They can be used to track deadlines, schedules, or durations.

Some of the most-used date and time functions are TODAY (which returns the current date), NOW (which returns the current date and time), and DATEDIF (which calculates the number of days, months, or years between two dates).

For examples and video, go to the Excel Date Functions page.

Engineering Functions

These functions are used to perform engineering calculations, such as conversions, complex numbers, bit operations, and error analysis.

Financial Functions

These functions are used to perform financial calculations, such as interest rates, loan payments, depreciation, and investment analysis.

Information Functions

These functions are used to test the type or value of a cell or expression. They return logical values such as TRUE or FALSE, or text values such as "text" or "value".

Logical Functions

These functions perform logical tests and return TRUE or FALSE based on the result. They can also be combined with other functions to create complex formulas that depend on certain conditions.

Some of the most-used logical functions are IF (which returns one value if a condition is true and another value if it is false), AND (which returns TRUE if all of its arguments are true and FALSE otherwise), and OR (which returns TRUE if any of its arguments are true and FALSE otherwise).

For examples and videos, go to the Excel Logical Functions page.

Lookup and Reference Functions

These functions search for a value in a table or range and return a corresponding value from another column or row. They can be used to retrieve information from large datasets or to create dynamic formulas that update automatically based on changes in data.

Some of the most-used lookup and reference functions are VLOOKUP (which looks for a value in the first column of a table and returns a value from another column in the same row), HLOOKUP (which looks for a value in the first row of a table and returns a value from another row in the same column), and INDEX (which returns a value from a specific position in a table or range).

For examples and videos, go to the Excel Lookup Functions page.

Math and Trigonometry Functions

These functions perform basic and advanced mathematical operations, such as addition, subtraction, multiplication, division, exponentiation, logarithms, trigonometry, etc.

Some of the most-used mathematical functions are SUM (which adds up a range of numbers), AVERAGE (which calculates the average of a range of numbers), and ROUND (which rounds a number to a specified number of digits)

For examples and video, go to the Excel Math and Trigonometry Functions page.

Statistical Functions

These functions perform statistical analysis on a set of data, such as finding the mean, median, mode, standard deviation, variance, correlation, etc.

Some of the most-used statistical functions are COUNT (which counts the number of cells that contain numbers), MIN (which finds the smallest value in a range of numbers), and MAX (which finds the largest value in a range of numbers).

For videos and examples, go to the Excel Statistical Functions page

Text Functions

These functions manipulate text strings, such as extracting, concatenating, replacing, formatting, etc. They can be used to clean up data, extract information, or create custom labels.

Some of the most-used text functions are LEFT (which returns a specified number of characters from the left side of a text string), RIGHT (which returns a specified number of characters from the right side of a text string), and LEN (which returns the length of a text string).

User-Defined Functions

These functions are created by users using Visual Basic for Applications (VBA) or add-ins. They allow you to extend the functionality of Excel and create custom calculations that suit your needs.

Web Functions

These functions are used to access data from the web or send data to the web. They allow you to interact with web services and online sources.