How to Create a Dictionary in Microsoft Excel

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.

 

Previous PHP Tutorial 8: One and Multi-Dimensional Arrays
Next Event and Event Handler in Visual Basic.Net

About author

Raza Bayani
Raza Bayani 42 posts

<p>Raza Bayani studies BCE (Bachelor in Computer Engineering) in Poly-technical University of Kabul. He is the CEO of CODEJOW group, a web designer, coder and recently a blogger. Raza thinks, he can help others and also boost-up his own programming skills being on codejow.com.</p>

View all posts by this author →

You might also like