Dictionary in Excel (Create & Use)

In this tutorial, you will learn how to create a data dictionary, custom dictionary, and VBA dictionary. In addition to these, we will discuss how to use the dictionary in Excel with the way of adding a new word to it. Here, we will show you how to create a data dictionary with the Excel Options window and then how to add a dictionary downloaded from the internet. Moreover, we will show you how to make your dictionary by using VBA.

We used Microsoft 365 to prepare this article. However, the methods are also applicable in all Excel versions.

Adding a dictionary in Excel can be particularly beneficial for individuals and organizations that work extensively with industry-specific terminology, technical jargon, or multilingual content. By incorporating a custom dictionary, users can ensure accurate spell-checking and improve overall data accuracy and consistency. Additionally, the dictionary feature can expedite the process of proofreading and minimize errors caused by typos or misspelled words.

Here is a sample of creating a dictionary, which is given below, and we will discuss it in detail later.

Download Practice Workbook

How to Create Dictionary in Excel?

In this section, we will show you ways to create dictionary in Excel. The most frequent ways are: using the Excel Options window, downloading from the internet, and using custom VBA.

1. Creating Data Dictionary in Excel Using Excel Formula

To create a data dictionary, we have to use the combination of VLOOKUP, TYPE, and INDEX functions in Excel. Here is a brief about the INDEX function.

Click here to enlarge the image

• First, we have to create a dataset to create a data dictionary.
• Then we have to create a data classification table using Value and Return Type. Return Type returns the same type of values in Excel in the Value.
• Here, we have created Data Dictionary using the formula in the C22 cell. The formula is given below.

2. Creating Custom Dictionary

Here, we will use Excel Options to add a dictionary(.dic) file downloaded from the internet.

• First, we have to press Alt + F + T to get Excel Options.
• Then we select Proofing >> Custom Dictionaries >> Add.

Click here to enlarge the image

• Now, select your dictionary file and then press Open.

• Here we can see a new dictionary file has been added.

3. Creating Dictionary Using VBA

We will create a custom format dictionary by using VBA code. We can create our own dictionary and add it to Excel.

• Now, to run the VBA code, we have to select Developer >> Visual Basic.

• Now we have to press Alt + T + R to get References – VBAProject window and check Microsoft Scripting Runtime and then press OK.

• Here, we will go to the Insert tab and select Module.

• Now we are going to write the code below by pressing the Run button or F5.

Sub VBA_Dictionary2()
  Dim Dictionary As Dictionary
  Set Dictionary = New Dictionary
With Dictionary
  .Add key:=11, Item:="Robert"
  .Add key:=12, Item:="Michael"
  .Add key:=13, Item:="Sabastean"
  .Add key:=14, Item:="Gwen"
  .Add key:=15, Item:="Bruce"
  .Add key:=16, Item:="Peter"
End With
Debug.Print (Dictionary(11))
End Sub

🔎 Code Explanation

• After that, we are going to press Ctrl + G for the Immediate window.
• Then, press Alt + V + H for the Watches window. Now, drag and drop Dictionary to Watches and Press F8 until the 4th line is executed.

Click here to enlarge the image

• Now, after pressing the “+” sign before the Dictionary expression in the Watches window, keep pressing F8 until you finish the line-by-line execution.

How to Use Dictionary in Excel?

We have pressed Alt + Left-Click after selecting cell B4 to get the built-in Excel dictionary.

How to Add a New Word to Excel’s Dictionary?

Here we have used the Excel option to add new words to the custom dictionary.

• We have pressed Alt + F + T to get Excel Options.
• Then we select Proofing >> Custom Dictionaries button >> Edit Word List… button >> Word(s) textbox.
• We can add any word you wish to this Word(s) text box. It will be added to the dictionary.

Which Things Should You Remember?

When working with a Dictionary object in Excel, here are a few important things to remember:

• Library Reference: Ensure that you have added the appropriate library reference to use the Dictionary object. In VBA, you can go to the Tools menu, select References, and check the box for Microsoft Scripting Runtime to enable Dictionary functionality.
• Declaration and Initialization: Declare a Dictionary object variable and initialize it using the New Dictionary syntax, as shown in your previous code snippet. For example: Dim Dictionary As New Dictionary.
• Adding Key-Value Pairs: Use .Add method to add key-value pairs to the dictionary. Specify the key and the corresponding value using the key:= and Item:= parameters, respectively. For example: Dictionary.Add key:=11, Item:=”Robert”.
• Error Handling: Ensure to handle errors that may occur when accessing dictionary elements. Check if a key exists in the dictionary using the .Exists method before accessing its value. For example: If Dictionary.Exists(11) Then Debug.Print Dictionary(11).

Frequently Asked Questions

1. What is a Dictionary object in Excel VBA?

The Dictionary object in Excel VBA is a data structure that allows you to store and retrieve key-value pairs. It provides efficient access to values based on their corresponding keys, making it useful for tasks such as data manipulation, lookup operations, and maintaining unique data sets.

2. How do I retrieve a value from a Dictionary in Excel VBA?

To retrieve a value from a Dictionary, use the key within parentheses. For example: Dictionary(11). This will return the value associated with the key 11.

3. Can I check if a key exists in a Dictionary before accessing its value?

Yes, you can check if a key exists in a Dictionary using the .Exists method. For example: If Dictionary.Exists(11) Then Debug.Print Dictionary(11). This prevents potential errors when accessing non-existent keys.

4. How can I remove a key-value pair from a Dictionary in Excel VBA?

You can remove a specific key-value pair from a Dictionary using the .Remove method. For example: Dictionary.Remove 11. This will remove the key 11 and its associated value from the Dictionary.

5. Can I iterate through a Dictionary in Excel VBA?

Yes, you can iterate through a Dictionary using a loop, such as For Each…Next. This allows you to perform operations on each key-value pair in the Dictionary.

Conclusion

In conclusion, this tutorial has provided a comprehensive guide on how to create and utilize dictionaries in Microsoft Excel, using methods such as employing Excel formulas to construct data dictionaries, incorporating custom dictionaries through Excel Options, and using VBA. These dictionaries enhance data accuracy, streamline proofreading, and ensure consistency in documents, making them invaluable tools for individuals and organizations dealing with complex terminologies or multilingual content. Excel’s built-in dictionary and the ability to expand it further through customization ensure that accurate spelling and grammar are maintained. By following the techniques outlined here, users can bolster their Excel proficiency, regardless of the version used, while safeguarding data precision.

Dictionary in Excel: Knowledge Hub

• Create Data Dictionary
• VBA Dictionary
• English Dictionary in Excel
• Lookup in Excel Dictionary

<< Go Back to Proofing in Excel | Learn Excel