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.
My challenge to you is to make an existing spreadsheet or an assignment that you have to complete dynamic (i.e. use cell references)!
Our team of business tutors is drawn from Harvard Business School, MIT’s Sloan School of Management, and Columbia Business School. We also hire Economics doctoral candidates at Harvard, MIT, Columbia, NYU, and Princeton. Some of our tutors have studied business concepts at the graduate level, top-tier financial institutions and management consulting firms.
Our tutors provide tutoring for all undergraduate business and MBA courses. Many of our business students use tutoring to address academic coursework, while others use it to prepare for the GMAT or for a challenging job.
Whether you are a college student, an MBA candidate or a business professional, we know that our students our busy, so in all tutoring, we take a highly structured and customized approach that will provide the most efficient and comprehensive service available.
Check out some of Enrique's previous blog posts below!
4 Excel Quick Tips to Navigate Data Faster
Comments