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.
- Place your words, their meanings and the pronunciations in three columns.
- Make a search area with excel tools and commands as below:
- 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:
12=Vlookup(the word your search for(1), the data range(2), the column NO. in the range(3),0 for exact match/1 for approximate match(4))
- Select the cell, where you enter the search term.
- 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)
- Type the column number, suppose we want to find meaning, that is the second column so type 2.
- 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.
repeat step 3 for the column words, meaning and the pronunciation to create a proper dictionary.
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.
- 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.)
- 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.