Zip Code Magic

Have you ever wanted Excel to automatically return the City and State when simply entering a ZIP code? In this guide, we’ll explore three reliable methods to make that magic happen. Using Excel Copilot, Power Query, and traditional Excel functions, we’ll show how to pull accurate location data from a ZIP code using a live web API. Whether you’re handling customer addresses or cleaning up data, this walkthrough makes address lookup seamless and dynamic—all from within Excel.

Video

What We’ll Cover

• Using the Copilot function for dynamic ZIP code lookups
• Setting up a live query using Power Query
• Building a formula-based lookup using traditional Excel functions
• Addressing regions with leading zeros

Method 1: Magic with Excel Copilot

First, we’ll harness Copilot’s AI capabilities alongside the WEBSERVICE function. We use a free public API that returns location metadata (country, city, latitude, state, etc.) based on the ZIP code.

Brief Overview

1. Enter a base API URL like "http://api.zippopotam.us/us/"
2. Use =WEBSERVICE(base_url & zip_code_cell) to pull JSON results directly into Excel.
3. Wrap it with a COPILOT function:
   

   =COPILOT("Get the state abbreviation", WEBSERVICE(url & zip_code))

4. Repeat the step above to get the city:
   

   =COPILOT("Get the place name", WEBSERVICE(url & zip_code))

Note: Copilot functions only work in Excel versions with AI capabilities enabled. Make sure Copilot is available in your instance.

Detailed Walkthrough

For more details and step-by-step walkthrough, visit this post: Zip Code COPILOT

Zip Code Lookup with COPILOT

Handling Leading Zeros

ZIP codes beginning with “0” (e.g., 01101) may be incorrectly formatted as numbers. To retain the leading zero:

1. Right-click the ZIP code cell
2. Select “Format Cells”
3. Choose Text and click OK
4. Re-enter the ZIP code

Method 2: Power Query ZIP Code Lookup

Power Query gives us an efficient way to scale ZIP code lookups against a list of values. Unlike Copilot, this method works even if you don’t have the latest Excel AI features.

Brief Overview

1. Select your ZIP code column and go to Data > From Table/Range.
2. In the Power Query Editor:
   • Delete any steps that change data types
   • Change the ZIP code column to Text (to preserve leading zeros)
3. Create a function:
   • Go to Advanced Editor
   • Turn your HTTP request into a parameterized function (e.g., fnZIPLookup)
4. Back in your ZIP code table:
   • Click Add Column > Invoke Custom Function
   • Select your function and map the ZIP code column as input
5. Expand the resulting records and choose the relevant fields: place name, state, etc.
6. Load the result into your worksheet.

Once set up, all we need to do is refresh the results—any newly entered ZIP codes will return updated city/state information automatically. This method is especially powerful for bulk data transformations.

Detailed Walkthrough

For more details and step-by-step walkthrough, visit this post: Zip Code Power Query

Zip Code Lookup with COPILOT

Method 3: Traditional Excel Formulas

Our last method works in any Excel version that supports standard functions, making it the most compatible approach. Here, we still use the WEBSERVICE function, but combine it with MID, LEN, and SEARCH to parse JSON strings.

Let’s assume the zip code input cell is C6.

Brief Overview

1. Use: =WEBSERVICE(“http://api.zippopotam.us/us/” & C6) to get the results
2. To extract the state abbreviation: =MID(C8, LEN(C8)-5, 2)
3. To extract the city name:

   • =LET(
       API, WEBSERVICE("http://api.zippopotam.us/us/" & C8),
       Start, SEARCH("place name", API)+14,
       End, SEARCH("longitude", API)-4,
       Length, End-Start,
       MID(API, Start, Length)
     )        

Detailed Walkthrough

For more details and step-by-step walkthrough, visit this post: Zip Code Power Query

Zip Code Lookup API

Summary: Best of All Worlds

We’ve explored three methods for dynamic ZIP code lookup in Excel:

• Copilot: Great for individual lookups using AI + API
• Power Query: Ideal for batch processing and dynamic updates
• Traditional Formulas: Fully compatible approach with deep control

Each method has its strengths. Use Copilot for simplicity, Power Query for scale, and formulas for compatibility. Whichever we choose, we’re enabling smarter Excel workflows with real-time data automation!

Download the Practice Workbook:

FAQs: City and State from ZIP Code in Excel

1. What API is used in this tutorial?

We use the free public API from zippopotam.us.

2. Do I need Copilot to make this work?

No. While Copilot makes it easier, we can use Power Query or standard Excel formulas if it’s not available.

3. How do I preserve leading zeros in ZIP codes?

Format the ZIP code cells as Text in the Format Cells dialog. This prevents Excel from auto-converting the value.

4. Can I use this for non-US ZIP codes?

Yes, provided the ZIP code format is supported by the API. Adjust the URL from /us/ to the appropriate country code.

5. What if I get a #VALUE error from WEBSERVICE?

This can occur if the response is too long or the API rate limit is reached. Consider using smaller batches or retrying later.

6. Can I extract more than just city and state?

Yes. The API also returns country, longitude, latitude, and more. Use Power Query or Copilot to extract these fields.

7. Is the setup secure?

The API is public and does not require authentication. It is safe for general location lookups.

8. Can I automate this lookup for 10,000+ rows?

Be cautious. The API has rate limits, so it’s not ideal for huge datasets unless you cache results or stagger requests.

9. Why is Copilot not working in my Excel?

Copilot is only available in certain Microsoft 365 plans with an active copilot license and the update installed.