0:00
hi and welcome in this video we'll learn
0:02
how to create a column chart where the
0:04
color of each column dynamically depends
0:06
on the value it represents when you
0:08
insert a column chart in Excel by
0:10
default all data series are formatted
0:12
with a single color while the fill tab
0:14
in the format data series dialogue box
0:16
provides various color options these
0:18
colors are not tied to the values of the
0:21
data series unfortunately Excel charts
0:24
don't directly support conditional
0:25
formatting but don't worry we have a
0:28
work around in this tutorial
0:30
we'll work with sales data for each
0:32
month from January to December our goal
0:35
is to create a chart where the color of
0:37
each column reflects its value for
0:39
example we want green color for highest
0:41
sale and red color for lowest sale to
0:44
achieve this we need to split the sales
0:46
data into separate columns for each
0:48
condition in Cel C2 the formula checks
0:51
if the value in B2 is 6,000 or higher if
0:54
it is the formula copies the value if
0:57
not it leaves the cell blank we then cop
0:59
Cy this formula down the column to apply
1:01
it to the entire range of data in cell
1:04
D2 the formula checks if the value in B2
1:07
is between 400 and 59.99 if true it
1:11
copies the value otherwise it leaves the
1:13
cell blank copy it down the column by
1:16
double clicking the fill handle in cell
1:18
E2 the formula checks if the value in B2
1:21
Falls between 2,000 and
1:23
$39.99 again apply the formula to the
1:25
entire column simply double click the
1:30
in cell F2 the formula extract sales
1:33
values less than 2,000 copy it down the
1:36
column as well at this point you've
1:38
created four new columns that classify
1:40
your sales data into categories
1:42
excellent good average and
1:46
low now select the data first highlight
1:50
the month column while holding down the
1:51
control key select the newly created
1:53
data in range C1 to f-13 this skips the
1:57
original sales column since the data has
1:59
already been split into four categories
2:02
next go to the insert Tab and choose a
2:04
column chart unlike the default chart
2:06
which uses a single color for all series
2:08
this chart now displays different colors
2:10
for each series grouped based on the
2:12
value ranges we Define using the if
2:15
conditions let's customize the colors
2:17
for each series further right click on a
2:19
series like the excellent category and
2:22
select format data series Under The Fill
2:24
options choose a color for that series
2:27
such as green for excellent repeat this
2:29
process for the groups assigning blue
2:31
for good yellow for average and red for
2:56
screen when you insert the chart you
2:58
might notice that the for data series
3:00
are slightly overlaid on top of each
3:02
other to fix this rightclick on any data
3:05
series and choose format data series
3:08
navigate to the series options tab
3:10
adjust the series overlap setting by
3:11
increasing it to 100% to ensure the
3:14
columns align properly that's it you've
3:16
successfully created a column chart with
3:18
conditional formatting the colors now
3:20
change dynamically based on the sales
3:22
values thank you for watching don't
3:25
forget to like share and subscribe for
3:26
more Excel tips and tutorials see you in
