1
00:00:00,080 --> 00:00:04,384
A total that includes its own cell cannot be calculated normally.

2
00:00:04,384 --> 00:00:07,906
In this fictional kit count, three sessions contain fifteen,

3
00:00:07,906 --> 00:00:12,211
twenty-eight and seven kits, so the independent total is fifty.

4
00:00:12,211 --> 00:00:16,515
We will deliberately create a circular range in a disposable Excel

5
00:00:16,515 --> 00:00:20,820
copy, inspect the exact formula, exclude the total cell and prove

6
00:00:20,820 --> 00:00:22,776
that no source value changed.

7
00:00:22,776 --> 00:00:27,081
The web and desktop versions have different tracing controls, so the

8
00:00:27,081 --> 00:00:29,429
formula itself is our dependable evidence.

9
00:00:30,029 --> 00:00:34,242
Our fictional workshop counted fifteen kits in the morning, twenty-eight

10
00:00:34,242 --> 00:00:37,306
in the afternoon, and seven in the evening.

11
00:00:37,306 --> 00:00:39,603
A correct total must be fifty.

12
00:00:39,603 --> 00:00:43,050
Before using Excel's warning, calculate that answer independently:

13
00:00:43,050 --> 00:00:47,263
fifteen plus twenty-eight is forty-three, plus seven is fifty.

14
00:00:47,263 --> 00:00:51,476
A known answer is the best guard against repairing the warning

15
00:00:51,476 --> 00:00:53,391
while keeping the wrong number.

16
00:00:53,391 --> 00:00:56,072
This arithmetic check is independent of Excel.

17
00:00:56,072 --> 00:00:59,902
It prevents a warning disappearing while a session is accidentally

18
00:00:59,902 --> 00:01:01,434
omitted from the total.

19
00:01:02,034 --> 00:01:06,355
Open the practice CSV in a workbook copy and arrange the

20
00:01:06,355 --> 00:01:10,676
three numeric counts in B3, B4 and B5, with TOTAL in

21
00:01:10,676 --> 00:01:13,032
A6 and its formula in B6.

22
00:01:13,032 --> 00:01:18,139
A CSV does not preserve formulas, so type them in the workbook copy.

23
00:01:18,139 --> 00:01:22,853
Check those addresses before entering a bad formula; a one-row shift

24
00:01:22,853 --> 00:01:24,424
would change the demonstration.

25
00:01:24,424 --> 00:01:27,173
Keep the CSV original outside the copy.

26
00:01:27,173 --> 00:01:31,494
The demonstration deliberately creates a bad formula and should never be

27
00:01:31,494 --> 00:01:34,637
performed in an only copy of real work.

28
00:01:35,237 --> 00:01:37,636
In B6, enter =SUM(B3:B6).

29
00:01:37,636 --> 00:01:42,034
The range begins with the morning value and ends with B6,

30
00:01:42,034 --> 00:01:44,832
the same cell that holds the formula.

31
00:01:44,832 --> 00:01:48,031
That makes B6 depend on its own result.

32
00:01:48,031 --> 00:01:52,429
Excel may show a circular reference alert, an unexpected zero, a

33
00:01:52,429 --> 00:01:57,227
previous value, or a status indicator depending on version and calculation settings.

34
00:01:57,227 --> 00:02:01,625
Do not use any displayed zero as a real kit count.

35
00:02:01,625 --> 00:02:06,423
The self-reference is direct, so there is no need to speculate

36
00:02:06,423 --> 00:02:10,821
about a complex dependency chain or a macro in this example.

37
00:02:11,421 --> 00:02:14,134
Select B6 and read the formula bar.

38
00:02:14,134 --> 00:02:18,785
Highlight the range B3:B6 mentally or with Excel's reference coloring.

39
00:02:18,785 --> 00:02:20,723
The last address is B6.

40
00:02:20,723 --> 00:02:24,987
That is enough to explain the loop: Excel needs the total

41
00:02:24,987 --> 00:02:26,538
to compute the total.

42
00:02:26,538 --> 00:02:30,801
In Excel for the web, this direct inspection is more dependable

43
00:02:30,801 --> 00:02:33,515
than assuming a desktop Formulas menu exists.

44
00:02:33,515 --> 00:02:37,778
A formula may show a stale value under some calculation settings.

45
00:02:37,778 --> 00:02:42,817
Its displayed number alone does not tell you whether the dependency is valid.

46
00:02:43,417 --> 00:02:47,217
Microsoft documents Formulas, Error Checking, Circular References in desktop Excel.

47
00:02:47,217 --> 00:02:51,777
That menu can list the circular cell and help trace larger chains.

48
00:02:51,777 --> 00:02:55,956
Some Excel web builds do not expose the same auditing controls.

49
00:02:55,956 --> 00:03:00,136
If the menu is unavailable, do not invent it; use the

50
00:03:00,136 --> 00:03:04,316
known B6 formula in this practice file, or open a copy

51
00:03:04,316 --> 00:03:07,355
in desktop Excel when you need full tracing.

52
00:03:07,355 --> 00:03:12,295
If a product screenshot lacks the auditing menu, that is a version difference.

53
00:03:12,295 --> 00:03:17,235
The cell address and formula still expose the problem in this small case.

54
00:03:17,835 --> 00:03:20,291
Edit B6 to =SUM(B3:B5).

55
00:03:20,291 --> 00:03:24,795
Now the range stops at the last source value, evening session,

56
00:03:24,795 --> 00:03:27,661
and excludes the total row. Press Enter.

57
00:03:27,661 --> 00:03:32,574
The corrected formula is not just shorter: it removes the self-reference

58
00:03:32,574 --> 00:03:35,030
while preserving all three counted sessions.

59
00:03:35,030 --> 00:03:40,762
Avoid changing the kit numbers to force a desired total; they were already correct.

60
00:03:40,762 --> 00:03:43,219
The colon defines the whole interval.

61
00:03:43,219 --> 00:03:47,722
Moving its end from B6 to B5 changes which cells participate,

62
00:03:47,722 --> 00:03:49,769
not the three source counts.

63
00:03:50,369 --> 00:03:52,717
Read B6: it should show fifty.

64
00:03:52,717 --> 00:03:57,412
Then compare with the independent arithmetic, fifteen plus twenty-eight plus seven.

65
00:03:57,412 --> 00:04:02,107
Also inspect B3:B5 to confirm none of the inputs moved or

66
00:04:02,107 --> 00:04:04,455
changed while the formula was edited.

67
00:04:04,455 --> 00:04:08,759
If a circular warning remains, another formula or workbook may still

68
00:04:08,759 --> 00:04:13,062
contain a loop; this single corrected cell is not proof that

69
00:04:13,062 --> 00:04:15,019
every workbook issue is gone.

70
00:04:15,019 --> 00:04:17,366
Do not stop after seeing fifty.

71
00:04:17,366 --> 00:04:22,844
Comparing each input catches a shifted row or a value overwritten during the edit.

72
00:04:23,444 --> 00:04:27,685
Excel can allow iterative calculation for specific models, but enabling

73
00:04:27,685 --> 00:04:32,774
it is not the right repair for an accidentally self-including total.

74
00:04:32,774 --> 00:04:38,712
It changes calculation behavior across the workbook and may conceal the simple boundary mistake.

75
00:04:38,712 --> 00:04:43,377
Leave calculation settings alone for this exercise and make the formula

76
00:04:43,377 --> 00:04:45,922
reference only the intended input cells.

77
00:04:45,922 --> 00:04:50,163
Iteration belongs to intentional models that depend on previous passes.

78
00:04:50,163 --> 00:04:54,404
A simple sum of counted items has no such purpose.

79
00:04:55,004 --> 00:04:57,361
Not every circular reference is direct.

80
00:04:57,361 --> 00:05:02,862
One formula can point to a second cell that points back to the first.

81
00:05:02,862 --> 00:05:07,577
This lesson's formula-bar method proves the direct B6 example only.

82
00:05:07,577 --> 00:05:11,899
For a larger indirect loop, use the desktop tracing tools Microsoft

83
00:05:11,899 --> 00:05:14,649
documents or inspect dependencies on a copy.

84
00:05:14,649 --> 00:05:19,364
Do not claim that changing B6 will solve an unrelated chain elsewhere.

85
00:05:19,364 --> 00:05:23,293
Document the additional cell path before editing an unfamiliar workbook,

86
00:05:23,293 --> 00:05:27,222
and work in a copy to preserve an audit trail.

87
00:05:27,822 --> 00:05:33,633
The final practice sheet has the original three fictional session values and one total.

88
00:05:33,633 --> 00:05:38,614
B3 is fifteen, B4 is twenty-eight, B5 is seven, and B6

89
00:05:38,614 --> 00:05:41,104
is fifty with =SUM(B3:B5).

90
00:05:41,104 --> 00:05:44,425
No circular input is included in the range.

91
00:05:44,425 --> 00:05:48,990
Save the corrected copy and compare it with the answer check

92
00:05:48,990 --> 00:05:52,726
before applying a similar change to a real workbook.

93
00:05:52,726 --> 00:05:56,876
The downloadable practice and independent answer both record the four

94
00:05:56,876 --> 00:06:00,196
expected addresses so the fix is reproducible later.

95
00:06:00,796 --> 00:06:05,419
The corrected practice cell B6 contains equals SUM open parenthesis B3

96
00:06:05,419 --> 00:06:08,781
colon B5 close parenthesis, and it returns fifty.

97
00:06:08,781 --> 00:06:12,563
Morning remains fifteen, afternoon twenty-eight and evening seven.

98
00:06:12,563 --> 00:06:16,346
The total cell is outside its own input range.

99
00:06:16,346 --> 00:06:22,229
Compare all four cells with the answer check, then save only the corrected copy.

100
00:06:22,229 --> 00:06:26,852
If a warning remains elsewhere, do not claim this single formula

101
00:06:26,852 --> 00:06:28,953
fixed a different circular chain.

102
00:06:28,953 --> 00:06:33,576
The written guide links the official Excel explanation and practice file.