What is Relative Cell Reference in Excel
There are three main cell references in Excel, viz. Relative, Absolute and Mixed. This article will help you understand what is relative cell reference in Excel.
In Excel, data is stored in a worksheet containing rows and columns and the intersection of a row and column is a cell. Rows are represented by numbers and columns by alphabets. A cell reference or cell address is a combination of the row number and column letter that identifies the cell, for e.g. A1, B2 etc. Cell references help to identify the data present in the specified cells. As shown in the figure below, A1 represents column A , row 1, and B2 represents column B, row 2.
WHAT IS RELATIVE CELL REFERENCE?
Relative cell reference is based on the relative position of the cell containing the formula and the cell/data it refers to. If the position of the cell containing the formula changes, its reference is also changed. If the formula is copied in rows or columns, the reference automatically changes. By default, all formulas use relative reference.
Let us understand relative cell reference with an example:
The spreadsheet below shows the marks of 5 students in 3 subjects. We need to calculate the total marks for each student.
To calculate the total mark for the first student, Mark, use the formula in cell E3 as
=B3+C3+D3
The following output is obtained
Since this calculation needs to be repeated for all other students, use autofill option on cell E3
Click on the formula tab in the ribbon and then on Show Formulas. You will see the following output which shows that cell references have changed acordingly:
Relative cell references are particularly useful when a calculation needs to be repeated across cells. We will learn Absolute cell reference in our next blog.
Explore additional insights like these and enhance your Excel proficiency with comprehensive knowledge from the book Excel Basics to Advanced.
Enhance your Excel skills by exploring MS Excel Blogs for more valuable tips.
Chandraish Sinha is the founder and President of Ohio Computer Academy, a company dedicated to providing IT education. An enthusiastic IT trainer, Chandraish embodies his company’s motto: Inspire, Educate & Evolve.
He has 20+ years of experience in Information Technology. He is an accomplished author and has published 11 books covering Business Intelligence related topics such as, Tableau, Power BI and Qlik. Checkout his Amazon Author profile.
His latest book Excel Basics to Advanced covers all the aspects of MS Excel and provides exercises for self-learning.
Similarly, his recent book, Dashboarding with Tableau, covers all the features in Tableau and includes exercises for self-learning.
He has implemented IT solutions in various domains viz. Pharmaceutical, Healthcare, Telecom, Financial and Retail.
He blogs regularly on various IT topics. Check them out in the links given below:
