Excel Tips Tutorial: How to Enter Credit Card Numbers In Excel
Excel Tips Tutorial: How To Make Gantt Chart in Microsoft Excel
Excel Tips Tutorial: Pivot Tables
Excel For Noobs Tutorial: How to use IF function for logical calculation
Excel For Noobs Tutorial: How to use data filtering in MS Excel
Excel Tips Tutorial: How to Use Concatenate Function to Join Cells Together
Excel For Noobs Tutorial: How to create graphs in Excel
Excel Tips Tutorial: How to Insert Cells in Data Tables in MS ExceI
Excel Tips Tutorial: How to Convert Values From One Measurement System to Another
Excel Tips Tutorial: How to Convert Numbers to Text
Excel Tips For Noobs: How To Add Diagonal or Crossed Lines to a Cell
Excel Tips Tutorials: How to Make A Pie Chart in Microsoft Excel
Excel Tips: How to Solve Circularity Errors With Iterations
Excel Tips Tutorial: How To Use Vlookup in Microsoft Excel
Excel Tips Tutorial: How to use SUMIF, COUNTIF and AVERAGEIF Functions in Microsoft Excel
Excel Tips Tutorial: How to Use Conditional Format in Microsoft Excel
Excel Tips Tutorial: How to Connect Links to Excel Worksheets
Excel Tips Tutorial: How To Write Formulas In Excel
Excel Tips Tutorial: How to Find and Replace Values in Microsoft Excel
Excel Tips Tutorial: VBA Visual Basic for Application For Beginners in Microsoft Excel
Excel Tips Tutorial: How to Use Trim, Upper, Lower and Proper In Microsoft Excel
Excel Tips Tutorial: How to Convert a PDF File to Editable Excel File
Excel Tips Tutorial: How to Use Pivot Tables in Microsoft Excel
Excel Tips Tutorial: How to Find and Select Content or Cells in Excel
Excel Tips Tutorial: How to Merge Styles and Themes of Old and New Excel Versions 2003 2007 2010 2013
Excel Tips Tutorial: Microsoft Excel Keyboard Shortcuts
Excel Tips Tutorial: How to Draw a Line Through a Word in Excel - Strikethrough
Excel Tips Tutorial: Horizontal Text Alignment in Excel (General, Left, Center, Right, Fill, Justify, Center across Selection and Distributed)
Excel Tips Tutorial: Vertical Alignment in Excel (Top, Center, Bottom, Justify, and Distributed)
Excel Tips Tutorial: How to Display Text at a 45 Degree Angle (Diagonal Text)
Excel Tips Tutorial: How to add a Background Image (Picture) to an Excel Worksheet
Excel Tips Tutorial: How to Use Format Painter to Copy and Paste Cell Formats
Excel Tips Tutorial: How to Use Cell Styles to Format Cells
Excel Tips Tutorial: How to Change the Default Style of an Entire Excel Workbook Using Cell Styles
Excel Tips Tutorial: How to Create Your Own New Cell Style in Excel
Excel Tips Tutorial: Understanding and Applying Themes in Excel
Excel Tips Tutorial: How to Pin Important Excel File to the Recent Files List
Excel Tips Tutorial: How to Add Folders to the Favorites List in the Open Dialog Box
Excel Tips Tutorial: How to Save Excel Workbook Files
Excel Tips Tutorial: How to Change the Default File Type to Save Excel Workbooks
Excel Tips Tutorial: How to Open and Recover an Unsaved Excel File
Excel Tips Tutorial: Rules for Filenames in Microsoft Excel
Excel Tips Tutorial: How to Print Only an Excel Chart without Printing out the Entire Worksheet
Excel Tips Tutorial: How to Remove Formula Error-Checking Smart Tags
Excel Tips Tutorial: How to Stop Automatically Creating Calculated Columns in Excel Tables
How to Name an Excel Table
Excel 2013 Tutorial: How to Refer to a Named Cell as a Constant
Excel Concatenate Function Tutorial - How to Join Text in Excel
How to Use If Else If Function in Excel 2013 - Nested If Function in Microsoft Excel
Excel Worksheets Tutorial for Microsoft Excel 2013
Absolute, Relative and Mixed Cell Reference Excel Tutorial
Excel 2013 Tutorial Creating and Opening Workbooks in Excel 2013
Excel 2013 Tutorial The Function Library
How to Share Workbooks in Excel 2013 Tutorial
Save and Save As Excel 2013 Tutorial
How to Recover Unsaved Files in Excel 2013 with the Auto Saved Feature
How to Export Excel Files to PDF Other Different File Types
Basic Excel 2013 Functions Tutorial
Excel Tutorial How to Use Nested Functions
How to Use GoTo Special in Microsoft Excel 2013 Tutorial
Excel Page Layout Tutorial
Microsoft Excel Tutorial Page Breaks, Headers and Footers
Excel 2013 Tutorial How to Format Fonts in Excel
How to Create a Custom List in Excel Tutorial
Data Validation Microsoft Excel 2013 Tutorial - How to Restrict Entering Data in a Cell
Understanding Excel Charts Tutorial
How to Create a Basic Chart in Excel Step by Step Tutorial
How to Create and Use a Pivot Table in Microsoft Excel 2013 - Excel Pivot Table Tutorial
How to Pivot Data with Excel Pivot Tables Tutorial
How to Create a Macro in Microsoft Excel 2013
Microsoft Excel 2013 Worksheet Template Tutorial How to Create
Drop Down Lists Tutorial in Excel 2013
How to Reference Formulas and Data on other Excel Worksheets Worksheets Microsoft Excel 2013 Tutorial
Excel Data Forms Tutorial Microsoft Excel 2013
How to Insert Drawing Objects into your Excel Spreadsheets Microsoft Excel 2013 Tutorial
Excel LOOKUP Function Tutorial Microsoft Excel 2013
Excel Workbook and Worksheet Objects Tutorial
Excel Tutorial: Variables in Microsoft Excel Visual Basic Application (VBA)
Excel VBA Tutorial: If Then and Else Statement in Microsoft Excel (VBA) Visual Basic Application
Excel Events Tutorial: Visual Basic Application Evens in Microsoft Excel 2013
Excel For Noobs Tutorial: Cell Reference for Formulas and Functions
Cell Reference for Excel Formulas and Functions
Most of your formulas will refer to cells or cell ranges for their function arguments. By referring to cell you make your worksheet dynamic, efficient and less error prone. So what exactly is cell reference? When you refer to a cell for a function argument or a component of a formula the result of the formula or function will be dependent on the value entered into the cells that the formula or function refers to. If we were to enter =A1+B1 into a cell then the result would be the value inside Cell A1 plus the value entered into cell B1. If we changed the values entered into either cell the result of the formulas would also change since it is dependent on the values in the referred cells.
There are three different types of cell reference:
Relative cell reference: If you copy and paste a formula that uses a relative cell reference the cells that the formulas refers to will change relative to the cell it was pasted into. For example if we enter =SQRT(A1) into cell A2 and then copy and paste that formula into cell B2 it will then refer to cell B1 for its argument. Since we moved the formula one column to the right the cell that it referred to for its argument also moved one column to the right.
Locked cell reference: When we lock a cell reference this means that no matter where we copy and paste the formula or function the cell reference will not change. The way that we lock cell references is by inserting the $ symbol before the column letter and the row number. If we entered =SQRT($A$1) into a cell and then copy and pasted that function into another cell, it would still refer to cell A1 since the cell reference is lock by the $ symbols.
Mixed of locked and relative cell reference: We can also mix locked and relative. For example if we entered =SQRT($A1) into a cell and the copy and pasted the function into a different cell the column would always be locked in column A however the row would change relative to the row that the function is pasted. For example we could paste the function several columns to the right but remain in the same row it was initially inserted and it would still refer to cell A1. This is because we remained in the same row and only change columns. Since the column was locked the cell reference didn’t change even though we pasted it several column to the right. However, if we were to move down one row then the function would refer to cell A2 since the row number of the cell reference is relative.
In the screenshot below we are referring to the previous months balance in cell B19 and the adding the current months Income in cell C17 and subtracting the current month’s expenses in cell C18. If we use to autofill feature to complete this budget the cells that the formula refers to will be the same relative to the location of the cell they are pasted into.
Now look at the picture below where I used the Show Formulas feature. You can see that a formula is entered into all columns of the Balance row. The formulas are the same except that the cells the formulas refer to are relative to their location. The formula is could be stated as “one column to the left + two rows above – one row above”
If you take a look at the screen shot below you can see that we added another aspect to this budget and the formula. We add the factor that the account the balance is in is earning a small interest rate. We added the interest to the formula for the balance by multiplying the previous months balance by the monthly interest rate in cell A23. You can see that we lock the cell reference to cell A23 for the interest rate by entering $A$23. Now no matter what cell we insert this formula, it will always refer to cell A23 for the interest rate.
How to Change the Type of Cell Reference
You can change your type of cell reference manually by inserting or removing the $ symbols or you can use the cell reference keyboard shortcut by simply pressing the F4 key on your keyboard. First you point your mouse at the cell reference in your formula or function and then simply press F4. If you continuously press F4 you will cycle through all the different types of cell reference.
How to Refer to cell in a Different Worksheet
You are not limited to referring to only cells within a single worksheet, you can also refers to cells in different worksheets and even entirely different workbooks. The notation used to refer to cells in a different worksheet is SheetName!CellAddress. If you were working in a workbook and wanted to refer to cell B2 in a sheet named Sheet2 then you could simply select cell B2 in Sheet2 or you could manually enter your cell reference by typing Sheet2!B2. Your sheet name will always be separated by an exclamation point (!).
If your sheet name has a space in the name for example Sheet 2 then you must enclose the sheet name in single quotation marks. For example, ’Sheet 2’!B2.
How to Refer to Cells in a Different Excel Workbook
If you want to refer to a cell within a different workbook you would refer to the cell using the following format:
So if we wanted to refer to cell B2 in Sheet2 in a workbook named Data.xlsx then we would refer to that cell by entering [Data.xlsx]Sheet2!B2.
If there are spaces in the name of the other workbook or the worksheet in the other workbook then you will enter the workbook name and worksheet name into single quotations as follows:
So if you were referring to cell B2 in a sheet names Sheet2 in a workbook named Original Data then your would refer to that cell by entering the following:
It is easiest to refer to cells by select the cell reference with your mouse than it is to manually type the cell reference, especially when referring to a closed workbook that is save on your computer. This is because when the workbook is closed you must specify the path and folders that your workbook is saved in. For example, if we referred to the previous workbook except we didn’t have it open we would have to enter the following cell reference:
=’C:\My Documents\[Original Data]Sheet2’!B2
You can also refer to a workbook that is located on another computer if the computers are linked in a network of computers. For example if you were going to refer to cell B2 in a workbook names Original Data that was located in Documents and the computer was named Computer1 then you would refer to that cell by inserting the following:
Just remember that the path needs to be accurate or your cell reference won’t work.
As you can see cell reference can actually get pretty complicated, it is best to always enter your cell reference select the cell you are referring to with your mouse. Excel will automatically make the correct changes to the cell reference when you do things link change the name of a worksheet or workbook and when you open and close workbooks or change the location of the files on your computer.
When referring to a cell in a different workbook or a different worksheet Excel always uses the locked cell reference therefore if you copy and paste a formula that refers to another worksheet or workbook be sure to change the cell reference to relative.
Find the next tutorial in the links below.