Making spreadsheets “dynamic” with cell referencing in Excel

excel
By Enrique

Excel cell refrencingWhat 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.

Screen Shot 2020-02-06 at 4.01.36 PM

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).

Screen Shot 2020-02-06 at 4.02.42 PM

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.

cell_referencing_blog_post_3

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.

Work with Enrique!

Check out some of Enrique's previous blog posts below!

4 Excel Quick Tips to Navigate Data Faster

Solving tough algebra problems with Excel’s Goal Seek feature

 

 

Comments

topicTopics
academics study skills MCAT medical school admissions SAT expository writing college admissions English MD/PhD admissions strategy writing LSAT GMAT GRE physics chemistry math biology graduate admissions academic advice ACT interview prep law school admissions test anxiety language learning premed MBA admissions career advice personal statements homework help AP exams creative writing MD study schedules test prep computer science Common Application summer activities history mathematics philosophy organic chemistry secondary applications economics supplements research 1L PSAT admissions coaching grammar law psychology statistics & probability legal studies ESL CARS SSAT covid-19 dental admissions logic games reading comprehension engineering USMLE calculus PhD admissions Spanish mentorship parents Latin biochemistry case coaching verbal reasoning DAT English literature STEM excel medical school political science skills AMCAS French Linguistics MBA coursework Tutoring Approaches academic integrity chinese letters of recommendation Anki DO Social Advocacy admissions advice algebra art history artificial intelligence astrophysics business cell biology classics diversity statement gap year genetics geometry kinematics linear algebra mechanical engineering mental health presentations quantitative reasoning study abroad technical interviews time management work and activities 2L DMD IB exams ISEE MD/PhD programs Sentence Correction adjusting to college algorithms amino acids analysis essay athletics business skills careers cold emails data science dental school finance first generation student functions graphing information sessions international students internships logic networking poetry resume revising science social sciences software engineering tech industry trigonometry writer's block 3L AAMC Academic Interest EMT FlexMed Fourier Series Greek Health Professional Shortage Area Italian Lagrange multipliers London MD vs PhD MMI Montessori National Health Service Corps Pythagorean Theorem Python Shakespeare Step 2 TMDSAS Taylor Series Truss Analysis Zoom acids and bases active learning architecture argumentative writing art art and design schools art portfolios bacteriology bibliographies biomedicine brain teaser campus visits cantonese capacitors capital markets central limit theorem centrifugal force chemical engineering chess chromatography class participation climate change clinical experience community service constitutional law consulting cover letters curriculum dementia demonstrated interest dimensional analysis distance learning econometrics electric engineering electricity and magnetism escape velocity evolution executive function freewriting genomics harmonics health policy history of medicine history of science hybrid vehicles hydrophobic effect ideal gas law immunology induction infinite institutional actions integrated reasoning intermolecular forces intern investing investment banking lab reports linear maps mandarin chinese matrices mba medical physics meiosis microeconomics mitosis mnemonics music music theory nervous system neurology neuroscience object-oriented programming office hours operating systems

Related Content