Tutorial Playlist
68 Lessons1. Excel Tutorial
2. Excel Worksheet for Practice
3. Google Sheets Vs. Excel
4. Excel Shortcut Keys
5. Excel Keyboard Shortcuts
6. Excel AutoSum Shortcut
7. Redo Shortcut in Excel
8. Charts in Excel
9. Pivot Charts in Excel
10. Excel Sum Formula
11. Excel Percentage Formula
12. Excel Age Calculation Formula
13. Excel Range Formula
14. Round Off Formula in Excel
15. VLOOKUP Formula in Excel
16. Excel Transpose Formula
17. Average Equation in Excel
18. How to Use DATEDIF Formula in Excel
19. IRR Formula in Excel
20. Standard Deviation Formula
21. Excel Age Calculation Formula
22. Excel MAX Function
23. Excel LEFT Function
24. Excel RIGHT Function
25. Trim Function In Excel
26. LookUp function in Excel
27. Columns in Excel
28. How To Sort Data In Excel
29. How To Freeze Panes in Excel
30. Page Setup in Excel
31. How to Recover an Unsaved Excel File
32. Concatenate in Excel
33. Count In Excel
34. IF Condition In Excel
35. H LOOK UP in Excel
36. How Do You Move Columns in Excel
37. Split Cells in Excel
38. Remove Blank Rows in Excel
39. How To Lock Cells in Excel
40. Data Validation in Excel
41. How to Insert Checkbox in Excel
42. How To Highlight Duplicates in Excel
43. Fill Series in Excel
44. How to Create Excel Drop-Down List
45. What-If Analysis in Excel
46. How to Use SUMIFS Function in Excel
47. INDIRECT Function in Excel
48. Pivot Table in Excel
49. Slicers in Excel
50. How to Create a Dashboard in Excel
51. Excel Data Cleaning
52. Data Analysis In Excel
53. Goal Seek in Excel
54. Solver in Excel
55. Power Query in Excel
56. Macros In Excel
57. How To Make Graph in Excel
58. How To Make Histogram In Excel
59. How To Convert PDF to Excel Without Software
60. Barcode Font For Excel
61. Gantt Chart in Excel
62. Excel RANK function
63. AND Function in Excel
64. How to Calculate NPV
65. Format Painter in Excel
66. Count Colored Cells in Excel
67. Amortization Schedule in Excel
68. Master Cell References in Excel
Now Reading
Are you still manually typing in your data and struggling with your formulas in Excel? Don't worry we have all been there, and done that, until I discovered cell references in Excel. With a simple cell reference, you can dramatically change how you work and how efficiently you complete your tasks.
To put it simply, Excel can find the values or data that you want the calculation to compute when you use a cell reference in a formula. This data can be any cell or range of cells on a spreadsheet. Excel offers three different kinds of cell references: mixed, absolute, and relative.
It's incredible how even a small adjustment to your strategy may increase your efficiency and save you time, which is often what's taught in Excel courses. Trust me, once you start using cell references in your work, you'll never return to your old ways!
Suppose you have worked on Excel before you would already know that a spreadsheet is made up of cells. Each of those cells has a unique cell address or reference. When you click on a cell have you noticed how an alphabet or a combination of alphabets with the number is displayed on the side?
That is the cell reference in Excel and is used in most Excel formulas.
The alphabet denotes the column that shows working on and the number identifies the row. Suppose you want to choose the 8th row of Column C, the name box will display “C8”.
Typing in the cell address of the cell in which you would want to add data or formulas in the cell box will redirect you directly to that cell in the spreadsheet. Not just one single cell, you can select as many cells, entire rows or columns as well.
When referencing data on a worksheet, data in various sections of the worksheet, or data on multiple worksheets within the same workbook, a cell reference can be used in formulae.
A cell reference is referred to as "range" when it relates to more than one cell at a time. In Excel, a range reference is a collection of cells organized into rows or columns. For example, the range of cells A3 through C8 is indicated by the notation (=A3:C8). There is a colon used in between otherwise, Excel will return the standard error #VALUE.
In Excel, we can make a reference by following a few easy steps. They are as follows:
For example,
You want to add the data in B2 and C2. We can enter =B2+C2 in D2 or the formula bar while selecting the D2 cell where we want our total data.
On clicking Enter, the results will show.
Using the techniques below, we may modify the cell address in an existing formula.
Excel offers two different styles of self-reference, A1 reference, and R1C1 reference. Allow me to elaborate on each point.
We already discussed that each row and column is identified by a specific number or alphabet that is unique. These are termed the row heading and column heading which is primarily what A1 Style referencing refers to. So for example, a cell reference of D9 denotes that the particular cell lies in column D and row 9.
This is the default reference style available in Excel for its users.
Apart from the default reference, R1C1 is another prominent cell referencing style. R1C1 is a style in which a cell in row 1, column 1 is designated by a number, indicating that this style recognizes both rows and columns.
If you want to shift from the default A1 style of referencing in Excel to the R1C1 style:
Employing the correct addressing type will enable you to create complex formulas and improve your productivity while using Excel. Speaking of which, there are three main types of cell references in Excel: mixed, relative, and absolute cell references.
Here’s a detailed explanation of the same:
1. Relative cell reference
When you need to repeat computations across different cells or worksheets, you can use relative cell references. These references are altered when a formula is copied to another cell or worksheet.
They are identified by their lack of $ symbol in the row and column coordinates, such as A1 or B6:C12. Excel's cell addresses are all relative by default.
It's important to note that relative references are affected by the relative positions of rows and columns when they are moved or copied across numerous cells. Therefore, it's best to use relative cell references if you need to repeat the same computation over several rows or columns.
Let's assume that you are trying to multiply the data that is available in column B by 8 and see the result in column C.
For this, your formula will be, =B2*8
2. Absolute cell reference
A reference that has the dollar symbol ($) in the row or column coordinates, such as $A$1 or $A$1:$B$10, is considered absolute.
Adding the same formula to other cells does not modify the absolute cell reference. When you need to replicate a formula to other cells without altering references or when you need to do several computations with a value in a single cell, absolute addresses come in handy.
After you have multiplied the data, you now want to multiply the values in column B by the value in column C2, enter the formula in D2 =B2 * $C$2.
You can then drag the fill handle to duplicate the formula along the column.
When copying a formula, the relative reference (B2) will shift according to the relative location of the row, while the absolute reference ($C$2) will remain locked in place on the same cell.
3. Mixed cell reference
It combines absolute and relative references wherein, one relative and one absolute coordinate are present in a mixed cell reference. Dollar signs are appended to either the letter or the number in an Excel mixed reference excel, for instance, $C6 or C$6
In many cases, just one coordinate—a column or row—needs to be corrected.
Various complicated equations can be simply carried out with the help of mixed cell reference because it cuts down on the process of having to do each calculation manually and uses the available resources of both relative and absolute references in Excel.
For instance, you would enter the following formula in column B3, copy it down and to the right, and multiply a column of numbers (column A) by three distinct numerals (B2, C2, and D2).
=$A3*B$2
To ensure the formula always multiplies the initial values in column A, lock the column coordinate with $A3. For the multiplier, lock the row location in B$2 since it's in row 2. Since the multipliers are in different columns, keep the column coordinate relative and adjust the formula accordingly.
As a consequence, a single formula is used for all computations, and it appropriately adjusts for each row and column where it is used.
So far we understood the major styles and types of referencing in Excel. However, you might be required to change the cell reference and operate a different formula to accommodate changes.
This can also be done with a simple switch between the different reference types.
To switch between reference types:
You can do this or you can also try a different method. By selecting the cell with the formula you can either manually press delete on the dollar ($) symbol or add a dollar symbol ($).
So, to conclude this fun tutorial, I’m just going to assert the importance of cell references in Excel. As a key element for carrying out formulas and other functions with the data in Excel, you can use them to perform task charts and also carry out other relevant Excel commands. However, be mindful of what type of reference you use because it will depend on the category.
Employers of major companies all around the world are vastly recognising the need for employees who can organize, calculate and draw inferences from data quickly and accurately. So if you want to get ahead of others and enhance your skills, upGrad has over 500 courses on offer where you can learn from the best, get degrees and certificates and be a master in Excel and beyond.
There three types of cell references in Excel, are relative cell reference, absolute cell reference, or mixed cell reference. All these three types of references can be used for carrying out specific tasks and have unique characteristics when being copied or located elsewhere.
To set a cell reference in Excel:
Let's assume that you are trying to multiply the data that is available in column B by 8 and see the result in column C. The formula is =B2*8. This type of reference comes in handy when similar or repetitive calculations need to be done across various columns and rows in the spreadsheet
You may "lock" or fix the original cell reference to keep it the same when you replicate it by placing a dollar sign ($) in front of the cell and column references.
Cell values are the data entered into spreadsheet rows and columns, while cell references identify where the data is located. References can be specific or span a range of cells. Excel operations rely on these references and formulas to manipulate cell values.
You will find the F4 key on the top row of your keyboard.
Devesh
Passionate about Transforming Data into Actionable Insights through Analytics, with over 3+ years of experience working in Data Analytics, Data V…Read More
Talk to our experts. We’re available 24/7.
Indian Nationals
1800 210 2020
Foreign Nationals
+918045604032
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enrolling. upGrad does not make any representations regarding the recognition or equivalence of the credits or credentials awarded, unless otherwise expressly stated. Success depends on individual qualifications, experience, and efforts in seeking employment.
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enr...