Data Formats in Excel

1 What is formatting?

2 Number formatting
3 Custom number formats
4 Conditional formatting
5 Custom conditional formatting

What is that you understand by formatting ?

Changing the way something looks with

dd-mm-yyyy
mm-dd--yyyy Use?
To make our data more prese
30-11-1990
11-30-1990
derstand by formatting ?

the way something looks without changing the actual meaning

To make our data more presentable

 A B C D
1
2
3
5
6
7 Why Formatting?
8 Formats change the appearance of the data to make the data m
9 display can be adjusted the way we want.
10
11

12
Raw Data
13 Employee ID Department Date of Joining
14 00001 Accounts Saturday, February 10, 2018
15 00002 Accounts Sunday, February 11, 2018
16 00003 Accounts Monday, February 12, 2018
17 00004 Admin Tuesday, February 13, 2018
18 00005 Admin ###
19 00006 Admin Thursday, February 15, 2018
20 00007 Admin Friday, February 16, 2018
21 00008 HR Saturday, February 17, 2018
22 00009 HR Sunday, February 18, 2018
23 00010 HR Monday, February 19, 2018
24
25
26
27
28
Python

29

30
31
32
33
34
35
36
 A B C D
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53 This is a long sentence I am trying to type .
54 Really Long one…................ Dummy
55
56 Name of the new cell will the top left cell in th
57
58
59
60 9/26/2022
61
62
63
64
65 ₹ 12,000.22250
66
67 1,000,000,000.00
68
69
70
71 Timezone determines if number is seperated lakhs wise ( indian styl
 E F G H
1
2
3
5
6
7

he data to
8 make the data more readable. Formats never change the underl

want. 9
10
11

12
Formatted Data
13 Salary before Tax Income Tax Employee ID
14 550,555.00 55,055.50 00001
15 809,845.32 80,984.53 00002
16 524,897.00 52,489.70 00003
17 638,137.77 63,813.78 00004
18 562,819.91 56,281.99 00005
19 1,267,118.61 126,711.86 00006
20 1,225,425.04 122,542.50 00007
21 2,673,606.61 267,360.66 00008
22 497,269.00 49,726.90 00009
23 2,030,657.72 203,065.77 00010
24
25
26
27
28

29

30
31
32
33
34
35
36
 E F G H
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
ong sentence
53 I am trying to type .
Long one…................
54 Dummy
55
e new cell 56
will the top left cell in the range
57
58
59
60
61
62
63 7.20E-01
64
65
66
67
68
69
70 ###
seperated71lakhs wise ( indian style) or millions ( international)
 I J K L M
1
2
3
5
6
7

ats never
8 change the underlying values, only

9
10
11

rmatted 12
Data
13
Department Date of JoiningSalary before Tax Income Tax
14 Accounts February 10, 2018 550,555.00 55,055.50
15 Accounts February 11, 2018 809,845.32 80,984.53
16 Accounts February 12, 2018 524,897.00 52,489.70
17 Admin February 13, 2018 638,137.77 63,813.78
18 Admin February 14, 2018 562,819.91 56,281.99
19 Admin February 15, 2018 1,267,118.61 126,711.86
20 Admin February 16, 2018 1,225,425.04 122,542.50
21 HR February 17, 2018 2,673,606.61 267,360.66
22 HR February 18, 2018 497,269.00 49,726.90
23 HR February 19, 2018 2,030,657.72 203,065.77
24
25
26
27
28

29

30
31
32
33
34
35
36
Task 1 - Format the following cell value with 1000's separaters and t
1,809,845.32

Task 2 - Format the following cell value as proper date

2/10/2018

Task 3 - Write the following cell value as percentage

27%

Exercise CNTR + 1

Raw Data
Employee ID Department Date of Joining Salary before Tax
00001 Accounts ### 550,555.00
00002 Accounts Sunday, February 11, 2018 809,845.32
00003 Accounts ### 524,897.00
00004 Admin ### 638,137.77
00005 Admin ### 562,819.91
00006 Admin ### 1,267,118.61
00007 Admin Friday, February 16, 2018 1,225,425.04
00008 HR ### 2,673,606.61
00009 HR Sunday, February 18, 2018 497,269.00
00010 HR ### 2,030,657.72
1000's separaters and two decimal digits

oper date

entage

Formatted Data
Income Tax Employee ID Department Date of Joining
55,055.50 1 Accounts 43141
80,984.53 2 Accounts 43142
52,489.70 3 Accounts 43143
63,813.78 4 Admin 43144
56,281.99 5 Admin 43145
126,711.86 6 Admin 43146
122,542.50 7 Admin 43147
267,360.66 8 HR 43148
49,726.90 9 HR 43149
203,065.77 10 HR 43150
Salary before TaxIncome Tax
550555 55055.5
809845.316693 80984.532
524897 52489.7
638137.767943 63813.777
562819.905397 56281.991
1267118.61319 126711.86
1225425.03855 122542.5
2673606.60816 267360.66
497269.003391 49726.9
2030657.71818 203065.77
Conditional formatting allows to apply the formats on the data ba
display colors, icons, and data bars to one or more cells based on th

HIGHLIGHT CELL RULES

Customer Name Sales Amount Profit

Alvarado Kriz $2,444.0 $103.0
Dennis Holloman $2,165.0 $178.0
Sanchez Bergman $2,100.0 $373.0
Lane Daniels $2,600.0 $328.0
Shepard Witt $1,300.0 $387.0
Dennis Holloman $1,535.0 $248.0
Moon Weien $1,100.0 $211.0
Carter Barreto $1,070.0 $211.0
Lane Daniels $2,700.0 $491.0
Wall Olsen $2,100.0 $422.0
Q ) - Highlight cells in in which the sales amt is less than 1500

DATA BARS

Customer Name Sales Amount Profit

Lane Daniels $2,735.0 $248.0
Alvarado Kriz $2,544.0 $103.0
Moon Weien $2,001.0 -$211.0
Sanchez Bergman $10,000.0 $373.0
Rowe Jackson $1,500.0 $328.0
Carter Barreto $2,100.0 $211.0
Mcconnell Tom $1,602.0 $491.0
Dennis Holloman $2,165.0 $178.0
Wall Olsen $1,500.0 $422.0
Shepard Witt $2,025.0 $387.0
Q ) - Apply the 3D data bar ( gradient) on sales amt and 2D data bar on profit

ICON SETS

Customer Name Sales Amount Profit

Lane Daniels $2,735.0 $248.0
Alvarado Kriz $2,544.0 $103.0
Moon Weien $2,001.0 $211.0
Sanchez Bergman $10,000.0 $373.0
Rowe Jackson $1,500.0 $328.0
Carter Barreto $2,100.0 $211.0
Mcconnell Tom $1,002.0 $491.0
Dennis Holloman $2,165.0 $178.0
Wall Olsen $1,500.0 $422.0
Shepard Witt $1,499.0 $387.0

Q ) -Apply the Pie chart icon set on the Profit column

mats on the data based on certain conditions. We can format the range to
e cells based on the cell value.

TOP / BOTTOM RULES

Customer Name Sales Amount Profit Customer Name

Sanchez Bergman $2,969.0 $373.0 Sanchez Bergman
Rowe Jackson $2,931.0 $328.0 Rowe Jackson
Moon Weien $3,800.0 -$500.0 Moon Weien
Wall Olsen $2,800.0 $422.0 Wall Olsen
Carter Barreto $1,070.0 $211.0 Carter Barreto
Mcconnell Tom $1,602.0 $491.0 Mcconnell Tom
Lane Daniels $3,735.0 $248.0 Lane Daniels
Alvarado Kriz $2,544.0 -$700.0 Alvarado Kriz
Dennis Holloman $2,165.0 $178.0 Dennis Holloman
Shepard Witt $2,025.0 $387.0 Shepard Witt
Q ) - Highlight the bottom 10 % of customers in terms of profit

COLOR SCALES

Customer Name Sales Amount Profit Profit

Lane Daniels $2,735.0 $248.0 $248.0
Alvarado Kriz $2,544.0 $103.0 $103.0
Moon Weien $2,001.0 $211.0 -$211.0
Sanchez Bergman $2,969.0 $373.0 $373.0
Rowe Jackson $2,931.0 $328.0 $328.0
Carter Barreto $1,070.0 $211.0 $211.0
Mcconnell Tom $1,602.0 $491.0 $491.0
Dennis Holloman $3,000.0 $178.0 $178.0
Wall Olsen $2,966.0 $422.0 $422.0
Shepard Witt $2,025.0 $387.0 $387.0
Q ) - Apply a color scale with respect to the sales amount , and let it range from light green to yellow

Sales Groups
>2500 Green Arrow
1500 - 2500 Yellow Arrow
<1500 Red Arrow

What is conditional format

What are its uses ?

e range to

Profit
$248.0
$103.0
$211.0
$373.0 $295.2
$328.0
$211.0
$491.0
$178.0
$422.0
$387.0

10
9
8
7
6
5
4
3
2
1
0
conditional formating
Formatting based on a given condition

e its uses ?