Conditional formatting icon sets is useful where you want to represent your data with Icons. Excel provides set of few icons which can be displayed as per your requirement. This guide Applies to Excel 2007 onward.
How to use Conditional Formatting Icon Sets
Compare data with targets
- Enter data in table format.In our example above region wise sales data is used now.
Here we have kept sales above 75% as green zone, between 75%-50% as yellow zone and below 50% as red zone. - Now click on Conditional Formatting button under Home tab.
- Select Icon set -> More Rules
Conditional Formatting Icon Sets
- New Formatting Rule windows will come up. Enter values as per your requirement. We are using values for zones as described in point 1.
Conditional Formatting Options
Icon style provides different shapes available under MS Excel. Note – Type should be in accordance to your data type in cell (Number in our excel – refer to sample excel sheet available for download at bottom). - Final Table will come up as below.
Another comparison using icon set.
Here we are comparing Region sales of a month with previous month i.e. we want to get red when sales fall with reference to previous month, green when sales increases with reference to previous month and so on.
Here we have done it a bit indirectly
- Firstly we have added a blank column adjacent to Feb and March column.
- Now type formula =H4-G4. This because this number will be negative number in case sale is down and positive if sales is up.
- Now the conditional formatting (Same as step 3 above).
Conditional Formatting options
- In new formatting windows use values as shown below. Tricky part – How to show no change in sales (Yellow). Here we defined three region first is greater 0.01 and second is between 0.01 – 0 and third is below zero. The yellow zone i.e. no change in sale will be actual sale till +0.01 (You can choose a lower number if required).
- Your table is ready.
Click here to download sample sheet
