How to set icon for top ranked value in excel năm 2024
In case you want to bring focus to top or bottom N values in a dataset, the best way is to highlight them in different colors. This article will teach you how to do this with Excel's presets and set up your own conditional formatting rule based on formula. Show
Highlighting cells with Excel conditional formatting may sound like a trivial task. But not if you think of how many ways you can actually do it. Do you want to shade the highest or lowest numbers in a range, column, or row? Or maybe you wish to show top 3 values in each row? And it would be really nice if you could control the number of highlighted cells directly on the sheet without having to go to the Conditional Formatting Rules manager every time. This tutorial shows how to do all this and a bit more! Highlight top or bottom N values in rangeIn Microsoft Excel, there are a few ways to highlight highest and lowest values with conditional formatting. Below, we provide an overview of 3 different methods, so you can choose the one that works best for you. Highlight top and bottom values with built-in ruleThe fastest way to highlight top 3, 5, 10 (or bottom n) values in Excel is to use an inbuilt conditional formatting rule. Here's how:
For this example, we choose to highlight top 3 values with the default Light Red Fill and get this result: More formatting options to show highest and lowest valuesIf you want more options than provided in the Top/Bottom presets, you can create a new rule from scratch:
For example, here's how you can highlight top 5 values with a green background color. Highlight top or bottom values with a formulaThe built-in rules described above are handy and easy to apply. However, they have one significant drawback - whenever you want to show a different number of values, you will have to change the number in the Conditional Formatting Rules Manager. To make the rule more resilient, you can base it on a formula. In our case, we will be using: LARGE function to shade top numbers: \=upper_left_cell>=LARGE(range, n) SMALL function to shade bottom numbers: \=upper_left_cell<=SMALL(range, n) To color highest or lowest values by using a formula-based rule, please follow these steps:
For more detailed instructions, please see . Done! The top 3 and bottom 3 values are highlighted in different colors. Later, if you wish to highlight, say, top 5 values, you will just type 5 in F2, and Excel will automatically apply the change. How this formula works: The LARGE function returns the n-th largest value in the specified range. In our case, it finds the 3rd largest value in A2:C8, which is 92. The formula compares each number in the selected range with the 3rd highest value. For any cell that is greater than or equal to 92, it returns TRUE, and the conditional formatting rule is applied. How to shade rows containing top or bottom N valuesWhen analyzing structured data, it may often be helpful to identify entire rows that contain top or bottom n values in the key column. For this, you can configure a formula-based conditional forming rule like explained in the previous example, but the formulas are a bit different. To show top n rows:
To show bottom n rows:
Where:
The rules are applied to the whole table except the header row (A2:B15). Like in the previous example, these rules are flexible. The formulas will recalculate automatically as soon as you change the numbers in the input cells (E2 and E3), and Excel will immediately reflect those changes and highlight a corresponding number of rows. How to highlight top N values in each rowIn case your dataset contains several numeric columns, you may want to show the highest or lowest values in each row. To have it done, you again create an Excel conditional formulating rule using a formula. For example, to highlight top 3 numbers in each row in the table below, the formula is:
The steps for creating the rule are described in , so here we only show the result. The rule is applied to all the numeric cells (B2:G2): To show bottom 3 rows, the formula would be:
How this formula works: As you may have noticed, this formula is very similar to the ones used in the previous examples, except the cell and range references. And that little change makes a big difference! In Excel conditional formatting, references are relative to the upper left cell of the range the rule applies to. So, we can pretend as if we are writing a formula for the leftmost cell, and Excel is "copying" it through all other cells in the selected range. In this example, the rule is applied to all the numeric cells (B2:G10), but the formula is written for row 2:
The LARGE function finds the 3rd largest value in B2:G2, which is 257. The formula checks if B2 is greater than or equal to 275, and if TRUE, applies the conditional formatting to that cell. Because B2 is a relative reference, Excel then virtually "copies" the formula to C2, D2, etc. As the column coordinates are locked with the $ sign ($B2:$G2), the range does not change when comparing other cells in the same row. For example, for C2, Excel will be evaluating this formula:
For row 3, the formula automatically changes like shown below because all row coordinates are relative:
And so on. The following tutorial can help you better understand the inner mechanics: Relative and absolute cell references in conditional formatting. That's how to highlight top and bottom values in Excel by using conditional formatting. I thank you for reading and look forward to seeing you on our blog next week! How do I add a status icon in Excel?Inserting icons To insert an icon, click the Insert tab and then select Icons. The Insert Icons menu will appear. You can scroll through a wide range of subjects, including people, technology, commerce, the arts, and more. Once you find an icon you like, select it and then click Insert. How do I set icon sets in Excel?Format cells by using icon sets. Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to.. On the Home tab, click Conditional Formatting.. Point to Icon Sets, and then click the icon set that you want. Tip: Icon sets can be combined with other conditional formats.. How do I color the top 3 values in Excel?On the Home tab, click Conditional Formatting > New Rule. In the New Formatting Rule dialog box, choose Format only top or bottom ranked values. In the drop-down list, select Top or Bottom, and type how many values to highlight in the box next to it. How do I add an icon in Excel?Click inside the cell of the spreadsheet where you want to insert the object. On the Create New tab, select the type of object you want to insert from the list presented. If you want to insert an icon into the spreadsheet instead of the object itself, select the Display as icon check box. Click OK. |