Author: Adam DuVander

Spreadsheets are often the best place to store and manipulate data. Most spreadsheet tools, like Excel, have many ways to analyze, filter, and transform said data. One common task is to identify duplicate data, like the same pizza place listed twice in a list of your favorite places. Once you’ve found any duplicates, you can either remove them or count them.

In this article, you’ll learn:

1. How to highlight duplicates in Excel
2. How to find duplicate values in Excel by using formula like COUNTIF or VLOOKUP

The popular methods of finding duplicates in Excel are ordered from easiest to most difficult. Let’s begin by taking a look at the first (and easiest!) method.

How to Highlight Duplicates in Excel

One of the most popular ways to identify duplicate data is to highlight them. Highlighting also happens to be the easiest of the three approaches to find duplicates in Excel and it provides a visual aspect other duplication identification methods lack. So, for all the visual learners out there, let’s get started e-highlighting with the simple steps outlined below.

1. Select the cell range in which you want to highlight duplicates
2. Ensure you’re in the Home tab (on the upper left), then select the Conditional Formatting dropdown
3. Opt for Highlight Cells Rules and click on Duplicate Values…

As shown above, the default settings include a Classic Style while 2 and 3-Color Scale, Data Bar, or Icon Sets are additional options. You may opt to keep the default Format only unique or duplicate values and subsequent duplicate when it comes to choosing which values in the selected range you want.

Light Red Fill with Dark Red Text is the default Format with setting. This is where you determine the text and fill color for your highlighting. There are other suggested options, and Excel also allows for a Custom Format… Once you’re satisfied with the settings, click OK and you’re done!

A similar method of highlighting duplicates in Excel is available in Google Sheets and other spreadsheet tools. And, though a well-liked method, there are advantages to instead using an Excel formula to identify duplicates.

How to Find Duplicate Values in Excel Using Formulas

Another way data analysts can check for duplicates is via Excel formula. While the thought of a page-long formula including various spreadsheet cells and mathematical symbols is daunting, the formulas used to check for duplicates in Excel are actually quite simple. In fact, they’re a great way to dip your toe into other Excel formulas, such as =CONCATENATE, which we describe in Advanced Excel Skills and Formulas to Impress Your Boss.

COUNTIF to Find Duplicates

The duplicate-checking formula uses =COUNTIF to “count” which cells contain data that appears more than once throughout the spreadsheet. Resulting values can either be “TRUE” (indicating duplicate data) or “FALSE” (showing non-duplicate data).

You may wish to begin by adding a heading like “Count” (or something similar) to a blank column, though this is optional. Then, do the following:

1. Copy and paste this formula into the first cell of a blank column: =COUNTIF(A:A,A1)>1
2. Change the A in the formula to coincide with the letter of the cell column you wish to find duplicates
3. Drag the cell’s contents down to the cells below

That’s it; any duplicate data will be identified in the new column as “TRUE” while non-duplicate data is indicated as “FALSE.” Next, you can sort by your count column and see all of the duplicates (or non-dupes) bunched together. In contrast to the visual method, this is a quick way to not only identify but remove, duplicates.

There are tweaks you can make to this general formula if you wish to customize the value results. For instance, you may want duplicate data to be identified as “Duplicate” instead of the default “TRUE” or “Unique” instead of “FALSE.” Or, remove the >1 and you’ll see the number of copies, which could be more than two.

Those customizations aside, we still haven’t covered all the ways you can find duplicates in Excel.

How to Find Duplicate Values in Excel Using VLOOKUP

The final manner of finding duplicates in Excel applies if you have two data columns. Another formula, the method uses =VLOOKUP to compare two separate columns for shared data and displays the commonalities in a third column.

In a third, blank column, add some sort of heading such as “Vlookup.” Then, get started with the following steps:

1. Copy and paste =VLOOKUP(B2,$A$2:$A$14 ,1,FALSE) into the second cell of the third, blank column
2. Adjust the cell letters and numbers to fit your data
3. Drag the original cell down to the rest of the rows

With the VLOOKUP formula, data unique to columns A and B are shown as “#N/A” while duplicates appear as themselves. And this concludes the most popular ways to find duplicates in Excel. What you do next is up to you, though we have a few suggestions. First, you may want to remove any duplicate data. Then, if your data contains location information, you can always map it for better visualization.

Make A Map with Your Data

Finally, make use of our Excel mapping tool to copy and paste your data from your spreadsheet directly to a custom interactive map like the one below, no coding required.

View Make a Map of My Location and Favorite Places in a full screen map

You can change the map’s base style and markers, group by multiple data columns, and more. Get started today.

Who doesn’t love an outing to a national or state park? The only downside of visiting these locations is that you often have to plan a road trip—unless you live in a rural area. But a majority of the world’s population lives in cities. Perhaps this is why the prevalence of urban parks continues to grow. City dwellers deserve accessible nature escapes too, even if they must trek down six flights of stairs from their apartment above a Subway to get there. But not all city parks are created equal.

Let’s take a look at the largest of these that park planners managed to squeeze into their city’s limits. We’ll identify the ten with the most acres in the world, the cities and countries with the most of these massive parks, and the easiest way to find them on the map below.

View Metropolitan parks by size in a full screen map

We plotted Wikipedia’s List of urban parks 1,000 acres or more on a map. An urban park, also known as metro park, city park, or municipal park, is a park contained entirely within a location’s city or metropolitan boundary.

Let’s take a peek at the ten largest city parks in the US and elsewhere in the world below.

The Ten Largest Urban Parks Worldwide

Football fields are 1.32 acres. Yet several massive metro parks measure in at almost 375,151 times that size. You may as well bring a tent, portable toilet, and three bottles of ibuprofen with you as you’ll be strolling for days before you make your first loop of any of the ten largest urban parks.

Name	City/Metropolitan area	Country	Size in acres
Chugach State Park	Anchorage	United States	495,199.20
Table Mountain National Park	Cape Town	South Africa	54,610.30
Izmaylovsky Park	Moscow	Russia	39,536.80
Pedra Branca State Park	Rio de Janeiro	Brazil	30,626.20
McDowell Sonoran Preserve	Scottsdale	United States	30,394.00
Losiny Ostrov National Park	Moscow	Russia	28,664.20
Sanjay Gandhi National Park	Mumbai	India	25,659.40
Franklin Mountains State Park	El Paso	United States	24,246.00
Bayou Sauvage National Wildlife Refuge	New Orleans	United States	22,758.40
Bukhansan National Park	Seoul	South Korea	19,748.70

Alaska’s Chugach State Park is easily the largest metro park contained entirely within a city’s boundaries—Anchorage, in this case. Chugach’s nearly half a million acres make it over nine times the size of even the second-largest city park. However, Chugach is only the third-largest of all states park in the U.S.

South Africa’s Table Mountain National Park in Cape Town is 54,610.30 acres. Table Mountain is also the last park in the top ten to span more than 50,000 acres.

The top ten largest city parks in the world consist of four located in the U.S. Also in the top ten are two metro parks in Russia and one in each of the following five countries: South Korea, South Africa, India, Canada, and Brazil. Now that we know where exactly the top ten are located, let’s take a look at the locations of the rest of the large city parks.

Cities & Countries With the Most Massive Metro Parks

We noted the ten largest city parks in the US and the world above. However, every single one of the 168 parks on the map is pretty dang big at 1,000 acres or more. So let’s see, location-wise, where they tend to reside.

Cities

There are plenty of cities worldwide that are home to city parks of 1,000+ acres. However, likely foreshadowing of the country with the most of these massive parks, only the following U.S. cities contain four or more:

• San Diego (7 large metro parks)
• Jacksonville (5)
• Phoenix (5)
• Los Angeles (4)
• Houston (4)
• Dallas (4)

The cities noted above are joined by 11 others with three 1,000+ acre metropolitan parks. This includes three international cities: Moscow, Madrid, and London. Plus, many cities have one or two massive parks, for a total of 168 unique cities after we counted the duplicates.

Countries

As for countries, the U.S. contains 115 large metro parks, more than any other country on Wikipedia’s list. Canada is where the next most (12) are located while the United Kingdom and Brazil are each home to four. Now let’s find out the easiest way to visualize these analyses.

Map Your Data Alongside Locations

When mapping data, you often have more than just a location. It’s easy to find insights into that additional data with BatchGeo’s grouping functionality.

With grouping, you’re able to select only the markers that meet certain requirements, filtering out the rest. Groups can be combined to zero in on very specific results, giving you insight into the story behind the map. For example, you can group our map of the largest metropolitan parks by ranking, managing authority, and size. You’re also able to set your desired default grouping of the map. See for yourself the insights you can gain with grouping at batchgeo.com.

In July 2020, the United States Supreme Court unanimously decided that states can require electors to support the winner of their popular vote. Those who don’t may be punished with a faithless elector fine or by being removed from the post altogether. Now, a faithless elector is not one who eschews religion. The term refers to a member of the U.S. Electoral College who doesn’t vote for the presidential or vice-presidential candidate they had pledged to vote for. That is, they break faith with the candidate they were pledged to and vote for another candidate, or fail to vote altogether.

Historically, political parties have been successful at keeping their electors faithful. But that makes the 165 cases of faithless electors all the more interesting, whether the reasoning is the death of a candidate, a form of protest, or even a simple spelling mistake. In the wake of SCOTUS’s monumental decision, let’s take a look back at the elections with faithless electors.

View Faithless electors in a full screen map

After cross-referencing Wikipedia’s List of faithless electors with FairVote.org‘s list, we identified 165 faithless electors. We note their name (if available), political affiliation, and the presidential or vice-presidential candidate (or non-candidate!) they voted for instead. You may sort the map by election year or party. Alternatively, read on for insights on the election years with the most faithless electors in U.S. history.

Election Years With Faithless Electors in the Double-Digits

As of 2016, there have been 20 elections with at least one faithless elector. Several elections even saw 10 or more faithless electors. When combined, these double-digit elections account for over 76% of the faithless electors, so let’s take a closer look.

• 1872 – 63 faithless electors
• 1832 – 30
• 1836 – 23
• 2016 – 10

The many faithless electors of 1872 were due to the death of the Liberal Republican Party’s nominee for president. Horace Greeley passed away before the Electoral College could vote, so 63 of the 66 electors pledged to him voted for other candidates. The year saw the most faithless electors in U.S. history, more than double even the next most.

The election of 1832 resulted in the first of many faithless electors who had issues with Martin Van Buren or his running-mate. That year, 30 electors from Pennsylvania refused to support Democratic V.P. candidate Martin Van Buren. Four years later in 1836, 23 Virginia electors pledged to Van Buren (for President) and Richard Johnson (for Vice President) abstained from voting for Johnson. This was the first abstention in faithless elector history.

Then there’s the 144-year gap between the previous most recent election to have faithless elector numbers in the double-digits (1872) and 2016. Ten electors worked to alter the result of the election in 2016. The electors consisted of eight Democrats and two Republicans. Now, let’s break down the political parties of faithless electors and the states they were supposed to represent.

Demographics: States and Parties of Faithless Electors

With 165 instances of faithless electing, there have to be some commonalities—perhaps location and party? Let’s go over the demographics of past faithless electors.

Electors From These States Are the Most Faithless

You know the saying “Never trust an elector from Pennsylvania“? Well, neither do we, but if the phrase ever caught on, it would be based on truth. Of the 165 faithless electors in total, 31 represented the Keystone State.

As for other states with plenty of faithless electors? Virginia has been the home state of 24 of these electors while both Missouri and Georgia have had 15. But what about the political party of these electors?

The Faithlessness of This Party is Elect-rifying

Of the six distinct political parties on the map, the party most faithless electors align with is that of the Democrats. In fact, 73 of the 165 faithless electors in U.S. history lean left. The party with the second-most of these electors is the Liberal Republican party; 63 members were faithless in the 1872 election.

Republicans have had 14 faithless electors while the other parties represented include Democratic-Republicans, the People’s Party, and Federalists with under 10 each.

Election Years Without Faithless Electors

Based on the previous information, you might think there’s at least one faithless elector in every election. However, this is not the case. There are several elections in U.S. history sans a single dissenting elector.

Notably missing on the graph are the Obama years. The elections with zero faithless electors this century took place in 2012 and 2008 when Barack Obama was on the ticket. Although to be fair, the one faithless elector in 2004 likely made a spelling mistake. The anonymous Minnesota elector cast a presidential vote for the V.P. candidate “John Ewards” [sic], rather than John Kerry. As a result, Minnesota amended its law so that votes cast for someone other than the candidate to whom the elector is pledged are invalidated.

Other notable election years when faithless electors were absent include 1964, which broke up what might otherwise have been a streak of six elections with faithless electors. As for the longest streak of elections sans this type of electors? From 1944 until 1916 (eight elections in a row) there were no instances of faithless electing. Another long streak occurred further back from 1868 to 1844, or seven election cycles long.

---

There has only ever been one election in which faithless electors changed the result (1836, though even that was fruitless as the V.P. candidate, Richard Johnson, was ultimately voted in by Congress). However, it’s still cool to look back on the history of faithless electors as there’s likely to be significantly fewer in the future thanks to the Supreme Court’s ruling.

For more election-related maps, read about the Births and Burials of US Presidents or the birthplaces of V.P.s, The President Abroad: International Travels Of U.S. Presidents, and Presidential Assassination Attempts Mapped. Otherwise, elect to make a map today at batchgeo.com.