Excel Basics Day 4: What is Referencing in Excel?
So far, in the past 3 instalments of this series we have :
- Discussed the basic structure of formula and mathematical expressions (Day 1 & 2).
- Learnt the names of the basic components of the Excel Window (Day 3).
Let’s have another look at the Excel widow we had discussed in the previous email:
The workbook, an Excel file is called as a workbook, is named as Budget.xlsx. (In a computer file name, the portion of the name after the dot is called as an Extension. For example, in the present case, the name of the file is Budget and its extension is .xlsx. The computer uses the Extension portion to determine the program in which the file should be opened. A file with an extension of .xls or .xlsx is opened in Excel; a file with an extension of .doc or .docx is opened in Microsoft Word; a file with an extension .pdf is opened in a pdf reader; a file with an extension of .ods is opened in Symphony, and so forth.)
The Budget workbook has two worksheets Income and Expenses. I have made a few entries in the Income worksheet. Let us take a look at the worksheet now and observe my annotations in the illustration below. I encourage you to make similar entries in Excel at your end so that you can follow better.
Please observe my annotations, highlighted in yellow. In the illustration, cell C2 is the active cell. As you would recall from Day 3, a cell is identified by the column letter followed by the row number of the cell. Cell C2 is in column C and row number 2. The address of the active cell is displayed in the Name Box and contents of the cell are displayed in the Formula Bar. If the cell contains any formula/mathematical expression, the formula/mathematical expression is displayed in the Formula Bar and the result of the formula/mathematical expression is displayed in the cell. However, if the cell does not contain a formula/mathematical expression, the cell, as well as the formula bar, displays the contents of the cell.
In the illustration, Tax for January’2017 has been calculated as 30% of the contents of the cell B2, which contains Gross Income, and Net Income for January’2017 has been calculated as Gross Income-Tax. The mathematical expression used in C2 is =B2*30% and mathematical expression used in D2 is =B2-C2
Such a method of using a formula, where we are using the cell name containing the value instead of the value itself, is called referencing. While a calculator can only accept a mathematical expression as 82457*30%, Excel can accept a mathematical expression =B2*30% where B2 contains 82457. Alternatively, we may say that while a Calculator can only do calculations on numbers, Excel can do calculations based on the reference to the location of the Number or we may say Excel allows referencing. And that is the first superpower of Excel.
We will explore the uses of referencing in the next instalment. Till then, bye-bye.
Please write in with suggestions and feedback.