Excel Zip Code Tricks: Leading Zeros, Shorten to Five Digits, and Make a Map

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:

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.