• BUS 115 - Business Applications: Excel in Practice
  • Acknowledgements
  • Excel Overview
  • Time Saver
  • Auto Fill and Fill Series
  • Cell Referencing
  • Absolute and Relative Referencing
  • Navigation Shortcuts
  • Managing Cells
  • Sorting and Filtering
  • Hiding and Grouping
  • Paste Special
  • Named Ranges
  • Ranking
  • Rounding
  • Sum and Count
  • Managing Worksheets
  • Lookup Functions
  • Data Validation
  • IF Statements
  • Formula Errors
  • Text Functions
  • Text to Columns
  • Logic Functions 2
  • Date and Time
  • Conditional Formatting 1
  • Standards of Professionalism
  • Spreadsheet Formatting and Design
  • Basic Charts
  • Dashboards
  • Conditional Formatting 2
  • Spreadsheet Protection
  • Basic Pivot Tables
  • Advanced Pivot Tables and Slicers
  • Logic Functions 3
  • Advanced Lookup Functions
  • Loan Amortization Schedule
  • Advanced Charts
  • Macros 1
  • Macros 2
  • Macros 3
  • How to Use Search Engines
  • Sandbox
  • Author Biographies
  • Why the Standards of Professionalism?
  • Learning New Functions
  • Download
  • Translations
  • Cell Referencing

    We are going to learn about cell referencing. Cell referencing in Excel is when you go to a cell and type in a formula that refers to data in other cells, which makes it calculate the result automatically. One of the main reasons cell referencing is important to learn is because it directly connects to a concept called circular referencing; circular referencing is a problem caused by referencing cells in the wrong way.

    First, let's suppose that we have ten different shapes. The shapes could either be a rectangle or a triangle. We want to know the area of the shape if it’s a rectangle and the area of a shape if it's a triangle. We are given the base and the height for each shape. (See Figure 3.1)

    BUS115_image_3.1.png
    Figure 3.1

    Practice Spreadsheet

    Download this workbook for the chapter.

    Area of a Rectangle

    We start by calculating the area of the rectangle, and a triangle as well. To get the area of a rectangle, it’s simply the base multiplied by the height.

    1. Select an empty cell and type an equals symbol ( = ) to start the formula.
    2. Select the reference cell for the Base data (B4 in Figure 3.2).
    3. Type an asterisk symbol ( * ) for multiplication.
    4. Select the reference cell for the Height data (C4 in Figure 3.2).
    5. Press Enter to complete the cell’s formula (=B4*C4).
      BUS115_image_3.2.png
       Figure 3.2
       

    Area of a Triangle

    1. Select an empty cell and type an equals symbol ( = ) to start the formula.
    2. Select the reference cell for the Base data.
    3. Type an asterisk symbol ( * ) for multiplication.
    4. Select the reference cell for the Height data.
    5. Type a forward slash ( / ) for division, and type 2 to divide the height by half.
    6. Press Enter to complete the cell’s formula (=B4*C4/2).

    We can select both formulas and double-click the little box in the lower-right to drag the formulas all the way to the bottom. (See Figure 3.3)

    BUS115_image_3.3.png
    Figure 3.3
     

    Let's assume that the base on this example is 18, which is the same as the height (see Figure 3.4). If the Base cell’s data changes, the formula’s cell will recalculate the area of the rectangle because it is referencing the Base cell.

    BUS115_image_3.4.png
    Figure 3.4
     

    Area of a Square

    Another way to calculate the area of a shape is if it is a square. In this case, it’s 18 by 18 so the base and height are the same (see Figure 3.5). We can do that by just taking either the base or the height and squaring it.

    1. Select an empty cell and type an equals symbol ( = ) to start the formula.
    2. Select the reference cell for the Base data.
    3. Type a caret symbol ( ^ ) and 2 to square the base.
    4. Press Enter to complete the cell’s formula (=B4^2).
      BUS115_image_3.5.png
      Figure 3.5
     

    Circular Referencing

    Now let's talk about what a circular reference is by going through an example. If we want to calculate the height of the rectangle, and we know its area and base data, we could divide the area by the base. (See Figure 3.6)

    BUS115_image_3.6.png
    Figure 3.6
     

    However, when we press Enter, we get an error message: 

    “There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells.” 

    Whenever you see this warning or any type of warning in Excel where it doesn’t recognize your formula or see some kind of problem, you should never ignore it. (See Figure 3.7)

    BUS115_image_3.7.png
    Figure 3.7

    In this case, it has a circular reference. When we select OK, we will see a blue line showing us where the reference is circular. It is circular because our area data cell refers to the Base and Height cells, and our calculation for the Height cell refers to the result of the initial area formula, so it’s referencing itself. (See Figure 3.8)

    BUS115_image_3.8.png
    Figure 3.8
     

    This reasoning or logic that we have used is circular and Excel cannot compute it—no calculator could compute it. We will need to eliminate one of the formulas. We can only reference the area formula on the inserted base and height data, or the height based on the inserted area and base data. We cannot do both.

    If you see circular referencing or an error message, you can navigate to the Formulas tab, select Error Checking, and choose Circular References to view cells with an error. (See Figure 3.9)

    BUS115_image_3.9.jpg
    Figure 3.9
     

    It may help solve the problem if you receive a workbook with a circular reference from someone else, or if you save your work and return to it later. Understanding how to resolve circular references is a key skill as you work and create formulas throughout a workbook that reference other cells.

    Supplemental Resource

    This content is provided to you freely by BYU-I Books.

    Access it online or download it at https://books.byui.edu/bus_115_business_app/cell_referencing.