It’s 4:55 PM on a Friday. You just need one VLOOKUP to work so you can send that report and start your weekend.
Choosing the right method from the start is…
Choosing the right method from the start is half the battle. Based on our hands-on testing with countless datasets, here’s how these five methods stack up.
The three functions you absolutely must know are:…
The three functions you absolutely must know are:
=UPPER(): Converts everything to ALL CAPS. Perfect for IDs, codes, and headers.
You can safely delete the helper column.
You can safely delete the helper column. 💡 Pro TipCombine case functions with the TRIM function to kill two birds with one stone.
If the preview doesn't appear, you can trigger…
If the preview doesn't appear, you can trigger it manually by clicking the Data tab and selecting Flash Fill, or by using the keyboard shortcut Ctrl + E. It's ridiculously fast.
Apply the Transformation: Right-click the header of the…
Apply the Transformation: Right-click the header of the column you want to change. A context menu will appear.
A Word of Caution: Macros make permanent changes…
A Word of Caution: Macros make permanent changes to your data that cannot be undone with Ctrl+Z. Always, always work on a copy of your file when running a new VBA script.
UPPER(...): It then forces that single character to…
UPPER(...): It then forces that single character to be uppercase. MID(A2,2,LEN(A2)): This is the clever part.
By default, most of Excel's lookup functions (VLOOKUP,…
By default, most of Excel's lookup functions (VLOOKUP, XLOOKUP, MATCH) are case-insensitive, meaning they treat "Apple" and "apple" as identical. If your lookup is failing, the culprit is more likely to be hidden characters or extra spaces.