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.
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 articles
<< Go Back to Dictionary in Excel | Proofing in Excel | Learn Excel
