I believe that one of the best tools available for visualizing data within an Excel spreadsheet is conditional formatting. This post will feature what you can do with data bars and icon sets. See my earlier my post on how easy it is to use conditional formatting to find duplicates in Excel. In this post, I am using some data from major league baseball to explore how to visualize win/loss streaks and comparing team’s winning percentages. The example spreadsheet represents MLB standings as of 7/27/2012. I copied the data from www.mlb.com and pasted it into an Excel spreadsheet which you can download (see link at end of post). The following screen shot represents the data before any applied conditional formatting.
The first step to take is select the data that you would like to apply the conditional formatting to. I selected the range from C4 through C39. After selecting your data, click the home tab–>conditional formatting command–>data bars. The data bars will grant you a lot of choices. Simply click the one that you like the best.
As you can see in the screen shot above, I applied the data bars to the winning/loosing streaks. So Cincinnati currently have a 7 game winning streak and the data bar on its record is displayed to the right and is green. This is a great news for Cincinnati fans. Contrast that with Milwaukee Brewers who have a 7 game loosing streak and its data bar is to the left and is red. Now I could customize the colors and select what color I would like the data bar to be for positive numbers and what color I would like to be for negative colors under manage rules.
Another great choice for conditional formatting is the icon sets. Icon sets group your data into parts. For example, in the screen shot below I selected all the baseball teams winning percentage (Range F4:F39). Then I clicked conditional formatting–>icon sets–>traffic lights. This grouped the data with the top 3rd winning teams with green traffic lights, the middle third with yellow traffic lights, and the bottom third teams with red traffic lights.
Download my baseball mlb conditional formatting.xlsx file. I’ve included a practice sheet that you can work with.
Be sure to download my Excel keyboard shortcuts handout if you haven’t already.