CUSTOM FORMATTING
Date Add Text Large Numbers
dddd 0 "Miles" 0.00,, "M"
Friday 446 Miles 1.36 M
42370 446 1358792
42371 563 8115961
42372 100 12848403
42373 1374 -50000
42374 1361 9418730
42375 14 3588459
42376 546 17700716
42377 242 8081417
Format Description
d,m,y For dates, months and years.
For Eg: If you write "dd" date will become "01", if 3 tim
"dddd" then "Thursday".
# Pound sign does not dislpay extra zeros.
One comma "," to display number in Thousands and tw
n is the value from 1 to 56 and nth value from color pa
[BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], [YELLOW],
[COLOR n]
Color Codes:
[Link]
Colors Icons
[>=300][Green]0;[Red]0 [Green][>300]0 "▲";[Red][<300]0 "▼";;
253 253 ▼
253 253
331 331
581 581
351 351
605 605
72 72
1459 1459
55 55
ars.
date will become "01", if 3 times "ddd" then weekday in sort form "Thu" and if 4 time
ay extra zeros.
number in Thousands and two comma ",," to display number in Millions
6 and nth value from color palette
CONDITIONAL FORMATTING
Rules Highlight Cells Rules Top/Bottom Rules Data Bars
1. > 6,000 in green Top 5 in Green Color by percentage
Criteria's 2. 4K to 6K yellow Top 20% Bottom 30% by percentile
3. < 4K in red Custom Formatting
2010 2011 2012
Salesman1 8,830 5,310 5,000
Salesman2 11,529 9,000 6,319
Salesman3 5,000 7,500 5,288
Salesman4 6,224 6,336 - 5,000
Salesman5 5,212 5,597 7,312
Salesman6 5,169 8,848 7,370
Salesman7 4,413 8,292 5,043
Salesman8 11,276 5,857 9,601
Salesman9 3,000 5,935 5,440
Salesman10 8,672 6,852 9,241
Color Scales Icon Sets Formula
Green to Red Greater 8K (Upwards) Compare with 2014
Customize Scalling 7K to 8K (Average) Entire Range
Below 7K (Downwards)
2013 2014 2015
6,404 5,310 7,071.00
8,330 9,000 5,172.00
7,959 9,000 7,683.00
6,095 6,336 7,100.00
9,742 7,500 5,935.00
6,361 8,848 5,554.00
6,710 8,292 6,281.00
6,003 5,857 5,439.00
8,565 5,935 6,845.00
9,695 6,852 8,213.00
Dollar Use and IF Formula
Sales Sales % Incentive Incentive
Salesman1 4,000
Salesman2 11,529
Salesman3 5,000
Salesman4 6,224
Salesman5 5,212
Salesman6 5,169
Salesman7 4,413
Salesman8 11,276
Salesman9 10,431
Salesman10 8,672
Total Sales 71,926
>= 8000 then incentive 5%
>=5000 and <8000 then incentive 2.5%
>=3000 and <5000 the 1%
<3000 no incentive
65 78
USD INR GBP
9
23
24
11
SUMIFS & COUNTIFS FUNCTIONS
Total Sales First Class Second Class Same Day
Binders
Total Orders First Class Second Class Same Day
DSLR
Order ID Order Date Ship Mode Customer Name
UN-2011-86536 8/2/2014 First Class James Smith
AU-2011-88612 7/30/2014 First Class John Johnson
AU-2011-77787 7/29/2014 Second Class Robert Williams
SE-2011-86411 8/2/2014 Second Class William Jones
AU-2011-16465 7/30/2014 Same Day David Miller
NE-2011-37144 8/9/2014 First Class Richard Davis
NE-2011-81382 8/8/2014 First Class Charles Garcia
UN-2011-86536 8/2/2014 First Class James Smith
AU-2011-88612 7/30/2014 First Class John Johnson
AU-2011-77787 7/29/2014 Second Class Robert Williams
SE-2011-86411 8/2/2014 Second Class William Jones
AU-2011-16465 7/30/2014 Same Day David Miller
NE-2011-37144 8/9/2014 First Class Richard Davis
NE-2011-81382 8/8/2014 First Class Charles Garcia
UN-2011-86536 8/2/2014 First Class James Smith
AU-2011-88612 7/30/2014 First Class John Johnson
AU-2011-77787 7/29/2014 Second Class Robert Williams
SE-2011-86411 8/2/2014 Second Class William Jones
AU-2011-16465 7/30/2014 Same Day David Miller
NE-2011-37144 8/9/2014 First Class Richard Davis
NE-2011-81382 8/8/2014 First Class Charles Garcia
UN-2011-86536 8/2/2014 First Class James Smith
AU-2011-88612 7/30/2014 First Class John Johnson
AU-2011-77787 7/29/2014 Second Class Robert Williams
SE-2011-86411 8/2/2014 Second Class William Jones
AU-2011-16465 7/30/2014 Same Day David Miller
NE-2011-37144 8/9/2014 First Class Richard Davis
NE-2011-81382 8/8/2014 First Class Charles Garcia
Product Sales Profit
Binders 132 5
DSLR 158 59
Phones 185 50
Binders 117 33
Phones 185 49
Phones 187 53
DSLR 173 42
Binders 132 5
DSLR 158 59
Phones 185 50
Binders ### 3300.00%
Phones 185 49
Phones 187 53
DSLR 173 42
Binders 132 5
DSLR 158 59
Phones 185 50
Binders 117 33
Phones 185 49
Phones 187 53
DSLR 173 42
Binders 132 5
DSLR 158 59
Phones 185 50
Binders 117 33
Phones 185 49
Phones 187 53
DSLR 173 42
TEXT FUNCTIONS
Louis Litt,New York
LEFT
RIGHT
MID
UPPER
LOWER
PROPER
FIND
SEARCH
SUBSTITUTE
LEN
Firstname lastname,location
Assignment
Name First Name Last Name Location
Louis Litt,New York
Harvey Specter,Alaska
Mike Ross,Arizona
Jessica Pearon,Arkansas
Sean Pual,California
Rachel Zane,Colorado
Robert Smith,Connecticut
Maria Garcia,Colorado
Lookup and Reference Fucntions
VLOOKUP
Q1. How much is the sales belongs to order ID AU-2011-77787? (From Table 1)
Q2. What is the order received date for product ID 5? (From Table 1)
HLOOKUP
Q1. How much is the sales belongs to order ID AU-2011-77787? (From Table 2)
Table 1
Order ID Order Date Product ID Product Sales
UN-2011-86536 8/2/2014 1 Binders 12
AU-2011-88612 7/30/2014 2 DSLR 158
AU-2011-77787 7/29/2014 3 AC 185
SE-2011-86411 8/2/2014 4 LEDTV 117
AU-2011-16465 7/30/2014 5 Phones 185
NE-2011-37144 8/9/2014 6 Iron 187
NE-2011-81382 8/8/2014 7 DSLR 173
AU-2011-77787
AU-2011-77787
Order Date Table 2
8/2/2014 Order ID UN-2011-86536 AU-2011-88612 AU-2011-77787
7/30/2014 Order Date 8/2/2014 7/30/2014 7/29/2014
7/29/2014 Product ID 1 2 3
8/2/2014 Product Binders DSLR AC
7/30/2014 Sales 12 158 185
8/9/2014
8/8/2014
SE-2011-86411 AU-2011-16465 NE-2011-37144 NE-2011-81382
8/2/2014 7/30/2014 8/9/2014 8/8/2014
4 5 6 7
LEDTV Phones Iron DSLR
117 185 187 173
CHARTING AND CUSTOM LABELS
2017
Jan 1828 ▼ SALES
Feb 2334 ▼ 12000 ▲
Mar 6872 ▲ 10000 ▲
Apr 1645 ▼
May 1582 ▼ 8000 ▲ 6872 ▲
Jun 3304 ▼
6000 ▲
Jul 3230 ▼
Aug 9797 ▲ 4000 ▼ 3304▼ 3
Sep 8532 ▲ 2334▼
1828▼ 1645▼ 1582▼
Oct 6789 ▲ 2000 ▼
Nov 3045 ▼
0▼
Dec 7541 ▲ Jan Feb Mar Apr May Jun
SALES
9797 ▲
8532 ▲
7541 ▲
2▲ 6789 ▲
3304▼ 3230▼ 3045▼
1645▼ 1582▼
ar Apr May Jun Jul Aug Sep Oct Nov Dec
Chart type
BUMP CHART
JAN FEB MAR APR MAY JUN
Gold 1,882 1,969 1,750 1,691 1,816 2,444
Iron 1,059 1,789 1,649 1,005 1,083 1,289
Silver 1,026 1,942 1,750 1,238 1,608 1,159
Copper 1,975 1,800 1,756 1,461 1,174 1,190
JAN FEB MAR APR MAY JUN
Gold 2 1 2 1 1 1
Iron 3 4 4 4 4 2
Silver 4 2 2 3 2 4
Copper 1 3 1 2 3 3
STEP CHART
Step Chart is used to show data which remain consistent until the next change.
Fare
1-Jan-17 1050
4-Jan-17 1350
7-Jan-17 1450
10-Jan-17 1200
13-Jan-17 1449
16-Jan-17 1303
19-Jan-17 1093
22-Jan-17 1336
25-Jan-17 1357
28-Jan-17 1046
31-Jan-17 1186
3-Feb-17 1006
6-Feb-17 1146
9-Feb-17 1468
12-Feb-17 1029
PROGRESS CHART
East West North
Sales % 80% 75% 81%
Remaining % 20% 25% 19%
BULLET GRAPH
Electronic Furniture Automobiles
Poor 60% 70% 60%
Fair 15% 10% 15%
Good 15% 10% 15%
Excellent 10% 10% 10%
Actual 68% 88% 73%
Target 90% 85% 92%
Waterfall Chart
Revenue
Start 2000
Jan 720
Feb -575
Mar 46
Apr 938
May 551
Jun -436
Jul -400
Aug -632
Sep 1200
Oct -379
Nov 176
Dec -947
Total 2262
FORM CONTROLS
Order ID Order Date Ship Date Ship Mode Customer Name Product
UN-2014-86536 8/2/2014 8/6/2014 First Class James Smith Phones
AU-2014-88612 7/30/2014 8/5/2014 First Class John Johnson DSLR
AU-2014-77787 7/29/2014 8/2/2014 Second Class Robert Williams Phones
SE-2014-86411 8/2/2014 8/6/2014 Second Class William Jones LEDTV
AU-2014-16465 7/30/2014 7/31/2014 Second Class David Miller Phones
NE-2014-37144 8/9/2014 8/11/2014 First Class Richard Davis AC
NE-2014-81382 8/8/2014 8/10/2014 First Class Charles Garcia DSLR
Value AC
Position 4
Sales Profit Product
12 5 Phones
158 59 DSLR
185 50 LEDTV
117 33 AC
185 49
187 53
173 42
PARETO/DUAL AXIS CHART
The Pareto principle, also known as the 80/20 rule, is a theory maintaining that 80 percent of the output from a given sit
# of Runing Cutoff
# Reasons Runing Total
complaints Total % %
1 Payment Gateway 84 84 40% 80%
2 Product Reviews 60 144 69% 80%
3 Delivery Days 20 164 78% 80%
4 User Interface 16 180 86% 80%
5 Cash on Delivery 9 189 90% 80%
6 Customer Support 8 197 94% 80%
7 Discounts 5 202 97% 80%
8 Product Support 4 206 99% 80%
9 Product Listing 2 208 100% 80%
10 Delivery Support 1 209 100% 80%
# of
Reasons
complaints
Payment Gateway 84
Product Reviews 60
Delivery Days 20
User Interface 16
Cash on Delivery 9
Customer Support 8
Discounts 5
Product Support 4
Product Listing 2
Delivery Support 1
of the output from a given situation or system is determined by 20 percent of the input.
INTERACTIVE CHART WITH COMBOBOX
INDUSTRY
400
350
300
250
200
150
100
50
0
2005 2006 2007 2008 2009 2010 2011 2012
Reference
2005 2006 2007 2008 2009 2010 2011
2 INDUSTRY 106 156 147 197 171 223 267
Data
2005 2006 2007 2008 2009 2010 2011
Lighting & Appliances 56 78 96 92 162 123 120
Industry 106 156 147 197 171 223 267
Transport 56 64 58 93 196 60 92
Agriculture 84 133 94 82 62 170 197
Y
2011 2012 2013 2014 2015
2012 2013 2014 2015
272 299 348 333
2012 2013 2014 2015
119 392 389 273
272 299 348 333
130 350 385 360
92 274 300 323
NAME RANGES
SCATTER PLOT AND BUBBLE CHART
Profit Sales
Customer 1 285 499 CORRELATION BETWEEN SALES AND PROFIT
Customer 2 150 265
Customer 3 91 107 600
Customer 4 234 489
500
Customer 5 176 497
Customer 6 180 400 400
Customer 7 78 301
Customer 8 52 298 300
Customer 9 74 370
Customer 10 204 296 200
Customer 11 95 109
100
Customer 12 21 192
Customer 13 212 305 -
Customer 14 69 299 - 50 100 150 2
Customer 15 5 141
Customer 16 106 467
Customer 17 178 419
Customer 18 11 231
Customer 19 66 348
Customer 20 26 129
Customer 21 240 392
Customer 22 450 479
Customer 23 185 213
Customer 24 181 206
Customer 25 73 120
Customer 26 64 284
Customer 27 102 115
Customer 28 141 238
Customer 29 229 416
Customer 30 44 161
Customer 31 24 150
Customer 32 27 221
Customer 33 191 304
Customer 34 156 243
Customer 35 125 238
Customer 36 28 123
Customer 37 59 194
Customer 38 130 422
Customer 39 77 160
Customer 40 374 374
Customer 41 5 107
Customer 42 257 331
Customer 43 332 475
Customer 44 143 260
Customer 45 112 307
Customer 46 251 336
Customer 47 157 204
Customer 48 84 280
Customer 49 101 226
Customer 50 3 309
ALES AND PROFIT
100 150 200 250 300 350 400 450 500
Budget vs. Actual Chart Template
Data Calculations
Product Budget Actual Var Var % Symbol Var 1 Var 2
Binders 8860 5340 -3520 -40% 👎 👎 40%
DSLR-1 11559 9030 -2529 -22% 👎 👎 22%
AC 5030 7530 2500 50% 👍👍 👍👍 50%
LEDTV 6254 6366 112 2% 👌 👌 2%
Phones 5242 5627 385 7% 👌 👌 7%
Iron 5199 8878 3679 71% 👍👍 👍👍 71%
DSLR-2 4443 8322 3879 87% 👍👍 👍👍 87%
Mobile 11306 5887 -5419 -48% 👎 👎 48%
Bags 3030 5965 2935 97% 👍👍 👍👍 97%
Symbol Mapping
% Symbol
-100% 👎
-5% 🤞
0% 👌
10% 👍
25% 👍👍
Chart
Budget vs. Actual
14000
12000 👎 22% 👎 48%
10000
👎 40% 👍👍 71%
👍👍 87%
8000 👍👍 50%
👌 2% 👍👍 97%
6000 👌 7%
4000
2000
0
Binders DSLR-1 AC LEDTV Phones Iron DSLR-2 Mobile Bags
Budget Actual