Bạn đang xem bài viết 3 Ways To Sort By Color In Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
There are several ways to sort data in Microsoft Excel. Learn how to use conditional sorting in Excel to sort by font color, cell background color, or icon color.
Instructions in this article apply to Excel for Microsoft Office 365, Excel 2019, Excel 2016, and Excel 2013 for Windows and Mac.
Select a Range to Be Sorted in Excel
Before data can be sorted, Excel needs to know the exact range to sort. Excel can automatically include related data in a range so long as there are no blank rows or columns within the selected area. Blank rows and columns between areas of related data are okay. Excel then determines if the data area has field names and excludes those rows from the records to be sorted.
Allowing Excel to select the range to be sorted is fine for small amounts of data. However, for large areas of data, the easiest way to ensure that the correct range is selected is to highlight it before sorting.
If the same range is to be sorted repeatedly, the best approach is to give the range a name. If a name is defined for the range to be sorted, type the name in the Name Box, or select it from the associated drop-down list. This way, Excel automatically highlights the correct range of data in the worksheet.
Any sorting requires the use of sort order. When sorting by values, there are two possible sort orders: ascending and descending. However, when sorting by colors, no such order exists, so you must manually define the color sort order.
How to Sort by Cell Background Color in Excel
In the example below, the records of students age 20 and younger are highlighted in red. To sort the data by cell background color so that the red entries appear on top:
Highlight the range of cells to be sorted (cells A2 to D11 in the example).
Select the Sort on drop-down arrow and choose Cell Color.
Clear the My data has headers check box so that the first row doesn’t get cut off.
Select the Order drop-down arrow and choose Red.
When Excel finds different cell background colors in the selected data, it adds those colors to the Order drop-down list in the dialog box.
Choose On Top from the drop-down list next to the sort order box so that the red cells will be at the top of the list, then select OK.
The four records with red backgrounds are grouped together at the top of the data range.
When working with calculations, you can make negative numbers in Excel appear red by default to help those numbers stand out more.
How to Sort by Font Color in Excel
In the example below, the records of students enrolled in nursing programs appear in red, and those enrolled in science programs are blue. To sort the data by font color:
Highlight the range of cells to be sorted (cells A2 to D11 in the example).
Select the Sort on drop-down arrow and choose Font Color.
Clear the My data has headers check box so that the first row doesn’t get cut off.
Select the Order drop-down arrow, then choose Red.
When Excel finds different font colors in the selected data, it adds those colors to the Order drop-down list in the dialog box.
Choose On Top from the drop-down list next to the sort order box so that the red entries will be at the top of the list.
Select Add to add a second sort level.
Use the same settings as the first sort level, but this time select the Order drop-down arrow and choose Blue.
Select OK to sort the data and close the dialog box.
The two records with the red font color are grouped together at the top of the data range, followed by the two blue records.
How to Sort by Icon in Excel
Icon sets offer an alternative to regular conditional formatting options that focus on the font and cell formatting changes. The example below contains dates and temperatures that have been conditionally formatted with the stoplight icon set based on the daily maximum temperatures.
Follow these steps to sort the data so that records displaying the green icons are grouped first, followed by the yellow icons, and then the red icons:
Highlight the range of cells to be sorted (cells A2 to B31 in the example).
Select the Column drop-down arrow, then choose the column containing the conditional icons (Temperature in the example).
Due to the way conditional formatting with icons works, you can leave the My data has headers check box selected.
Select the Sort on drop-down arrow, then choose Conditional Formatting Icon.
Select the Order drop-down arrow, then choose Green.
Choose On Top from the drop-down list next to the sort order box so that the green icon entries will be at the top of the list.
Select Add to add a second sort level.
Use the same settings as the first sort level, but this time select the Order drop-down arrow and choose Yellow.
Select Add to add a third sort level, then use the same settings as the first two levels, but this time select the Order drop-down arrow and choose Red.
Select OK to sort the data and close the dialog box.
The records with the green icon are grouped together at the top of the data range, followed by the records with the yellow icon, and then those with a red icon.
How Do You Sort By Color In Microsoft Excel?
There are several ways to sort data in Microsoft Excel. Learn how to use conditional sorting in Excel to sort by font color, cell background color, or icon color.
Select a Range to Be Sorted in Excel
Before data can be sorted, Excel needs to know the exact range to sort. Excel can automatically include related data in a range so long as there are no blank rows or columns within the selected area. Blank rows and columns between areas of related data are okay. Excel then determines if the data area has field names and excludes those rows from the records to be sorted.
Allowing Excel to select the range to be sorted is fine for small amounts of data. However, for large areas of data, the easiest way to ensure that the correct range is selected is to highlight it before sorting.
If the same range is to be sorted repeatedly, the best approach is to give the range a name. If a name is defined for the range to be sorted, type the name in the Name Box, or select it from the associated drop-down list. This way, Excel automatically highlights the correct range of data in the worksheet.
Any sorting requires the use of sort order. When sorting by values, there are two possible sort orders: ascending and descending. However, when sorting by colors, no such order exists, so you must manually define the color sort order.
How to Sort by Cell Background Color in Excel
In the example below, the records of students age 20 and younger are highlighted in red. To sort the data by cell background color so that the red entries appear on top:
Select the Order drop-down arrow and choose Red.
When Excel finds different cell background colors in the selected data, it adds those colors to the Order drop-down list in the dialog box.
The four records with red backgrounds are grouped together at the top of the data range.
When working with calculations, you can make negative numbers in Excel appear red by default to help those numbers stand out more.
How to Sort by Font Color in Excel
In the example below, the records of students enrolled in nursing programs appear in red, and those enrolled in science programs are blue. To sort the data by font color:
How to Sort by Icon in Excel
Icon sets offer an alternative to regular conditional formatting options that focus on the font and cell formatting changes. The example below contains dates and temperatures that have been conditionally formatted with the stoplighticon set based on the daily maximum temperatures.
Follow these steps to sort the data so that records displaying the green icons are grouped first, followed by the yellow icons, and then the red icons:
Select Add to add a third sort level, then use the same settings as the first two levels, but this time select the Order drop-down arrow and choose Red.
Thanks for letting us know!
Other Not enough details Hard to understand
3 Ways To Unhide Multiple Sheets In Excel + Vba Macros
Bottom line: Learn a few different ways to unhide (show) multiple sheets at the same time with a VBA macro or add-in.
Skill Level: Intermediate
Cannot Unhide Multiple Sheets in Excel??
As you probably know, you cannot unhide two or more sheets at the same time in Excel. The Unhide menu only allows you to select one sheet at a time.
#1 – Use the VBA Immediate Window to Unhide All
The fastest way to make all the sheets visible in Excel is to use a macro (VBA). The following line of VBA code uses a For Next Loop to loop through each sheet in the active workbook and make each sheet visible.
For Each ws In Sheets:ws.Visible=True:Next
You can run this code in the VB Editor’s Immediate Window in three easy steps:
Alt+F11 (opens the VB Editor Window)
Ctrl+G (opens the Immediate Window)
Paste the following line of code in the Immediate Window and press EnterFor Each ws In Sheets:ws.Visible=True:Next
The screencast below shows how to implement these steps.
The colon character “:” used in the code allows you to basically combine multiple lines of code into one line. This makes it possible to run in the Immediate Window because the Immediate Window only evaluates one line of code at a time.
#2 – Use a Macro to Unhide Multiple Sheets
If you are scratching your head at that line of code in #1, this section should help explain it better.
The macro below is basically that same line of code, but it is broken up into multiple lines. This makes it much easier to read and understand.
Sub Unhide_Multiple_Sheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End SubDownload the file that contains the macro.
Unhide Multiple Sheets chúng tôi (64.2 KB)
The lines in the code above that start with “For” and “Next” represent a For-Next Loop Statement. The first line “For Each ws In ActiveWorkbook.Worksheets” tells the macro to loop through each worksheet in the worksheets collection of the workbook.
When the “Next ws” line of code is hit, the macro jumps back up to the first line of code within the loop and evaluates it again. It continues to loop through all the sheets in the workbook’s worksheet collection (Activeworkbook. Worksheets).
We can then use “ws” inside the loop to change the current worksheet’s properties. In this case we are setting the “Visible” property of the sheet to be visible (xlSheetVisible). The visible property has three different properties to choose from:
xlSheetHidden
xlSheetVeryHidden
xlSheetVisible
Here is the documentation on the VBA Visible property from Microsoft. And checkout my article on the For Next Loop for a detailed explanation of how it works.
Unhide Sheets That Contain a Specific Name
What if we only want to unhide the sheets that contain the word “pivot” in the sheet name?
We can add a simple IF statement to the macro to only unhide sheets that contain a specific name or text.
Sub Unhide_Sheets_Containing() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible End If Next ws End SubDownload the file that contains the macro.
Unhide Multiple Sheets chúng tôi (64.2 KB)
The InStr function searches for text in a string and returns the position of the first occurrence of the text. It is short for InString, and the function is similar to the SEARCH or FIND functions in Excel.
So in this case we are looking for any sheet that contains the word “pivot” in the sheet name. The “ws.name” reference returns the name of the worksheet that is currently being evaluated in the For-Next loop.
If the word “pivot” is NOT found in the sheet name, then the IF statement will evaluate to False and the code will skip all lines until it gets to the “End If” line. Therefore, the sheet that is currently being evaluated in the loop will keep its current visible property (visible or hidden).
This macro works great if you are hiding and unhiding sheets every day/week/month for a report that you maintain. Run the macro to unhide specific sheets when you open the workbook. After you are finished, run the same code but change the visible property to xlSheetHidden to re-hide the sheets (you can create a new macro for this).
#3 – Use Tab Hound or Tab Control
The screencast below shows how simple this is.
This makes the process of unhiding multiple sheets really fast!
Tab Hound also contains additional ways to filter the sheet list. You can type a search in the search box, filter for all visible or hidden tabs, and even filter by tab color. This makes it easy to find the sheets you are looking for and then perform actions on them like hiding/unhiding.
This video also shows how to quickly hide and unhide multiple sheets with Tab Hound.
If you are producing weekly or monthly reports, and want to make sure all the right sheets are hidden before you send it out, the Tab Control add-in can save you a lot of time.
Here is a scenario that we commonly face…
We need to update a workbook with new data this week and make some changes before emailing it out. Those updates require us to unhide a few sheets, make the changes, then hide the sheets again. It can be a time consuming process if you have to hide/unhide a lot of sheets.
The Tab Control add-in is included with Tab Hound.
Unhiding multiple sheets at the same time in Excel will require code or a macro. There is one other way using Custom Views, but it has limitations if you use Excel Tables (and I love Tables).
Hopefully you learned some VBA code that you can implement. You can also add the macros to your Personal Macro workbook to run them anytime you need.
If coding isn’t your thing then checkout the Tab Hound add-in. It will save you time and make your life a lot easier. (win-win!) 🙂
Colors In An If Function
Unfortunately, there is no way to acceptably accomplish this task without using macros, in one form or another. The closest non-macro solution is to create a name that determines colors, in this manner:
Select cell A1.
Use a name such as “mycolor” (without the quote marks).
In the Refers To box, enter the following, as a single line:
=IF(GET.CELL(38,Sheet1!A1)=10,"GO",IF(GET.CELL(38,Sheet1!A1) =3,"Stop","Neither"))
With this name defined, you can, in any cell, enter the following:
=mycolorThe result is that you will see text based upon the color of the cell in which you place this formula. The drawback to this approach, of course, is that it doesn’t allow you to reference cells other than the one in which the formula is placed.
The solution, then, is to use a user-defined function, which is (by definition) a macro. The macro can check the color with which a cell is filled and then return a value. For instance, the following example returns one of the three words, based on the color in a target cell:
Function CheckColor1(range) If range.Interior.Color = RGB(256, 0, 0) Then CheckColor1 = "Stop" ElseIf range.Interior.Color = RGB(0, 256, 0) Then CheckColor1 = "Go" Else CheckColor1 = "Neither" End If End FunctionThis macro evaluates the RGB values of the colors in a cell, and returns a string based on those values. You could use the function in a cell in this manner:
=CheckColor1(B5)If you prefer to check index colors instead of RGB colors, then the following variation will work:
Function CheckColor2(range) If range.Interior.ColorIndex = 3 Then CheckColor2 = "Stop" ElseIf range.Interior.ColorIndex = 4 Then CheckColor2 = "Go" Else CheckColor2 = "Neither" End If End FunctionWhether you are using the RGB approach or the color index approach, you’ll want to check to make sure that the values used in the macros reflect the actual values used for the colors in the cells you are testing. In other words, Excel allows you to use different shades of green and red, so you’ll want to make sure that the RGB values and color index values used in the macros match those used by the color shades in your cells.
One way you can do this is to use a very simple macro that does nothing but return a color index value:
Function GetFillColor(Rng As Range) As Long GetFillColor = Rng.Interior.ColorIndex End FunctionNow, in your worksheet, you can use the following:
=GetFillColor(B5)The result is the color index value of cell B5 is displayed. Assuming that cell B5 is formatted using one of the colors you expect (red or green), you can plug the index value back into the earlier macros to get the desired results. You could simply skip that step, however, and rely on the value returned by GetFillColor to put together an IF formula, in this manner:
=IF(GetFillColor(B5)=4,"Go", IF(GetFillColor(B5)=3,"Stop", "Neither"))You’ll want to keep in mind that these functions (whether you look at the RGB color values or the color index values) examine the explicit formatting of a cell. They don’t take into account any implicit formatting, such as that applied through conditional formatting.
For some other good ideas, formulas, and functions on working with colors, refer to this page at Chip Pearson’s website:
Cập nhật thông tin chi tiết về 3 Ways To Sort By Color In Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!