Sometimes, when writing a formula or a function, you will need to refer to the address of one or several cells. Learning the few following tricks will certainly help you speeding up things and performing complex calculations with (almost) no stress.
Single cells
To refer to a single cell, just type its coordinates (e.g “B5”). It is possible to refer to a specific cell by giving it a name! Let’s assume that you want to put a reference value in the cell C2. Click on C2, give it the name “reference” by typing that word in the name box and press ENTER. From now on, every time you will write a formula which refers to the word “reference” (e.g. “=10*reference+6”), MS Excel will silently translate “reference” and replace it by the value contained in C2.
Range of consecutive cells
A range of consecutive cells within a column or a row may be defined by writing the address of the first cell and the address of the last cell separated by : (e.g. “C2:C8” refers to C2, C3, C4, C5, C6, C7 and C8). Thus, typing “=SUM(C2:C8)” will give you the sum of all the cells between C2 and C8. Note that a range may encompass several rows and columns, in which case the address of the range starts with the address of the cell in the top left corner and finishes with the cell in the bottom right corner, again separated by a “:”.
Multiple cells and ranges
You may even decide to select single cells and diverse ranges of cells. In that case, you’ll need to list these individual cells and ranges and to separate them with a semi-colon “;”. For instance the formula “=AVERAGE(A1;B2;C1:D4;D8:F8)” will calculate the average of the contents of A1, B2, C1, C2, C3, C4, D1, D2, D3, D4, D8, E8 and F8. To select all these cells and ranges when typing the formula, hold the CTRL key while selecting the cells with the mouse, or simply type the whole formula directly in the formula bar and press ENTER.