1
00:00:01,070 --> 00:00:06,890
When you work with Excel formulas you might come across something that's called circular reference.

2
00:00:06,890 --> 00:00:09,470
So let's say you receive this file from your colleague.

3
00:00:09,500 --> 00:00:13,370
You open it and you immediately get this pop up message.

4
00:00:13,370 --> 00:00:20,300
There are one or more circular references where a formula refers to its own cell either directly or

5
00:00:20,390 --> 00:00:21,080
indirectly.

6
00:00:21,090 --> 00:00:24,570
So this might cause them to calculate incorrectly.

7
00:00:24,710 --> 00:00:30,340
Try removing or changing these references or moving the formulas to different cells.

8
00:00:30,360 --> 00:00:34,880
Now you're wondering how do I find that cell causing this problem?

9
00:00:34,880 --> 00:00:38,770
You have a lot of worksheets, where with that cell be?

10
00:00:38,810 --> 00:00:40,460
This is what you need to do.

11
00:00:40,490 --> 00:00:46,530
Go to the formulas tab, under formula auditing there's something called error checking.

12
00:00:46,670 --> 00:00:53,570
Click on it and you see this circular reference and here it tells you the sheet name as well as the

13
00:00:53,570 --> 00:00:57,350
cell that's causing this circular reference.

14
00:00:57,410 --> 00:01:00,620
Click on it and it takes you directly there.

15
00:01:00,740 --> 00:01:03,150
So this sheet is called "circular ref"

16
00:01:03,230 --> 00:01:06,700
The cell is B5 and why do we get this?

17
00:01:06,710 --> 00:01:09,480
Can you spot the problem in the formula?

18
00:01:09,710 --> 00:01:15,530
It's adding the result of the formula to itself. So that's a circular reference.

19
00:01:15,530 --> 00:01:20,890
It's a mistake in this case I just need "=B3+B4" and press enter.

20
00:01:20,890 --> 00:01:27,560
So once that's corrected. When I go back to error checking, circular reference is grayed out.

21
00:01:27,560 --> 00:01:35,060
So just keep this in mind that you don't by mistake include the cell that you have the formula in, in your calculation

22
00:01:35,060 --> 00:01:39,110
as well. Because that's going to cause this circular reference.