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

Get FREE Advanced Excel Exercises with Solutions!

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.

List of states with city to create hierarchy in Excel

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

List of zip code under each city in Excel

Next, we do this for other states.

  • Data of Florida state.

  • Data of Alabama state.

  • Data of California state.

  • Data of Hawaii state.

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

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.

Table for hierarchy of the state, city and zip code in Excel

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

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

  • The Data Validation dialog box appears.

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

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

  • After that, put a formula on the Source box.
=Hierarchy!$B$5:$B$9

Insert formula in data validation for hierarchy in Excel

  • Finally, click the OK button.

Get option from drop-down list

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.

Copy format of data validation in other cells of dataset for hierarchy in Excel

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

Copy data validation format to other cells for creating hierarchy in Excel

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
=OFFSET(Hierarchy!B$11,1,MATCH($B5,Hierarchy!$B$11:$F$11,0)-1,5,1)
  • Then, press the OK button.

Formula for hierarchy in Excel for city

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

Expand format of data validation to other cells to create a hierarchy in Excel

  • 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.
=OFFSET(Hierarchy!B$19,1,MATCH($C$5,Hierarchy!$B$19:$F$19,0)-1,5,1)
  • Click on the OK button.

Apply formula of for hierarchy of zip code in Excel

  • 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.
=OFFSET(Hierarchy!B$27,1,MATCH($C$6,Hierarchy!$B$27:$F$27,0)-1,5,1)

  • The formula of Data Validation for Cell D7.
=OFFSET(Hierarchy!B$35,1,MATCH($C$7,Hierarchy!$B$35:$F$35,0)-1,5,1)

  • The formula of Data Validation for Cell D8.
=OFFSET(Hierarchy!B$43,1,MATCH($C$8,Hierarchy!$B$43:$F$43,0)-1,5,1) 

Formula for hierarchy in Excel for Zip code

  • The formula of Data Validation for Cell D9.
=OFFSET(Hierarchy!B$51,1,MATCH($C$9,Hierarchy!$B$51:$F$51,0)-1,5,1) 

  • Look at the dataset again.

Hierarchy table of the state, city and zip code in Excel

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

Alok Paul
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo