Excel Guides

Evaluating Formulas in Excel

When you enter a formula in Excel, the formula is evaluated from left to right, according to the following rules:

  1. If a reference is found, its value is used in the calculation.
  2. If a number or logical value is found, it is used in the calculation.
  3. If a text string is found, it is evaluated as 0 (zero).
  4. If an operator is found, the operation is performed on the preceding and succeeding values, and the result is used in the calculation.

The order of operations for evaluating formulas is as follows:

  1. ( ) Parentheses first. Anything within parentheses is calculated first. For example, 3+(2*5) equals 13, not 15.
  2. : Range operator second. The range operator produces a reference to all cells between two references. For example, A1:A5 refers to cells A1 through A5.
  3. , Comma operator third. The comma operator separates arguments within a function. For example, SUM(A1:A5) equals 15 (the sum of cells A1 through A5).
  4. % Percent fourth. The percent sign calculates percents. For example, 50%*100 equals 50 (50 percent of 100).
  5. (+ Addition and - Subtraction) fifth. These operators add or subtract values. For example, 3+2 equals 5 and 3-2 equals 1.
  6. (* Multiplication and / Division) sixth. These operators multiply or divide values. For example, 3*2 equals 6 and 3/2 equals 1.5.
  7. (^ Exponentiation) seventh. This operator raises a number to a power. For example 2^3 equals 8 (two cubed or two raised to the third power).
    • (Note: The ^ operator has higher precedence than * / operators. Therefore 2^3*4 equals 32 (two cubed times 4), not 64 (4 times two cubed).)

Evaluating Formulas That Contain Cell References


When you enter a formula that contains cell references into a cell on your worksheet, Excel adjusts those cell references automatically when you copy or move the cell that contains the formula no matter where you copy or move it within your worksheet or workbook.


Relative Cell References


By default, all cell references in formulas are relative references. Relative references adjust based on how they are copied or moved relative to other cells.
For example, if you copy cell C1 to D1 , both of these formulas adjust their cell references accordingly.



The formula in cell D1 returns 7 because it was copied from C1 . When this formula was copied over one column to D1 , each relative reference changed by one column B1 becomes C1 , C1 becomes D1 , and so on.


The formula in cell E4 returns 10 because it was copied from D4 . When this formula was copied down one row from D4 to E4 , each relative reference changed by one row B4 becomes B5 , C4 becomes C5 , and so on.


You can use relative references whenever possible because they make your formulas easier to understand and maintain.[7]


Absolute Cell References


An absolute reference does not change when you copy or move a cell that contains an absolute reference.[8] An absolute reference is designated in a formula by placing a dollar sign ($) before both parts of the cell address for example, $A$1 . Both parts of an absolute reference must always contain dollar signs.[9] You can use an absolute reference for only part of a cell address for example, A$1 or $A1 . When only one part of the address contains dollar signs ($), that part remains constant while the other part changes.[10] For more information about mixed references see Absolute vs Mixed References below.[11] Here are some examples of how copying cells with absolute references affects those cells' contents and adjacent cells' contents.[12]

MixedCellReferences

Mixed references contain both absolute and relative components.$A$2 is an absolute reference; A$2 and $A2 are mixed references.$A$2 does not change if you copy it across columns but does change if you copy it down rows.$A$2 changes if you copy it down rows but does change if you copy it across columns.$A$2 changes if you copy it down rows but does change if you copy it across columns.$A$2 changes if you copy it down rows but does change if you copy it across columns.$A$2 changes if you copy it down rows but does change if you copy it across columns.$A$2 changes if you copy it down rows but does change if you copy it across columns.$A$2 changes if you copy it down rows but does change if you copyit across columns.$A$2 changesifyoucopyitdownrowsbutdoeschangeifyoucopyitacrosscolumns.$AA changesifyoucopyitdownrowsbutdoeschangeifyoucopyitacrosscolumns.$AA changesifyoucopyitdownrowsbutdoeschangeifyoucopyitacrosscolumns.$AA changesifyoucopyitdownrowsbutdoeschangeifyoucopyitacrosscolumns.

Relative vs Mixed vs Absolute Referencing Explained with Examples

< td width = '27%', height = 18'>Relative Reference (& lt): $B $ 5 = OFFSET ($ B $ 4,, 1,, 1), which means "one row below and one column right of B4". If we were to drag this formula rightwards two columns then we would get = OFFSET ($ B $ 4,, 3,, 1), which means "three column right of B4". If we were also drag this downwards two rows then we

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.
Relative vs Mixed vs Absolute Referencing Explained with Examples
 Type of Reference                 ; & nbsp ; & nbsp ; & nbsp ; & nbsp ; & nbsp ; & nbsp ; & nbsp ; & nbsp ; & nbsp ; & nbsp ; & nbsp ; & nbsp ; & nbsp ;