How to Create a Dictionary in Microsoft Excel

Microsoft Office applications can be used in many other ways and for different purposes. You may have heard about personal dictionaries a lot, but in this article I am going to show you, how to use Microsoft Excel to create a personal language dictionary. Thing you need for creating a personal dictionary in Excel are MS Excel itself installed on your computer, words and meanings that you want add to your dictionary.

Making a dictionary is an easy task and can be done by different methods. In this tutorial I am going to show you the two basic ways that a simple excel user can do that.

The First Method: Using Excel Functions



This method works with important functions such as: Vlookup, Lookup or Hlookup. Follow the below steps accurately.

  1. Place your words, their meanings and the pronunciations in three columns.

    Words and Meanings in Excel
    Words and Meanings in Excel
  2. Make a search area with excel tools and commands as below:Search Area in Excel
  3. Use “Vlookup” function to find the searched word. (Vlookup must be used three times in order to find the word, it’s meaning and the pronunciation.) the syntax for “Vlookup” is as below:



    1. Select the cell, where you enter the search term.
    2. Select all the table including the header of the table. (better to select the range and give a unique name to the range before the “vlookup” function then you can use the name instead of selecting all range)
    3. Type the column number, suppose we want to find meaning, that is the second column so type 2.
    4. Type 0 at the end if you want excel to find the exact word, type 1 if you want excel to find the approximate match for the search term.

Note: commas are necessary between function parameters.

Vlookup function used in Excel
Vlookup function used in Excel

repeat step 3 for the column words, meaning and the pronunciation to create a proper dictionary.


Search with Vlookup in Excel
Search with Vlookup in Excel

The Second Method: Using Advanced Filter

For using this method, you need to know about macros, filter and advanced filter. Following steps must be done to have an advance filtered dictionary.


  • Make a criteria range above data table with one of the headings (ex: words) and a blank cell under the mentioned cell.
  • Go to VIEW tab and Record a Macro. Give a name and assign a shortcut key.
    Recording a Macro in Excel
    Recording a Macro in Excel


    Macro Name and Shortcut key in Excel
    Macro Name and Shortcut key in Excel
  • Go to DATA tab and click advance in the filter group. Select the all data and the criteria cells (two cells, the cell containing “words” and the blank cell under that.)
Data tab and Advanced Filter in Excel
Data tab and Advanced Filter in Excel
Working with Advanced Filter in Excel
Working with Advanced Filter in Excel
  • Press “Ok” and Stop “Macro” from status bar.
  • Now type a word in criteria’s blank cell. (under the cell containing “words”)
  • Press the assigned shortcut key to implement the Macro and enjoy the filtration.



Note: The criteria should be above table.

To bring back all the results from filtered state, clear the criteria and press shortcut key.

Done! Now you have your own personal excel dictionary.

 

Comments

  • Leave a Reply