Up next in 10
Sort Data By Icons, Font Color, and Cell Color in Excel
25K views
Nov 7, 2024
In this video, you’ll learn how to sort data in Excel using conditional formatting. Typically, we sort records or rows in a table based on the values in one or more columns. However, Excel also offers custom sorting options that allow you to sort by font color, fill color, or cell icons created through conditional formatting. This powerful feature enables you to organize and analyze your data visually, making it easier to spot trends and insights at a glance. 00:00 Introduction 00:18 Sort by Cell Color 02:07 Sort By Cell Icon 03:06 Sort By Font Color
View Video Transcript
0:00
welcome to this tutorial on sorting
0:02
conditional formatted data in Excel
0:04
typically when we sort records or rows
0:06
in a table we do so based on the values
0:08
in one or more columns however did you
0:11
know that Excel allows you to sort by
0:13
font color fill color or cell icons
0:15
created through conditional formatting
0:17
in this tutorial I'll guide you through
0:19
the process let's apply some conditional
0:21
formatting to our data first select the
0:24
range of data you want to format go to
0:26
the Home tab and click on conditional
0:28
formatting in the Styles group choose
0:30
highlight cells rules then select
0:32
greater than from the drop- down menu
0:34
Define a value greater than 32 and
0:37
select a green fill with dark green text
0:39
for the formatting click okay to apply
0:41
this Rule now you'll notice that all
0:43
cells containing values greater than 32
0:45
have been formatted with a green
0:47
background and dark green text repeat
0:49
the previous steps but this time select
0:51
the less than rule set the threshold to
0:53
20 and choose a light red fill with dark
0:56
red text for formatting click okay you
0:58
should see that all cells with values
1:00
less than 20 are now highlighted with a
1:02
red background and dark red text finally
1:05
repeat the steps to apply conditional
1:07
formatting to cells containing values
1:09
between 19 and 31 choose a yellow fill
1:11
with dark yellow text and click okay as
1:14
you can see the cells with values
1:16
between 19 and 31 have been successfully
1:18
formatted with a yellow background and
1:20
dark yellow text now we have our data
1:22
formatted let's sort these items based
1:24
on their cell colors go to the Home tab
1:27
click on sort and filter drop down and
1:29
choose custom sort in the sort dialogue
1:31
box select the column where you applied
1:33
the conditional formatting from the sort
1:34
by drop-down list under sort on select
1:37
sell color from the drop- down menu
1:39
under order choose the color you want to
1:41
appear at the top of your sorted list
1:43
click add level button it will insert a
1:45
then by level repeat the previous steps
1:47
and under order choose the color you
1:49
want to appear at the bottom of your
1:51
sorted list click okay to close the sort
1:53
dialog box and there you have it your
1:56
data is now sorted according to the
1:58
conditional formatting you applied this
2:00
feature allows you to easily organize
2:02
your data visually making it even more
2:04
effective for analysis next we'll sort
2:07
our data by cell icons created through
2:09
conditional formatting let's start by
2:11
applying conditional formatting to our
2:12
data using icon sets go to the
2:15
conditional formatting menu and select
2:17
icon sets then choose a shape set that
2:19
suits your data now you'll see that each
2:21
cell displays a green yellow or red icon
2:24
based on its value now let's sort our
2:26
data based on these cell icons select
2:29
custom sort from the sort and filter
2:31
options in the sort dialogue box that
2:33
appears choose the column where you
2:35
applied the conditional formatting from
2:36
the sort by drop-down list under sorton
2:39
select conditional formatting icon from
2:41
the drop-down menu next under order
2:44
select the icon that you want to appear
2:46
at the top of your sorted list let's add
2:48
another sorting Criterion click the add
2:50
level button in the new level choose the
2:52
icon you want to appear at the bottom of
2:54
your sorted list finally click okay to
2:57
close the sort dialogue box your data is
2:59
is now sorted according to the
3:01
conditional formatting icons you applied
3:04
lastly we'll sort our data by font color
3:06
we'll Begin by using conditional
3:08
formatting to change the font color of
3:10
cells based on their values select the
3:12
cell range you want to sort by access
3:14
the conditional formatting menu then
3:16
navigate to highlight cells rules and
3:18
choose greater than in the dialogue box
3:20
that appears select custom format this
3:23
will open the format cells dialogue box
3:26
go to the font tab choose your desired
3:28
font color and click okay in the left
3:30
box enter the value for which you want
3:32
the formatting to apply when a cell's
3:34
value is greater than that number click
3:36
okay to apply the rule you'll notice
3:38
that some cells now have a different
3:40
font color next repeat these steps for
3:42
the less than and between rules
3:44
selecting a different font color for
3:46
each rule in the format cells dialogue
3:58
box now that we have our font colors set
4:00
up let's sort our data based on these
4:02
colors select custom sort from the sort
4:05
and filter options in the sort dialogue
4:07
box that appears choose the column where
4:09
you applied the conditional formatting
4:11
from the sort by drop-down list under
4:13
sort on select font color from the drop-
4:15
down menu next under order choose the
4:18
font color you want to appear at the top
4:20
of your sorted list click the add level
4:22
button in the new level select the font
4:24
color you want to appear at the bottom
4:26
of your sorted list click okay to close
4:28
the sort dialogue box
4:30
now you know how to sort your data
4:31
that's conditionally formatted by icons
4:33
font color or cell color thanks for
4:36
watching if you found this video helpful
4:38
please like share and subscribe to my
4:40
channel for more Excel tutorials your
4:42
support means a lot