Author: Adam DuVander

Excel is a powerful tool that experts and novices alike turn to for their data needs. This includes location data like addresses, cities, and states, and even zip codes, where Excel tricks come in handy. There are tricks to keeping or showing leading zeros in zip codes and to shortening zip codes to five digits.

In this post, we’ll show you how to:

• Keep or show leading zeros
• Shorten to five digits from Zip+4 format
• Make a map based on a list of addresses or zip codes

The first Excel zip code trick is how to keep or show leading zeros.

Keep or Show Leading Zeros

As you type numbers beginning with “0” into Excel or Google Sheets, the zeros are automatically removed. However, there may be times when you wish to keep those leading zeros. Phone numbers, product numbers, and zip codes are examples of such occasions. Imagine you’re working with location data from Puerto Rico. It would be incredibly frustrating to type in a “00923” zip code only to have “923” appear.

Keep Leading Zeros as You Type

While keeping leading zeros isn’t the default in Excel, there is a way to keep zeros as you type by changing the format to “Text.” Do this by following the steps below:

1. Select your desired cell(s)
2. Navigate to the Home tab in Excel
3. Click the dropdown of the Number option
4. Select Text

This amends the cell’s default settings, though an error ⚠️ message will appear. Click Ignore Error, and now, leading zeros will remain as you type. Alternatively, you can add an apostrophe (’) in front of the number with leading zeros, and Excel will treat it as text. However, perhaps you need your zip codes in a number format so you can use other Excel magic. If this is the case, you’ll want to show leading zeros a different way.

Show Leading Zeros in Cells

You can show leading zeros in cells in a way that still treats the data as a number via formatting.

1. Select your desired cell(s)
2. Right-click on the cell(s) and select “Format Cells…”
3. Navigate to the “Special” Category
4. Select Zip Code

This method is best for situations in which you need to work with a numeric dataset. Another Excel zip code trick you need to know is how to shorten zip codes to five digits.

Shorten to Five Digits

While less common, U.S. zip codes occasionally come in a nine-digit form called Zip+4. In fact, the five-digit zip codes most of us are familiar with are actually abbreviated versions of nine-digit ZIPs. In many cases, though, five-digit zip codes are preferred. If your spreadsheet contains nine-digit zip codes you want to get rid of, you can shorten the nine-digit zip codes to five digits in one fell swoop with a simple formula.

Follow the steps below to shorten to five digits:

1. Insert a new column to the right of your current zip code column
2. Type or copy and paste =LEFT(C2,5), replacing C2 with your cell’s identifier
3. Drag the formula down the rest of your column

Now your nine-digit ZIPs are shortened to five-digit zip codes. And, since you also now know how to keep and show leading zeros, you can take your data one step further and make a map.

Make a Map

Once you’ve mastered Excel zip code tricks like displaying leading zeros and shortening numbers to five digits, you can make a map. BatchGeo allows you to map your data for free. Create custom maps like the one of places in San Juan, Puerto Rico below.

View San Juan, Puerto Rico locations in a full screen map

When you’re ready to make your own map, gather your data in a spreadsheet. Our post on how to simplify complicated data in Excel spreadsheets can guide you. Once you have your data, get started making your map with the following steps:

1. Open your spreadsheet
2. Select (Ctrl+A or Cmd+A) and copy (Ctrl+C or Cmd+C) all your data
3. Open your web browser and go to batchgeo.com
4. Click on the location data box with the example data in it, then paste (Ctrl+V or Cmd+V) your own data
5. Check to make sure you have the proper location data columns available by clicking “Validate and Set Options”
6. Select the proper location column from each drop-down
7. Click “Make Map” and watch as the geocoder performs its process

Now that you know how to make a map with BatchGeo, you can make custom maps like cities around the world by latitude and longitude or national animals of every country. You can even customize the base style and marker shape and color of your maps. It’s easy to let BatchGeo’s geocoder map your data in seconds, leaving plenty of time to customize your map exactly how you want.

By 2027, cars may be exclusively self-driving and universal health care may not just be a distant dream. Also by 2027, seniors (60+) will begin leaving millions of houses in metro areas across the United States. As baby boomers continue to age, millions of their residences will be put on the market, resulting in a large housing boom. This is all set to take place between 2027 and 2037 and it’s been dubbed the “Silver Tsunami.”

As with any tsunami—water-based or otherwise—it’s best to be prepared. First-time homebuyers may wish to be especially prepared as the Silver Tsunami is expected to put millions of homes on the market, lowering the market rate. However, some metro areas will see a heavier influx of available homes than others. Therefore, preparation for this housing boom involves knowing which metro areas will see the highest increase in houses on the market between 2027 and 2037.

View Silver Tsunami in a full screen map

The map above displays Zillow’s mortality data which is used to predict the metro areas that will see the most homes go on the market. You can manipulate the sortable categories on the map so that you too can understand the Silver Tsunami and how it will affect the housing market. Or read on for an explanation as to where the Silver Tsunami will hit the hardest when it begins in 2027.

Locations Hit Hardest by Silver Tsunami in 2027

By 2027, seven metro areas in the U.S. will see 13.5% or more of houses put on the market as a result of the Silver Tsunami. Three of the seven areas are located in the same state, as you can see on the table below or on the map when sorting by 2027 and selecting the first range (“15.2%-13.5%”).

Metro Area	State	By 2027
Tampa	FL	15.20%
Miami	FL	15.20%
Tucson	AZ	14.80%
Orlando	FL	14.40%
Dayton	OH	14.30%
Pittsburgh	PA	13.60%
Knoxville	TN	13.50%
Greensboro	NC	13.30%
Cleveland	OH	13.20%
Buffalo	NY	13.20%

It seems Florida is the place to be (or at least buy a home) in 2027. A popular retirement hub, it’s no wonder so many homes are expected to go on the market as a result of the Silver Tsunami. The metro areas most affected in the Sunshine State are Tampa, Miami, and Orlando. These three Florida cities have anticipated rates of homes released to the market by seniors above 14%. Yet Tucson, Arizona, breaks up the Floridian monopoly of the top spots. Tucson has an estimated share of homes to be released to the market by seniors just above Orlando.

Additional metro areas that can expect an influx of homes on the market after the passing of elderly folks include Dayton, Pittsburgh, and Knoxville. And while Knoxville is the last to have a rate above 13.5%, it should be noted that Greensboro, North Carolina, Cleveland, Ohio, and Buffalo, New York also have significantly higher rates than most metro areas. Greensboro will likely see 13.3% of houses go on the market while both Cleveland and Buffalo can expect 13.2%. Ohio, like Florida, has more than one metro area where plenty of current-seniors’ homes will be put on the market. So, renters in Florida and Ohio may want to start saving up for 2027 when tons of houses are sure to swarm the market.

However, those who don’t end up finding a home they love in 2027 can wait ten more years to get more than double the choices.

Ten Years Later: Where the Silver Tsunami Strikes in 2037

Just ten years after the start of the Silver Tsunami, available houses will have more than doubled in many metro areas. While you may predict that the Silver Tsunami will benefit the same places in 2037 as in 2027, it’s not 100% the case—aside from Tampa.

In 2037 as in 2027, Tampa, Florida reigns as the #1 metro area with the most houses available from the Silver Tsunami. Tampa’s available homes will increase from 15.2% in 2027 to 33.2% in 2037—an 18% change.

However, unlike Tampa, the number of homes on the market in Miami did not increase enough from 2027 to guarantee a second-place ranking again in 2037. In fact, purchasable homes in Tucson, Arizona are expected to surpass those in Miami by 2037. In 2027, 14.8% of homes are expected to be put on the market in Tucson. In 2037? Over thirty-two percent of homes will become available—an increase of 17.8% in just ten years. This is why Tucson ends up at #2 a decade after the initial Silver Tsunami wave. In 2037, Miami will drop to #3 with an increase in homes of just 16.7% from 2027.

Orlando continues being the fourth metro area with the biggest increase in homes in 2037, as does Dayton in fifth. However, Knoxville and Pittsburgh flip in rank from 2027 to 2037. Available homes in Knoxville will have risen to 30.8% (a 17.3% change from 2027) while Pittsburgh’s purchasable houses will reside at 30.2% (a 16.6% change). While Pittsburgh may have gone from #6 to #7 in the ranks, the metro area is still notable for being the last with an available housing rate of 30% or more in 2037.

Other interesting data trends include the disappearance of Buffalo, New York from the top ten in 2037 as well as Albuquerque, New Mexico’s sudden appearance. Buffalo’s available homes are only set to increase by 15.8%, earning it a rank of #12 while Albuquerque should increase by 16.9%, putting it in ninth place in 2037.

Yet, not every metro area is as lucky as the ones noted above when it comes to the Silver Tsunami.

Regions Rejected by the Silver Tsunami

A decade is more than enough time for the Silver Tsunami to make its way across the nation. Yet, by 2037, several metro areas in the U.S. aren’t anticipated to see much of a housing boom.

Viewing the map with BatchGeo’s heat view feature enabled, we see seven states with metro areas notably absent from any activity. These include Montana, Idaho, Wyoming, North and South Dakota, Nebraska, and Iowa.

The heat view also makes it clear that aside from the single metro areas of Oregon (Portland) and Washington (Seattle), the Silver Tsunami pretty much skips the Northwest. Now, this doesn’t mean the children and grandchildren of baby boomers are hoarding their homes in this region. What it does mean is that there likely isn’t a huge population of older folks in these areas.

On the other hand, the entire East of the country (aside from the hole that is West Virginia) lights up like a firecracker via heat view. This means more baby boomers live in metro areas on the East Coast than the West and they’ll likely leave millions of houses on the market between 2027 and 2037.

---

So, you want to save big and buy a house in 2027 or 2037? Start planning now. Make an open house map or any other map today for free with BatchGeo.

When you have children, summer breaks can seem long and never ending. Even for those without kids, it can be nice to break up monotonous summer months—or any other time of year, really—with a fun road trip. However, it’s not all snacks and singalongs; road trips require a lot of planning.

You may know that you want to pass through specific cities or see certain attractions along the way. Maybe you’re also planning to stop somewhere for the night to recharge or to prolong your stay at your final destination. You could start your road trip plans by making a mental list of these locations or maybe you’ll save yourself a headache by writing it down somewhere.

Wouldn’t a map make more sense? You can first brainstorm your desired locations in an Excel spreadsheet, then easily make a Google Maps road trip map you’ll be able to load on your phone.

You can create your road trip map with the three steps below:

• Determine the desired locations of your road trip
• Compile a spreadsheet (with categories and directions)
• Visualize the destinations of your road trip with Google Maps

As with any journey, to get started, first determine where it is you wish to go.

Determine the Desired Locations of Your Road Trip

The first step in planning a road trip with Google Maps is perhaps the most fun: deciding where you want to go! There are millions of cool places you can navigate to by car, though you may end up focusing on just one or two desired locations and the stops you’ll make along the way.

Spontaneous road trippers may feel extensive planning takes the excitement out of a road trip. After all, you could theoretically save time by just getting in the car and going, searching for attractions and restaurants near you on your phone. However, foodies and planners know that to get the best experience out of your trip, you can’t just stop at the nearest McDonald’s. Plus, if you have kids, food allergies, or specific ways of eating, prior research is always appreciated.

So, you may know that you want to pass through specific cities or see certain attractions along the way. Maybe you’re also planning to stop somewhere for the night to recharge or to prolong your stay at your final destination. Or, if you’re struggling to come up with ideas, you can always look to popular destinations, such as:

• National Parks
• State Parks
• Historic Locations You Can Still Find on Route 66
• World’s Largest Roadside Attractions

Other than attractions, you may also want to plan stops at lodging, gas stations, family, or restaurants like the best restaurants in 25 major cities.

You could keep a mental list of all these locations, or maybe you saved yourself a headache by writing it down. But the best way to keep track of your road trip locations for mapping purposes is to first add them to a spreadsheet.

Compile a Spreadsheet (With Categories and Directions)

With a better idea about the locations you wish to visit throughout your road trip, transfer them from your mental list, Word document, or Notes app into an Excel spreadsheet or Google Sheets. This will aid you when it comes time to create a Google map.

To compile your spreadsheet, you’ll want to put the information into several different columns with headings such as:

• Location name
• Address
• City
• State
• Postal code
• Category

For some Excel tricks on how to easily separate your data into columns, see Simplify Complicated Data in Excel Spreadsheets.

The address, city, state, and postal code are fairly self-explanatory; they’ll be needed for mapping the locations later. Location name is meant to identify the place you want to visit. You may choose to name it something like “Grandma’s house” or “highest-rated restaurant in Casper, Wyoming.” A category heading and subsequent column is useful if you want to later be able to sort your map.

Why Categorize Your Locations in a Spreadsheet

By assigning each of your desired road trip locations a “category” or “type,” you’ll be able to sort your map by the categories, filtering out the rest. This will certainly come in handy if, in the middle of your road trip, your hangry partner needs food immediately. Bypass attractions, gas stations, and lodgings when you sort the map by restaurants or fast food joints. Or, if you’re past “E”, you can narrow down gas stations without seeing the other points on your map.

To add categories, simply add a new column in your Excel spreadsheet. In the new Category column, assign each location a one or two word categorical description such as “Attraction,” “Lodging,” “Restaurant,” or “Gas.” You’ll elect if and how you want these categories to appear on your map when you start plotting your points.

Link Directions Between Places With This Excel Skill

Another useful column you may wish to add to your Excel spreadsheet are links to the Google Maps directions between your places. However, you don’t want to manually search Google Maps. Instead, make use of Excel’s CONCATENATE and knowledge of Google Maps’ links to come up with a formula like this:

=SUBSTITUTE(CONCATENATE("http://maps.google.com/?q=", B2, " ", C2, " ", D2, " ", E2, " to ",B3, " ", C3, " ", D3, " ", E3), " ", "%20")

As you likely know the order in which you’ll be visiting each location, the formula above creates a direction link from this row’s location to the next row’s location based on the columns you already created. So to work, the road trip locations need to be in a specific order. The last stop is empty because…you will have arrived! Now let’s see how to get this spreadsheet with categories and links into Google Maps.

Visualize the Destinations of Your Road Trip With Google Maps

Now that you’ve determined some of the locations of your road trip and compiled a spreadsheet complete with categories, it’s time to map your road trip with Google Maps, via BatchGeo. BatchGeo uses the Google Maps Geocoding API, known to have some of the highest quality data available. Other services require you to write code or manually geocode addresses one by one, but our service performs everything you need to do in several easy steps:

1. Open your spreadsheet
2. Select (Ctrl+A or Cmd+A) and copy (Ctrl+C or Cmd+C) all your data
3. Open your web browser and navigate to batchgeo.com
4. Click on the location data box with the example data in it, then paste (Ctrl+V or Cmd+V) your own data
5. Check to make sure you have the proper location data columns available by clicking “Validate and Set Options”
6. Select the proper location column from each drop-down
7. Click “Make Map” and watch as the geocoder performs its process

You now have a map of your road trip with Google Maps, like the one below:

View Yellowstone road trip example in a full screen map

Our example road trip map depicts travel from Cheyanne, Wyoming to the destination of Yellowstone National Park (for the geysers, of course!). With two attractions and various restaurants, gas stations, restrooms, and hotels, the map has everything you need when traveling long (or short!) distances. You even have the option to automatically number each stop along the way in a particular order.

We may be biased, but we think BatchGeo is one of the best road trip apps. Plan your own road trip with Google Maps today via BatchGeo, which uses the Google Maps API so that you can create custom maps for free to better visualize your data.