The Complete Guide to Conditional Formatting in Excel

Spreadsheets are known to be one of the best places to store your data. But with large datasets, it can be difficult to identify trends in your information. To get to the real story behind your values, you need an easy way to narrow down what’s important. Enter Excel’s conditional formatting tool. We’ll cover what conditional formatting is and does, along with step-by-step instructions on how to implement it in your own spreadsheets:

Let’s kick off this guide with a clear overview of this popular Excel tool.

What Is Conditional Formatting?

Let’s say you have a spreadsheet containing hundreds of cells of data. While each piece of information is important to the big picture, you might strain your eyes reading every single data point. You’re often just looking for specific numbers or textual info—say a range of the highest and/or lowest data.

In that case and many others like it, a way to zero in on the data that fits what you’re looking for would save time, keeping you focused on the information that matters. What better way than to specially format the desired cells to draw your attention? To ensure you continue to save time, this should be a fairly automatic process, which is exactly what conditional formatting does with its eight rules.

The 8 Conditional Formatting Rules Explained

Now that we better understand the purpose of conditional formatting, let’s jump into some of the various options or rules within the conditional formatting tool itself, including:

  • Highlight Cell Rules
  • Top/Bottom Rules
  • Data Bars
  • Color Scales
  • Icon Sets
  • New Rule
  • Clear Rules
  • Manage Rules

While we find ourselves reaching most often for the highlight and new (custom) rules, the others also have their specific uses, as we’ll identify.

Highlight Cell Rules

The first conditional formatting rule allows you to automatically highlight the data you specify. Whether that be numerical (for example, any data Greater Than the national average of 3.6 ICU beds per 10,000) or textual data, you’ll find an option that works for your information.










You can even use custom Excel formulas to determine the data to format. There are hundreds of TRUE or FALSE formulas ranging from basic to complex. To use this option:






  1. Open your spreadsheet
  2. Select the desired data column(s) you wish to manipulate within the sheet
  3. Either navigate to menu Format and Conditional Formatting… or in the Home tab, click Conditional Formatting
  4. Click Highlight Cell Rules

Highlight Cell Rules includes Excel’s easy way to find duplicates via Duplicate Values. For all of the above, you may choose to leave Excel’s default formatting settings (which determine the text and fill color for your highlight). Otherwise, you can opt for custom formatting.

Top/Bottom Rules

This second handy rule automatically formats the top or bottom range of your data. Whether that’s items, percentages, or averages is up to you. Unlike our Highlight Cell Rules example above, if you opt to go the average route, there’s no need to calculate nor input an average beforehand—let Excel do the math for you.

To get started with this rule, open your spreadsheet and select your desired data column(s). Then, either navigate to menu Format and Conditional Formatting… or in the Home tab, click Conditional Formatting and select for Top/Bottom Rules.

Data Bars, Color Scales, & Icon Sets

These three conditional formatting rules take data visualization to the next level. For example, with Data Bars, the longer the bar, the higher the value of the data. As with every option, you decide the formatting. In this specific case, choose between Gradient or Solid Fill for your data bars.









Instead of a bar, Color Scales assigns a color shade to a cell’s value. A 2 or 3-Color Scale corresponds to the minimum, midpoint, and maximum thresholds of your data.






Icon Sets, on the other hand, utilize icons to represent your data. Choose from directional icons like arrows, various shapes or other indicators, and rating icons. Then, designate icons for values greater than, less than, or equal to your data. To incorporate any of these visual effects in your sheets:

  • Open your spreadsheet
  • Select the desired data column(s)
  • Either navigate to menu Format and Conditional Formatting… or in the Home tab, click Conditional Formatting
  • Select your desired visual option











Choosing your bar, color scale, or icon is a pretty customized option. But for even more customization, you can create your own conditional formatting rules.

New, Clear, & Manage Rules

The final conditional formatting options allow you to easily add a new rule or modify the rules you’ve already set up. With Clear Rules, you have the choice to clear the entire sheet of rules or just certain cells you’ve selected.

When you Manage Rules, you’ll see a convenient list of everything you’ve set up in your spreadsheet. You can edit, change the rule order, or make a number of other modifications here. As the last of the conditional formatting options, let’s now see how else we can analyze data.

Maps Offer More Data Analysis

In addition to examining data with Excel’s tools, (you can check out our other Excel posts, like How to Find Duplicates in Excel and 5 Excel Tips From the Guy Who Built It, there are other ways to level up your data analysis. One such way is mapping. With a map of your location data, you get a visual element of data analysis (much like conditional formatting), though maps provide far more than a simple highlight, as you can see below.

View ICU beds by city in a full screen map

Visualize your data as markers on a custom map with multiple map marker colors, automatic grouping, and more. This gives you better insight into your data than if you’d left it in a spreadsheet. You can get started mapping your spreadsheets and check out all that comes with it at batchgeo.com.

WNBA Champions on a Map

Sports bring people together and it seems there’s always a season to keep up with, especially when it comes to basketball. Not only do the NCAA and NBA leagues draw massive crowds but the WNBA also has thousands of fans who flock to their games—especially the finals.

The WNBA Finals are a best-of-five series between the two semifinal winners held every October. We’ll take a look at WNBA finals history and which team has the most championship appearances. Plus, we can’t examine sports without talking about wins: the most and those that were oh-so-close on the map below.

View WNBA Champions in a full screen map

Wikipedia provided the open-source list of WNBA champions data for our map of women’s professional basketball championships. Explore the winners (and losers) geographically or by map groups like finals year or team’s current status. We’ll also highlight some trends below.

WNBA Championship History: Most Appearances

Though the league is only 12 active teams, there have been 15 WNBA teams that have played their way through the regular season to make it to the finals—regardless of winning or losing once they got there. The Minnesota Lynx and Los Angeles Sparks have appeared in more than five WNBA finals each. Between them, they’ve appeared 11 times, although just one of those was together. With six appearances, the Lynx have the Sparks’ five finals appearances beat. The only teams close to catching up to their record are the Detroit Shock (4), Houston Comets (4), Seattle Storm (4).

Both the Shock and the Comets are no longer in the league. The Shock relocated (first to Tulsa and later to Dallas where they were rebranded as the Wings) while the Comets folded altogether. Some other teams that were no stranger to the WNBA Finals before franchise changes include the Sacramento Monarchs and Charlotte Sting, both of which folded. The Connecticut Sun (formerly Orlando Miracle) and Las Vegas Aces (previously known as Utah Starzz, San Antonio Silver Stars, and San Antonio Stars) had faced relocations in the past.

But which teams haven’t been so lucky?

Zero Finals Appearances

As for the teams that have never once made it to the finals, there are only three. They’re all former teams, so unlikely to make a final in the future, unless the team is revived. These never-champs are:

  • Cleveland Rockers
  • Miami Sol
  • Portland Fire

The Cleveland Rockers were one of the original teams. established at the same time as the league in 1997. Yet the Rockers never made it to the finals before the team folded in 2003. In the case of both the Miami Sol and the Portland Fire, each only had a two-year run (2000–2002), so their lack of finals appearances is easily explained. Let’s move on to wins.

WNBA Most Championships

As for wins, which is the goal for every team, nine teams have more than one championship under their belt:

  • Houston Comets
  • Minnesota Lynx
  • Seattle Storm
  • Detroit Shock
  • Los Angeles Sparks
  • Phoenix Mercury
  • Indiana Fever
  • Sacramento Monarchs
  • Washington Mystics

Which team—or teams—truly hold the crown? The Houston Comets (folded), Minnesota Lynx, and Seattle Storm have the most finals wins of any WNBA team (four wins each). While the Comets’ last win was in 2000 (the team was dissolved after the 2008 season), Minnesota won as recently as 2017 and the Storm were the 2020 champs.

The next-most WNBA finals wins appear to come in threes: the Detroit Shock, Los Angeles Sparks, and Phoenix Mercury have three W’s on their record. Combined, the WNBA championship wins of the Indiana Fever, Sacramento Monarchs (which moved to Tulsa after 2009 and then Dallas following the 2015 season), and Washington Mystics also add up to three wins. Now let’s move on to the teams with a lot of wins and no losses.

WNBA Champions With A Winning Streak

While Minnesota, Houston, and Seattle have the same high number of finals wins (four each), they do differ in their winning percentage. Only the Comets and Storm have a perfect score, winning as often as they’ve appeared in the championships.

As the Minnesota Lynx has seen two finals losses, their winning percentage is quite a bit lower at 0.667. The teams with a better winning percentage than the Lynx? Both the Detroit Shock and Phoenix Mercury out-score the Lynx with 0.75 percent wins. Those that fall below the Lynx? The Los Angeles Sparks (0.6), Sacramento Monarchs (0.5), Washington Mystics (0.5), and Indiana Fever (0.333). But even a winning percent of 0.333 is better than 0.

No Win Teams

Several WNBA teams have made an appearance or two in the finals but have yet to win a title. These include:

  • New York Liberty
  • Atlanta Dream
  • Connecticut Sun
  • Las Vegas Aces
  • Charlotte Sting
  • Chicago Sky

The New York Liberty has had the most opportunities to bring home a trophy, yet they’re still 0 for 4 in championships. The two teams that flubbed three chances each to win are the Atlanta Dream and Connecticut Sun. As for the rest of the teams without a W, the Las Vegas Aces have had two chances while both the Charlotte Sting and Chicago Sky have had just one throughout WNBA finals history.

And there’s certainly not a lack of basketball leagues to support. To dive into other dribbling champs, check out NBA Finals on a Map: Most Appearances, Most Wins or map your favorite league’s stats at batchgeo.com.

Pinpoint 569 Shipwrecks in International Waters

Everyone knows about the sinking of the Titanic, perhaps in part due to Kate Winslet and Leonardo DiCaprio’s heartbreaking portrayal of a fictional couple aboard. While we also all acknowledge that there was more than enough room on that door/raft, many people can’t name any other international shipwrecks, even those that took place more recently than the Titanic. Yet, 569 international shipwrecks took place between 1628 and 2015 and all had their own tragic backstories. Let’s take a look at where the final resting places for these ships are located via the map below.

View International Shipwrecks in a full screen map

We gathered information about the 569 international shipwrecks from Wikipedia and its subpages. Note that there are even more mappable shipwrecks that took place in domestic waters but we didn’t want to go overboard with data.

Group the map by ocean, subregion, or the smaller bodies of water to see where most of these wrecks reside, or read on for more information about their oceanic locations.

Shipwrecks by Ocean

Seventy-one percent of the planet is ocean, which has been divided into four ocean basins for geographical, cultural, and scientific reasons, per the National Ocean Service. The original four basins were the Atlantic, Pacific, Indian, and Arctic oceans though most oceanographers now recognize a fifth basin: the Southern Ocean. You can sort the map by these basins, though we summarize the ocean basins and their shipwreck count below.

  • Atlantic Ocean – 323 shipwrecks
  • Pacific Ocean – 158
  • Indian Ocean – 56
  • Arctic Ocean – 29
  • Southern Ocean – 3

Being only the second largest of the world’s oceans doesn’t stop the Atlantic Ocean from claiming the most shipwrecks. Almost 57% of international ships have gone down there while the Pacific Ocean (the #1 largest ocean) has half the amount of shipwrecks. However, the Atlantic was the body of water between European explorers and the New World. Frequent travel likely propels it to this grim number one position.

The coldest of all the oceans, the Arctic Ocean is partly covered by sea ice year-round. Due to this, it is less frequently travels, so it has a lower number of wrecks when compared to the other oceans. The same goes for the Southern Ocean (also known as the Antarctic), which has temperatures between −2 to 10 °C (28 to 50 °F). Recognized in 2000, only three shipwrecks have occurred there: in 1819, 2007, and 2010. The 1819 wreck of the San Telmo resulted in the deaths of 644 people, possibly the first to die in the ‘future’ Antarctica. Additionally, San Telmo Island off the north coast of Livingston Island is named after the ship. But what about the number of shipwrecks in more specific regions of each ocean basin or even in bays, channels, and seas?

Ocean Bays, Channels, Seas, and Subregions Where Ships Sink

Each of the five ocean basins has subregions such as the North Pacific, South Pacific, Mid-Atlantic, South Atlantic, or Central Indian Ocean. Of these, the North Pacific subregion has been the site of the most wrecks: 124.

Additionally, there are 33 bays, channels, and seas within the ocean basins and their subregions. Of these, the Mediterranean Sea (between Southern Europe and North Africa) is the most common for shipwrecks; 129 to be exact. Sixty-two wrecks have taken place in the North Sea, the second-most, including the Bourbon Dolphin shipwreck.


Over 20 shipwrecks occurred in each of the Norwegian, Phillippine, South China, and Baltic seas. Then there are 27 more bays, channels, or seas with one shipwreck or more. To decrease the chances of a shipwreck, even in one of these locations, it’s helpful to know the months shipwrecks occur most often.

Dangerous Months for Ships

Do ships tend to sink more often when faced with the icy waters of winter months? Or are wrecks more common throughout the summer months when ships may sail more frequently? Let’s find out.

Month Number of shipwrecks
May 65
June 61
April 50
February 48
March 46
December 46
September 45
October 45
November 45
August 45
July 36
January 32

As you can see from the table above, May is the month of many wrecks. Forty-six of the 65 May shipwrecks occurred in the vast Atlantic Ocean. June, too, has seen a lot of ships sink, as has April. It would appear that the Northern hemisphere’s late spring and early summer is the time of year when international ships tend to go down.

On the other hand, the month of January has only seen 32 international shipwrecks between 1628 and 2015. In addition to January, the months of July and August-November seem to have calmer waters. It’s interesting to see May and June rack up high numbers while the months immediately following have 20+ fewer shipwrecks. Get an even better idea of the months, years, and even days when wrecks occur by grouping by time period on the map.

Group and Filter the Map by Multiple Columns: Oceans & Months

With BatchGeo you can group and filter any of your maps by a data column. For an even more detailed view, you can filter your maps with multiple column values at the same time.

For example, on the map of international shipwrecks, select the Atlantic Ocean from the ‘Ocean’ category so the map only displays wrecks in that basin. To add a second (and third and fourth and so on, if desired) column to the filter—like the month of May— select an additional category, which will keep the Atlantic Ocean filter from before. Note that you can do this with any of your maps made with BatchGeo, making map data analysis easier than ever before.

In the case of international shipwrecks, most occur in the Atlantic Ocean during May, which we easily discovered thanks to multi-column map grouping and filtering. Other ocean-related maps available for your perusal include the busiest ports worldwide and every US shark attack fatality since 1900, which are made with the help of BatchGeo’s online mapping tool.