[EXCEL] – FORMULA REFERENCES

WHAT ARE FORMULA REFERENCES IN MICROSOFT EXCEL?

Formula references in Microsoft Excel are when we create a simple formula or a formula that uses a function, you can refer to data in worksheet cells by including cell references in the formula arguments.

For example, when you enter or select the cell reference A1, the formula uses the value of that cell to calculate the result. By default, the formula reference in cells will be called as “relative reference“.

FORMULA REFERENCES IN MICROSOFT EXCEL

In Microsoft Excel, we have three types of formula references:

  1. Relative Reference: by default, all cell reference in excel will be relative reference, which means that the reference is relative the location of cells. For example, we refer to cell A1 from cell C1 (referring two columns to the cell), and if we copy and paste the formula from cell C1 to D1 then the reference will be changed from A1 to B1.
  2. Absolute Reference or Fixed Reference: this reference will be used if we want to fix the column and row address like $A$1. For example, if we refer to cell A1 from C1 (like $A$1), and if we copy and paste the formula from cell C1 to D1 or any other cells reference will not change and the value will be picked from cell A1 only.
  3. Mixed Reference: this reference will be used if we want to fix either column or row, then will use mixed reference, like fixing column as $A1 or fixing row as A$1.

Shortcut key for changing formula references in cell is function F4 .

(check the below video to learnt more about what and how to use cell formula references in Microsoft Excel in details.)

TIPS & TRICKS

  1. In Microsoft Excel, formula references are powerful concepts. We can create dynamic formulas using references concept.
  2. Utilize formula references in spreadsheets to implement dynamic formula-based reports

RELATED ARTICLES

  1. Check the following article to know more about WEEKNUM function in Microsoft Excel: http://msofficeguru.in/microsoft-excel/date-time-functions/excel-weeknum-function/
  2. Check the following article to know more about NETWORKDAYS function in Microsoft Excel: http://msofficeguru.in/microsoft-excel/date-time-functions/excel-networkdays-function/

Leave a Reply

Your email address will not be published. Required fields are marked *