Eliminate Manual Work with the Microsoft Excel Geography Feature

By Ian Richardson | August 3, 2022

Have you ever needed to look up the country your customer or vendor is located in?

There are some websites where you can look up zip codes, but that is a lot of manual work. Luckily, there is a better way!

We recently assisted a customer that needed to have sales by country for taxation purposes. The data was not available in their accounting package and they were faced with the repetitive task of looking up zip codes one by one.

How the Microsoft Excel Geography Feature can help

Exporting your sales data to excel, including the zip code from the ship to address of the sale we can use the Geography Feature in Excel.

The Geography Feature works with 5-digit zip codes and the codes must be numbers, not formulas. So we use the LEFT function to strip the +zip code 4 from the data. Paste Special Values the result so you are left with 5 digits.

Client Name Address 1 City State ZIP Sales
Aaron Fitz Electrical One Microsoft Way Redmond WA 98052-6399 $20,000.00
Aaron Fitz Electrial 11403 45 St. South Chicago IL 60603-0776 $10,000.00
Adam Park Resort P.O. Box 1391 Indianapolis IN 46206-1391 $15,000.00
Adam Park Resort Suite 9876 Indianapolis IN 46206-1391 $5,000.00
Advanced Paper Co. 456 19th Street S. Chicago IL 60603-911 $12,000.00

Strip the +4 zip portion out

Customer Name Address 1 City State Zip Sales Left Zip
Aaron Fitz Electrical One Microsoft Way Redmond WA 98052-6399 $20,000.00 98052
Aaron Fitz Electrical 11403 45 St. South Chicago IL 60603-0776 $10,000.00 60603
Adam Park Resort P.O. Box 1391 Indianapolis IN 46206-1391 $15,000.00 46206
Adam Park Resort Suite 9876 Indianapolis IN 46206-1391 $5,000.00 46206
Advanced Paper Co. 456 19th Street S. Chicago IL 60603-911 $12,000.00 60603

Highlight the Left Zip Values from the Data Menu, then click Geography.

Picture1

Geography icons will then be added to each data item. When you select that item, or a range of items, a Geography+ menu appears.

Picture2

Select Admin Division 2 to get the Country for this zip code.

Picture3

Picture4

That's not all! Note how there is now a Geography+ icon next to the Country as well. While this is not pertinent to the taxation request posed by our client, Excel can now show you interesting and useful things such as the Largest City of Population and others.

Picture5

Client Name Address 1 City State Zip Sales Left Zip County Population
Aaron Fitz Electrical One Microsoft Way Redmond WA 98052-6399 $20,000.00 98052 King 2,233,163
Aaron Fitz Electrical 11403 45 St. South Chicago IL 60603-0776 $10,000.00 60603 Cook 5,275,541
Adam Park Resort P.O. Box 1391 Indianapolis IN 46206-1391 $15,000.00 46206 Marion 954,670
Adam Park Resort Suite 9876 Indianapolis IN 46206-1391 $5,000.00 46206 Marion 954,670
Advanced Paper Co. 456 19th Street S. Chicago IL 60603-911 $12,000.00 60603 Cook 5,275,541

Questions?

We have a team of experts who can help you with this, or any other Microsoft solutions or features of those solutions! Please reach out to us today!

Related Posts

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X