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 NameAddress 1CityStateZIPSales
Aaron Fitz ElectricalOne Microsoft WayRedmondWA98052-6399$20,000.00
Aaron Fitz Electrial11403 45 St. SouthChicagoIL60603-0776$10,000.00
Adam Park ResortP.O. Box 1391IndianapolisIN46206-1391$15,000.00
Adam Park ResortSuite 9876IndianapolisIN46206-1391$5,000.00
Advanced Paper Co.456 19th Street S. ChicagoIL60603-911$12,000.00

Strip the +4 zip portion out

Customer NameAddress 1CityStateZipSalesLeft Zip
Aaron Fitz ElectricalOne Microsoft WayRedmondWA98052-6399$20,000.0098052
Aaron Fitz Electrical11403 45 St. SouthChicagoIL60603-0776$10,000.0060603
Adam Park ResortP.O. Box 1391IndianapolisIN46206-1391$15,000.0046206
Adam Park ResortSuite 9876IndianapolisIN46206-1391$5,000.0046206
Advanced Paper Co. 456 19th Street S. ChicagoIL60603-911$12,000.0060603

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 NameAddress 1CityStateZipSalesLeft ZipCountyPopulation
Aaron Fitz ElectricalOne Microsoft WayRedmondWA98052-6399$20,000.00 98052King2,233,163
Aaron Fitz Electrical11403 45 St. SouthChicagoIL60603-0776$10,000.00 60603Cook5,275,541
Adam Park ResortP.O. Box 1391IndianapolisIN46206-1391$15,000.00 46206Marion954,670
Adam Park ResortSuite 9876IndianapolisIN46206-1391$5,000.00 46206Marion954,670
Advanced Paper Co.456 19th Street S.ChicagoIL60603-911$12,000.00 60603Cook5,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