How to Create Data Dictionary in Excel (with Easy Steps)

If you want to create a data dictionary in Excel, you have come to the right place. Here, we will walk you through 3 easy steps to do the task smoothly.

What Is Data Dictionary?

A Data Dictionary also known as metadata, contains the names, definitions, and attributes of data elements and models. Using the data dictionary, conventions, and consistency will be defined for the project.

For an organization to perform properly, data discovery and understanding are becoming increasingly important. Using Data Dictionaries has many benefits, such as:

Inconsistencies in data can be avoided by using it
The project can be unified through unified nomenclature
Enhance the searchability and understanding of data
Integrate data from different sources into a single source of truth

There are various tools for creating Data Dictionaries, they are

Spreadsheet or word processor
DBMS + GUI tool (+ Generator)
Database documentation tools
Data Modeling tools
Data Dictionary tools

How to Create Data Dictionary in Excel: Step-by-Step Procedure

In the following article, we will describe 3 easy steps to create a Data Dictionary in Excel.
Here, we used Excel 365. You can use any available Excel version.

Step 1: Creating Basic Dataset

In this step, we will create a basic dataset for a company. After that, using the dataset, we will create a Data Dictionary.

First of all, we will input ID No. in the dataset.

Furthermore, we will add Employee Name to the dataset.

Moreover, we inserted Gender into the dataset.

In addition, we included Age in the dataset.

Finally, Location has been added to the dataset.
As a result, you can see the complete Dataset.

Step 2: Forming Data Type Classification Table

In this step, we will make a Data Type Classification table for creating a Data Dictionary. This table is essential to identify the data type in the Data Dictionary.

In the first place, we put the Value in the Data Type Classification table.
Next, we will insert the return type corresponding to these values.

Therefore, we added return type of values in the Return Type column.
As a result, the Data Type Classification table is complete.

Step 3: Creating a Data Dictionary

In this step, we will create a Data Dictionaryin Excel.

In the beginning, we will add the Columns in the Data Dictionary table.
Here, we added the columns that are presented in the Dataset table.

Now it is time to include the Data Type column.
Afterward, we will type the following formula in cell C23.

=VLOOKUP(TYPE(INDEX($B$4:$F$5,2,MATCH(B23,$B$4:$F$4,0))),$B$14:$C$17,2,FALSE)

Formula Breakdown

The VLOOKUP function searches for a value in a range of cells
The TYPE function finds the value type of a given value.
The INDEX function finds a value within a table.
The MATCH function looks up a particular value in an array.
VLOOKUP(TYPE(INDEX($B$4:$F$5,2,MATCH(B23,$B$4:$F$4,0))),$B$14:$C$17,2,FALSE) → becomes
- Output: Number
Explanation: Number is the data type for ID No.

After that, press ENTER.
Therefore, you can see the result in cell C23.
In addition, we will drag down the formula with the Fill Handle tool.

Hence, you can see the complete Data Type column.

Furthermore, we added a column Description to the Data Dictionary table.
After that, we will describe all of the columns.
The description makes the characteristics of the columns easier to understand for the readers.

Moreover, we inserted the Field Size column into the Data Dictionary table.
Here, the Field Size includes the number of maximum characters each field of a column will have.
Therefore, you can see the complete Data Dictionary.

Read More: How to Use VBA Dictionary in Excel

Practice Section

You can download the above Excel file and make a Data Dictionary yourself.

Download Practice Workbook

You can download the following Excel file and practice while reading this article.

Data Dictionary.xlsx

Conclusion

Here, we show you 3 easy steps to create a Data Dictionaryin Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section. You can visit our website Exceldemy for more related articles.