Automated English–Portuguese Dictionary in Excel using Power Query

Excel Dictionary English–Portuguese: Build a Bilingual Lookup in 5 StepsA bilingual dictionary inside Excel can save translators, language learners, teachers, and multilingual teams hours of repetitive work. Instead of switching between apps or dragging words through online translators, you can build a compact, searchable, and reusable English–Portuguese dictionary directly in Excel. This guide walks you through a practical five-step process to create a bilingual lookup that supports quick searches, two-way translation, context notes, and expandable term lists.

---

Why build an Excel bilingual dictionary?

• Portable and offline — works without internet access.
• Customizable — add fields like part of speech, usage notes, example sentences, and frequency.
• Integrates with workflows — use formulas, data validation, and Power Query to connect with other spreadsheets or corpora.
• Two-way lookup — translate English→Portuguese and Portuguese→English from the same dataset.

---

What you’ll build

• A master glossary table with English and Portuguese entries plus optional metadata.
• A clean lookup area with input cells and automatic translation results.
• A two-way search that handles exact and partial matches.
• Optional enhancements: dropdown suggestions, fuzzy matching, and export as CSV.

---

Step 1 — Prepare your master glossary

1. Open a new workbook and create a worksheet named Glossary.
2. Create these columns (Row 1): ID, English, Portuguese, PartOfSpeech, Gender, Notes, Example.
   • ID: a unique numeric or alphanumeric key (helps with deduplication).
   • PartOfSpeech and Gender (for Portuguese nouns) help with grammatical accuracy.
3. Populate rows with pairs. Start small (200–500 rows) and expand. You can import lists from CSVs, bilingual corpora, or glossaries from translation memory exports.

Tips:

• Keep English in column B and Portuguese in column C to simplify formulas below.
• Normalize entries: trim whitespace, use consistent casing (e.g., sentence case or lowercase), and avoid duplicate IDs.

---

Step 2 — Convert the glossary into an Excel Table

1. Select the header row plus data and press Ctrl+T (or Insert → Table).
2. Name the table — e.g., tblGlossary (Table Design → Table Name).
3. Tables make formulas easier (structured references), support sorting/filtering, and expand automatically as you add entries.

Example structured column references: tblGlossary[English], tblGlossary[Portuguese].

---

Step 3 — Create a simple two-way lookup area

On a new worksheet (Lookup), design a small interface:

• Cell B2: label “Search word”
• Cell C2: the input cell for the word to translate (leave blank initially)
• Cell B4: label “Direction”
• Cell C4: a dropdown with two options: “English → Portuguese” and “Portuguese → English” (Data → Data Validation → List)

Add result fields:

• B6: “Match (exact)” — C6 will display an exact match if present.
• B8: “Matches (partial/fuzzy)” — C8:C12 will list the top partial matches.
• B14: “Notes / Example” — C14 will show Notes or Example for the top matched entry.

Exact match formula (English → Portuguese):

• Use XLOOKUP (Excel ⁄₂₀₂₁) for straightforward exact lookup:

  
  =IF(C4="English → Portuguese", XLOOKUP(TRIM(C2), tblGlossary[English], tblGlossary[Portuguese], "Not found", 0), XLOOKUP(TRIM(C2), tblGlossary[Portuguese], tblGlossary[English], "Not found", 0)) 

For older Excel versions use INDEX/MATCH:

=IF(C4="English → Portuguese",     IFERROR(INDEX(tblGlossary[Portuguese], MATCH(TRIM(C2), tblGlossary[English], 0)), "Not found"),     IFERROR(INDEX(tblGlossary[English], MATCH(TRIM(C2), tblGlossary[Portuguese], 0)), "Not found")) 

---

Step 4 — Add partial and fuzzy matching

Partial matching (starts with / contains):

• Use FILTER (Excel ⁄₂₀₂₁) to populate a list of entries where the lookup term appears in either column:

  
  =IF(C4="English → Portuguese", FILTER(tblGlossary[Portuguese], ISNUMBER(SEARCH(C2, tblGlossary[English])), "No partial matches"), FILTER(tblGlossary[English], ISNUMBER(SEARCH(C2, tblGlossary[Portuguese])), "No partial matches")) 

  This returns all matching translations where the search term is found inside the source column. Wrap with TAKE to limit results:

  
  =TAKE(FILTER(...), 10) 

Fuzzy matching:

• Excel’s built-in FUZZYLOOKUP add-in (older) or Power Query’s Merge with fuzzy matching can help when spellings differ or you want approximate matches.
• Power Query method:
  1. Load the glossary to Power Query (Data → From Table/Range).
  2. Create a small one-row table with the search term and load it to Power Query.
  3. Merge the search-term query with the glossary query using Fuzzy Matching (Merge Queries → Use fuzzy matching, set similarity threshold).
  4. Load the results back to the worksheet.

---

Step 5 — UX improvements and automation

Dropdown suggestions:

• Create a named range or dynamic list of unique English and Portuguese words (from tblGlossary) and use data validation with a searchable dropdown add-in or Excel’s built-in autocomplete for single-entry cells.

Show grammatical info:

• Return PartOfSpeech and Gender together with the translation using XLOOKUP and concatenation:

  
  =IF(C4="English → Portuguese", LET(trans, XLOOKUP(TRIM(C2), tblGlossary[English], tblGlossary[Portuguese], "Not found"),     pos, XLOOKUP(TRIM(C2), tblGlossary[English], tblGlossary[PartOfSpeech], ""),     gen, XLOOKUP(TRIM(C2), tblGlossary[English], tblGlossary[Gender], ""),     IF(trans="Not found", "Not found", trans & IF(pos<>"", " — " & pos, "") & IF(gen<>"", " (" & gen & ")", ""))), /* reverse direction similar */ ) 

Auto-update and export:

• Tables auto-expand. Use Power Query to regularly sync a central CSV or database and refresh the table.
• Export the glossary as CSV (File → Save As → CSV) to share with CAT tools.

---

Example workbook layout

• Sheet Glossary: tblGlossary (ID | English | Portuguese | PartOfSpeech | Gender | Notes | Example)
• Sheet Lookup: Search input, direction dropdown, exact result, partial/fuzzy list, metadata fields.
• Optional Sheet: Settings (similarity threshold, max results) and a sheet for Power Query helper queries.

---

Tips for data quality and scale

• Use unique IDs to avoid ambiguous duplicates.
• Keep entries lowercase or use helper columns with LOWER() to standardize lookups.
• Include common variants (plurals, conjugations) or add a column for lemma/base form.
• Regularly audit the glossary for duplicates and inconsistent translations. Use conditional formatting to highlight identical source strings with different targets.
• When scaling to thousands of entries, consider a proper database or a translation memory system; Excel is excellent for small-to-medium glossaries and rapid prototyping.

---

Advanced ideas

• Connect Excel to Google Sheets or a cloud CSV to let multiple collaborators update the glossary.
• Build a simple macro or Office Script to add new entries from the Lookup sheet into tblGlossary with validation checks.
• Use VBA or Office Scripts to create a keyboard shortcut that looks up the selected word in any worksheet and displays the translation in a floating form.

---

This setup gives you a practical, extendable English–Portuguese dictionary inside Excel that’s useful for translators, language learners, and teams working across both languages.