If you own a business or your job has anything to do with sales, you likely have all sorts of information about your customers or leads. But are you making the most of this data?

If you store this information in Excel or another tool that can export a CSV file, you may be overlooking key metrics that could help improve your company, such as dates. For example:

• First purchase date
• Last purchase date

You can make more of the data you have by going from this…

…to this!

When you separate dates in Excel, you can sort your data by year, month, and day, making it easier to find patterns. You can also use Excel to compare dates, determining the duration of the engagement you’re measuring.

We used the following tips to split data of the birthdates and tenures of 115 US Supreme Court Justices, which is why the customer names in our examples may sound familiar. So, let’s get started with Excel’s formulas and features.

Split Dates into Sortable Days, Months, and Years

Say you have a cell that contains year, month, and day — this may not be the best format if you’re trying to isolate the most common months.

There are many ways of separating dates into unique columns in Excel, but perhaps the easiest is using “Text to Column.” Google Sheets has a similar feature. Here’s how to do it in Excel:

• Select the cells you’d like to separate.
• Navigate to the “Data” tab.
• Select “Text to Columns…”.
• Choose whether your data is currently separated by Delimited characters such as commas or colons or Fixed width with spaces between each field (we usually opt for the first).
• Click “Next” and either check off the delimiters your data contains (tabs, semicolons, commas, spaces, or you can customize any other delimiter, such as backspaces or hyphens) or set the column breaks.
• Select “Next” and choose where you want your separated data to end up.
• Click “Finish,” and you’re done!

Now that you’ve separated days from months and years, you can sort the individual columns or use pivot tables to count and summarize. You can also use an Excel formula to compare dates.

Calculate the Time Between Two Dates

You have two dates split into individual columns in Excel. Some data analysis requires you to calculate how many years, months, or days are between them. Below is the simple Excel formula that allows you to do this:

=DATEDIF(Start_date, End_date, Unit)

• In a new column, start by typing “=DATEDIF(“
• Follow up by either clicking the cell with your start date or typing in its corresponding letter and number (i.e., D2) followed by a comma.
• Now, click your end date or enter it in (i.e., E2), again followed by a comma.
• Next, in quotations, specify whether you want the time between the two dates to be in days, months, years, or some combination.
• “d” is used for the difference in days
• “m” means the difference in complete months
• “y” is for the difference in complete years
• Close your parentheses.

Note: There are also ways to exclude data, such as:

• “md”: Difference in days, excludes months and years
• “ym” : Difference in months, excludes years
• “yd”: Difference in days, excludes years

For our purposes, we want only the years and days between our two dates so we used two formulas in two columns, “Years,” and “Days.”

=DATEDIF(D2,E2,“y”)

and

=DATEDIF(D2,E2,“yd”)

Drag it down all your rows, and you’ll automatically calculate the difference!

Map Your Data and Dates

With your data organized and some Excel math under your belt, let’s see how you can get the most out of your spreadsheet. Plotting your points on a custom map is the natural next step if your data contains locations, such as addresses, cities, or states.

There are quite a few ways to do this, from desktop GIS software like ArcGIS or a Google Maps API, as explained in Introduction to Map Making on the Web. But the easiest method is to use Batchgeo, a dedicated tool that geocodes your location data. Here’s how to make a map with our free geocoder:

• Open your spreadsheet.
• Select (Ctrl+A or Cmd+A) and copy (Ctrl+C or Cmd+C) your data.
• Open your web browser and navigate to batchgeo.com.
• Click on the location data box with the example data, then paste (Ctrl+V or Cmd+V) your data.
• Ensure you have the proper location data columns by clicking “Validate and Set Options.”
• Select the proper location column from each dropdown.
• Click “Make Map” and watch as the geocoder performs its process.

Thanks to splitting our data and our date math, our map looks like this:

View Sales Data with Dates (After) in a full screen map

Otherwise, it would have looked something like this:

View Sales Data with Dates (Before) in a full screen map

Check out our other Excel tips to upgrade your spreadsheet game even further:

• How to Find Duplicates in Excel
• Excel Data Visualization Examples
• Simplify Complicated Data in Excel Spreadsheets

Or get mapping today for free at batchgeo.com.

What makes Google Sheets such a collaborator’s paradise? If you’re familiar with the web-based spreadsheet application, you know it enables users to create and edit files online while collaborating with others in real time.

Multiple users can simultaneously view and edit a spreadsheet, making it ideal for group projects, data collection, and analysis. This article will explore these collaborative properties and show how Google Sheets can hold and shape data that you can then use to create a map.

Google Sheets Was Created for Collaboration

What are the features that facilitate collaboration in Google Sheets? Let’s look into them.

Let’s say you’re assigned a group project on national flowers and trees. You might gather the data from Wikipedia (flowers and trees) and format the data for a map.

The following Google Sheets features will help you finish this assignment or similar tasks as painlessly as possible:

• Tracking edits and revision history
• Highlighted editor changes
• Permissions system

Tracking Edits and Revision History

Google Sheets automatically tracks changes made by each user in a shared document. The revision history feature provides a detailed log of all edits, including who made the changes, what changes were made, and when they were made. Users can review and revert to previous versions of the document, ensuring data integrity and accountability.

Highlighted Editor Changes

When multiple users edit a Google Sheet, each user is assigned a unique color and cursor. Changes made by each editor are highlighted in their respective colors, making it easy to identify who’s working on which parts of the spreadsheet in real time.

Permissions System

Google Sheets also offers a robust permissions system that allows document owners to control access and actions within the document. Owners can set permissions to specify who can view, edit, or comment on the document. This system ensures that sensitive data is protected, and that only authorized users can make changes.

With a better understanding of how to use Google Sheets collaboratively, we can move on to how to take your data and turn it into a map.

Map Your Google Sheet Data

View National flowers and trees in a full screen map

Spreadsheets are great tools for storing your data. Some even offer a few features that can help put your data into context (highlighting duplicates, creating pivot tables, etc.). Plus, we’ve just gone over how much Google Sheets excels at all things collaboration.

But when it comes to visualization, most spreadsheets, even Google Sheets, fall short. So, if you or anyone in your group is a visual learner, and your data contains location information like countries, states, or cities, consider moving the data into a custom map.

Here’s how to do it:

• Open the spreadsheet.
• Select (Ctrl+A or Cmd+A) and copy (Ctrl+C or Cmd+C) the data.
• Open a web browser and navigate to batchgeo.com
• Click on the location data box with the example data in it, then paste (Ctrl+V or Cmd+V) your data.
• Check you have the proper location data columns by clicking “Set Options”.
• Click “Map Your Data” and watch as the geocoder turns your data into geographic coordinates.

Once you’ve completed these steps, you can share the link with the rest of the group.

If anyone else wants to edit the data, they have to do so on the shared spreadsheet. However, BatchGeo Pro users can edit the map’s style, marker shape, colors, and more without using the same account.

Share Your Map with More Members

With your group’s data mapped, it’s time to learn how to make edits together. As we mentioned in the previous section, BatchGeo Pro users can edit a map with no need to share usernames or passwords.

To ensure the others can edit, you can right-click the map and select “Edit Map.” In the “Share” section, you can choose between “Public,” “Unlisted,” or “My Subscription Users.”

• Public: Anyone can discover your map through search or on our site
• Unlisted: Only those with the unguessable URL can access your map, which will not be searchable or listed on our site
• Your Subscription Users (Pro): Up to 10 users can access your map (best for collaboration)

You can also opt to “Password Protect” your map right below the “Share” options. This way, your account will have direct access when logged in, while other users will need to enter a password to see the map.

Continue the collaboration with BatchGeo or BatchGeo Pro!

From California to Connecticut, the USA has witnessed the birth of over 150 basketball players inducted into the Naismith Memorial Basketball Hall of Fame.

More than a dozen other countries have also offered a native son—or two, or three—who went on to achieve the highest level of success in the NBA.

View NBA Hall of Famers by Birthplace in a full screen map

Click any marker and see the details for the Hall of Fame players, including their stats and ages. You can even choose one or more categories to filter your data. For example, did you know that the 12 players with more than 22,000 career points and who retired before 37 were all born east of the Mississippi River? Facts like these are far easier to see on a map than to read in a spreadsheet.
Almost Every Hall of Famer is from the USA
Basketball was invented in 1891 in Springfield, Massachusetts. With its roots in the United States, it’s no surprise that nearly 90% of inducted players are American. Their birthplaces are scattered among 28 states and 110 cities.

The top states for basketball inductees roughly follow those with the largest populations. New York, California, and Pennsylvania lead the way. But the top 10 (or so, there are some ties) includes some surprises:

• New York (23)
• California (17)
• Pennsylvania (13)
• Illinois (11)
• Louisiana (8)
• North Carolina (8)
• Michigan (7)
• Virginia (6)
• Ohio (6)
• Kentucky (6)
• Texas (6)

Louisiana and Kentucky are 25th and 26th in population, yet their players shine in basketball. Similarly, Texas has a larger population than every state but California, yet it barely makes the list.

Basketball breaks tradition with baseball and football, which share Chicago as the most common birth city. As mentioned, the NBA largely follows population trends in producing Hall of Famers.

New York City leads the way by far with 17 players (including eight from Brooklyn, a borough of NYC). That leaves only six New Yorkers from outside the Big Apple in the Hall. Other top cities include Philadelphia (6), Oakland (4), Chicago (4), Detroit (4), and Washington DC (4). Ten other cities can claim two or more Hall of Famers.

Interestingly, no players in the Hall of Fame were born in Massachusetts, despite the state being the birthplace of basketball. Of course, basketball has since been exported all over the world. One in 10 basketball Hall of Famers comes from outside the US.
Countries with the Most Basketball Hall of Famers
While the international wing of the Hall of Fame is much smaller, it is well represented. Foreign-born Hall of Famers in basketball come from 18 countries, more than any other American sport. For example, the Baseball Hall of Fame has players from nine non-US countries.

Which countries produce basketball stars who play in the US won’t surprise anyone watching the sport in the last few decades. To start, we turn to Eastern Europe, which has a significant presence in the Olympics, the European leagues, as well as the NBA. Croatia has three Hall of Famers, and Lithuania has two. All other countries only have one inductee. Europe continues to represent, with a player each in Germany, Spain, France, Belgium, and Serbia.

We can look further than Europe, though. Almost every continent has at least one player in the basketball Hall of Fame. Africa has three (Hakeem Olajuwon was the first, in 2008). South America has Manu Ginóbili (2022), while Asia has Yao Ming (2016). There are no Hall of Famers from Australia or New Zealand. Or Antarctica.

There are two cities outside the US that have made an impact on the Hall of Fame: Kaunas, the largest city in Lithuania, is the birthplace of both Arvydas Sabonis and Šarūnas Marčiulionis; Meanwhile, in Croatia, the city of Split (the second largest in the country) splits its pride between Toni Kukoč and Dino Radja.
Make a Map of Your Own Data
You may have your own data sitting in a spreadsheet on your computer. Instead of basketball Hall of Famers, it might consist of customer addresses, business locations, or a list of deliveries. In each case, maps can help tell the story. They’ll plot your course or show the trends.

BatchGeo is your fastest way to make a custom map. There’s even sample data to use if you’re not yet sure which spreadsheet to upload. Try it today for free!