Remove duplicates in Excel

Remove Duplicates lives at Data ▸ Remove Duplicates (Alt, A, M). It scans the selected range and deletes every row that repeats an earlier one. Everything about the dialog comes down to one set of checkboxes, the ones that decide which columns define "a duplicate." Tick only OrderID, and two rows with the same order ID count as duplicates even if every other field differs, so the later one is destroyed. Tick OrderID and Date, and two rows are duplicates only if both match. Getting this wrong is silent and permanent. Tick too few columns and you delete rows that were legitimately distinct. Tick too many and true duplicates survive. Always run it on a copy of the data, because it deletes in place with no undo once the file is closed. And always decide consciously which columns make up identity before you click OK. For a non-destructive first look, use Conditional Formatting ▸ Highlight Duplicate Values, eyeball what's about to die, and then remove.

There's a subtlety worth taking in here. Remove Duplicates keeps the first occurrence and removes the later ones. So if the rows carry different data in the columns you didn't tick, the copy that survives depends on sort order. When the surviving copy matters, sort on purpose first. Sorting newest-first, for example, keeps the latest.

How does Text to Columns work?

Text to Columns (Data ▸ Text to Columns, Alt, A, E) splits one column into several, in one of two modes:

• Delimited splits on a character: comma, tab, semicolon, space or a custom character. A cell holding Ashok Traders,Maharashtra,27AABCU9603R1ZM,85000 split on the comma becomes four columns. You can treat consecutive delimiters as one, which helps with space-padded data. You can also set a text qualifier (") so a comma inside quotes isn't treated as a split.
• Fixed width splits at character positions you choose. It's for legacy reports where each field occupies a fixed number of columns, like 2024-04-01BharatSteel 0000145000. You click the ruler to place the break lines.

Text to Columns has a second use that matters more than it looks: coercion. Run a single column through it and click Finish, with the column itself as the destination, and Excel has to re-parse each cell. Text that looks like numbers becomes real numbers. Dates that imported in the wrong regional order get fixed too, because the wizard's Column data format ▸ Date ▸ DMY/MDY dropdown lets you tell Excel which order the source used. It's the fastest fix for the classic "my numbers are left-aligned text and won't sum" problem.

What is Flash Fill, and when should you use it?

Flash Fill (Ctrl+E, or Data ▸ Flash Fill) recognises patterns. It isn't a formula. Type the result you want for the first row or two, say the first name pulled out of Anil Sharma, or an email like asharma@firm.com built from name parts. Press Ctrl+E and Excel infers the pattern and fills the rest of the column. It's superb for one-off reshaping: splitting, joining, re-casing, inserting characters. Its limit is that it's static. The filled values don't update if the source changes, and a subtle pattern can be mis-guessed. For anything that must stay live, reach for real text formulas like LEFT, TEXTBEFORE, TEXTSPLIT. Flash Fill suits a quick manual clean, and formulas suit a repeatable pipeline.