Google Sheets is an incredibly powerful tool when it comes to managing your data, but like all spreadsheet software, it has a few quirks. When you’re working with locations on a spreadsheet, you may notice your ZIP codes in an odd format, for example, missing the leading zeros that make up the code. 

What can you do to fix these errors and make sure you can map your data accurately? In addition, how can you standardize your ZIP codes to be five digits rather than nine? In this article, we’ll talk you through how to do both.

Formatting US ZIP codes

If you have a spreadsheet with US ZIP codes, you’re likely seeing the leading zeros stripped for states like Maine and Connecticut or territories like Puerto Rico. This happens because Google Sheets automatically treats your field like a number, where a leading “0” is unneeded. This clearly isn’t true for a ZIP code, though. 

The issue can be easy to miss and annoying when you move on to creating a map using the data.

Luckily, there’s a fairly simple solution, which involves changing the format of your cells. Click Format in the top navigation of your Sheet, go to Number, and scroll down to the bottom of the menu to click Custom number format.

In the window that appears, put in either “00000” or “00000-0000” as your format, depending on the number of digits you have in the ZIP codes you’re dealing with. This allows you to standardize to one format. 

If you have a mix of five- and nine-digit codes, the next section shows you how to make your ZIP codes the same length.

And like magic, your cell will show all the digits rather than removing the leading zeros.

Converting Nine-Digit ZIP Codes to Five

The US has two ZIP code formats: the commonly seen five-digit ZIP code and the ZIP+4, also known as an extended ZIP code. These nine-digit ZIP codes include the same information as the five-digit codes, plus additional information about delivery routes in the area. 

If you’re dealing with a large number of addresses, chances are the ZIP codes will be a blend of five- and nine-digit numbers. Standardizing to the five-digit format is the easier approach, as you remove the extra four digits rather than expanding them to nine.

While this isn’t quite as simple as bringing back the leading zeros, it’s still straightforward. In your Sheet, add a column to the right of the one containing your ZIP codes by clicking Insert, then Columns, then Insert 1 column right.

In the cell to the right of your first row, add the following formula, making sure it specifies your cells: `=LEFT(A1,5)`.

Click the dot on the bottom right of your resulting cell and drag it down all your rows. This will automatically apply the formula to all selected cells in that column.

Make a Map!

Now that you’ve properly formatted ZIP codes in Google Sheets and fixed two common issues, you can take that data and map it using BatchGeo. If you’re using Excel, we got you covered — check out our article on Excel Zip Code Tricks.

Maps are the best way to visualize location data. Create one today.

As H.G. Wells wrote, “Human history in essence is the history of ideas.” 1925 was certainly a year of big ideas — and resulting events — around the world.

Let’s take a look back at 1925 and the political, cultural, and social milestones that continue to impact the world today.

Between January and December, Wikipedia notes a number of major 1925 events, such as the Scopes Monkey Trial in Tennessee, which challenged the teaching of evolution in schools; the publication of The Great Gatsby in New York, which became a cultural icon; the opening of the first Surrealist art exhibition; and Britain’s return to the gold standard. 

These events and more are easily mapped using our Batchgeo tool.

View 1925 Events in a full screen map

The 14 Categories of 1925 Events

Here’s the full breakdown of the 14 categories the above article puts these major 1925 events into:

Category	No. of 1925 Events
Politics	16
Sports	3
Crime	3
Technology	5
Social & Culture	7
Education	2
Disaster	5
Conflict	2
Business	3
Exploration	2
Aviation	1
Art & Music	6
Massacre	1
Historical	2

Let’s take a closer look at where they occurred.

Events in the United States

Of the 58 major events that took place 100 years ago, 18 focus on one country: the United States. The most common categories mentioned are political, technology, and disasters. However, art and culture also made a strong showing, with two major events each.

Some notable events include Nellie Tayloe Ross becoming the first female governor (Wyoming) in the United States on January 5th. She was followed 12 days later by Ma Ferguson, who became the first female governor of Texas.

The Grand Ole Opry was first broadcast on radio as the “WSM Barn Dance” on November 28, starting what was soon to become a 100-year-old institution. And the Scopes trial (also known as the “Scopes Monkey Trial”) took place in Tennessee between July 10 and July 21, bringing intense scrutiny to the teaching of evolution.

Check out the rest of the US events on the map before moving on to the ones that occurred elsewhere in the world in 1925.

International Events

There may have been 18 major events taking place in the US, but the majority (40) happened internationally. History isn’t US-specific, after all. 

In January, the year started with a bang when the Federation of the Autonomous States of Syria was officially dissolved. The State of Syria replaced the States of Aleppo and Damascus. 

In February, Benito Mussolini gave a pivotal speech that’s largely seen as the start of his dictatorship. 

And March saw the first issue of the newspaper Pravda in what was then the Soviet Union; the publication continues in Russia today.

Later in the year, the Locarno Treaties were signed in London, intended to secure the post-war continental European territorial settlement. In Portugal, the money forgery and fraud activity of Alves dos Reis was exposed. Margaret Mead landed in American Samoa to begin nine months of fieldwork, which culminated in her book Coming of Age in Samoa. 

Meanwhile, 1925 was a year of technological advancements that continue to resonate a century on. Notably, in London, John Logie Baird successfully transmitted the first television pictures, in grayscale of course. And English racing motorist Malcolm Campbell became the first man to exceed 150 mph (241 km/h) on land.

We also can’t forget about the popular category of sports, which saw the formation of multiple football clubs. April saw the founding of the now well-known football club Colo-colo in Chile. And June brought the founding of the Turkish football club Göztepe.

Group Your Data with Batchgeo

You can group your data in a similar way when you map it with BatchGeo. And check out our previous posts highlighting the events that happened more than 100 years ago — these include major events in 1917, 1918, 1919, 1920, 1921, 1922, 1923, and 1924.

Online maps can be indispensable to navigate, explore, and visualize information. However, the power of these maps is often underutilized.

In this post, we’ll showcase how you can maximize your online maps by grouping data using markers. But before you can make the most of your online maps, let’s cover some basics.

Map-Making on the Web

You don’t need a background in geography or any programming knowledge to create custom maps on the web, but there are some important pointers to keep in mind as you create them.

Typically, web-based mapping tools rerquire a spreadsheet of the data you want to map — this can be in Excel, Google Sheets, or other compatible alternatives.

The data should include at least some location information, with separate columns for address, city, state, country, etc. If your data isn’t already separated, you can use the “Text to Columns” functionality to do so quickly.

In addition to location details, your data may include other information; one example is the height metric you can see on our map of the tallest lighthouses in the US.

This brings us to map grouping, through which you can select only the markers that meet certain requirements, filtering out the rest. Let’s talk about the benefits of map grouping.

What Is Map Grouping?

Groups can be combined to zero in on specific results, giving you insight into the story behind the map. While no prep work is required (BatchGeo will intuitively find a home for any additional data you have), you can certainly make the most of grouping by doing a bit of data manipulation, which we’ll show you next.

View Global city rating in a full screen map

The map above of every city’s globalization rating contains location information (in this case, city and country names). It also includes the classification, which becomes available for group selection.

Let’s try grouping some markers. Let’s say you want only the cities with the best globalization classification (Alpha + and Alpha ++):

• Hover above the dropdown in the lower left of the map to reveal the grouping columns.
• Choose ‘Classification’ from the menu. Each marker on the map will change color based on its group.
• Select the top two ranges (‘Alpha +’ and ‘Alpha ++’). As you select each, the map will filter to include only the markers that match your selection.

Troubleshoot Missing Map Groups or Ranges

One common hiccup in grouping is missing groups or unexpected formatting within your mapped data. But there are ways to troubleshoot these issues, as we’ll cover next, starting with missing groups.

Fix Missing Groups with “N/A”

If you find that a column eligible for grouping is not showing up in the dropdown menu at the lower left-hand corner of your map, follow these steps for effective troubleshooting:

• First, return to your spreadsheet and identify the column or columns that aren’t showing up for grouping.
• Next, look for any empty cells in columns that normally contain data.
• Sort the column(s) to push the empty cells to the bottom.
• Input “N/A” into the first empty cell, ensuring it’s in all caps.
• Drag this entry down to apply “N/A” to all empty cells.

Once completed, edit your map with the new data, and the missing groups should appear.

Adjust the Format of Non-Ranging Numbers

If your numerical data is grouping correctly but is lacking your desired ranges, it’s time to make further adjustments in your spreadsheet. Follow these steps to rectify the issue:

• Identify the column with the numerical data that isn’t ranging.
• Head to the “Format” menu and check the current format under “Number.”
• Toggle to “Plain text” or “Automatic”, whichever is not currently selected.

Of course, if you’d rather not have your data grouped, you can disable it by editing your map. In “Validate and Set Options,” select “Single Color” for the Group By option.

Maps Work Better with Grouping!

Thanks to online maps and the grouping feature from BatchGeo, understanding your location data is easier than ever.