1
00:00:00,080 --> 00:00:04,248
Here is a small order list that looks usable at first glance.

2
00:00:04,248 --> 00:00:08,068
But Maya has extra spaces, the category labels disagree, dates and

3
00:00:08,068 --> 00:00:12,236
amounts arrived as text, and order O one oh three appears twice.

4
00:00:12,236 --> 00:00:14,320
Every name and order is fictional.

5
00:00:14,320 --> 00:00:17,793
We will clean a working copy, then prove what changed.

6
00:00:18,393 --> 00:00:19,790
First, save a copy.

7
00:00:19,790 --> 00:00:23,631
The Before tab in the download is the untouched audit trail.

8
00:00:23,631 --> 00:00:25,377
It has nine data rows.

9
00:00:25,377 --> 00:00:29,218
If a conversion or duplicate decision goes wrong, you need a

10
00:00:29,218 --> 00:00:30,965
reliable place to compare against.

11
00:00:30,965 --> 00:00:34,806
I make a working copy before editing any field, and I

12
00:00:34,806 --> 00:00:36,552
record the starting row count.

13
00:00:37,152 --> 00:00:40,615
One row should represent one order, with six clear headers.

14
00:00:40,615 --> 00:00:44,424
Select a cell in the working range, choose Home, Format as

15
00:00:44,424 --> 00:00:48,926
Table, confirm the full range, and check that the first row contains headers.

16
00:00:48,926 --> 00:00:53,774
A Table gives us filter controls and makes the working area easier to inspect.

17
00:00:53,774 --> 00:00:56,544
It does not clean the values for us.

18
00:00:57,144 --> 00:00:59,515
Before changing anything, scan each column.

19
00:00:59,515 --> 00:01:02,281
Maya Stone has spaces around the name.

20
00:01:02,281 --> 00:01:05,837
Workshop appears with different capitalization and a trailing space.

21
00:01:05,837 --> 00:01:10,184
Order dates use several text patterns, and some amounts include a

22
00:01:10,184 --> 00:01:12,555
dollar sign while others do not.

23
00:01:12,555 --> 00:01:18,087
These are visible examples, but a real list may contain less obvious problems too.

24
00:01:18,687 --> 00:01:22,974
For ordinary leading, trailing, or repeated spaces, use a helper column

25
00:01:22,974 --> 00:01:27,261
with TRIM on a cell, fill it down, inspect the results,

26
00:01:27,261 --> 00:01:29,990
then paste values back only when satisfied.

27
00:01:29,990 --> 00:01:33,108
In this demo, Maya's surrounding spaces disappear.

28
00:01:33,108 --> 00:01:37,006
TRIM does not remove every kind of invisible space, including

29
00:01:37,006 --> 00:01:40,903
nonbreaking spaces, so a stubborn value needs a closer look.

30
00:01:41,503 --> 00:01:46,519
Cleaning text is not permission to rewrite people's names or notes automatically.

31
00:01:46,519 --> 00:01:50,764
The sample changes ALMA CHEN to Alma Chen because this fictional

32
00:01:50,764 --> 00:01:52,307
record has been reviewed.

33
00:01:52,307 --> 00:01:55,780
On a real list, names can have deliberate capitalization.

34
00:01:55,780 --> 00:02:00,024
Check the source or ask the owner before changing a value

35
00:02:00,024 --> 00:02:01,568
whose meaning is uncertain.

36
00:02:02,168 --> 00:02:05,724
Next, make a small mapping for known category variants.

37
00:02:05,724 --> 00:02:09,675
Lowercase workshop and Workshop with a space both become Workshop.

38
00:02:09,675 --> 00:02:14,021
Lowercase consulting becomes Consulting, and training with a space becomes Training.

39
00:02:14,021 --> 00:02:17,577
This is a controlled choice for three known labels.

40
00:02:17,577 --> 00:02:21,923
Do not silently force an unfamiliar category into one of them.

41
00:02:22,523 --> 00:02:25,324
Now look at the order-date column.

42
00:02:25,324 --> 00:02:29,724
The Before sheet contains strings such as 2026 slash 10 slash

43
00:02:29,724 --> 00:02:31,724
07 and Oct 09, 2026.

44
00:02:31,724 --> 00:02:36,525
A display format alone does not turn text into a date value.

45
00:02:36,525 --> 00:02:40,926
Convert only dates whose meaning is clear, then check the resulting

46
00:02:40,926 --> 00:02:43,726
day, month, and year in several rows.

47
00:02:44,326 --> 00:02:48,189
Excel can use DATEVALUE for many text dates, but parsing can

48
00:02:48,189 --> 00:02:50,647
depend on the text and your locale.

49
00:02:50,647 --> 00:02:55,564
That is why this sample uses unambiguous year-first values or a written month.

50
00:02:55,564 --> 00:02:59,427
In the Cleaned tab, the dates are actual date values displayed

51
00:02:59,427 --> 00:03:00,831
as year, month, day.

52
00:03:00,831 --> 00:03:05,748
If a result shifts a month, stop and fix the interpretation before replacing originals.

53
00:03:06,348 --> 00:03:08,802
The amount column has the same issue.

54
00:03:08,802 --> 00:03:12,658
One cell reads dollar one hundred twenty as text, another reads

55
00:03:12,658 --> 00:03:17,215
eighty-five without a symbol, and another has spaces around two hundred forty.

56
00:03:17,215 --> 00:03:20,020
A sum of text cells can be misleading.

57
00:03:20,020 --> 00:03:24,227
Convert each amount to a number, then apply a currency display format.

58
00:03:24,227 --> 00:03:27,031
Formatting is the last step, not the conversion.

59
00:03:27,631 --> 00:03:31,447
In the Cleaned tab, the amount cells are numeric, so the

60
00:03:31,447 --> 00:03:34,221
reviewed eight rows total one thousand fifteen dollars.

61
00:03:34,221 --> 00:03:38,037
Look at several examples: Maya is one hundred twenty, Alma is

62
00:03:38,037 --> 00:03:41,158
two hundred forty, and Sam is one hundred sixty.

63
00:03:41,158 --> 00:03:44,974
If a value becomes one hundred times larger or smaller, you

64
00:03:44,974 --> 00:03:47,748
have a parsing problem, not a formatting problem.

65
00:03:48,348 --> 00:03:52,411
Order O one oh three appears twice in the Before tab.

66
00:03:52,411 --> 00:03:57,211
Matching order IDs alone do not prove that the full row is redundant.

67
00:03:57,211 --> 00:04:01,274
Compare all six fields: the contact, category, date text, amount text,

68
00:04:01,274 --> 00:04:02,751
and note match here.

69
00:04:02,751 --> 00:04:06,444
This one is a reviewed duplicate in our fictional list.

70
00:04:06,444 --> 00:04:10,506
A repeated ID with different details would need investigation, not deletion.

71
00:04:11,106 --> 00:04:15,111
Excel's Remove Duplicates tool works from the columns you select.

72
00:04:15,111 --> 00:04:19,116
If you select only Order ID, Excel can remove an entire

73
00:04:19,116 --> 00:04:21,665
row even when its other fields differ.

74
00:04:21,665 --> 00:04:25,306
For this particular example, select all six relevant columns after

75
00:04:25,306 --> 00:04:28,219
checking the two O one oh three records.

76
00:04:28,219 --> 00:04:32,588
Keep the Before tab so you can undo or reconstruct the decision.

77
00:04:33,188 --> 00:04:36,949
After that review, remove the second O one oh three row

78
00:04:36,949 --> 00:04:38,317
from the working table.

79
00:04:38,317 --> 00:04:42,420
The Cleaned tab shows the intended result: Alma's order appears once.

80
00:04:42,420 --> 00:04:47,548
Do not treat the tool's message as proof that every other row is correct.

81
00:04:47,548 --> 00:04:52,334
It only tells you what matched the selected columns under Excel's comparison rules.

82
00:04:52,934 --> 00:04:54,626
Now compare the record counts.

83
00:04:54,626 --> 00:04:57,333
Before has nine rows and Cleaned has eight.

84
00:04:57,333 --> 00:05:01,392
That difference of one is expected because we approved one duplicate removal.

85
00:05:01,392 --> 00:05:05,114
If the count fell by two, or did not change, pause.

86
00:05:05,114 --> 00:05:08,835
The Read Me tab gives the same checks so you can

87
00:05:08,835 --> 00:05:11,880
follow them while practicing with your own working copy.

88
00:05:12,480 --> 00:05:14,603
We can also explain the total.

89
00:05:14,603 --> 00:05:19,556
Interpreting the nine original amount strings gives one thousand two hundred fifty-five dollars.

90
00:05:19,556 --> 00:05:22,740
The duplicate Alma row is two hundred forty dollars.

91
00:05:22,740 --> 00:05:26,278
Subtract it and the cleaned total is one thousand fifteen.

92
00:05:26,278 --> 00:05:30,170
That arithmetic is a useful check, but it does not replace

93
00:05:30,170 --> 00:05:33,354
a row-by-row review of the source values.

94
00:05:33,954 --> 00:05:38,221
Before calling the work finished, look for anything the five steps

95
00:05:38,221 --> 00:05:41,713
missed: blank required fields, unusual categories, impossible dates, or

96
00:05:41,713 --> 00:05:43,653
a number that changed meaning.

97
00:05:43,653 --> 00:05:46,757
Excel will not reliably identify every business error.

98
00:05:46,757 --> 00:05:51,800
In this small practice file, the checks are simple enough to do manually.

99
00:05:51,800 --> 00:05:55,680
A larger list needs documented rules and a review sample.

100
00:05:56,280 --> 00:05:59,958
Download the workbook with Before, Cleaned, and Read Me tabs.

101
00:05:59,958 --> 00:06:04,004
Try the workflow on a separate copy, compare your result to

102
00:06:04,004 --> 00:06:07,314
the reviewed example, and explain every row you remove.

103
00:06:07,314 --> 00:06:12,463
The habit is repeatable: preserve the source, clean known problems, then reconcile what changed.

104
00:06:12,463 --> 00:06:16,509
That is more dependable than clicking a single cleanup command and

105
00:06:16,509 --> 00:06:17,980
hoping for the best.