The Excel Formulas Ultimate Guide 2019

 

Introduction to Excel Formulas

In this guide, we are going to cover everything you need to know about Excel Formula. Learning how to create Excel formulas is the building block to the exciting journey to become an Excel Expert. So, let’s get started!

 

What is an Excel Formula?

Excel Formulas are often referred to as Excel Functions and it’s no big deal but there is a difference, a function is a piece of code that executes a predefined calculation and formula is an equation created by the user.

Formula is an expression that operates on values in a cell or range of cells. An example of a formula looks like this: = A2 + A3 + A4 + A5. This formula will result in the sum of the range of values from cell A2 to Cell A5.

Function is a predetermined formula in Excel. It performances calculation in a particular order defined by its parameters. An example of a function is =SUM (A2, A5). This function will also provide the addition of values in the range of cells from A2 to A5 but here instead of specifying each cell address we are using the SUM function.

How To Write Excel Formulas

Let’s look at how to write a formula on MS Excel. To begin with, a formula always starts with a ‘+’ or ‘=’ sign, if you start writing the formula without any of these two signs, Excel will treat the value in that cell as text and will not perform any function.

In the screenshot below, you need to calculate the total sales amount by multiplying quantity sold with unit price. Select cell D2, type the formula ‘=B2*C2’ and press Enter. The result will display on cell D2.

Apply A Formula To An Entire Column Or Range

To copy a formula to adjacent cells, you can do the following:

  1. Select the cell with the formula and the adjacent cells you want to fill, then drag the fill handle.

  1. Select the cell with the formula and the adjacent cells you want to fill, then press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.
  2. Select the cell with the formula and the adjacent cells you want to fill, then Click Home > Fill, and choose either Down, Right, Up, or Left.

How to Copy A Formula And Paste It

Excel allows you to copy the formula entered in a cell and paste it on to another cell, which can save both time and effort. To copy paste a formula:

  1. Select the cell whose formula you wish to copy
  2. Press Ctrl + C to copy the content
  3. Select the cell or cells where you wish to paste the formula. The copied cells will now have a dashed box around them.
  4. Press Alt + E + S to open the paste special box and select ‘Formula’ or Press “F’ to paste the formula.

Excel Formulas

  1. The formula will be pasted into the selected cells.

Basic Excel Formulas

Let’s start off with simple Excel Formulas. In the screenshot below, there are two columns containing numbers and we would like to use different operators like addition, subtraction, multiplication, and division on those to get different results. Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for subtraction (-), an asterisk for multiplication (*), a forward slash for division (/).

  • Addition– To add values in the two columns, simply type the formula =A2+B2 on cell C2 and then copy paste the same on the cells below.
  • Subtraction – To subtract values in Column B from Column A, simply type the formula =B2-A2 on cell C2 and then copy paste the same on the cells below.
  • Multiplication – To multiply values in the two columns, simply type the formula =A2*B2 on cell C2 and then copy paste the same on the cells below.
  • Division – To divide values in Column a by values in Column B, simply type the formula =A2/B2 on cell C2 and then copy paste the same on the cells below.

We can also calculate the percentage using Excel Formulas. In the image below, we have sales in Year 1 and Year 2 and we would like to know the % growth in sales from Year 1 to Year 2.

Excel Formulas

To do that, select cell C2 and type the formula = (B2 – A2)/B2*100.

Basic Excel Functions

A function is a predefined formula that performs calculations using specific values in a particular order. Excel includes many common functions that can be useful for quickly finding the sum, average, count, maximum value, and minimum value for a range of cells. a function must be written a specific way, which is called the syntax.

The basic syntax for a function is an equals sign (=), the function name, and one or more arguments. A Function is generally comprised of two components:

1) A function name -The name of a function indicates the type of math Excel will perform.

2) An argument – It is the values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names

Let’s look at some common Excel Functions:

SUM Function

The SUM formula adds 2 or more numbers together. You can use cell references as well in this formula.

Syntax: =SUM(number1, [number2], …)

Example: =SUM(A2:A8) or SUM(A2:A7, A9, A12:A15)

AUTOSUM Function

The AutoSum command allows you to automatically insert the most common functions into your formula, including SUM, AVERAGE, COUNT, MIN, and MAX.

Select the cell where you want the formula, Go to Home > Click on AutoSum > From the dropdown click on “Sum” > Enter.

COUNT Function

If you wish to how many cells are there in a selected range containing numeric values, you can use the function COUNT. It will only count cells that contain either numbers or dates.

Syntax: =COUNT(value1, [value2], …)

In the example below, you can see that the function skips cell A6 (as it is empty) and cell A11 (as it contains text) and gives you the output 9.

COUNTA Function

Counts the number of non-empty cells in a range. It will include cells that have any character in them and only exclude blank cells.

Syntax: =COUNTA(value1, [value2], …)

In the example below, you can see that only cell A6 is not included, all other 10 cells are

counted.

AVERAGE Function

This function calculates the average of all the values or range of cells included in the parentheses.

Syntax: =AVERAGE(value1, [value2], …)

ROUND Function

This function rounds off a decimal value to the specified number of decimal points. Using the ROUND function, you can round to the right or left of the decimal point.

Syntax: =ROUND(number,num_digit)

This function contains 2 arguments. First, is the number or cell reference containing the number you want to round off and second, number of digits to which the given number should be rounded.

  • If the num-digit is greater than 0, then the value will be rounded to the right of the decimal point.
  • If the num-digit is less than 0, then the value will be rounded to the left of the decimal point.
  • If the num-digit equal to 0, then the value will be rounded to the nearest integer.

Example:

MAX Function

 This function returns the highest value in a range of cells provided in the formula.

Syntax: =MAX(value1, [value2], …)

In the example below, we are trying to calculate the maximum value in the dataset from A2 to A13. The formula used is =MAX(A2: A13) and it returns the maximum value i.e. 96.251

 MIN Function

This function returns the lowest value in a range of cells provided in the formula.

Syntax: =MIN(value1, [value2], …)

In the example below, we are trying to calculate the minimum value in the dataset from A2 to A13. The formula used is =MIN(A2: A13) and it returns the minimum value i.e. 24.178

TRIM Function

 This function removes all extra spaces in the selected cell. It is very helpful while data cleaning to correct formatting and do further analysis of the data.

Syntax: =trim(cell1)

In the example below, you can see the name in cell A2 contains an extra space, in the beginning, =TRIM(A2) would return “Steve Peterson” with no spaces in a new cell.

Advanced Excel Formulas

 Here is a list of Formulas that we would feel comfortable referring to as the top 10 Excel Formulas. These formulas can be used for basic problems or highly advanced problems as well, though advanced excel formulas are nothing but a more creative way of using the formulas. Sure, there are legitimate Advanced Excel Formulas such as Array Formulas but in general the more advanced the problem, the more creative you need to be with Formulas.

Currently, Excel has well over 400 formulas and functions but most of them are not that useful for corporate professionals so here is a list that we believe contains the 10

best Excel formulas.

Starting with number 10 – it’s the IFERROR formula

  1. IFERROR Function –
  2. SUBTOTAL Function –
  3. SMALL/LARGE Function –
  4. LEFT Function –
  5. RIGHT Function –
  6. SUMIF Function –
  7. MATCH Function –
  8. INDEX Function –
  9. VLOOKUP Function –
  10. IF Function –