In data analytics, raw data is processed using various parameters and tools to reveal trends and information to help companies make better decisions.
Although there have been several new tools and programming languages in the market to help in data analytics, Excel still retains the number one spot. It is because excel comes in-built with bar charts, pie charts, and even pivot tables that help in data analytics and decision-making.
There are many functionalities in Excel like VLOOKUP, SUMIF, AVERAGEIF, etc. that can make the process of data analytics smoother.
However, the most basic and simple functionalities are Relative and Absolute Cell References.
What Are Relative Cell References?
A relative cell reference is the default value of any cell in a spreadsheet. It is a combination of rows and columns. For example, a single cell C2 is a relative cell reference.
Relative cell references are free and not locked. They correspond to the change in the location of a cell. All cells, unless otherwise specified, are relative cell references.
When copied to different cells, relative cell references change to correspond with the change in rows and columns.
For example, you create a sales invoice for a number of items sold. Column B contains a list of all items, column C contains the number of items sold, and column D has the rates for each item.
Using a simple formula =C2*D2, we can quickly get the correct answer. Once the formula is copied to the following rows, it automatically calculates the correct answer by corresponding to the change in rows.
What are Absolute Cell References?
An absolute cell reference means that the value of a cell is locked and cannot be changed when copied anywhere else in a workbook.
If you want the value in a particular cell in a workbook to be the same, you can lock the cell with an absolute reference.
Absolute cell references can be identified by the dollar ($) signs that appear in the formula. For example, if cell C3 is locked, the reference will be $C$3.
Here is an example.
You need to calculate sales tax for items in your store. In your Excel worksheet, cell C3 contains the tax rate. For calculating the tax of each item, in the formula for calculating tax, cell C3 is locked as $C$3.
No matter where the formula is copied in the workbook, the tax rate in C3 is absolute and can not be changed.
Difference Between Relative And Absolute Cell References
Relative cell references give an outcome that changes concerning the rows and columns when a formula is copied elsewhere in the worksheet.
On the other hand, absolute cell references remain constant as one or more of the values used in the formulas will remain constant, irrespective of where the formula is copied in a worksheet.
For example, you would use relative cell references when you want to calculate something for a range of items. You can input the formula in one cell and copy it to the others in the range. It would give accurate results for the entire range of items.
Use
Absolute cell references can be used to lock a particular cell in a workbook.
For instance, if you are calculating the tax on a range of products, you can make the cell containing the tax rate an absolute reference using $ ‘dollar’ signs. This will ensure that while calculating the tax amount for the entire range of items, the tax rate in the calculation remains the same.
All absolute cell references will have dollar signs to lock in the value. To make a complete reference into a relative reference, you need to remove the dollar signs from the formula.
A relative cell reference could be any basic formula in Excel.
For example, if you are calculating the total amount in an invoice, and the value of each line item is listed from cells C2 to C9, you would use the formula =SUM(C2:C9).
If you have a discounted value of each item in column D from cells D2 to D9, you can simply copy the SUM formula from column C to column D and automatically get the correct results for the formula =SUM(D2:D9).
Conclusion
Excel is an excellent tool for data analytics because it can be customized with functionalities and fields depending on the amount of data you have to analyze. Being proficient in Excel can help you get a head start in understanding how large amounts of data can be processed for easier understanding and analysis.
SkilloVilla’s Data Analytics Career Track can teach you the core concepts of Excel and Data Analytics. The curriculum has been designed by industry experts based on what is currently relevant in the market. The course can help you get expertise in excel, from the most basic to advanced functions, and help you start your career in data analytics and get placed in top companies in the industry.
