Eliminate Manual Work with the Microsoft Excel Geography Feature
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.
Geography icons will then be added to each data item. When you select that item, or a range of items, a Geography+ menu appears.
Select Admin Division 2 to get the Country for this zip code.
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.
| 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!
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.