Basic formulas

Basic formulas | Advanced 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?

1. Double-click with left mouse button on the cell you want to use.
   
   An input prompt appears in the cell.
2. Begin typing an equal sign, it looks like this: =
   
   This tells Excel to interpret the remaining text you type a s a formula.
3. I am going to type TODAY(), it will return the current date.
   
   Press Enter when you have finished entering the formula.
   

Back to top

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:

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

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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

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

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

Use the SUMIFS function.

Back to top

How can I multiply numbers in a cell range?

Use the PRODUCT function.

Back to top

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.

Back to top

How do I round a number up?

I recommend that you try the ROUNDUP function.

Back to top

How do I round a number down?

I recommend that you try the ROUNDDOWN function.

Back to top

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.

Back to top

How can I get the current date?

Use the TODAY() function.

Back to top

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.

Back to top

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.

Back to top

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")

Back to top

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.

Back to top

Can a formula count values?

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

Back to top

Can a formula count values based on a condition?

Yes, use the COUNTIF function.

Back to top

Can a formula count empty cells?

Yes, use the COUNTBLANK function.

Back to top

Can a formula count cells containing only numbers?

Yes, use the COUNT function.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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

Back to top

How do I calculate sine?

Use the SIN function.

Back to top

How do I calculate cosine?

Use the COS function.

Back to top

How do I calculate tangent?

Use the TAN function.

Back to top

How do I calculate secant?

Use the SEC function.

Back to top

How do I calculate cosecant?

Use the CSC function.

Back to top

How do I calculate cotangent?

Use the COT function.

Back to top

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