How to batch geocode and validate addresses in Excel

Batch address verification & geocoding in Excel is possible through 7 simple steps:

1. Open Excel, go to the "Home" tab, and click "Add-ins."

2. Search for "Smarty Address Verification" and click "Add". Follow the prompts to open it in Excel.

3. Once in Excel, select "Home" > "Add-ins" > "Smarty Address Verification."

4. If you don't already have one, sign up for a free account with Smarty and create a new secret key under "API Keys" on the account dashboard.

5. Add your secret key's "auth id" and "auth token" to the plugin by selecting "Show Tools" > "Enter Credentials." After entering your auth ID and auth token, click submit.

6. Enter each address in a single column, one address per cell. Highlight the fields you want to validate and click "Validate Selected Addresses"

7. You're done. Do a happy dance!

For more detailed instructions, read on or watch the step-by-step videos below.

We know you've been dreaming about batch address verification in Excel to keep your spreadsheets clean. Now, you can access the power of Smarty's geocoding data directly in Excel by using Smarty's Excel Address Verification Add-in. Don't be caught sleeping on this exciting new feature. If by "Excel" you were really trying to find something that works with other spreadsheet software like Google Sheets, you could try our Google Sheets extension instead.

Here's a brief overview of what we'll be covering:

• Excel Address Validation via AppSource add-in

  • Video: How to validate and geocode addresses in Excel using Smarty's add-in

  • Preparing your address data

  • Installing the Excel add-in

  • Validating addresses in Excel

  • Geocoding lists of addresses

  • Excel add-in capabilities & limitations

• Excel address validation via spreadsheet upload

  • Prepping your address data

  • Validating addresses via spreadsheet upload

  • Geocoding lists of addresses

  • Upload tool capabilities & limitations

• Comparison table: Excel add-in vs bulk upload web tool

• What is all the additional metadata in the response? What can I do with it?

• TL: DR - Can we skip to the good part?

Excel Address Validation via AppSource add-in

Video: How to validate and geocode addresses in Excel using Smarty's Add-in

Sadly for them, Excel doesn't have a native address correction or validation function. Gladly, for Smarty, we made an add-in for US addresses. But what about non-US addresses? We made something for that, too. For international addresses, you can use the bulk address validation plugin and upload a spreadsheet—more on that in the next section.

Preparing your data

As we mentioned in the introductory section above, your data cannot be parsed (separated into multiple consecutive columns or cells) if you want to run it through the Excel Address Validation Add-in. You will first need to combine the address elements into a single cell.

One way to accomplish this is by using the TEXTJOIN function:

=TEXTJOIN(" ", TRUE, A2:E2)

Here's a breakdown of the arguments.

• " " adds a space between the values of each column,

• TRUE ignores any blank cells in the range,

• A1:E1 combines all the values from A1 to E1 into a single value.

Here's an example of TEXTJOIN:

TEXTJOIN would create an output like this:

Installing the Excel add-in

Have no fear! Smarty is here! We don't mind holding your hand through this process, either. We like you that much.

Here's how to install the add-in in a simple, step-by-step way with screenshots to help you along your journey:

1. Create your free account: Go to any of our product pages (in this example, we'll demonstrate through US Address Verification's Free Trial), and click "Start."

You'll be taken to our shopping cart page. Don't worry. It's totally free. We're just giving you a receipt for your records. Click continue.

You can now click the box that says you agree to our Privacy Policy and Terms of Service and select how you'd like to sign up. For this walkthrough, we'll use the "Continue with Google" option.

Once you have selected your desired account, Smarty will bring you to this sign-up page! Fill out the sections and click "Create Account."

After filling out the form, a helpful, happy little pop-up box will appear, verifying that you have signed up for the greatest free trial ever.

Click "Start using your account."

2. Get the secret keys: Once you've signed up, you can access our dashboard. Neat! Select "API Keys."

Congratulations! You now have the Auth ID and Auth Tokens. Feel free to copy them by clicking on the copy link!

3. Add the keys to the add-in within Excel

4. Explanation of the "license" feature

If you have more than one active Smarty license (let's be real, you probably have them all), a best practice is to list the license value found on the subscriptions tab to instruct the plugin where to pull data and lookups from. For example, if you want rooftop geocodes but also have "US Address Verification," you'll want to specify to draw from the "US Rooftop Geocoding" license.

Validating addresses in Excel

Select the addresses you would like validated from your imported list. Leave the first row blank so that the headers for each column can be populated.

Next, click "Validate Selected Addresses."

Voila! This will populate columns to the right of your highlighted columns, indicating whether your contact records are validated or not. Batch address validator tool = 1. Wrong addresses = 0.

But what about the other batch geocode and address verification services you see below? Don't worry! We're covering that next.

Geocoding lists of addresses

Smarty's address verification and geocoding Excel add-in can use the same process to append geocodes with rooftop-level precision to your addresses. To perform this function, you will need a US Rooftop Geocoding license. You can add this paid subscription to your account under "Subscriptions."

In Excel, highlight the mailing list to which you want geocodes appended (make sure the first row is blank) and select "Validate and Retrieve Geocode." In our example, we have chosen "Enhanced" and indicated we're using the "us-rooftop-geocoding-cloud" license. You will need a license to access the rooftop-accurate geocodes using the batch address verification tool.

Once we submit the request, the incoming addresses will now contain appended lat/long coordinates alongside their precision-level results and confirmation levels.

The difference between ZIP9 and Rooftop is pretty important to understand when analyzing thousands of addresses in a spreadsheet. You can read more about that distinction and what it means for you and your organization here.

Excel add-in capabilities & limitations

The Excel address validation add-in can be used to validate as many US addresses, both postal addresses and non postal addresses, as Excel and your subscription will allow. Literally millions of addresses won't stop this thing. Your free trial guarantees you at least 1,000 address lookups. However, a pay-to-play plan on an annual or monthly basis can greatly expand these capabilities. We even have unlimited plans if you wanna go whole-hog.

Maybe you just REALLY don't want to download one more thing. We get it. You can also use our bulk address validation and geocoding tool. This tool can do everything that our address verification and geocoding Excel add-in can, but it can also process international addresses, find cities by ZIP Codes, ZIP Codes by city, and much more. Full documentation can be found here.

For your largest file with street addresses, use our CLI tool as it has no size limitations other than your subscription level.

Section 2 - Excel Address Validation: How-to via spreadsheet upload

You don't want to download one more stinkin' thing onto your computer, much less an add-in. We get it. That's why Smarty made it easier.

You can now use our batch address verification and geocoding services for Excel spreadsheets. It's a simple data prep and upload process that puts your address validation game up a notch. Our US Bulk Address Validation and International Bulk Address Validation tools always provide accurate address results and geocoding information to boot.

Preparing your address data

Your top row in your spreadsheet MUST content column headers that are spelled exactly like this:

For US street addresses:

street | city | state | zipcode

street | city | state

street | zipcode

address (entire address in a single column)

Secondary address information such as suite, apartment, unit, etc. should be included in a separate column that is labeled, "secondary." This can be included in any of the first three options we mentioned earlier.

For example:

street | secondary | city | state | zipcode

You can look at this sample spreadsheet here but be warned that this may make you hungry.

You can also perform an Excel spreadsheet batch address validation with international street addresses. Here's the format:

country | address1 | locality | administrative_area | postal_code

country | address1 | locality | administrative_area

country | address1 | postal_code

country | freeform (entire address except country in a single column)

Once it's neat and tidy, save your spreadsheet in one of the following file formats: .xls .xlsx .csv .tsv .psv .txt

Validating addresses via spreadsheet upload

If you don't already have a free trial, you'll want to head over to Smarty.com and get started! Refer back to "create your free account" for a refresher on how to do this.

If you already have an account, your spreadsheet is prepped, so let's get your delivery addresses validated! Head to your Smarty account and click on "Tools" in the left-hand column.

Next, select "List Processing."

You'll be taken to this page, where you can use our batch address verification services for your entire list of addresses simply by uploading that beautiful file you just prepped. Be sure to note whether you are doing this Excel address validation for United States, International, or ZIP Codes. For this example, we'll be focusing on US Bulk Address Validation.

Select "enhanced matching for more flexible address matching and to gain additional access to over 20 million non-USPS addresses.

Ta-da! Sit back and enjoy the results of crisp, valid addresses! Don't address checks feel nice? Feel free to watch our video again, if you so choose, to see what Excel validation and geocoding look like in action.

Geocoding lists of addresses

Make sure you have a geocoding license through Smarty, which should appear in the dropdown license menu. You can select that before clicking "PROCESS MY LIST" to receive rooftop-accurate geocoding as an additional metadata point in your validating and geocoding results.

Upload tool Capabilities & limitations

This tool helps you excel (see what we did there?) in address validation and Excel geocoding. It's virtually unlimited in the number of addresses it can process; the only limitations come from your subscription parameters and the capabilities of Excel. Smarty even provides you with 10 free addresses in the demo tool.

Comparison table: Excel add-in vs bulk upload web tool

Each of our tools has unique capabilities and limitations. If you're still a little fuzzy on which tool you should use, this comparison table will help you decide.

What is all the additional metadata in the response? What can I do with it?

There are many helpful data points that can be returned along with the validation and geocoding process when using Smarty's Address Verification Add-in in Excel. Here is just a brief overview of a few of the ones we think you'll find useful when managing your databases.

Residential Delivery Indicator (RDI)

The Residential Delivery Indicator (RDI) tells you if a mailing address is a residential or commercial address. This is tracked through the United States Postal Service (USPS) and is highly useful when ensuring that when you send something, it's being received.

County FIPS Codes

US addresses don't automatically say what county they're in by default. So, if county data is important (insurance people, we're looking at you), County FIPS Codes and county names are included as a metadata point through the US Address Verification Excel Add-in as well.

DPV footnotes & DPV match code

The DPV footnotes & DPV match code provide valuable analysis of the deliverability of each address. It is the process of verifying that an address is actually deliverable and show what might be missing from that address to make it so. The level of match that is reported can tell you how accurate that match is.

Other metadata points

Smarty offers comprehensive address validation services with up to 55 data points across address components, metadata, and analysis.

Here are key examples you may find useful:

• Vacant:This metadata point indicates that a delivery point was once active but is currently vacant or unoccupied. It indicates that the residence is no longer accepting deliveries as the mail receptacles aren't being emptied and are filling up. This usually happens when a resident hasn't picked up packages or mail for over 90 days. Y = Address is vacant. N = Address is not vacant. [blank] = Address wasn't submitted for vacancy verification.

• Time zone & Daylight Savings Indicator (DST): This information indicates whether the time zone "obeys" daylight savings time rules. AKA, does this address spring forward or fall back and adjust their clocks accordingly? True = Time zone observes daylight saving time. If the DST is absent from the response, then the address does not observe daylight saving time.

Explore all available data points and their definitions in the US Address Verification API documentation.

TL:DR - Can we skip to the good part?

Batch address verification and geocoding in Excel just got a whole lot easier with Smarty's streamlined process. After installing the plugin, you can integrate it into Excel and use your Smarty authentication ID and tokens, depending on the license you've purchased or gotten via free trials.

At Smarty, we geek out about maintaining clean spreadsheets. Whether you're validating addresses for US locations with our US Address Verification Excel Add-in, or tackling international addresses through our bulk validation tool, we've got you covered. We'll even give you a sample CSV file to download and try out. Try Smarty's address verification tools today:

Talk to an expert here.

Download the plugin here.

View our helpful webinar here.