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.

Creating Dataset for Data Dictionary in Excel

  • 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.

Formatting Data Type Classification Table for Data Dictionary in Excel

  • 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.

Inserting Columns for Data Dictionary in Excel

  • 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.

Determining Data Type for Data Dictionary in Excel

  • 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.


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.


Related article


<< Go Back to Dictionary in Excel | Proofing in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of NAME from Bangladesh University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also interested... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo