Start Here
Click Consulting
Purpose To provide sample exercises for using the Conditional Formatting feature.
Topics
The topics covered are:
Sheet Topic
Exercise 1 Greater Than / Less Than
Exercise 2 Equal To / Not Equal To
Exercise 3 Duplicate Values
Exercise 4 Data Bars
Exercise 5 Icon Sets
Exercise 1
Click Consulting
Exercise Using the techniques presented in the book, please perform the following:
Highlight all variances greater than +10% with Green Fill with Dark Green Text
Highlight all variances less than -10% with red Light Red Fill with Dark Red Text
SG&A Variance to Forecast
DeptID Department Actual Forecast Variance
100 Corporate $187,274 $175,000 -7.01%
101 Accounting $61,456 $60,000 -2.43%
102 HR $88,133 $90,000 2.07%
200 Operations $167,209 $150,000 -11.47%
202 Research $116,801 $125,000 6.56%
206 Manufacturing $175,202 $165,000 -6.18%
301 Sales $169,975 $125,000 -35.98%
305 Marketing $212,012 $185,000 -14.60%
400 Logistics $172,371 $200,000 13.81%
410 IT $92,749 $100,000 7.25%
420 Warehouse $57,845 $75,000 22.87%
Exercise 2
Click Consulting
Exercise Using the techniques presented in the book, please perform the following:
Conditionally format the Diff value with Green Fill with Dark Green Text if the value equals 0
Conditionally format the Diff value with Light Red Fill with Dark Red Text if the value is greater than 0
Conditionally format the Diff value cell with Light Red Fill with Dark Red Text if the value is less than
Summary
Total Debits $51,706.00
Total Credits $51,706.00
Diff $0.00
Telephone Expense Allocation
Account Department Comments Debit Credit
Telephone Operations To allocate telco by cell number $3,750.82
Telephone Research To allocate telco by cell number $2,120.97
Telephone Manufacturing To allocate telco by cell number $1,297.83
Telephone Sales To allocate telco by cell number $12,725.13
Telephone Marketing To allocate telco by cell number $1,982.20
Telephone Logistics To allocate telco by cell number $125.25
Telephone IT To allocate telco by cell number $2,207.85
Telephone Warehouse To allocate telco by cell number $497.20
Telephone Accounting To allocate telco by cell number $1,145.75
Telephone Corporate To allocate telco by cell number $25,853.00
$25,853.00 $25,853.00
e value equals 0
he value is greater than 0
if the value is less than 0
Diff
$3,750.82
$2,120.97
$1,297.83
$12,725.13
$1,982.20
$125.25
$2,207.85
$497.20
$1,145.75
-$25,853.00
$0.00
Exercise 2 Support
Click Consulting
Purpose Provides lists used on the Exercise 2 worksheet.
No exercises here; only provided for reference.
Note: I converted the account region below to a Table named tbl_accounts
I set up a custom named range called dd_accounts that refers to tbl_accounts
I converted the department region below to a Table named tbl_departments
I set up a custom named range called dd_departments that refers to tbl_departments
Account List
Account
Checking account
Money market
Savings
Prepaids
Deposits
Accounts Receivable
Inventory
Furniture and Fixtures
Computer Equipment
Machinery
Automobiles
Accounts Payable
Payroll liabilities
Taxes
Loan from officer
Retained earnings
Current year earnings
Income
COGS
Wages
Salary
Overhead
Office supplies
Marketing
Trade shows
Computer software
Small office equipment
Postage
Telephone
Internet
Travel
Meals and Entertainment
Department List
Department
Corporate
Accounting
HR
Operations
Research
Manufacturing
Sales
Marketing
Logistics
IT
Warehouse
bl_accounts
to tbl_accounts
departments
ers to tbl_departments
Exercise 3
Click Consulting
Exercise Using the techniques presented in the book, please perform the following:
Highlight the duplicate checks
Exercise On the basis of CK num kindly highlight the duplicate VID Code
Check List
CkNum VID Date Amount
4752 SSC201 9/12/2012 $1,460
4753 TNK900 9/19/2012 $645
4754 MER332 9/26/2012 $772
4755 TME400 10/3/2012 $469
4753 TNK900 9/19/2012 $645
4757 TRI002 10/17/2012 $489
4758 MOR300 10/24/2012 $1,692
4759 SMI331 10/31/2012 $239
4760 SSP204 11/7/2012 $880
4761 SMI333 11/14/2012 $347
4762 MER333 11/21/2012 $718
4758 MOR300 10/24/2012 $1,692
Exercise 4
Click Consulting
Exercise Using the techniques presented in the book, please perform the following:
Apply yellow gradient Data Bar formatting to the Amount column
Sales by Rep
RepID Amount
BEO $2,925
BYG $2,122
DAR $2,852
DMK $1,337
GSL $1,967
JUB $1,113
TPS $2,684
Exercise 5
Click Consulting
Exercise Using the techniques presented in the book, please perform the following:
Apply Icon Sets conditional formatting to the Turnover column
Inventory Turnover
Item Turnover
AB101 12.5
XY200 20.1
CG231 8.2
HA882 1.1
ZZ750 2.1
LL002 3.6
PY552 10.9
JJ120 18.2
JA221 11.7
RJ061 5.2
