How to Create a Hierarchy of the State City and Zip Code in Excel

To express an address, usually we need state, city name, and code. In a state, there are several cities. And under one city there are several zip code areas. In this article, we will create a hierarchy with state, city, and zip code in Excel. Like, first we will choose a state. Then, choose the cities of that state. Lastly, we will get zip code areas under each city.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

What Is a Hierarchy Table?

The hierarchy table is a table that is used to represent the relationship between different events. It tells how the events are related to each other. Which is the parent component and which is the child component.

The following shows the state, city, and zip code hierarchy.

Steps to Create a Hierarchy of the State City and Zip Code in Excel

We will show all the steps in detail of how to create a hierarchy with state, city, and zip code in Excel. First, we made a date set of 5 states. We will collect further data based on this dataset.

⦿ Step 1: Prepare Data

In this step, we will prepare data for the hierarchy of states, cities, and zip codes.

• First, we collect the name of 5 cities in each state.

• After that, we collect several zip codes under the cities of Georgia state.

Next, we do this for other states.

• Data of Florida state.

• Data of Alabama state.

• Data of California state.

• Data of Hawaii state.

Our dataset is ready now.

Read More: How to Create Hierarchy in Excel Pivot Table (with Easy Steps)

⦿ Step 2: Create a List of States Using Data Validation

To create a hierarchy we will apply the data validation feature of Excel.

• First, we create a table for creating a hierarchy list.

• After that, select Cell B5.
• Choose Data Tools from the Data tab.
• Select the Data Validation option.

• The Data Validation dialog box appears.

• Choose the List option from the Allow box drop-down list.

• After that, put a formula on the Source box.

• Finally, click the OK button.

We can see a drop-down symbol beside the selected cell.

• After that, copy Cell B5 by pressing Ctrl + C.
• Then, choose Range B6:B9.

• Then, go to the Paste drop-down.
• Choose Paste Special option.

• Paste Special window appears.
• Check the Validation option from the Paste section.

• Finally, click on the OK button.

We can see the drop-down symbol is showing for the other cells.

• Insert options from the drop-down list of each cell.

Read More: How to Add Row Hierarchy in Excel (2 Easy Methods)

⦿ Step 3: Create a City List with Data Validation

In this step, we will apply data validation in the City column.

• Choose Cell C5 to apply Data Validation.

• Go to the Data Validation window as shown before.
• Now put the following formula on the Source

• Then, press the OK button.

• Look at the dataset.

We can see the drop-down list and the options at the drop-down list. Expand the drop-down list to Range C6:C9.

• Choose Cell C5.
• Copy that cell by pressing Ctrl+C.
• Then, choose Range C6:C9. After that, press Ctrl+ Alt+ V for paste special.
• Then, check the Validation option from the Paste Special window.

• Finally, choose the OK button.

We can see options from the data validation drop-down list.

Read More: How to Create Multi Level Hierarchy in Excel (2 Easy Ways)

⦿ Step 4: Create Zip Code Drop-Down List

Now, we will apply data validation to the Zip column. This data validation is based on the values of Range C5:C9.

• Select Cell D5 first.

• Enter the Data Validation window as shown before.
• Inset a formula on the Source box.

• Click on the OK button.

• Now, look at the dataset.
• Click on the down arrow of the drop-down list.

See the Zip code on the list. We apply this for the rest of the cells of Range C5:C9.

• The formula of Data Validation for Cell D6.

• The formula of Data Validation for Cell D7.

• The formula of Data Validation for Cell D8.

• The formula of Data Validation for Cell D9.

• Look at the dataset again.

We can select Zip codes from the drop-down list.

Conclusion

In this article, we described how to create a hierarchy of the state, city, and zip code in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Related Articles

• How to Make Hierarchy Chart in Excel (3 Easy Ways)
• Create Date Hierarchy in Excel Pivot Table (with Easy Steps)
• How to Use SmartArt Hierarchy in Excel (With Easy Steps)