Excel Tutorial For Beginners



Microsoft Excel Video Tutorials For Beginners

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: Essentials of Formulas and Functions in Excel

Excel For Noobs Tutorial: Excel Operators and Formulas

Excel For Noobs Tutorial: Order of Operations Operator Precedence (PEDMAS)

Excel For Noobs Tutorial: Worksheet Functions and Formulas

Excel For Noobs Tutorial: How to Insert Functions in Excel (Manual, AutoComplete, Function Library, Insert Function Dialog Box)

Excel For Noobs Tutorial: How to Use Functions and Cell Range Names

Excel For Noobs Tutorial: How to Search for and Insert Functions Using the Insert Function Dialog Box

Excel For Noobs Tutorial: The Insert Function and Function Arguments Dialog Boxes

Excel For Noobs Tutorial: Cell Reference for Formulas and Functions

Excel For Noobs Tutorial: Formulas and Funtions in the Tables Total Row

Excel For Noobs Tutorial: Excel Tables Formula Cell Reference

Excel For Noobs Tutorial: Circular Reference and Enabling Iterations to Fix Circular Reference Formulas

Excel For Noobs Tutorial: Excel Formula Errors

Excel For Noobs Tutorial: How to Turn Off Automatic Calculations for Excel Formulas and Functions

Excel For Noobs Tutorial: How to Define Cell Names and Refer to Named Cells in Formulas and Functions

Excel For Noobs Tutorial: How to Name Formulas in Excel 2013 Tutorial

Excel For Noobs Tutorial: Cell Range Names References and Intersects


Listen to a Bestseller for $7.49 at audible.com

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.
    Excel for Beginners Formulas and Function s Cell Reference Tutorial


    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”

    Excel 2013 tutorial formulas functions cell reference


    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.

    excel 2013 formulas and functions cell reference tutorial


    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:

    =[WorkbookName]SheetName!CellAddress

    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:

    =’[Workbook Name]Sheet2’!CellAddress

    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:

    =’[Original Data]Sheet2’!B2

    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:

    =’\\Computer1\C:\Documents\[Original Data]Sheet2’!B2

    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.


    Excel For Noobs Tutorial: Essentials of Formulas and Functions in Excel

    Excel For Noobs Tutorial: Excel Operators and Formulas

    Excel For Noobs Tutorial: Order of Operations Operator Precedence (PEDMAS)

    Excel For Noobs Tutorial: Worksheet Functions and Formulas

    Excel For Noobs Tutorial: How to Insert Functions in Excel (Manual, AutoComplete, Function Library, Insert Function Dialog Box)

    Excel For Noobs Tutorial: How to Use Functions and Cell Range Names

    Excel For Noobs Tutorial: How to Search for and Insert Functions Using the Insert Function Dialog Box

    Excel For Noobs Tutorial: The Insert Function and Function Arguments Dialog Boxes

    Excel For Noobs Tutorial: Cell Reference for Formulas and Functions

    Excel For Noobs Tutorial: Formulas and Funtions in the Tables Total Row

    Excel For Noobs Tutorial: Excel Tables Formula Cell Reference

    Excel For Noobs Tutorial: Circular Reference and Enabling Iterations to Fix Circular Reference Formulas

    Excel For Noobs Tutorial: Excel Formula Errors

    Excel For Noobs Tutorial: How to Turn Off Automatic Calculations for Excel Formulas and Functions

    Excel For Noobs Tutorial: How to Define Cell Names and Refer to Named Cells in Formulas and Functions

    Excel For Noobs Tutorial: How to Name Formulas in Excel 2013 Tutorial

    Excel For Noobs Tutorial: Cell Range Names References and Intersects