How to Find Similar Words in Excel Using Power Query
Question details
The user needs to compare text from two Excel fields to identify rows with matching or similar words.
- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- Comparing inconsistent names or partial text matches across different data sources to flag similarities.
- Observed behavior
- Standard formulas produce outliers or inaccurate comparisons, requiring a more robust text similarity solution like fuzzy matching.
Ensure your data is organized into formatted Excel Tables (Ctrl+T) before attempting to merge queries or apply text comparison formulas.
Use Power Query Fuzzy Matching
Use this built-in Excel 365 feature for the most accurate comparison of inconsistent names and partial text matches.
Power Query in Excel 365 offers a Fuzzy Merge feature that uses advanced text similarity algorithms. This is highly recommended over standard formulas when dealing with spelling variations, typos, or partial matches, as it reduces the manual effort needed to identify outliers.
Select your first dataset and navigate to Data > From Table/Range. Repeat this process for your second dataset so both are loaded into the Power Query Editor.
In the Power Query Editor, go to the Home tab and click on 'Merge Queries' (or 'Merge Queries as New').
Select the two tables from the dropdown menus and click on the columns you want to compare. Check the box labeled 'Use fuzzy matching to perform the merge'.
Expand the 'Fuzzy matching options' to adjust the Similarity Threshold (between 0.00 and 1.00) based on how strict you want the word comparison to be, then click OK and load your data.
Use Formula-Based Matching with Exclusion Rules
Use this method if you do not have access to Power Query or prefer working directly with worksheet formulas.
Easily Compare Text and Find Similar Words in WPS Office
WPS Spreadsheet offers a comprehensive set of functions and data filtering tools to help you compare text fields, identify matches, and clean your datasets efficiently without complex add-ins.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing the text fields you want to compare.
- 2. Apply text comparison functions: Select a blank column and enter a combination of SEARCH and ISNUMBER functions to identify partial matches between your target cells.
- 3. Filter the results: Navigate to the Data tab and click AutoFilter to easily sort and view only the rows that contain similar words.
Frequently Asked Questions
What does the similarity threshold do in fuzzy matching?
The similarity threshold ranges from 0.00 to 1.00. A score of 1.00 requires an exact match, while a lower score (like 0.60) allows for partial matches, minor variations, and typos between the compared words.
Why do formula-based comparisons produce unexpected outliers?
Standard formulas like SEARCH look for exact substring matches regardless of context. For example, searching for the word 'car' will incorrectly flag words like 'carpet' or 'cart' as a match, which requires you to manually add exclusion rules.
Is Power Query fuzzy matching available in older Excel versions?
Fuzzy matching is natively integrated into Power Query for Excel 365 and Excel 2019 or later. For older versions, users typically need to download and install the official Microsoft Fuzzy Lookup Add-In separately.
