Author: Adam DuVander

Excel spreadsheets are supposed to make your work easier. However, sometimes you find yourself saddled with a spreadsheet full of data in the wrong columns. Or maybe your spreadsheet contains unnecessary information mixed in with the important stuff. This often happens when copying and pasting or exporting large amounts of data from the web into Excel. You need to simplify your data—just not manually. Seeing as Excel offers hundreds of different features and formulas, you’re faced with another dilemma: how do you start simplifying?

In the spirit of simplification, we’ll demonstrate just three essential Excel skills:

1. Separate data into appropriate columns
2. Remove unnecessary data
3. Join data together

These three Excel skills make simplifying complicated data easy. We find ourselves using them frequently as we prepare location data to make custom maps. Take a look at how they helped us simplify one of our Excel spreadsheets that we eventually turned into a map.

The spreadsheet above contains rainfall totals for 282 U.S. cities which we pulled from the National Centers for Environmental Information. It looks pretty straightforward, right? However, our spreadsheet didn’t start off looking like this. Had it not been for the three Excel skills described—which also work in Google Sheets—it would have taken us hours to manually simplify the data. Below is what we actually started out with.

As you can see, the precipitation data for each city is housed in one cell, in one column, and is full of links that we don’t need. Not very useful, huh? Manually separating each of the 282 rows of data into individual columns and deleting the links one by one wasn’t realistic, so what did we do?

Separate Data in Excel by Splitting “Text to Columns”

Excel’s “Text to Columns” feature can detect separations in data (think spaces or commas between data bits) housed in the same cell and will move each individual data piece into its own column. This feature can save you a lot of time in simplifying complicated data with just a few clicks of your mouse. Google Sheets also offers a similar feature.

Not only does “Text to Columns” simplify data in instances like above, where all of the data from the web was exported into the same cell, but it also lends a helping hand in common situations where you almost always need to separate data. For example, when you have a city and state or latitude and longitude coordinates in the same cell.

Here’s how to use “Text to Columns” in Excel:

1. Select all of the cells you’d like to separate in one column (in our case, we selected cells 1-282 in column A)
2. Navigate to the “Data” menu
3. Select “Text to Columns…”

A Text Wizard will appear. Select the type of data you have: either Delimited (characters such as commas or tabs separate each field) or Fixed width (fields are aligned in columns with spaces between each field). Click Next. Depending on your data type, you’ll either set the delimiters your data contains (tabs, semicolons, commas, spaces, or you can customize any other delimiter) or you’ll set the column breaks. Select Next and identify the columns and set the data format. Click Finish.

You’ll see from our example data above that while this feature may not immediately yield the perfect spreadsheet, it certainly gets you closer to the simplified spreadsheet you want without hours of manually moving your data into different columns.

Combine Data in Excel with CONCATENATE

The opposite of separating data with text to columns, Excel’s CONCATENATE function can combine data from separate cells into one cell.

Let’s use CONCATENATE to combine each month’s precipitation data into the same cell. This might be useful if we wanted to see all the data in one place or to provide an overview. We’ll leave the city, state, and annual precipitation data in individual columns. To do so:

1. Insert a new column to the left of your data or scroll to the right to access an existing but empty column
2. Double click the first cell that isn’t a header
3. Type the following: =CONCATENATE(
4. Add the first cell number you wish to combine (such as B2), followed by a comma
5. Optional: to add a space or another separator between the data, type ", ", between each cell number
6. Continue adding in the cell numbers you wish to combine

Alternatively, copy and paste our formula: =CONCATENATE(E2, ", ", F2, ", ", G2, ", ", H2, ", ", I2, ", ", J2, ", ", K2, , ", ", L2, ", ", M2, ", ", N2, ", ", O2, ", ", P2), making sure to replace our cell numbers with your own. Our formula includes commas and a space between each data point. Once you have just one cell CONCATENATED, simply drag the function down to the rest of your cells to combine all of your data.

You can also use CONCATENATE in Google Sheets and we cover more use cases for CONCATENATE here. But what other Excel skills could we need?

Remove Unwanted Data with Find and Replace Wildcards

Thanks to Excel’s “Text to Columns” feature, our spreadsheet is in a much more manageable state. However, moving data into separate columns won’t remove unwanted data, unless it was already formatted with natural separations. In that case, “Text to Columns” would move the data to a separate column which we could easily delete. This was the case with our precipitation data’s first column: NORMALS. We went ahead and deleted the whole column.

But we also wanted to get rid of the links within our data that weren’t moved to separate columns. If the links were all the same web address, we could have used Find and Replace to copy and paste the repetitive link into the Find what search bar, and replaced it with nothing, thus deleting it. But as you can see in our data, and as may be the case in yours, the links are different. Since we don’t want to manually delete each link, we need to get a little creative with Excel’s Find and Replace feature.

We’ll be using the feature with Excel’s Wildcards, which are similar to Regular Expressions (RegEx) in Google Sheets. Instead of finding and replacing only data that is explicitly written out, Wildcards allow you to remove different data based on criteria you outline. Let’s see when and how this works:

You have all sorts of different, unwanted links mixed in with data you want to keep; they all come after the data you wish to keep, and they all begin with “(”. Using find and replace with wildcards:

1. Navigate to the Edit menu
2. Select Replace…
3. In the Find what search bar, type the following wildcard ,* replacing the comma with the first character of the data you wish to remove —in our case, (
4. Select Replace All.

You can follow the same steps listed above if your unwanted data appears before the data you need to keep. Just use the following wildcard instead: *,

Replace the comma with the last character of the data you’d like to remove.

Take a look at our spreadsheet now. You’ll see that all of the unwanted data (in our case, links) that had appeared after the data we wished to keep have been removed. It looks pretty nice, but now what can we do with our data?

Make a Map with Your Simplified Data

Unsurprisingly, we make a lot of maps at BatchGeo. There are many location data sources, but they don’t all have the data in the perfect format. These Excel skills—separating your data with “Text to Columns”, combining data with CONCATENATE, and removing unwanted data with find and replace—help us simplify the otherwise complicated data. Then we make it even easier for others to understand by making a map!

Maps can highlight insights you may have otherwise missed if your data was stuck in a spreadsheet. And with features like map grouping and a heat map function for your Excel spreadsheet, BatchGeo provides even more insights than your average mapping software. Let’s see how our precipitation data looks when mapped:

View Rainfall Totals by City in a full screen map

By mapping our Excel spreadsheet data, we learned that the island of Pohnpei received the most precipitation over the past 30 years and June is the rainiest month of the year. There are even more insights in our post dedicated to the subject: Average Precipitation of 282 Cities.

You can create a map now from your own Excel data. Or, if you’re looking for even more Excel spreadsheet knowledge, here are some advanced Excel skills and formulas to impress your boss.

A state or city’s average weather and nightlife activities are useful to know before traveling. But what about the quality of its drivers? This data is available as a city’s average years between collisions. More years between car crashes indicate better drivers. Yet, the range of years between accidents in the U.S.’s most populated cities is as wide as the open road.

For example, a driver in the worst city gets in a car accident once every 4.19 years, on average. Compared to the U.S.’s national average (one accident every 10.57 years), that’s pretty bad. On the other hand, some cities have average car crash frequencies closer to just once every 14.95 years. If you could decide whether to get in a car accident once every 4.19 years or once every 14.95 years, which would you choose? It all comes down to which U.S. states and cities are home to the best drivers (on average) versus the places that should consider mandating monthly driver’s ed.

View Best and Worst Drivers in a full screen map

The driving data of 200 U.S. cities used to create the map was obtained from Allstate’s 2019 best drivers report. The report took into account accident claims from major U.S. cities and compared them to the national average (one accident every 10.57 years). The map can help help you steer clear of the cities with the worst drivers, but let’s first draw attention to some of the best.

10 Cities With the Best Drivers

Out of the 200 cities on the map, 10 stand out. These cities are where drivers go longer between car crashes than the national average (10.57 years) and any other city in the nation. The more years between car accidents, the better the average driver, so the following 10 cities must be the best in the country:

1. Brownsville, Texas (14.95 years)
2. Boise, Idaho (13.65 years)
3. Huntsville, Alabama (13.39 years)
4. Kansas City, Kansas (13.21 years)
5. Laredo, Texas (13.02 years)
6. Olathe, Kansas (12.66 years)
7. Fort Collins, Colorado (12.6 years)
8. Overland Park, Kansas (12.44 years)
9. McAllen, Texas (12.42 years)
10. Cape Coral, Florida (12.24 years)

Brownsville, Texas’s place as the #1 city with the best drivers is unquestionable for two reasons. First, Brownsvillians only get into accidents every 14.95 years. That’s 4.38 years longer between accidents than the national average. It’s also an entire 1.3 years longer between collisions than even the next best city. Secondly, Brownsville held the first place spot two years in a row. That’s right, Brownsville was also the #1 best driving city in 2018. Plus, it ranked at #2 back in 2017. No one can deny that Brownsville is home to some of the nation’s best drivers.

The second best driving city is Boise, Idaho. Drivers in this city only get in accidents every 13.65 years, on average. Boise moved up to #2 from third place the year prior. Kansas City (the current #4) may not be too happy about that seeing as Kansas City used to be #2. The city is now relegated to fourth place with drivers crashing every 13.21 years, on average.

Like Kansas City, there are other cities hovering around the top 10 that were bumped up or down in 2019. None may be as bummed about their placement as Madison, Wisconsin (#11). Madison dropped several spots from 2018 to 2019. Previously #5, the city narrowly missed the top 10 in 2019 as its residents collide on average every 12.18 years. It should be noted though that drivers in Madison still go much longer between crashes when compared to the national average of 10.57 years. The only other member of 2018’s top 10 to fall in the ranks in 2019 is Cape Coral, Florida. Previously #8, Cape Coral still resides in the top 10 in 2019.

On the other hand, Olathe, Kansas, which was #11 in 2018, rose five spots in 2019, easily making the top 10. It’s drivers get in accidents every 12.66 years, on average. Similarly, Overland Park, Kansas, which ranked at #13 in 2018, also rose up five spots to make it to #8. To discover how your city ranks, use the search bar on the map.

Now, if you were to adjust for factors like population density and rainfall totals by city, you’d end up with a different top ten. Group the map by these categories (“Ranking Standardized for Population Density” or “Ranking Standardized for Annual Precipitation”) to see how Brownsville and Boise fare when these are factored in.

Overall, the top 10 is comprised of one city from Idaho, Alabama, Colorado, and Florida. Kansas and Texas are then home to three top driving cities each (including Laredo, Texas, which is also the U.S. city with the most twin towns and sister cities. That means 60% of the top 10 are from one of two states. Does that make Kansas and Texas the safest states to drive? Let’s find out.

Safest States to Drive in the U.S.

Americans get into car accidents once every 10.57 years on average. However, some states are home to several cities that go longer between collisions. We zeroed in on the cities doing better than the national average and identified the states where many of these cities are located.

Arizona is home to the most cities with better than average collision frequencies: six. These include Scottsdale and Mesa (years between collisions of 11.63 and 11.6, respectively), Chandler (11.21 years), Peoria (10.84), Tucson (10.75), and Gilbert (10.75). Texas is also where five cities with better than average collision frequency are located. The #1-ranking Brownsville (14.95 years between collisions) is in good company with Laredo (13.02 years), McAllen (12.42), Corpus Christi (11.53), and Amarillo (11.49).

Other notable states with plenty of safe cities to drive in are Kansas and Colorado; each have four cities where drivers go longer between collisions than the national average. Three cities each in Alabama, Florida, and North Carolina are also better than average. Tennessee, Missouri, and Nebraska each have two better than average cities while Nevada, Alaska, Kentucky, Illinois, Oregon, Indiana, Iowa, and Ohio have one each.

Biggest Safe Driving Changes Overtime

While the following cities may not be in the top 10 of best drivers nor be located in one of the safest states, they are pressing on the gas to rise in the ranks of best drivers. On the other hand, some cities are crashing and burning in the ranks.

Cities that took a slight detour in the best driving ranks include multiple cities in California, namely Pomona, Escondido, Fresno, and Elk Grove. Pomona previously ranked as the 112th best driving city but in 2019, the city dropped 38 points and now sits at #150. Both Escondido (previously #98) and Fresno (previously #117) saw a 25 point-difference in their rankings for 2019. Escondido’s rank in 2019 is #123 while Fresno is at #142. Elk Grove was #81 in 2018 but resides at #104 on 2019’s list, a 23 point drop.

On a more positive note, there were also many cities that rose in the ranks in 2019. For example, Little Rock, Arkansas sat at #158 on the list of America’s best drivers in 2018. But this year, Little Rock earned the #112 spot. Vancouver, Washington also rose through the ranks from #151 to #114 while Syracuse, New York cracked the top 100, going from #121 in 2018 to #85 in 2019.

Cities With Bad Drivers

We’ve focused on the 10 cities with the best drivers, the safest states in which to drive, and the places that rose in the ranks or took a slight tumble in 2019. Now it’s time to note the cities with the bad drivers. But you better watch out, they’re coming in fast (and likely without a turn signal).

1. Baltimore, Maryland (4.19 years)
2. Washington, District of Columbia (4.36 years)
3. Boston, Massachusetts (4.89 years)
4. Worcester, Massachusetts (5.14 years)
5. Glendale, California (5.31 years)
6. Los Angeles, California (5.81 years)
7. Springfield, Massachusetts (5.82 years)
8. Providence, Rhode Island (6.19 years)
9. Alexandria, Virginia (6.22 years)
10. Oakland, California (6.31 years)

Coming in dead last are the drivers of Baltimore, Maryland. These speed racers get into accidents more frequently (every 4.19 years, on average) than any other city in the U.S. Not only was Baltimore ranked last in 2019, but the city was in the same spot in 2018. Safe to say drivers in Baltimore can expect to swerve quite often.

Washington, D.C. drivers also frequently collide: every 4.36 years on average, to be exact. Three Massachusetts cities face similarly increased rates of crashing: Boston, Worcester, and Springfield. Drivers in these places must ask themselves why there are so many bad drivers daily. To learn more about the rest of America’s bad drivers, including the three cities in California in the bottom ten, sort the map by “Average Years Between Collisions” and select the two lowest ranges: “4.36-4.19” and “7.33-4.89”.

---

We can’t wait to see which U.S. city rises in the ranks or drops a point or two next year. In the meantime, you can plan your road trip of Route 66 and the historic locations you can still find or make your own map with BatchGeo today.

There are many custom maps you can make online, such as a neighborhood garage sale map or even a Google Maps store locator without code. While maps made for your eyes only can be helpful, they become even more powerful tools when shared. Whether you want to share your custom maps on social media via the web or on a mobile device, it’s easy to do with BatchGeo. Just obtain an image of your map—a screenshot or the high-resolution file you get with BatchGeo Pro will suffice.

Once you have your image, you’ll want to ensure it fits the ideal dimensions of your preferred social media platforms (we cover the sizing requirements below). Then, you’re ready to share the image of your map and the link to your website or map with your friends and fans on Facebook, Instagram, or Twitter.

The top social media platforms don’t permit users to embed HTML within posts. This means that when you share your maps on Facebook, Instagram, or Twitter, users won’t be able to interact with your map the same way they can when they see it on BatchGeo or embedded in your website. While we check in with Mark Zukerberg and co. about this, you have several options to get your location data in front of friends and followers on the world’s largest social media platforms through the use of image posts. Your options include taking a screenshot of your map or downloading a high-resolution PNG image (a BatchGeo Pro feature).

Take a Screenshot of Your Map

The first way to get an image of your map suitable to share on social media is also the most customizable: take a screenshot of your map. Use the built-in screenshot feature of your computer to grab a portion of your screen: Windows + Shift + S on Windows 10 or Command + Shift + 4 on Mac. On Windows, you’ll be directed to the Snipping Tool or the newer Snip & Sketch. On Mac, you’ll need to drag the crosshairs cursor across the area of the map you wish the include in your screenshot. Once you have your desired area covered, simply let go of your mouse to take your screenshot.

You can also take a screenshot of your map via your Android or Apple smartphone. On an iPhone 8 or earlier, press the Top or Side button and the Home button at the same time and quickly release both buttons. On an iPhone X or later, press the Side button and the Volume up button at the same time and quickly release both buttons. For any Android phone, locate your device here to learn how to take a screenshot.

Taking a screenshot of a specific area on your map—whether on a computer or mobile device—allows you to control and customize the area of your map you’d like to share. You can zoom in and out of BatchGeo maps on both a web browser or mobile device. You may choose to zoom in on one location, making it the focal point of your image, or zoom out to capture your map as a whole.

Download BatchGeo Pro’s High-Resolution PNG Image

The second way to get an image of your map to share on social media is by utilizing a BatchGeo Pro high resolution, printable PDF or PNG file. To make use of this feature, right-click on your map within the BatchGeo web browser. Click Export PDF / Image and select PNG Image and High Res 11” x 17” Printout. A clear, high-quality PNG image of your map will be downloaded on your computer. You either can upload the image from your computer into a social media post via a web browser or, if you’d like to do so on a mobile device, send the image to your phone and upload accordingly.

Resize Your Images to Look Their Best

Each of the three top social media platforms has their own ideal sizing dimensions when it comes to sharing images. We’ll list the specifics for each platform below so that you can adjust your screenshot or high-res image to fit those dimensions. Another option is to use a tool like Sprout Social’s free Social Media Image Resizer to ensure your social media posts always look their best.

Ideal Image Sizing for Facebook

Facebook’s ideal image dimensions are 1,200 pixels wide and 630 pixels tall. Once you have resized your photo, select Photo/Video on your Facebook feed and select the image. To share an image via the Facebook mobile app, send the optimally-sized image from your computer to your phone. Then, open up the Facebook mobile app and upload the image.

Whether you plan to share a screenshot of your map or the high-res image BatchGeo Pro provides, be sure to include a status that links back to your own website or map so users can easily click over to the interactive version.

Instagram’s Ideal Image Sizes

Instagram users expect high-quality images when browsing on the app. We can use the same methods we used to obtain a screenshot of your map or use BatchGeo Pro to download an automatic high-resolution, printable PNG image. You’ll just need to adjust the dimensions before you share your map in an Instagram story or post.

Instagram Story

The ideal dimensions for sharing a map in an Instagram story are 1,080 x 1,920 pixels. You can manually edit your screenshot or high-res image to fit those dimensions, or you can use a social media image resizer tool. Once you have the image of your map in the correct size, send it to your phone. To add it to your Instagram story, click the Camera button on the top left corner of Instagram and select it from your camera roll.

Of course, as with Facebook, it’s important to share your image with a link to either your website or to the map itself so users can view the interactive version. As Instagram only allows you to post direct links in your stories if you have over 10,000 followers or you’re a verified user, you can add the link to your bio (which is clickable for everyone) and point users there for easy navigation.

Instagram Post

Instagram posts have different ideal dimensions than their story counterparts. To share an optimal-sized screenshot or high-res image of your map in an Instagram post:

1. Adjust the size to be 1,080 x 1,080 pixels for a square photo, 1,080 x 566 pixels for a horizontal image, or 1,080 x 1,350 pixels for a vertical post
2. Or, upload the image to a social media image resizer
3. Then, share the image to your phone and post it on Instagram by clicking at the bottom of the screen → Library (iOS) or Gallery (Android) at the bottom of the screen and select the photo of your map you’d like to share
4. Add a hashtag or two, such as #batchgeo just like these other BatchGeo users did.

Once again, it’s always important to share a link either to your website or to the map itself so users can view the interactive version of your map’s image. Yet, Instagram also prevents clickable links in your captions. Stick it in your bio instead and let your followers know you did so.

Ideal Image Sizing for Twitter

To share your map on Twitter, use a screenshot, or high-res image. Ensure the image will look it’s best on Twitter’s platform by re-sizing it to Twitter’s recommended size dimensions of 1024 x 512 pixels. Then, upload your image using Twitter in your web browser or on your mobile device.

---

It’s time to share your maps with friends or fans use either using a screenshot or the printable, high-res images you get with BatchGeo Pro. Share to Facebook, Instagram, Twitter, Tumblr, or even Pinterest using the same methods as described above: obtain a screenshot or high-quality image and post to the social media platform with a link to your website or map. Knowing the right dimensions for each platform, or letting an online tool do it for you, will ensure your personal or business social media profile looks great 100% of the time you share your maps.