1
00:00:00,080 --> 00:00:04,044
You change a quantity in Excel, but a correct formula still

2
00:00:04,044 --> 00:00:05,485
displays the old number.

3
00:00:05,485 --> 00:00:07,647
Manual calculation could be the cause.

4
00:00:07,647 --> 00:00:11,610
In our fictional order workbook, changing one quantity from two to

5
00:00:11,610 --> 00:00:15,934
three should raise the grand total from one hundred thirty-eight dollars

6
00:00:15,934 --> 00:00:17,736
to one hundred sixty-three.

7
00:00:17,736 --> 00:00:21,339
We will test that exact result, then restore automatic calculation.

8
00:00:21,939 --> 00:00:24,977
Download the practice workbook and open the Orders sheet.

9
00:00:24,977 --> 00:00:29,027
Column B holds quantities, C holds unit prices, and D holds formulas.

10
00:00:29,027 --> 00:00:32,740
The first line is KIT dash one oh one: two units

11
00:00:32,740 --> 00:00:34,765
at twenty-five dollars, for fifty.

12
00:00:34,765 --> 00:00:37,803
The other lines are forty-eight and forty dollars.

13
00:00:37,803 --> 00:00:41,178
Their sum in D eight is one hundred thirty-eight.

14
00:00:41,178 --> 00:00:42,866
Work in your own copy.

15
00:00:43,466 --> 00:00:46,363
Select D five and inspect the formula bar.

16
00:00:46,363 --> 00:00:49,622
It should contain equals B five times C five.

17
00:00:49,622 --> 00:00:53,967
D eight should contain equals SUM of D five through D seven.

18
00:00:53,967 --> 00:00:57,951
If you see typed text or an error instead, this specific

19
00:00:57,951 --> 00:01:00,847
Manual mode fix may not explain your problem.

20
00:01:00,847 --> 00:01:04,107
Do not replace the formulas with hard-coded totals.

21
00:01:04,707 --> 00:01:09,410
In Excel for the web, open Formulas, then Calculation Options.

22
00:01:09,410 --> 00:01:14,113
Microsoft lists Automatic, Automatic Except for Data Tables, and Manual.

23
00:01:14,113 --> 00:01:16,465
Confirm Manual before this exercise.

24
00:01:16,465 --> 00:01:21,638
In desktop Excel, use Formulas, Calculation Options, or File, Options, Formulas.

25
00:01:21,638 --> 00:01:28,223
A desktop setting change affects other open workbooks, so check those before switching modes.

26
00:01:28,823 --> 00:01:31,357
Change B five from two to three.

27
00:01:31,357 --> 00:01:35,339
In Manual mode, D five may still display fifty dollars and

28
00:01:35,339 --> 00:01:38,598
D eight one hundred thirty-eight until calculation runs.

29
00:01:38,598 --> 00:01:40,046
Those are stale values.

30
00:01:40,046 --> 00:01:44,028
If Excel recalculates your first edit after importing the file, confirm

31
00:01:44,028 --> 00:01:47,287
Manual in the menu and try another harmless edit.

32
00:01:47,287 --> 00:01:51,631
The correct answers for B five equals three are seventy-five and

33
00:01:51,631 --> 00:01:53,441
one hundred sixty-three dollars.

34
00:01:54,041 --> 00:01:57,931
In Excel for the web, choose Calculate Workbook on the Formulas

35
00:01:57,931 --> 00:02:01,114
tab; in a narrow window, check the More menu.

36
00:02:01,114 --> 00:02:04,651
In desktop Excel, use Calculate Now or press F nine.

37
00:02:04,651 --> 00:02:07,834
Then inspect both results, not just the grand total.

38
00:02:07,834 --> 00:02:12,785
D five must be seventy-five dollars and D eight one hundred sixty-three.

39
00:02:12,785 --> 00:02:15,968
If they differ, inspect the formulas and inputs again.

40
00:02:16,568 --> 00:02:21,957
If you want formulas to update when inputs change, choose Automatic under Calculation Options.

41
00:02:21,957 --> 00:02:25,806
Excel for the web applies that choice to this workbook.

42
00:02:25,806 --> 00:02:29,655
Desktop Excel applies a mode change to all open workbooks.

43
00:02:29,655 --> 00:02:33,890
Test one more harmless quantity edit in your practice copy and

44
00:02:33,890 --> 00:02:37,354
confirm the dependent values update without clicking Calculate Workbook.

45
00:02:37,354 --> 00:02:41,588
Manual mode can be intentional in large workbooks, so coordinate before

46
00:02:41,588 --> 00:02:43,128
changing a shared workflow.

47
00:02:43,728 --> 00:02:47,433
The workbook and independent answer check are below the video.

48
00:02:47,433 --> 00:02:51,880
The initial total is one hundred thirty-eight dollars; after changing B

49
00:02:51,880 --> 00:02:55,956
five to three and recalculating, it is one hundred sixty-three.

50
00:02:55,956 --> 00:03:00,032
If your formula is displayed as literal text or returns an

51
00:03:00,032 --> 00:03:02,255
error, use a separate formula diagnosis.

52
00:03:02,255 --> 00:03:06,331
This test solves the stale-result case caused by Manual calculation.