Subscribe for access to this FREE e-learning mini-course!


Your First Name:


Your Surname:


Your Email Address:



Subscribe
Close


Small logo
Hamburger menu
Menu
bookings:   0333 6000 111
service:       0333 6000 555
    or           
Free Video Tutorials : Excel Formulas
Free Video Tutorials : Excel Formulas
Home Quick Course Finder Microsoft Software Microsoft Excel Excel Resources
What is a Circular Reference Warning in Excel?
Search

EXCEL FORMULAS: CIRCULAR REFERENCE WARNING

See what a Circular Reference warning means and how to locate it in your Excel spreadsheet

Background information and resources for Excel courses that we hope you find useful

If you are creating a formula which refers to the same cell that you are currently working on (or refers to it indirectly via another cell), you will get a circular reference warning.

Watch our video to find out What is a Circular Reference Warning in Microsoft Excel?

WHAT IS A CIRCULAR REFERENCE WARNING AND WHY DO WE GET IT?

A circular reference is an iteration that in theory, could go on forever. If you are trying to create a formula by using the value in its own cell to calculate the answer, you will get this error. You can compare it to a dog chasing its own tail!

Take it in a more Excel related example. Click into cell A1 and type in the formula:
=A1+10

Since you are already in A1, it will keep trying to add 10 to the current value and go on adding 10 indefinitely. This is when the circular reference warning appears.

If you refer to the cell you are using in a formula directly or indirectly, it will continually try to calculate with each new value you get, so generally, this is not a good idea.

If you are editing a formula, you might inadvertently create a circular reference. Excel doesn't immediately tell you where this is located so even though the formula you are working on looks legitimate, it may indirectly refer back to itself if you were able to trace through the steps of the formula. If you do get this error and you don't know where it came from, you can locate the cell of the circular references within a sheet, by using the Formulas tab and the Error checking menu.

Excel Formulas: Circular reference warning

If you are editing a formula, you might inadvertently create a circular reference. Excel doesn't immediately tell you where this is located. You can locate circular references within a sheet, by using the Formulas tab.

You may also see the formula auditing arrows appear on the screen. These can be turned off by clicking the Remove Arrows button on the Formulas tab.

Excel Formulas: Circular reference warning

CREATING A CIRCULAR REFERENCE

There are occasions where you may want to create a circular reference. One scenario could be to create an automatic date/time stamp.

If you want to turn circular references on, open the Excel Options and enable iterative calculation from the Formulas category of tools.

Creating a Circular Reference
WE HOPE YOU FOUND THIS USEFUL

Want to see how we can really help you?

LINKS TO MORE ...

Main Excel Resources Page

Excel Resources

Video tutorials & articles, and templates to help you work better in Excel

      Click to view

 

Video Tutorials - Excel Formulas

Video Tutorials - Excel Formulas

Learn and work with new Excel formulas to help create effective, automated spreadsheets

Click to view

Excel Formulas: Calculate difference in dates

Excel Formulas: Calculate difference in dates

Learn how to calculate the difference between two dates in your Excel spreadsheet in a few easy steps

      Click to read more

 

Excel Formulas: Why a formula doesn't work

Excel Formulas: Why a formula doesn't work

Find out why your formula might not work in Microsoft Excel and how to fix it

      Click to read more

Excel Formulas: How to work out gross profit

Excel Formulas: How to work out gross profit

Find out how to use a formula to work out gross profit in Microsoft Excel

      Click to read more

 

Excel Formulas: The DATEDIF function

Excel Formulas: The DATEDIF function

Find out what the DATEDIF function is and how to use it in Microsoft Excel

      Click to read more

GET IN TOUCH

Call us on:
0333 6000 111

   You can Email us direct (You'll usually get a response within 2-3 working hours)
   Or fill out the details below and we'll be in touch very soon:
SEND MESSAGE

WHAT IS A CIRCULAR REFERENCE WARNING IN EXCEL?

We can enhance your skills with courses for Microsoft Excel spreadsheets at any of our twelve training centres UK wide, or we can come to your offices: if you have groups of three or more staff who need training, this is a very convenient, flexible and cost effective option. Moreover, most of our excel courses are 1 day, which keeps "out of office" periods for attendees to a minimum.

We have been delivering Microsoft Excel course all over the UK since 1995 and have continually developed our philosophy of high quality, flexible and results-driven provision of courses for Excel to ensure constantly improving standards of quality.   So you can be confident that we have the expertise, and the experience, to give you a great return on your investment in Free Video Tutorials : Excel Formulas.

Why not contact us today to see how we can help with providing your MS Excel classes ?


Stay in touch

Stay In Touch!

Google+
LinkedIn
Twitter
FaceBook
YouTube

Contact Details

Activia Training

Locations Address: Head Office, Regus House,
268 Bath Road
Slough, Berkshire SL1 4DX

Phone Sales:           0333 6000 111

Service:       0333 6000 555

Accounts:   0333 6000 777



Back to top