Basic formulas

The formulas presented in this category are easier to type and not complicated to understand, the sidebar (or click the hamburger icon if you are on mobile) displays all articles in this category.

How do I enter a formula?
How do I create cell references in a formula?
How do type a formula and not let Excel evaluate/calculate?
Can I combine functions in a formula?
Can a formula return multiple values?
How do I add two numbers?
How do I divide two numbers?
How do I subtract a numbers?
How can I multiply two numbers?
How can I sum numbers in a cell range?
How can I multiply numbers in a cell range?
How do I calculate the power of a number?
How do I round a number up?
How do I round a number down?
What is an Excel date?
How can I get the current date?
Can I add days to a date?
Can I subtract days to a date?
How can I get the current time?
How can I add hours to a time value?
Can a formula count values?
Can a formula count values based on a condition?
Can a formula count empty cells?
Can a formula count cells containing only numbers?
Can a formula count text values?
Can a formula count boolean values?
Can a I use parentheses in a formula?
What is a logical expression?
What is a comparison operator?
How do I calculate sine?
How do I calculate cosine?
How do I calculate tangent?
How do I calculate secant?
How do I calculate cosecant?
How do I calculate cotangent?
How do I remove the sign before a number?

How do I enter a formula?

Double-click with left mouse button on the cell you want to use.

An input prompt appears in the cell.
Begin typing an equal sign, it looks like this: =

This tells Excel to interpret the remaining text you type a s a formula.
I am going to type TODAY(), it will return the current date.

Press Enter when you have finished entering the formula.

How do I create cell references in a formula?

You can type the cell references or you can use your mouse to click on a cell, here is how:

Select a cell.
Type the equal sign =
Use your mouse and click on a cell you want to reference and the cell reference will appear automatically in the formula.

How do I type a formula and not let Excel evaluate/calculate the formula?

Begin your formula with a ' character (apostrophe) and then type the equal sign and the formula. Excel will interpret it as a text value and the ' character (apostrophe) will not be shown in the cell. It will, however, be displayed in the formula bar.

The image above demonstrates a formula that begins with a apostrophe. You can also use the FORMULATEXT function to show the contents of a cell.

Can I combine functions in a formula?

Yes, and I highly recommend it. This makes Excel so powerful and interesting to use, you can customize the formulas to your needs.

The image above demonstrates two functions TODAY function and YEAR function. The TODAY function returns an Excel date that Excel formats as a date. The YEAR function then extracts the year from the Excel date.

Can a formula return multiple values?

Yes, in fact there are a few functions that actually return multiple values by design. They return values distributed over a number of cells and they require you to enter them as an array formula in order to show all values.

You can also create an array formula that uses regular functions, however, it returns multiple values. This may seem confusing but most regular functions can be used in array formulas.

The new TEXTJOIN function can also concatenate multiple values and return them to single cell.

How do I add two numbers?

Simply type =5+9 in a cell and then press Enter. Excel returns the calculated value and the Formula bar shows the arithmetic operation.

You can also use cell references pointing to different cells, the image above demonstrates two numbers 9 and 5 in cell B2 and B3 respectively. The formula in cell D2 adds the numbers in cell B2 and B3 based on cell references.

How do I divide two numbers?

Use the forward slash character / to divide numbers.

Replace the numbers with cell references to cells containing the numbers you want to divide.

How do I subtract a numbers?

Use the forward slash character / to divide numbers.

Replace the numbers with cell references to cells containing the numbers you want to divide.

How can I multiply two numbers?

Use the asterisk character * to multiply numbers.

Replace the numbers with cell references to cells containing the numbers you want to multiply.

How can I sum numbers in a cell range using a formula?

Select the cell where you want the formula.
Type the equal sign =
Type SUM(
Use your mouse and click and hold on a cell containing the first number you want to add.
Drag with mouse to include the remaining cells.
Release mouse button.
Type an ending parentheses )
Press Enter.

How can I sum numbers in a cell range based on a condition?

Use the SUMIFS function.

How can I multiply numbers in a cell range?

Use the PRODUCT function.

How do I calculate the power of a number (number is raised to a power) ?

Use the POWER function or simply use this character ^. Example =10^2 returns 100.

How do I round a number up?

I recommend that you try the ROUNDUP function.

How do I round a number down?

I recommend that you try the ROUNDDOWN function.

What is an Excel date?

It is actually an integer from 1 and up. 1 is 1/1/1900 and 1/1/2000 is 36526 so 1/1/2000 is 36525 days from 1/1/1900.

Try it yourself, enter a date in a cell. Select the cell containing the date and then press CTRL + 1 to format the cell.

Change the formatting to "General". Click OK.

The cell now shows the number representing the date.

How can I get the current date?

Use the TODAY() function.

Can I add days to a date?

Yes, since dates are integers you simply add a number representing days to the date. The image above displays 1/1/1900 in cell B3, cell C3 contains 7.

The formula in cell D3 is =B3+C3, it adds 7 to 1/1/1900 and returns 1/8/1900.

Can I subtract days to a date?

Yes, simply use the minus sign in your formula, for example: =B3-C3

The image demonstrates the same formula as the previous example above, however it uses negative numbers as dates instead.

How can I get the current time?

The NOW function returns the current date and time, however the TEXT function can format the value to show only the time part. Use the following formula: =TEXT(NOW(),"HH:ss")

How can I add hours to a time value?

1 day or 24 hours is 1 in Excel, 1 hour is 1/24 so to add 1 hour simply add 1/24 to the Excel date and time value.

Can a formula count values?

Yes, the COUNTA function counts non-empty values in a given cell range.

Can a formula count text values?

Yes, the image above shows a formula in cell D3 that counts text values in cell range B3:B11.

=SUMPRODUCT(ISTEXT(B3:B11)*1)

The ISTEXT function returns TRUE if a cell contains a text value and FALSE if not. The SUMPRODUCT function simply adds the values and returns the total.

Can a formula count boolean values?

Yes, the image above shows a formula in cell D3 that counts logical values in cell range B3:B11.

=SUMPRODUCT(ISLOGICAL(B3:B11)*1)

The ISLOGICAL function returns TRUE if a cell contains a boolean value and FALSE if not. The SUMPRODUCT function simply adds the values and returns the total.

Can a I use parentheses in a formula?

Yes, use it to change the order of calculation. The picture above shows two formulas, the parentheses determines the order of calculation.

The formula in cell C3 calculates 5+4 first and then multiplies with 9. 9*9 = 81

The formula in cell C4 calculates the multiplication first and then the addition. 4*9 equals 36 and then adds 5 equals 41.

What is a logical expression?

It is an expression that returns either TRUE or FALSE or their equivalents 1 or 0 (zero). It uses the comparison operators in order to determine the outcome.

Example formula: =B3>5

The formula above returns TRUE if the value in cell B3 is larger than 5 and FALSE if it is equal to or smaller than 5.

A logical expression is used in IF functions, Conditional Formatting and in SUMPRODUCT formulas to name a few.

What is a comparison operator?

It is a character that compares two or more values and is used in logical expressions. They are:

With these three characters you can create logical operators:

< - Less than
> - Larger than
= - Equal to
<= - Less than or equal to
>= - Larger than or equal to
<> - Not equal to

How do I calculate sine?

Use the SIN function.

How do I calculate cosine?

Use the COS function.

How do I calculate tangent?

Use the TAN function.

How do I calculate secant?

Use the SEC function.

How do I calculate cosecant?

Use the CSC function.

How do I calculate cotangent?

Use the COT function.

How do I remove the sign before a number?

The ABS function allows you to delete the minus sign.

Latest articles in Basic formulas category

Extract values between two given delimiting strings

The image above demonstrates a rather small formula in cell D3 that extracts values in cell B3 based on two […]

How to do tiered calculations in one formula

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]

How to sum a cell range

I will in this article demonstrate different ways to sum values, the first method is so easy and fast it's […]

INDEX MATCH – Last value

INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first […]

Calculate the number of weeks between given dates

This article demonstrates Excel formulas that calculate complete weeks between two given dates and weeks and days between two given […]

How to replace part of formula in all cells

This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]

If cell has value

The easiest way to check if a cell has a value is, in my opinion, to use the equal sign […]

Count cells with text

Table of Contents Count cells with text Count cells with text excluding cells containing a space character Count text values […]

Count rows containing data

Table of Contents Count rows with data Count non-empty rows Count cells between two values Count cells based on a […]

Get the latest revision

Table of Contents Get the latest revision Create a list with most recent data available 1. Get the latest revision […]

How to extract numbers from a cell value

What's on this page How to extract numbers from a cell value - Excel 2016 Sort and return unique distinct […]

How to remove numbers from a cell value

This article demonstrates an array formula that extracts all characters except numbers from a cell, cell C3 contains the formula […]

Extract specific word based on position in cell value

Table of Contents Extract first word in cell value Extract the first word in cell - return warning if not […]

INDEX and MATCH – multiple criteria and multiple results

This article demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel […]

Count cells containing text from list

The array formula in cell F3 counts cells in column B that contains at least one of the values in […]

If cell contains multiple values

This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 […]

SMALL function – INDEX MATCH

This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]

How to ignore zeros using the SMALL function

This article shows how to create a formula that sorts numbers from small to large excluding zeros. I will also […]

SMALL function – multiple conditions

This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]

SMALL function for text

This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]

How to AVERAGE time

Table of Contents How to AVERAGE time How to enter an array formula Explaining formula How to AVERAGE time hh […]

SMALL function ignore duplicates

This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]

INDEX MATCH – Case sensitive

The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]

Excel formula not working

This article explains why your formula is not working properly, there are usually four different things that can go wrong. […]

INDEX MATCH – multiple results

This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]

Match two columns and return another value on the same row

This article demonstrates formulas that match two conditions in a column each and return another value on the same row […]

If cell equals value from list

This article demonstrates formulas that check if a cell value is equal to any value in a given list. Table […]

If cell contains text from list

This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]

INDEX MATCH with multiple criteria

This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]

How to use the COUNTIF function to count not blank cells

The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]

If cell contains text

This article demonstrates different formulas based on if a cell contains a given text. Formula in cell C3: =B3=$E$3 The […]

Use IF + COUNTIF to evaluate multiple conditions

The image above demonstrates a formula that matches a value to multiple conditions, if the condition is met the formula […]

IF function with AND function – multiple conditions

The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]

How to simplify nested IF functions

Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are […]

How to create running totals

This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]

Find empty cells and sum cells above

This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and […]

Find last value in a column

This article demonstrates formulas that return the last value in a given cell range or column. The image above shows […]

Sum numerical ranges between two numbers

This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]

How to calculate overlapping time ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

Find last matching value in an unsorted list

This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]

Lookup with any number of criteria

This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]

Count specific multiple text strings in a given cell range

This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]

How to create name initials

The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]

Extract table headers based on a condition

This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]

Lookup multiple values across columns and return a single value

This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]

Find entry based on conditions

Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department. I have a […]

7 days (weekly) date ranges using a formula

Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]

Running totals based on criteria

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

Sum based on OR – AND logic

Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]

Formula for matching a date within a date range

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

Find the most recent date that meets a particular condition

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

Lookup two index columns

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]