Excel Icon SetsUndoubtedly visual graphics make your data look more attractive. Icons increase the readability of your text. If you're want to make your data good-looking in Excel, you can also opt for Icon Sets. Unlike Color Scales, icon sets take a range of values and apply visual icons to represent those values. You can display icons like traffic lights, stars, or arrows based on your values with a conditional formatting rule. For example, you can show a 0 rating star for a number less than 30, a half-filled star for a value less than 80 and greater than 30, and a 5-star Rating for values that are above 80. This conditional formatting feature is useful in representing data like using a rating system, visually showing completed jobs, representing sales numbers, etc. What are Excel Icon Sets?"Excel icon sets is a part of conditional formatting that helps the users to visually represent the data with arrows, shapes, check marks, flags, rating starts and other objects." The Icon Sets conditional formatting feature in Excel aids the users to visually represent their data with arrows, directions, shapes, checkmarks, rating stars, and other icons. It beautifies the number representation in your Excel worksheet. In Excel, you will find different types of inbuilt Icon sets options used to visualize the selected cell based on their numbers or values. For example, you can use the star rating icon to display the ratings of any product, showing completed tasks, representing sales. You can apply the icon sets to your data by accessing the Home menu ribbon's conditional formatting drop-down list. All the formatting options will be displayed. Click on the icon sets option (refer to the below image). Icon Set OptionsBy default there are four categories of Excel icon sets. All the icons in the list serves some purpose.
If you wish to customize the color or functioning of the icons based on some criteria, you should select the More Rules? option. Points to Remember
ExamplesExample 1: Using Icon sets feature highlight the cell values with different object based on some criteria.Below given is the Excel table, representing the list of household spending. Using Excel Icon sets, list down the spendings in the order of their values. Use a green circle to represent the highest figures (greater than 100), orange circles for medium values (greater than 60 and lower than 100), and red circles for lower values (below 60).
The Icon Sets conditional formatting feature in Excel aids the users to visually represent their data with arrows, directions, shapes, check marks, rating stars and other different icons. To list down the spendings with Excel icon sets in the order of their values, follow the below steps: STEP-1 Select the range of cells Select the cells you wish to add the icons using the Icon sets conditional formatting feature. In our case, we have to add the icons in column D. Therefore, we have selected the cells from D6 to D14. Refer to the below image: Step 2: Select Conditional Formatting Icon Sets Go to the Home tab of the Excel ribbon. Apply the icon sets by selecting the Conditional Formatting (listed in Styles group) > Icon Sets Step 3: Click on 'More Rules'
Step 4: Apply the format and specific criteria Excel will bring up 'New Formatting Rule' window, do the following'
Important Tips:
Step-4 The icons will be applied according to the criteria As a result, you will notice the following:
Look out at the below figure for the resulting output. A common belief among Excel users is that Excel icon sets can only be used to format cells based on their values. But it is not all true! With good knowledge and more effort, one can set Excel icons based on the blank and non-blank cell values in a row, as shown below. Example 2. Apply an icon set based on blanks and non-blanks cell valuesIn the below example, you are given a list of data inputs for different commands. There were few errors in the command because of that it hasn't processed any output. Using Excel Icon set add three different icons depending on whether the cells in the same row are blank or non-blank.
Solution: To solve the above, firstly, we must find the count of the total number of blank cells in a row. For that, we will need the help of an Excel function, i.e., COUNTBLANK. To use an Excel icon set for the above criteria, execute the following steps: Step 1: Add an Empty Column The first step is to add an empty helper column named with 'Icon' In this helper column, we will count the number of blank cells present in a row. Step 2: Count the blank cells Next, we will use the inbuilt COUNTBLANK function of Excel. Start the function using equal to (=) function followed by COUNTBLANK. In the parameter specify the range of the row. Your formula will become: =COUNTBLANK(B12:F12) Refer to the below image: Drag the formula using the '+' icon to copy the formula down the cells. Don't worry because here we have used relative range reference, so the specified range in the formula will change as per the row. As shown below, you will have the count of blank cells in the helper column. STEP-3 Select the range of cells Select the cells you wish to add the icons using the Icon sets conditional formatting feature. In our case, we have selected the cells from D6 to D14. Refer to the below image: Step 4: Select Conditional Formatting Icon Sets
Step 4: Apply the format and specific criteria Excel will bring up 'New Formatting Rule' window, do the following:
Refer to the below image: Step 5: Excel will add the icon sets according to the criteria As a result, you will notice the checkmark icon if all the cells in the row are filled with data, an exclamatory icon if some cells are blank values and cross icon if all the cells in a row are blank. Look out at the below figure for the resulting output.
Next TopicExcel ROWS Function
|