How to Use Icon Sets to Represent Values in Microsoft Excel


If you’re on the lookout for a singular means to symbolize your knowledge in Microsoft Excel, think about using icon units. Similar to color scales, icon units take a variety of values and use visible results to symbolize these values.

With a conditional formatting rule, you may show icons like site visitors lights, stars, or arrows based mostly on the values that you just enter. For instance, you may present an empty star for a worth of 10, {a partially} crammed star for 30, and a accomplished crammed star for 50.

This function is nice for issues like utilizing a score system, displaying accomplished duties, representing gross sales, or displaying a flux in funds.

Apply a Quick Conditional Formatting Icon Set

Like different conditional formatting guidelines in Excel, akin to highlighting top- or bottom-ranked values, you might have some fast choices to select from. These embrace fundamental icon units utilizing three, 4, or 5 classes with a variety of preset values.

Select the cells that you really want to apply the formatting to by clicking the primary cell and dragging your cursor by means of the remaining.

Then, open the Home tab and go to the Styles part of the ribbon. Click “Conditional Formatting,” and transfer your cursor to “Icon Sets.” You’ll see these fast choices listed.

As you hover your cursor over the assorted Icon Sets, you may see them previewed in your spreadsheet. This is a nifty means to see which set of icons works finest for you.

If you notice one that you really want to use, merely click on it. This applies the conditional formatting rule to your chosen cells with the icon set that you just selected. As you may see in the screenshot under, we chosen the celebs from our preliminary instance.

Create a Custom Conditional Formatting Icon Set

As beforehand talked about, these Icon Set choices from the pop-out menu have preset values connected. So, in the event you want to alter the ranges to match the information in your sheet, you may create a customized conditional formatting rule. And it’s simpler than you would possibly suppose!

Select the cells the place you need to apply the icons, go to the Home tab, and select “New Rule” from the Conditional Formatting drop-down listing.

On the Home tab, click Conditional Formatting, New Rule

When the New Formatting Rule window opens, choose “Format All Cells Based on Their Values” on the prime.

Pick Format All Cells Based on Their Values

At the underside of the window, click on the Format Style drop-down listing and decide “Icon Sets.” You’ll then customise the main points for the rule.

Choose the Icon Style in the subsequent drop-down listing. Again, you may decide from three, 4, or 5 classes. If you like the icons in the other association, click on “Reverse Icon Order.”

A helpful function of the Icon Sets customized rule is that you just aren’t caught with the precise set of icons that you choose. Below that Icon Style drop-down field, you’ll see containers for the icons in the group. This permits you to customise the precise icons in your rule. So in the event you, for instance, need to use a star, flag, and arrow as a substitute of three stars, go for it!

The ultimate half to establishing your rule is getting into the values for the vary. Choose “Greater Than” (>) or “Greater Than or Equal to” (>=) in the primary drop-down field. Enter your worth in the subsequent field and select whether or not it’s a quantity, %, method, or percentile. This provides you nice flexibility to arrange your rule.

Add the values and type of values

Now, click on “OK” to apply your rule.

One extra helpful function price mentioning is that you would be able to show the icon solely. By default, Excel reveals each the icon and the worth that you just enter. But there is perhaps circumstances the place you propose to rely solely on the icon. In that case, verify the field for “Show Icon Only.”

Here’s a terrific instance of utilizing Icon Sets the place you solely need to present the icon.

We need to show inexperienced, yellow, and purple site visitors gentle icons to point out whether or not our order is new, in progress, or full. To accomplish that, we’ll merely enter the numbers one, two, or three. As you may see, the values aren’t necessary in this state of affairs. They’re solely used to set off the icon, which is what we would like to see.

So, we do the next:

  1. Select our three-category site visitors gentle icons.
  2. Reverse the order (as a result of we would like the most important quantity represented by purple).
  3. Enter our values “3” and “2” as “Numbers.”
  4. Check the field to present the icon solely.

Now, all we now have to do in our sheet is kind “1” for brand new orders, “2” for these in progress, and “3” for full orders. When we hit Enter, all we see is our inexperienced, yellow, and purple site visitors gentle indicators.

Hopefully, this how-to for utilizing Icon Sets in Microsoft Excel prompts you to reap the benefits of this glorious function. And for an additional means to use conditional formatting, check out how to create progress bars in Excel.





Source link

This Web site is affiliated with Amazon associates, Clickbank, JVZoo, Sovrn //Commerce, Warrior Plus etc.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *