Excel Basics - Cell Referencing

 

One of the fundamental skills you have to master in order to use Excel well is how to reference cells for use in formulas and functions - or even just to display them elsewhere.

Excel is made up of columns (up and down) and rows (left to right).  The columns are labeled with letters: from A to IV. The rows are numbered from 1 to 65,536.  Basic cell referencing is as simple as "ColumnRow" so, for example, "A1" or "D45" or "AA2145". 

Ranges

If you want to reference a set of cells together you can do that in a couple of ways. One way is to list the cell addresses separated by commas.  "A1,B3,D4" for example. 

Another way is to use a range, which is usually the best way if the cells are contiguous.  To use a range, name the first cell and the last cell and separate them with a colon (:). For example:  D2:D30 which will contain D2, D30 and all of the cells between them.

You can also create ranges that span columns.  D2:F30 will contain D2 and F30 and all of the cells between them (including E2 to E30).

Named Ranges

You can give a range of cells, or even a single cell, a friendly name to make it easier to reference in functions or formulas. To do that select the cell or range of cells you want to name with your mouse and click the Name Box at the left end of the toolbar just above the worksheet then just type the name you want to give it.

For example: If cell D11 contains the tax rate you want to work with, clicking on it and naming it "TaxRate" makes it easy to refer to in your formulas and functions. If you ever change it (or move it) you don't have to worry because your formulas and functions will continue to work.

Cells on Other Sheets

If you have a workbook with multiple sheets (the tabs at the bottom of the sheet) you can easily reference cells that are on other sheets. All you need to do is preface the cell reference with the sheet name, in single quotes, followed by an exclamation mark. Here, let me give an example:

'Sheet2'!A3 would give you the value contained in cell A3 on Sheet2. 

Another option is to use the Named Ranges I mentioned above. When you do that you don't need to use the Sheet name at all because the named ranges can be used throughout the workbook.

Cells in Other Workbooks

You can even reference cells that are in totally different workbooks - though that gets a little more complicated because it requires that your workbooks can "see each other".  If you send the destination workbook to somebody without including the source workbook they'll get broken functions or formulas because Excel won't be able to find the referenced cells.

To do that you just specify the filename of the other workbook in square brackets, followed by the sheet and cell as above.  For example: [PropertyTaxes]'Sheet3'!D5 would pull the value from cell D5 on Sheet3 of the PropertyTaxes.xlsx file in the same folder as the workbook you're currently working on. If the workbook exists in a different folder from your current workbook you'll want to specify the full path like [P:\Tax Info\Workbooks\PropertyTaxes].