Posted by Enrique Parada on April 4, 2020
What is a cell reference?
What is a cell reference? In Excel, it is when a cell derives its value based on the value of another cell. The below is a simple example of a cell reference. Note that the value in cell A3 is derived from the value in cell A1. How do you create a reference to another cell? In the cell that you want to create a cell reference, press the ‘=’ key on your keyboard and then click on the cell that you want to reference.
Why is cell referencing useful? Cell referencing makes your spreadsheet more dynamic. What does “making a spreadsheet dynamic” mean? In our above example, whenever you change the value in cell A1, the value in cell A3 also changes because of the cell reference that we’ve created. As an example, if we changed cell A1 to say “Hello”, then the value in cell A3 will automatically update to “Hello”. This example doesn’t do the usefulness of cell referencing any justice, so let’s use a more practical example.
Let’s say we are calculating the total revenue (price per product multiplied by # of units sold) from selling a product. In our example below, instead of typing =3*200 in the total revenue column (Cell C8), use cell referencing instead. When you use cell referencing, the formula should reference the cell where the price is and then multiplied by the cell where the # of units sold is (see Cell C10 for the formula).
Now that you’ve used cell referencing to calculate ‘Total Revenue,’ whenever you change ‘Price per product’ and/or ‘# of units sold’ (i.e. cell A10 and/or cell B10), the ‘Total Revenue’ cell automatically gets updated (see GIF below). The next time you hear a classmate, boss, co-worker, etc. to make a spreadsheet more dynamic, this is exactly what they are referencing (pun intended). The opposite of cell referencing is what you call “hardcoding” the values (i.e. how we calculated revenue in cell C8 above). I would stray away from hardcoding values unless absolutely necessary.
Try to take an existing spreadsheet that’s filled with hardcoded values and create cell references instead!
To select a group of cells, all you need to do is hold the shift key and then selecting the cells you need by using the arrow keys. Combine this with #1 (i.e. CTRL + SHIFT + arrow key) to select all contiguous cells in a row or column.
