Our Excel Consultants use spreadsheets to process your data. At its simplest level, an Excel spreadsheet is simply a table of information, a database. To perform calculations on data we need a way to refer to the data and do math on it.
Cell References
The most distinctive feature of Excel is that it has a row of letters across the top of the page, and numbers going down the page (as in the image). Every cell is therefore given a unique name. "Aaron" is in cell A1 as it is in column A, row 1. "Bob" is in cell A2. If you wanted to ensure cell A3 always contained the same information as cell A1, you could enter the formula "=A1" into the cell. "A1" is a cell reference.
This can be used to create formulas e.g. "B1 + B2" would equal "50 + 20", or "70". Another feature of Excel is that, were you to insert a column between A and B, then each of your references to cell B1 would update to cell C1. As the content has moved, it is assumed your reference should move with the content. If you wanted the cell in column C in each row to display e.g. "Aaron has 50 cakes", "Bob has 20 cakes", then the relevant formulas would be ="&A1&" has "&B1&" cakes" and ="&A2&" has "&B2&" cakes". When you copy the formula from cell C1 to C2, it will assume you want to refer to Bob, not Aaron, because he appears in the new row. Therefore, a basic cell reference can be very powerful.
Unfortunately this power also becomes a weakness. What if you wanted to say "Aaron has 50 cakes" in the first row, and "Aaron has 20 cakes" in the second row. That would demand an absolute cell reference. An absolute cell reference is one that is fixed. To keep referring to cell A1, regardless of output cell, you need to add dollar symbols e.g. A1->$A$1. The first dollar symbol says you always want to refer to column A, the second dollar symbol that you want to refer to row 1. So if we want to say "Aaron has X cakes" in column C, in all rows of our spreadsheet, we need to fix the row and can refer to $A$1, or just A$1. Copy the formula ="&A1&" has "&B1&" cakes" from cell C1 to cell C2 and observe we are referring to Aaron in both cases.
We can take things a step further in our use of absolute cell references by using a range name. What does "A1" mean if you aren't looking at the spreadsheet? The answer is very little. Just above the spreadsheet in the top left hand corner, next to any entered formulas, you will see "A1" when you open a new spreadsheet because "A1" is the name of the selected cell. However, you can give cells a second name simply by typing over the row/column reference e.g. "AaronCakes" is the name given to cell B1 in the above example. Now when writing a formula you can simply refer to e.g. "AaronCakes" and "BobCakes". It is far clearer to a user what is going on. You can also name rows, columns and different cell selections. As names can be given to any number of cells, they are called Range Names and, should you need to edit/delete names, you can do so from Formulas->Defined Names->Name Manager.
In the case of both absolute cell references and their extension as named cells, the addresses still update if rows or columns are inserted into the spreadsheet. The point of referring to a fixed location would be to e.g. add sales tax to a list of prices. The sales tax is in a single cell, whereas the prices are in a table. If you move the sales tax cell by inserting/deleting rows/columns, you still want your Excel formulas to reference the same content.
Range References
If you want to refer to multiple cells, you have to define the area of cells you want to refer to. The simplest, and most common, example is where you wish to refer to a rectangle. Let's say you want to sum both Aaron and Bob's cake sales. Excel includes the SUM function that will add up all the cells within a range. A rectangle can be defined by its top left and bottom right corner. The top left is B1, the bottom right is B2. We can define the range as "B1:B2" and find the sum using "=SUM(B1:B2)" which will return the answer 70. To refer to all four cells with content, we would use A1:B2.
We do have the option of referring to cells individually if we separate them using commas so "A1:B2" could become "A1, A2, B1, B2". You can see that referring to cells individually quickly becomes cumbersome. Even referring to cells can be lengthy if we wish to sum an entire column of data. Excel 2010 has 1,048,576 rows so you could sum column B by referring to "B1:B1048576". The fact we want to include all rows means the row reference becomes irrelevant and Excel gives us the shortcut of simply dropping the row numbers; so "B:B" refers to column B. Note it cannot be shortened further to "B" as this would be impossible to distinguish from a mis-typed cell reference.
As with individual cells, we do have the power to use absolute cell references as well as relative cell references. There are very few cases in which the use of B:B would produce different behaviour to $B:$B but, if "SUM(B:B)" represented total cake sales and you wanted to use it in similar calculations in columns C and D, you should use "SUM($B:$B)"
Sheet and Workbook References
There is one footnote to make to all the above. An Excel file can contain many different sheets, all of which have a cell A1. Generally calculations are contained on a single sheet so it can be assumed that any formula in a cell on Sheet1 wants to refer to cells on Sheet1. In certain circumstances, you might want to refer to a cell on Sheet2. This means a qualifier needs to added to the front of your cell or range reference. This takes the form of the sheet's name and an exclamation mark e.g. "Sheet1!A1" refers to cell A1 on Sheet1 regardless of which sheet it appears on in a workbook. One advantage of range names (covered earlier) is that, by default, these names apply across an entire file e.g. it is assumed you only have "AaronCakes" on one sheet.
If you try to re-name sheets within Excel, you will see all your formulas adapt to reflect the name change. You will also observe that you cannot give two sheets the same name. However, if you have multiple Excel workbooks, it is possible to have two sheets with the same names and cell references. Therefore your reference may need to be extended further to include the file name. Details are not included here because there is an inherent danger to referencing cells in other files which is that the file may move and that the link may be broken. This is particularly the case if files are being e-mailed around. For that reason, even our Excel experts try to avoid linking files!
