With new Excel 2007 and 2010, Microsoft has put many functionality in conditional formatting which allow you instance format and conditional rules. You just simply use the pre-ruled set and choose the existing format to make your data look professional.
There are 5 pre-ruled already set for you to choose:
- Highlight Cells Rules: which allow you to choose whether when to highlight the cell. E.g you choose to highlight the cell when the data is smaller than 100.
- Top/Bottom Rules: this rule is used to identify the top/buttom values with the format specified.
- Data Bars: is used to show the bar within the data you format.
- Color Scales: is used to show different color highlighted for different scales of data.
- Icon Sets: is used to put the different icon for different data set.
1. Highlight Cell Rules:
For those rules under this rules work the same. When you click any rules (let's say Greater Than) then you input the value that you want to put the conditional format and choose the format your want on the right drop box. Then you cells will be formatted accordingly.
- Greater Than/Less Than: to use when you want to highlight the cell when the value is greater/less than the specified value
- Between: when the value is somewhat between the specified values.
- Equal to: when the value is exactly the same as the value you specified.
- Text that Contains: used to format the text.
- Date Occurring: used to format the date
- Duplicate Values: used to highlight the duplicate data.
2. Top/Bottom Rule
For top/bottom rule, it is used to highlight or format the cell with the top/bottom values such as:
- Top 10 items: it is not necessary to 10, you can specify how many top value to be formatted.
- Top 10%..: you can specify how many percentage on the top value to be formatted
- Bottom 10 items and button 10% is similar to the above options
- Above Average/Below Average: it will be formatted if the value is above/Below the average of the total value.
3. Data Bar
With data bar, you can put the bar chart in your cell directly. Just select the whole data, then select the format from data bars as shown in the picture, then you will have the embed data automatically.
4. Icon sets:
The icon set allow you to place the icon embeded in the cells directly according to the data you input. It works very perfect to what you need.