Conditional formating in Office 2007 [Hack Your Day]
A great new feature in Office 2007 is conditional formating. While its use is mainly visual, since if you are doing statistical analysis you would use variance, standard deviation and the lot to numerate the properties of data sets, you can use the conditional formating options to get a quick glance, or for other features, like determining duplicate values and so on. Let me show you a few great uses that are easy to implement and could help you in your daily work. It may also help you in productivity, since the simple duplicate checking may free up a lot of time.
Duplicate values
First of all, you will need a data set to work with. I fabricated a table with dates from January 1st to January 31st and I filled up the column next to the dates with site visitor counts (the values aren’t real). First of all, select the values you want to check. This can be any set, from highlighting a few values, to selecting multiple rows and columns.
The next step is to “Conditional Formating” in the “Home” tab on the ribbon. Click on “Highlight Cell Rules” and click on “Duplicate Values”, it should be on the bottom. From the next menu you can choose to highlight unique values (also a very helpful tool) or duplicate values. Excel also gives you the option to format the highlight as you see fit, so if you need to you can even incorporate it into a beautiful presentation, because you can adjust the formatting to match the table or the presentation.
Highlighting values in a range
You can use the same menu “Conditional Formating -> Highlight Cell Rules” to highlight cells that match a specific range. If I wanted to take a look at the days that yielded less than 500 visitors on my blog I would choose the “Less Than” option and enter 500. You can adjust the highlight formatting in the same way as before.
You can also use the other highlight types to your advantage by highlighting values equal to a specific one, or even better values between two numbers. If you’re a teacher you could highlight all the children who are above the D grade, but below the A grade for example.
You could also use the “Top/Bottom” rules for the same purpose. You can highlight the bottom or top 10%, but what I find really helpful, is highlighting all values above or below average. You could do this via the “More than” option, and calculate the average for yourself, but this is a much quicker and easier way. You can use this for a huge number of purposes from managing order performance, from advertising productivity optimization and a host of others.
Data bars and color scales
The biggest eye-candy in Excel 2007 is a somewhat useful, great way to visualize data values. Data bars will lay a gradient bar over each cell, filling it up according to the value, and it’s relation to the other values. The largest number in a highlighted set will have a full bar, the lowest will have a near empty one, and the ones in between will be filled according to their values.
Color scales will show you the relative scale of the data in the set by laying a different background color in each cell. The lighter the color, the smaller the number.
Both versions work by selecting the cells and applying the highlight. I find that the yellow to dark orange works best because it is what our eyes are most used to, and you will see the relationships at a glance.
Icon sets
Icon sets work the same way as data bars and color scales, but I think their role in your personal productivity could be much larger. Icon sets basically puts an icon next to each value, the actual icon will depend on the relative value in the cell. By default the icons split the data into equal sets, so in a 3-icon set the icon indicating the top values will be shown next to the top 34%, the middle one from 33% - 66% and the one indicating the smallest values will show up beside the bottom 33%.
The great thing is that you can customize everything in the”more rules” section. You can edit the percentages at which icons are displayed for example, a very helpful feature if the standard deviation of your data is low (ie: it doesn’t vary do much). You can reverse the icon order with one click at the bottom, without having to rewrite the percentages, so if for your the lowest value is the best, you can show this easily.
Original post here: Daniel
Comments: