Salesperson Q1 Sales Q2 Sales Q3 Sales Q4 Sales Total Sales
Jimbob 42 33 31 22 128
Benny 55 35 13 43 146
Lynn 66 39 23 55 183
Shana 33 75 56 12 176
Bob 45 45 89 64 243
Marsha 77 63 65 53 258
Salesperson Q1 Sales Q2 Sales Q3 Sales Q4 Sales Total Sales
Jimbob 42 33 31 22 128
Benny 55 35 13 43 146
Lynn 66 39 23 55 183
Shana 33 75 56 12 176
Bob 45 45 89 64 243
Marsha 77 63 65 53 258
gb
db
db
db
bd
bd
bd
bd
db
db
db
bd
bd
bd
db
db
dfb
db
db
d
bd
b
bd
db
db
d
bd
bd
bd
bd
dfb
fdb
dfbdf
INSTRUCTIONS
Fill the Total Sales Column G in the table with
the SUM function.
In the colors worksheet – Set Conditional
Formatting for the values in Column G (Total
Sale) to Highlight the cells to have a Red Fill if
the total sales are below 175 and a Green Fill
if Total Sales are 175 and above.
Total Sales
Salesperson Q1 Sales Q2 Sales Q3 Sales Q4 Sales Total Sales
Jimbob 42 33 31 22 128
Benny 55 35 13 43 146
Lynn 66 39 23 55 183
Shana 33 75 56 12 176
Bob 45 45 89 64 243
Marsha 77 63 65 53 258
Salesperson Q1 Sales Q2 Sales Q3 Sales Q4 Sales Total Sales
Jimbob 42 33 31 22 128
Benny 55 35 13 43 146
Lynn 66 39 23 55 183
Shana 33 75 56 12 176
Bob 45 45 89 64 243
Marsha 77 63 65 53 258
g
erg
erg
g
g
eb
eb
eb
eb
rb
e
be
be
be
eb
eb
eb
eb
e
be
be
eb
ffb
df
bd
bd
b
bd
b
b
e
reg
eg
eb
eb
b
rtn
rnweb
eqb
eqb
bqe
b
eab
eqb
b
we
h
e
b
we
g
we
bh
e
g
we
g
we
bh
we
ghwe
h
e
ge
ge
he
he
INSTRUCTIONS
In the Flags worksheets – Set Conditional Formatting for the
values in columns C, D, E and F (Quarterly Sales) to show a red
flag if sales were below 20, a yellow flag if sales were between
20-50 and a green flag for sales higher than 50. To do this:
Total Sales
ng for the
o show a red
ere between
o do this:
Salesperson Q1 Sales Q2 Sales Q3 Sales Q4 Sales Total Sales
Jimbob 42 33 31 22 128
Benny 55 35 13 43 146
Lynn 66 39 23 55 183
Shana 33 75 56 12 176
Bob 45 45 89 64 243
Marsha 77 63 65 53 258
INSTRUCTIONS
In the Data Bars worksheet – Set conditional Formatting for the valu
Column G (Total Sales) to use a Purple Gradient Data Bar
matting for the values in
Data Bar
Salesperson Q1 Sales Q2 Sales Q3 Sales Q4 Sales Total Sales
Jimbob 42 33 31 22 128
Benny 55 35 13 43 146
Lynn 66 39 23 55 183
Shana 33 75 56 12 176
Bob 45 45 89 64 243
Marsha 77 63 65 53 258
INSTRUCTIONS
In the Text Highlight worksheet, Set Conditional Formatting for
the values in column B (Salesperson)to Highlight the cells to
have a Yellow Fill if the name contains “bob”
ng for
to
UK Top 10 Songs 2016
Rank Song Title
1 ONE DANCE
2 7 YEARS
3 CHEAP THRILLS
4 I TOOK A PILL IN IBIZA
5 THIS IS WHAT YOU CAME FOR
6 LUSH LIFE
7 CLOSER
8 LOVE YOURSELF
9 WORK
10 CAN'T STOP THE FEELING
0 Songs 2016 INSTRUCTIONS
Artist
DRAKE FEAT WIZKID & KYLA
I’ve Set Conditional Formatting for t
Title and Artist Columns so that the
LUKAS GRAHAM
Highlights Red if it contains any wor
SIA letter Z in. Please clear all Condition
MIKE POSNER formatting from this table
CALVIN HARRIS FEAT RIHANNA
(Highlight the cells in the table – Ho
ZARA LARSSON
Conditional Formatting – Clear Rule
CHAINSMOKERS FEAT HALSEY Rules for Selected Cells).
JUSTIN BIEBER
RIHANNA FEAT DRAKE
JUSTIN TIMBERLAKE
UCTIONS
onal Formatting for the Song
Columns so that the cell
f it contains any word with the
se clear all Conditional
m this table
ells in the table – Home Tab –
matting – Clear Rules – Clear
ed Cells).
Salesperson Q1 Q2 Q3 Q4
Zariah 88 95 55 90
Benny 65 75 60 88
Lynn 66 77 88 99
Shana 100 80 100 75
Bob 45 55 25 70
Marsha 77 63 65 87
Maria 48 70 71 32
Zane 26 37 44 21
glove 8 92 71 85
Cole 3 62 38 71
Step 1 Enter 4 new names and sales data for each new person
Set Conditional Formatting for Column G of the table above to format cells in the order total
column that contain above average values by applying Green Fill With Dark Green Text [Select
Step 2
column – Home tab – Conditional formatting – Top Bottom rules – Above Average – Change
the colors – OK]
Bonus - Set Column G to round to ONE decimal
Save as "Last Name + First Name + Conditional
Formatting" when finished and send it to me for
grading
Average Sales
82
72
82.5
88.8
48.8
73
55.3
32
64
43.5
bove to format cells in the order total
Green Fill With Dark Green Text [Select
ttom rules – Above Average – Change
itional
me for
