Dictionary in Excel (Create & Use)

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of Dictionary in Excel

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.

INDEX function in Excel

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.


Adding data dictionary

🔎 Formula Explanation

  • MATCH(B22, $B$4:$F$4, 0): The MATCH function in Excel searches for the value in cell B22 within the fixed range $B$4:$F$4 and returns the relative position of the match (column number) with an exact match requirement (0 for exact match).
  • INDEX($B$4:$F$5, 2, MATCH(B22, $B$4:$F$4, 0)): The INDEX function in Excel returns the value at the intersection of row 2 and the column where the value in cell B22 matches the values in the fixed range $B$4:$F$4.
  • TYPE(INDEX($B$4:$F$5, 2, MATCH(B22, $B$4:$F$4, 0))):The is used to determine the data type of a value.It takes the value from step 1 and returns its data type, which could be one of the following: 1 (number), 2 (text), 4 (logic) and 16 (error).
  • VLOOKUP(TYPE(INDEX($B$4:$F$5, 2, MATCH(B22, $B$4:$F$4, 0))), $B$14:$C$17, 2, FALSE): The VLOOKUP function in Excel searches for the result of the INDEX-MATCH operation’s data type (either 1 for a number or 2 for text) within the range $B$14:$C$17 and returns the corresponding value from the second column with an exact match requirement (FALSE). Here return type is Number.

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.
Adding new dictionary from Excel options.

Click here to enlarge the image

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

Selecting Dictionary from custom dictionary

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

Added new dictionary

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.

Adding Visual Basic

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

Checking Microsoft Scripting Runtime

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

Inserting new 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

Sub VBA_Dictionary2()

  Dim Dictionary As Dictionary

  Set Dictionary = New Dictionary

The snippet creates a dictionary object and assigns it to the variable “Dictionary.” It allows storing key-value pairs for easy retrieval of values based on their corresponding keys.

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

In this code snippet, a dictionary object named “Dictionary” is being used to store several key-value pairs. The keys range from 11 to 16, and the corresponding values are names such as “Robert,” “Michael,” “Sabastean,” “Gwen,” “Bruce,” and “Peter”.The “.Add” method is used to add each key-value pair to the dictionary. This code segment provides a convenient way to populate the dictionary with data for later retrieval or manipulation.

Debug.Print (Dictionary(11))

It prints the name of key:=11 in the Immediate window.

End Sub

  • 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.
Adding dictionary to watches

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.

Line by line execution of vba code

Note: You can add items to a Dictionary using the .Add method. Specify the key and the corresponding value using the key:= and Item:= parameters, respectively. For example: Dictionary.Add key:=11, Item:=”Robert”.

How to Use Dictionary in Excel?

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

Finding Synonyms using 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.

Add a New word to Excel

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.


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

<< Go Back to Proofing in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Joyanta Mitra
Joyanta Mitra

Joyanta Mitra, a BSc graduate in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. Specializing in programming, he has authored and modified 60 articles, predominantly focusing on Power Query and VBA (Visual Basic for Applications). His expertise in VBA programming is evident through the substantial body of work he has contributed, showcasing a deep understanding of Excel automation, and enhancing the ExcelDemy project's resources with valuable... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF