Microsoft Excel 2013

MS Excel is a spreadsheet-based software developed by Microsoft that allows the user to store, organise, and analyse information and various types of data. Anyone can use excel for certain activities like keeping a budget, organising training logs, creating invoices, etc. Excel makes it easy to work with different kinds of data. 

Types of Cell References in Excel

A worksheet in Excel is made up of cells. These cells can be referenced by specifying the row value and the column value.

There are three types of cell references in Excel that you can use:

• Relative Cell References

• Absolute Cell References

• Mixed Cell References

Let us learn about them briefly:- 

1. Relative Cell Reference: This is the most widely used type of cell reference in formulas. Relative cell references are basic cell references that change and adjust when copied or when using AutoFill.

Relative Cell Reference 

2. Absolute Cell Reference:  An absolute cell reference is a cell reference in a spreadsheet application that remains constant even if the shape or size of the spreadsheet is changed, or the reference is moved or copied to another cell or sheet. 

Absolute Cell Reference

3. Mixed Cell Reference:  Mixed Reference is a type of Absolute reference in which either the column is made constant or the row is made constant. When we make any row or column other than the column name or row number does not change as we copy the formula to another cell. 

Mixed Cell Reference 

Difference between Absolute Cell Reference and Relative Cell Reference

Relative Cell References in Excel

In Excel, columns are represented Alphabetically and rows numerically. In Excel, a cell reference is a Relative reference. Relative References in Excel Formulas

• In Excel, if you want to use a formula in a particular cell and want to copy it into another cell then you should use a relative reference.

• When to Use Relative Cell References in Excel:

When we need to create a formula for a certain range and the formula needs to refer to a relative cell reference then we can use it.

Absolute Cell References in Excel

An absolute reference is a fixed reference in a formula. It will only refer to the specific row and columns. It won’t change when the formula is copied to another cell or while using AutoFill. By using the Dollar Sign ($) you can make any cell an absolute.

• Use of Dollar ($) Sign

When a Dollar Sign ($) is added in front of the row or column it means the cell’s values won’t change while copying it to the other cell. That makes a cell reference absolute.

• When to Use Absolute Cell References in Excel

When we don’t want to change the cell references while copying the formula then we can use absolute cell references.

When we have fixed values depending on what we want to use formulas.

Summary

MS Excel is spreadsheet-based software that is commonly used to record and analyse numerical data, irrespective of how small or large quantities of data may be. It doesn't matter, excel makes it easy for us. Excel is used by people dealing with complicated data.

A worksheet in Excel is made up of cells. These cells can be referenced by specifying the row value and the column value. The power of Excel lies in the fact that you can use these cell references in other cells when creating formulas.

Practice Questions

1. Which of the following is a type of cell reference?

• Mixed 

• Absolute

• Relative

• All of the above

2. ___ is a spreadsheet software used to record and analyse numerical data.

• MS Excel

• MS Word 

• MS Office

• MS Paint

Sample Solved Questions

1. What is the role and type of cell referencing in a spreadsheet?

Ans: Relative cell references: It does not contain dollar signs in a row or column, e.g., A2. Relative cell reference type in excel changes when a formula is copied or dragged to another cell. In Absolute cell references: Absolute Cell Reference contains dollar signs attached to each letter or number in a reference.

2. What is MS Excel used for?

Ans: In a worksheet, relative cell is provided by Microsoft Excel. MS Excel provides a relative cell reference which is powerful data visualisation and analysis software, which uses spreadsheets to store, organise, and track data sets with functions and formulas. Excel is used by marketers, accountants, data analysts, and other professionals.