advanced excel 2013
TRANSCRIPT
-
7/25/2019 Advanced Excel 2013
1/243
SMART-TRAIN
-
7/25/2019 Advanced Excel 2013
2/243
-
7/25/2019 Advanced Excel 2013
3/243
2013-
.
2015
-
7/25/2019 Advanced Excel 2013
4/243
-
7/25/2019 Advanced Excel 2013
5/243
2013-
-
7/25/2019 Advanced Excel 2013
6/243
-
7/25/2019 Advanced Excel 2013
7/243
-
7/25/2019 Advanced Excel 2013
8/243
-
7/25/2019 Advanced Excel 2013
9/243
) )
3
3
7
9
7
03
31
61
73
-91
13
-
7/25/2019 Advanced Excel 2013
10/243
-
7/25/2019 Advanced Excel 2013
11/243
FORMULA BASED CODITIONAL FORMATTING
-
7/25/2019 Advanced Excel 2013
12/243
2
1.(
)
Home
Conditional formatting
.
.New Rule
1-1
-
7/25/2019 Advanced Excel 2013
13/243
3
BuildinConditional Formatting
.
:
.
.A1:B10A1
New RuleConditional Formatting
(Use Formula to determine which cells to format13
)
=istext(A1) FalseTrue
format.
1-2
-
7/25/2019 Advanced Excel 2013
14/243
4
() .
B3
=istext(B3)
A1
.
2
.
1-3
-
7/25/2019 Advanced Excel 2013
15/243
5
Weekday()
()Weekday.71
.76
1-4
=OR(WEEKDAY(A1)=7;WEEKDAY(A1)=6)
1-4
-
7/25/2019 Advanced Excel 2013
16/243
6
True()OR
)7)6
.A1true
.
1-5
-
7/25/2019 Advanced Excel 2013
17/243
7
3
""C6
B6
C2:C5
B6:C6
.
B6:C6
:B6
=COUNT($C$2:$C$5)=4
C2:C5True
1-6
-
7/25/2019 Advanced Excel 2013
18/243
8
C2:C5
..B6C6
4
.
1-7
-
7/25/2019 Advanced Excel 2013
19/243
9
A7:F7
.
.
.
=OR(weekday($A2)=6;weekday($A2)=7)
1-8
-
7/25/2019 Advanced Excel 2013
20/243
10
.
1-9
-
7/25/2019 Advanced Excel 2013
21/243
11
-
7/25/2019 Advanced Excel 2013
22/243
12
-
7/25/2019 Advanced Excel 2013
23/243
FORMULA BASED DATA VALIDATION
-
7/25/2019 Advanced Excel 2013
24/243
14
2.
DataData tab.
Validation.
Allow
Buildin
2-1
-
7/25/2019 Advanced Excel 2013
25/243
15
Allow
FormulaAllowCustom.
.
1
2-3A1:A10
AllowCustomData Validation
:Formula
=ISTEXT (A1)
22
-
7/25/2019 Advanced Excel 2013
26/243
16
(
.)
A1.
.
2-3
2
A1:A12
.D1
:
=A1>$D$1
A1
$D$1
.D1
-
7/25/2019 Advanced Excel 2013
27/243
17
3
A1:C20
.()COUNTIF
:
1
:
=COUNTIF($A$1:$C$20;A1)=1
A1
.$A$1:$C20
2-4
-
7/25/2019 Advanced Excel 2013
28/243
18
4
(
)E5
B1:B6
:
=SUM($B$1:$B$6)
-
7/25/2019 Advanced Excel 2013
29/243
19
6
A1:A10
:
=A5>A4
.
2-6
-
7/25/2019 Advanced Excel 2013
30/243
20
2-7
-
7/25/2019 Advanced Excel 2013
31/243
21
-
7/25/2019 Advanced Excel 2013
32/243
22
-
7/25/2019 Advanced Excel 2013
33/243
ADVANCED FILTERING
-
7/25/2019 Advanced Excel 2013
34/243
24
3
.
.
:
)Filter)Data tab.1
)(
2.
Number Filter
Greater
Than OR Equal to
3.
3-1
-
7/25/2019 Advanced Excel 2013
35/243
25
.DataClear Filter
.
.
65( )
() ()
)200000)3
3
(
)200000.
3-2
-
7/25/2019 Advanced Excel 2013
36/243
26
:
1.
.
..2
..3.
.
.
.
1
3
>=3.
-
7/25/2019 Advanced Excel 2013
37/243
27
DataAdvanced
.
.
3-3
-
7/25/2019 Advanced Excel 2013
38/243
28
2
3250000
()
3-5
3-4
-
7/25/2019 Advanced Excel 2013
39/243
29
(
).D5:E6
:
()
().
3
250000
3
.D5:E73-6
()
3-5
-
7/25/2019 Advanced Excel 2013
40/243
30
4
Adams
250000
:.3
)Adams(
)
250000((
)
)Adams
)3()
3-7
3-6
-
7/25/2019 Advanced Excel 2013
41/243
31
(
)
(
)
.
.
3-7
-
7/25/2019 Advanced Excel 2013
42/243
32
.
:
1.
True or False
..2
.3.
1
70%
83
H6:H7:
=G12>0.7*AVERAGE($G$12:$G$136)
.
True.
D1270%
$G$12:$G$136
)
AVERAGE)
.
-
7/25/2019 Advanced Excel 2013
43/243
33
2
.70%Adams
3-9
() adams
.
3-8
-
7/25/2019 Advanced Excel 2013
44/243
34
3-9
-
7/25/2019 Advanced Excel 2013
45/243
35
-
7/25/2019 Advanced Excel 2013
46/243
36
-
7/25/2019 Advanced Excel 2013
47/243
ARRAYS FORMULAS
-
7/25/2019 Advanced Excel 2013
48/243
38
4.
.
14
4-1
-
7/25/2019 Advanced Excel 2013
49/243
39
.Karley brackets
.
-.
4-2
.
D
D2
=B2*C2
6
4-2
-
7/25/2019 Advanced Excel 2013
50/243
40
()
D2:D7.66.
:
.D2:D7.1
:.2
=B2:B7*C2:C7
.3 Enter
.CTRL+SHIFT+ENTER
.:
{=B2:B7*C2:C7}
43
-
7/25/2019 Advanced Excel 2013
51/243
41
.
6
.
B2:B7
.C2:C7
6
.6.
:
..1
2.
.
.
..3
)(
{=SUM (B2:B7*C2:C7)}
Ctrl+Shift+Enter
.
.D
B2:B7
C2:C7
.
SUM.
.
-
7/25/2019 Advanced Excel 2013
52/243
42
.
.
{}
:
{10,0,5,25}
:
=SUM({1,2,3,4}*{5,6,7,8})
RAM
:
{5,12,21,32}
.70SUM
:
()
{}.
Ctrl+Shift+Enter
.
.
-
7/25/2019 Advanced Excel 2013
53/243
43
( , )
:
{40,5,45,12}
:
={40,5,45,12}
.Ctrl+Shift+Enter
)
;
(
:
{12;45;78;50}
:
={12;45;78;50}
.Ctrl+Shift+Enter
-
7/25/2019 Advanced Excel 2013
54/243
44
,( (
) 4.(3;
:
{1,2,3;4,5,6;7,8,9;10,11,12}
:
={1,2,3;4,5,6;7,8,9;10,11,12}
.Ctrl+Shift+Enter
4-4
D3:F9
4-4
-
7/25/2019 Advanced Excel 2013
55/243
45
4-5
K10:M12
.
{=K5:M7}
K10:M12
K5:M7
.K10:M12K5:M7
K10:M12
K5:M7
K5:M7
K10:M12
F9 Ctrl+Shift+Enter
.
45
-
7/25/2019 Advanced Excel 2013
56/243
46
4-6
B1
=LEN(A1)
SUM.
:
{=SUM(LEN(A1:A14))}
LEN
:
.
}55545545445232{:
{=SUM({5;5;5;4;5;5;4;5;4;4;5;2;3;2}))}
4-6
-
7/25/2019 Advanced Excel 2013
57/243
47
(
)
A1:A10:
{=SUM(SMALL(
;{1,2,3}))}
SMALL
.SMALL.
.
25SMALL1
.230
:SUM
{5,0,2}
3
4-7
-
7/25/2019 Advanced Excel 2013
58/243
48
IF
.
1
0
0)1 .(
SUM
.
{=SUM(IF(ISTEXT(A1:D5);1;0))}
:
{=SUM(ISTEXT(A1:D5)*1)}
IF:
TRUE*1=1
FALSE*1=0
48
-
7/25/2019 Advanced Excel 2013
59/243
49
FALSETRUEISTEXT
011
.
C3D3
.
4-9
-
7/25/2019 Advanced Excel 2013
60/243
50
.
D4-10
D17
:
=AVERAGE(D2:D15)
.D
:D
{=AVERAGE(C2:C15B2:B15)}
B2:B15
C2:C15
AVERAGE
.
4-10
-
7/25/2019 Advanced Excel 2013
61/243
51
:
.
(
1500680369
(
.
:
{=SUM(IF(MONTH(B4:B13)=H4;C4:C13;0))}
.H4
MONTH) MONTH
(
IF
() IF
H4MONTH
.C4:C13
SUM.
4-11
-
7/25/2019 Advanced Excel 2013
62/243
52
.4-12
COUNTA.
(A5:A17 Kamal
(
COUNTA
.
1:
Kamal
Kamal
1/3=1.33333
)
1
3
).:
{=SUM(1/COUNTIF(A5:A17;A5:A17))}
COUNTIF
A5:A15)COUNTIF
(
1
.SUM
-
7/25/2019 Advanced Excel 2013
63/243
53
.
FREQUENCY
TRANSPOSE.
FREQUENCY
.
FREQUENCY
:
()
E5D3:D11
" "E610000
" ".2000010000
412
-
7/25/2019 Advanced Excel 2013
64/243
54
:E5:E11
{=FREQUENCY(B:B;D3:D11)}
CTRL+SHIFT+ENTER
4-14 4-13
-
7/25/2019 Advanced Excel 2013
65/243
55
TRANSPOSE
TRANSPOSE
.
4-15
( )A3:I6
()
:
{=TRANSPOSE(A3:I6)}
CTRL+SHIFT+ENTER 4-16
4-15
4-16
-
7/25/2019 Advanced Excel 2013
66/243
56
.
Copy
Paste Special
Transpose..
4-17
-
7/25/2019 Advanced Excel 2013
67/243
57
-
7/25/2019 Advanced Excel 2013
68/243
58
-
7/25/2019 Advanced Excel 2013
69/243
DATABASE FUNCTIONS
-
7/25/2019 Advanced Excel 2013
70/243
60
5.
"D. "Database
:
DAVERAGE
DCOUNT
DCOUNTA
DGET
DMAX
DMIN
DPRODUCT
DSTDEV
DSTDEVP
DSUM
DVAR
DVARP
-
7/25/2019 Advanced Excel 2013
71/243
61
..
1
)
Data
(.
B1:C2DSUM
:. (J6criteria(
=DSUM(data; $F$6;Criteria)
DAVERAGE, DMIN, DMAX
51
-
7/25/2019 Advanced Excel 2013
72/243
62
DGET
DGET.:
.
DGET
A2
B2:D2.
Employees
A1:A2
.Criteria
:B2
=DGET(Employees;B1;Criteria)
DGET.C2:D2
.
5-2
-
7/25/2019 Advanced Excel 2013
73/243
63
DGET
Data1
E3
.
IndexMatchDGET.
Criteria1B2:C3
E3:
=DGET(Data1;D7;criteria1)
5-3
-
7/25/2019 Advanced Excel 2013
74/243
64
-
7/25/2019 Advanced Excel 2013
75/243
65
-
7/25/2019 Advanced Excel 2013
76/243
66
-
7/25/2019 Advanced Excel 2013
77/243
ADVANCED LOOKUP FUNCTIONS
-
7/25/2019 Advanced Excel 2013
78/243
68
6.
VLOOKUP
VLOOKUP
.
.
.
VLOOKUP:
VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])
/:
-
7/25/2019 Advanced Excel 2013
79/243
69
Lookup_value ..
Table_arraycol_index_numrange_lookup.
.TRUE1FALSE0.1
/
TRUErange_lookup table_array VLOOKUP.
range_lookupFALSE
table_array.
range_lookupFALSEVLOOKUPtable_array.
lookup_value
.
#N/A
.
VLOOKUP
B2:.B1
=VLOOKUP(B1;D1:E11;2;FALSE)
(B1 D1:E11/) 2
() FALSE.0
-
7/25/2019 Advanced Excel 2013
80/243
70
VLOOKUP
.
.
( )
.
5000
.5%250050001%
:
=VLOOKUP(J2;$L$1:$M$10;2;1)
L1:M10(J2 $L$1:$M$10)
.
6-1
-
7/25/2019 Advanced Excel 2013
81/243
71
VLOOKUP
VLOOKUP
3
Table1
3
Table2
.:
=VLOOKUP(C2;IF(B2
-
7/25/2019 Advanced Excel 2013
82/243
72
(
)
IF
.
D2
.
MATCH
/.MATCH
A
.C2
Match:
=MATCH(C2;A2:A31;0)
/
01/
-1
6-3
-
7/25/2019 Advanced Excel 2013
83/243
73
:
1
.
(
)
0
Match
MATCH1.
.
.1
6-4
-
7/25/2019 Advanced Excel 2013
84/243
74
Match
C2
A
#NAMATCH
.MATCHIF
"":
=IF(ISERROR(MATCH(C2;A:A;0));" "" ")
.MATCHISERROR
INDEX
INDEX.
C3:C7
)6-5(:
=INDEX(C3:C7;4)
INDEX
.
.
:C3:J7
=INDEX(C3:J7;3;4)
.
-
7/25/2019 Advanced Excel 2013
85/243
75
:
INDEX
C3:J7))6-6
.
.RQP
:
=INDEX($C$3:$J$7;Q2;R2)
.
.
6-5
-
7/25/2019 Advanced Excel 2013
86/243
76
INDEXMATCH
INDEX
MATCH
.
INDEXMATCH
6-7
P2
Q2.
M
MATCH:
=MATCH(P2;M:M;0)
INDEX
MATCH
N.:
=INDEX(N:N;MATCH(P2;M:M;0))
6-6
-
7/25/2019 Advanced Excel 2013
87/243
77
:
VLOOKUP
INDEX
MATCH
VLOOKUP
.INDEXMATCH
INDEXMATCH
.
B1
B2
B8.
6-7
-
7/25/2019 Advanced Excel 2013
88/243
78
(
.(
)
(
:B4MATCH
=MATCH(B1;E2:E14;0)
.E2:E12B1
:B5MATCH
=MATCH(B2;F1:J1;0)
B2
F1:J1.
B4
B5
B1B6INDEX
B2:
=INDEX(F2:J14;B4;B5)
6-8
-
7/25/2019 Advanced Excel 2013
89/243
79
.F2:J14
:B8
=INDEX(F2:J14;MATCH(B1;E2:E14;0);MATCH(B2;F1:J1;0))
INDEXMATCH
.
)6-11(
D1:F12
B1:B2
.
6-9
-
7/25/2019 Advanced Excel 2013
90/243
80
6-12
.:
=INDEX(G1:G12;MATCH(B1&B2;F1:F12;0))
6-11 610
6-12
-
7/25/2019 Advanced Excel 2013
91/243
81
MATCH
.MATCHINDEX
(MATCH(B1&B2;F1:F12;0MATCH
( () )
.F1:F12&
.
()
:
.
=INDEX(F1:F12;MATCH(B1&B2;D1:D12&E1:E12;0))
.Ctrl+Shift+Enter
6-13
-
7/25/2019 Advanced Excel 2013
92/243
82
MATCH
D1:D12&E1:E12
.MATCH
INDEX
(INDEX 6-15
/:
INDEX(reference, row_num, [column_num], [area_num])
6-14
615
-
7/25/2019 Advanced Excel 2013
93/243
83
:INDEX
Reference..Row_num..Column_num..Area_num.Row_num
1.Column_num
2
.
Area_num
INDEX
1.
((A1:B4,D1:E4,G1:H4Reference
Area_num 1
A1:B4
Area_num 2
D1:E4
Area_num3
G1:H4
INDEX
6-16
.
(INDEX.
) :banana, pear, and apple:
=INDEX((banana;pear;apple);MATCH(N5;A1:A6;0);MATCH(N4;A1:J1;0);IF(N
3="banana";1;IF(N3="pear";2;3)))
/INDEX
MATCH)(
IF
1
Banana
2
Pear
3
.Apple
3IFAppleN3
.Apple
-
7/25/2019 Advanced Excel 2013
94/243
84
INDIRECT
.INDIRECT
.
:
=INDIRECT(a2)
(
)
A2
.INDIRCTA2
6-16
-
7/25/2019 Advanced Excel 2013
95/243
85
J2
A2.
Reference text&
INDIRECT
:
=INDIRECT(J4&J3)
B3
6-17
618
-
7/25/2019 Advanced Excel 2013
96/243
86
:
6-19
INDIRECT1
.
INDIRECT1
.B10:B12B9
G6
East
:
=East!G6
6-19
-
7/25/2019 Advanced Excel 2013
97/243
87
:
=INDIRECT(A9&"!"&"g6")
.
50
100
INDIRECT.
6-20
-
7/25/2019 Advanced Excel 2013
98/243
88
2:
INDIRECT
C2
.C2D2
.AZG3:Q3
F3:Q14 Formulas
Defined names(
)
Create From Selection(
)
Left column. ()
6-21
-
7/25/2019 Advanced Excel 2013
99/243
-
7/25/2019 Advanced Excel 2013
100/243
90
=INDIRECT(B3)
.B3INDIRECT
Home
Find and select)
(
Go to special
(
)
.Delete().Blanks
624
-
7/25/2019 Advanced Excel 2013
101/243
91
INDIRECT
INDEXMATCH
INDIRECT
:
.
.
.:
D2:H14Formulas
Create FromDefinbed Names
SelectionLeft Column.
6-25
-
7/25/2019 Advanced Excel 2013
102/243
92
E1:H14FormulasCreate From
Selection
Top Row.
:
=INDIRECT(B1) INDIRECT(B2)
B2B1INDIRECT
(" ")
)B1
)
(B2.(
OFFSET
OFFSET
.
:
OFFSET(reference, rows, cols, [height], [width])
:/OFFSET
Reference..
.#!VALUEOFFSET
Rows.
/
..
Cols
.
(.(
Height ..
Width ..
-
7/25/2019 Advanced Excel 2013
103/243
93
6-26A1
:
=OFFSET(A1;4;0)
A1
0
.
6-26
-
7/25/2019 Advanced Excel 2013
104/243
94
3B1
:4
=SUM(OFFSET(B1;4;0;3;1))
OFFSET
SUM.
OFFSET
A .:D2
=OFFSET(A1;COUNTA(A:A)1;0)
A1
COUNTA(A:A)1
627
-
7/25/2019 Advanced Excel 2013
105/243
95
.0A1A
.
::
=AVERAGE(OFFSET(B1;COUNTA(B:B)1;0;3;1))
OFFSET
:
OFFSET(B1;COUNTA(B:B)1;0;3;1)
B1
COUNTA(B:B)1
0
(B3
)B9
().1
628
-
7/25/2019 Advanced Excel 2013
106/243
96
CHOOSE
CHOOSE
.:
CHOOSE(index_num; value1; [value2], ...)
index_num
.
CHOOSE
254.
:
=CHOOSE(3;B6;C6;D6;E6;F6;G6)
.1193
6-29
-
7/25/2019 Advanced Excel 2013
107/243
97
CHOOSE
6-30
( )K4
.K8
:
=SUM(B6:CHOOSE(K4;B6;C6;D6;E6;F6;G6))
B6
K4
(CHOOSE(K4;B6;C6;D6;E6;F6;G6CHOOSE
( )K4
.
K2
:
=SUM(B6:CHOOSE(MATCH(K4;N3:N14;0);B6;C6;D6;E6;F6;G6))
6-30
-
7/25/2019 Advanced Excel 2013
108/243
98
MATCH.
:
MATCH.
N3:N14
Data Validation
Data ValidationData
Data Validation
List
Allow
))6-32Source
631
-
7/25/2019 Advanced Excel 2013
109/243
99
6-32
-
7/25/2019 Advanced Excel 2013
110/243
100
-
7/25/2019 Advanced Excel 2013
111/243
101
-
7/25/2019 Advanced Excel 2013
112/243
102
-
7/25/2019 Advanced Excel 2013
113/243
ADVANCED CHARTING
-
7/25/2019 Advanced Excel 2013
114/243
104
.7
.
. 2013
.
.
-
InsertCharts-
2013
Recommended Charts
.
Bar Chart7-1
-
7/25/2019 Advanced Excel 2013
115/243
105
+
()Chart Tools
Format
.
7-1
-
7/25/2019 Advanced Excel 2013
116/243
106
7-3
.Total
( Category Axis
/)Xaxis
.
7-2
-
7/25/2019 Advanced Excel 2013
117/243
107
7-4
.
) NWNE(
Legend.
7-3
7-4
-
7/25/2019 Advanced Excel 2013
118/243
-
7/25/2019 Advanced Excel 2013
119/243
109
.
Clustered Column7-7
.
7-6
7-7
-
7/25/2019 Advanced Excel 2013
120/243
110
100%100% Stacked Column Chart )(
100%
.
.
7-8
-
7/25/2019 Advanced Excel 2013
121/243
111
Pie Charts
.
.
:
..
.
.
Pie ChartInsert
7-9
-
7/25/2019 Advanced Excel 2013
122/243
112
)-(
.
.3DRotation
7-10
-
7/25/2019 Advanced Excel 2013
123/243
113
. ......
7-11
-
7/25/2019 Advanced Excel 2013
124/243
114
Pie of Pie Charts( )
.
7-12
Fun Computer games
Computer Hardware
Computer Software
.
7-12
-
7/25/2019 Advanced Excel 2013
125/243
115
Pie ChartInsert Pie of Pie Chart
.
3
:
1-
Format Data Series(Split Series BySeries Option-2
)
:-3
a.Position
.
7-13
-
7/25/2019 Advanced Excel 2013
126/243
116
b.Value
c.Percentage
) ( .
d.
Custom
.
7-14
-
7/25/2019 Advanced Excel 2013
127/243
117
Combo Charts
()
.
2013
Combo Charts
Combo Charts.
.
Combo
Chart-
.
Combo Charts
.
)
(
Combo Chart.
-
7/25/2019 Advanced Excel 2013
128/243
118
Combo Chart ComboInsert
Chart
7-15
7-16
-
7/25/2019 Advanced Excel 2013
129/243
119
)Line Chart )
Bar Chart.
ChangeDesign
Chart Type
.
Axis Titles
+
Axis titles.
7-17
-
7/25/2019 Advanced Excel 2013
130/243
120
.
( )100%
100%..
.
7-18
-
7/25/2019 Advanced Excel 2013
131/243
121
) ).1000
)
.(
:
7-19
-
7/25/2019 Advanced Excel 2013
132/243
122
Format Data Series
Series Options
Gap Width
0.
Axis Options
0
1
7-20
7-21
-
7/25/2019 Advanced Excel 2013
133/243
123
.
.7-23
.
B21
.A26:A28
50%
" " ("No Border"No Fill
)
B21
B26
:
=MIN(B21;100%)/2
B21:MIN
.2100%
.100%MIN
:B27
=50%A26
.
-
7/25/2019 Advanced Excel 2013
134/243
-
7/25/2019 Advanced Excel 2013
135/243
125
AllChartsTemplates.
7-23
7-24
-
7/25/2019 Advanced Excel 2013
136/243
126
Freeze Charts
Copy as picture
.
alculate NowF9
Formulas.
7-25
-
7/25/2019 Advanced Excel 2013
137/243
127
7-28 .Sales
7-26
7-27
-
7/25/2019 Advanced Excel 2013
138/243
128
:3
.-1.0-2
.-3
Select DataDesign
.Select Data SourceHidden and Empty Cells
.Hidden and Empty Cells Settings
7-28
7-29
-
7/25/2019 Advanced Excel 2013
139/243
129
-
7/25/2019 Advanced Excel 2013
140/243
130
-
7/25/2019 Advanced Excel 2013
141/243
PIVOT TABLES AND CHARTS
-
7/25/2019 Advanced Excel 2013
142/243
132
8.
ivot Tables
.
..
.
. .
.
8.
-
7/25/2019 Advanced Excel 2013
143/243
133
.
.
.
Insert Pivot Table
Select a table or range. /
8
-1
-
7/25/2019 Advanced Excel 2013
144/243
134
() :
COLUMNS.
ROWS
.
Values
.
Filters
.
82
-
7/25/2019 Advanced Excel 2013
145/243
135
SalespersonRows
ValuesTotal Cost
.
8-3
-
7/25/2019 Advanced Excel 2013
146/243
136
Summarize
Values By.
Value Field
Settings
.
8-4
-
7/25/2019 Advanced Excel 2013
147/243
137
2:
.
Salesperson
ROWS
Summarize Values By
.
8-5
-
7/25/2019 Advanced Excel 2013
148/243
138
3:
ROWS
. /
8-6
8-7
-
7/25/2019 Advanced Excel 2013
149/243
139
4
Filters.
/
Value
Field Settings
Number Format
8-8
8-9
-
7/25/2019 Advanced Excel 2013
150/243
140
AnalyzeClear
.
.
Analyze
Refresh.
8-
10
-
7/25/2019 Advanced Excel 2013
151/243
141
Design
Pivot Table
Styles
Pivot Table Style Options
.
LayoutDesignLayout
.
8-11
8-12
-
7/25/2019 Advanced Excel 2013
152/243
142
.
..
:
( )Ctrl
.Group
,Group1
Group2,.
8-13
-
7/25/2019 Advanced Excel 2013
153/243
143
.
.
8-14
-
7/25/2019 Advanced Excel 2013
154/243
144
.
Group
2006
2007 8-16
8-15
-
7/25/2019 Advanced Excel 2013
155/243
-
7/25/2019 Advanced Excel 2013
156/243
146
8-18
.
(10 )21-3011-201-10
Score
ValuesStudentROWS
Existing Worksheet
.Create Pivot Table
8-18
-
7/25/2019 Advanced Excel 2013
157/243
147
8-19
8-20
-
7/25/2019 Advanced Excel 2013
158/243
148
10Group
8-211001
/
0
.1-1011-2027
Field Settings
(Show Items with No DataLayout and Print
.(
8-21
-
7/25/2019 Advanced Excel 2013
159/243
149
.
.
.
.
.
8-22 :
.1%120000$
(
SUMIFIF (
:
-
7/25/2019 Advanced Excel 2013
160/243
150
Extended price.
8-22
8-23
-
7/25/2019 Advanced Excel 2013
161/243
151
CalculatedFields, Items & SetsAnalyze
FieldInsert Calculated Field.Name
Formula
:
= IF('Extended Price'>=120000;0.01*'Extended Price';0)
Insert Field
8-24
-
7/25/2019 Advanced Excel 2013
162/243
152
.
.
8-25
-
7/25/2019 Advanced Excel 2013
163/243
153
Analyze
Fields, Items
& Sets
Calculated Item
Create calculated Item
:FormulaQ1
= january+ february+ march
Add.
./
Design
Grand Totals
OFF.
826
-
7/25/2019 Advanced Excel 2013
164/243
154
Slicers
.
SlicerInsert SlicerAnalyze
Slicer( )Slicer
8-27Slicer
.
8-27
-
7/25/2019 Advanced Excel 2013
165/243
155
Timeline
Timeline Analyze.
Insert Timeline.
828
-
7/25/2019 Advanced Excel 2013
166/243
156
8-29
8-30
-
7/25/2019 Advanced Excel 2013
167/243
157
.
:
Analyze
Tools
Pivot Chart.
InsertChartPivot Chart
InsertChartsPivot ChartPivot Chart & Table
8-31
.
Analyze
PivotChart
.
.
8-31
-
7/25/2019 Advanced Excel 2013
168/243
158
:
.
.
.
.
.
-
7/25/2019 Advanced Excel 2013
169/243
-
7/25/2019 Advanced Excel 2013
170/243
160
-
7/25/2019 Advanced Excel 2013
171/243
MACRO
-
7/25/2019 Advanced Excel 2013
172/243
162
9.
.
.
.:
.
.
.
.
.
Developer
)(Customize the ribbonRibbon
Developer
-
7/25/2019 Advanced Excel 2013
173/243
163
:
1.
Record Macro(
)
9-1
9-2
-
7/25/2019 Advanced Excel 2013
174/243
164
.2 Record Macro .
Shortcut key
Store Macro in.
:Store Macro in
This workbook
(
)
xlsm
New Workbook
(
)
.
Personal Workbook
.(
)
( )
..
3.
.4.
Stop the Recording
.status bar/
Macros
.
.1 RibbonCustomize the
ribbon()
New GroupNew Tab.2
..3
-
7/25/2019 Advanced Excel 2013
175/243
165
Button
9-4.1
Insert
()Assign Macro.2
.
9-3
9-4
-
7/25/2019 Advanced Excel 2013
176/243
166
(
)
.
Use Relative Reference
.
(
)
(Data Bar "" Data
Bars(.
1.
Data BarsConditional FormattingHome .
Stop The Recording.
".2
"
9-5
-
7/25/2019 Advanced Excel 2013
177/243
167
ClearConditional Formatting
Rules.()
Buttons.3
.
Buttons
Buttons
)Line Chart(
)Bar Chart.(
:.1
9-6
-
7/25/2019 Advanced Excel 2013
178/243
168
a.Chart ToolsFormatChange Chart
Type()
b.
c.
.2
.Buttons.3
Buttons
Product
sales pearson
.Region
9-7
-
7/25/2019 Advanced Excel 2013
179/243
169
:
:.1
a.) )A
Analyze
b.ClearClear All
c.:
i.
Product
ii.Total Cost
d.
.2.
..3
9-8
-
7/25/2019 Advanced Excel 2013
180/243
170
-
7/25/2019 Advanced Excel 2013
181/243
171
-
7/25/2019 Advanced Excel 2013
182/243
172
-
7/25/2019 Advanced Excel 2013
183/243
CREATING INTERACTIVE WORKSHEETS
-
7/25/2019 Advanced Excel 2013
184/243
174
10.
.
Form Controls
. . .
:
Ribbon
Customize the ribbon
.
-
7/25/2019 Advanced Excel 2013
185/243
175
Form ControlsActive X controls.Form Controls
Active X Controls
VBA .Form Controls.
. .
Format Control ( )
10-2
10-1
-
7/25/2019 Advanced Excel 2013
186/243
176
(Tab)
Control ) )
.
Check Box / .
True .False Check Box
:
.1 developer Insert Check Box.
10-2
-
7/25/2019 Advanced Excel 2013
187/243
177
.2 Format
Control.( )
.3 Control ( )
.4 State )Unchecked (
Linked Cell Check Box
True .False
10-3
10-4
-
7/25/2019 Advanced Excel 2013
188/243
178
Check Box
Check Box
10-5
2012.
2011 2011 . 2011
show 2011 trends
10-5
-
7/25/2019 Advanced Excel 2013
189/243
-
7/25/2019 Advanced Excel 2013
190/243
180
.
.
Option Button
.
.:
DeveloperInsert.1
Option Button.
.2 Control
.Value
10-7
-
7/25/2019 Advanced Excel 2013
191/243
181
10-8
10-9
.
10-8
10-9
-
7/25/2019 Advanced Excel 2013
192/243
182
J8.
21
3.
10-11
10-10
-
7/25/2019 Advanced Excel 2013
193/243
183
.O5K3
=IF($J$8=1;K9;IF($J$8=2;K13;K17))
(1J8 -K9
32J8).
.
.
10-11
-
7/25/2019 Advanced Excel 2013
194/243
184
Combo Box
Combo Box
:
1.
insert
Combo Box
2.
3.
Control
.
2
.
10-12
-
7/25/2019 Advanced Excel 2013
195/243
185
Combo Box
.
10-13
-
7/25/2019 Advanced Excel 2013
196/243
186
Combo Box Control
Input range L7:L14 .
J7.
L1:P2
10-14
M2:
=INDEX(M7:M14;$J$7)
P1 Index
J7
M2
2012 index 2012M7:M14 .
North J7 3 index
M7:M14.
.
10-14
-
7/25/2019 Advanced Excel 2013
197/243
187
Scroll Bar
:Scroll Bar
Scroll barInsert.1
.2
MaximumMinimum valuecontrol.3
Value.Incremental Change
.Scroll Bar
10-15
-
7/25/2019 Advanced Excel 2013
198/243
-
7/25/2019 Advanced Excel 2013
199/243
189
-
7/25/2019 Advanced Excel 2013
200/243
190
-
7/25/2019 Advanced Excel 2013
201/243
DATA ANALYSIS USING WHAT IF? AND SOLVER
-
7/25/2019 Advanced Excel 2013
202/243
192
11.
-
hatIf
nalysis
.
Excel:
.
.
.
32
.
.
Solver.
Solver
.
.Excel
Analysis Pack..
Goal Seek
. .
-
7/25/2019 Advanced Excel 2013
203/243
193
.
.
)11-1(
E5
PMT 1800$
.
:
1.
Data
WhatIf Analysis
)-(
()Goal Seek.2
3.
Set cell:(:
)
E7
-1800$-
To value:
(
)
() .OK
.4 cancelOK
.
11-1
-
7/25/2019 Advanced Excel 2013
204/243
194
.
.Solver
Data Tables
.
.
.
.
C12:C18.
11-2
-
7/25/2019 Advanced Excel 2013
205/243
195
1.
11-3
)C11:D18(
(
PMT
(
.
2.
Data
What-if Analysis
Data Tables
.
3.
)
C12:C18
(
Column Input Cell()
B7.
4..
11-3
-
7/25/2019 Advanced Excel 2013
206/243
196
11-4
11-5
-
7/25/2019 Advanced Excel 2013
207/243
197
.
.
:
.1
.2 ()
.(
)
)
A11
(
).(
3.
What-If Analysis
Data Tables
.
4.
Row Cell Input
Column Input Cell.
11-6
-
7/25/2019 Advanced Excel 2013
208/243
-
7/25/2019 Advanced Excel 2013
209/243
199
:
.
.
.
32 .
11-9
-
7/25/2019 Advanced Excel 2013
210/243
200
.Excel
.
"" : "
."
.
.
.
.
.
.
.().
()
CreateDefined NamesFormulasFrom Selection
Right Column
.
-
7/25/2019 Advanced Excel 2013
211/243
201
11-10
11-11
-
7/25/2019 Advanced Excel 2013
212/243
202
:
Scenario ManagerWhatIf AnalysisData.1.
()Add.2.3 Scenario name
Changing Cells . ) (.Scenario Values
11-12
-
7/25/2019 Advanced Excel 2013
213/243
203
.4 (
(
11-13
11-14
-
7/25/2019 Advanced Excel 2013
214/243
204
.5
DataScenario ManagerShow.
Summary
Scenario Summary
.
11-15
-
7/25/2019 Advanced Excel 2013
215/243
205
.
.
.
11-16
-
7/25/2019 Advanced Excel 2013
216/243
-
7/25/2019 Advanced Excel 2013
217/243
207
Solver
G14.1500
.B2:F13
:
SolverSolverData.1Parameters.
:Set ObjectiveSolver Parameter.2:Value of By Changing Variable Cells
Solve.
11-18
-
7/25/2019 Advanced Excel 2013
218/243
208
Keep Solver Solution
Restore Original Values
.
11-19
-
7/25/2019 Advanced Excel 2013
219/243
209
.
37.25 PC
Restore Original Values.
Solver
Solver Parameter Subject tothe constrains
Add(
(
Cell Reference
.
.
.
-
7/25/2019 Advanced Excel 2013
220/243
-
7/25/2019 Advanced Excel 2013
221/243
211
-
7/25/2019 Advanced Excel 2013
222/243
212
-
7/25/2019 Advanced Excel 2013
223/243
213
-
7/25/2019 Advanced Excel 2013
224/243
ERRORS DETECTION AND CORRECTION
-
7/25/2019 Advanced Excel 2013
225/243
-
7/25/2019 Advanced Excel 2013
226/243
216
.
:
.
:
.
.
:
Ctrl+Shift+Enter
.
:
######DIV/0!
#N/A
#NAME?
#NULL!
#NUM!
#REF!
#VALUE!
.
!#DIV/0
.
.!#DIV/0
0
-
7/25/2019 Advanced Excel 2013
227/243
217
.
D212-1
CIF
:
=IF(C2=0;;(C2B2)/c2)
IFERROR
:
=IFERROR((C2B2)/C2;)
#N/A
.#N/A
.VLOOKUP
12-1
-
7/25/2019 Advanced Excel 2013
228/243
218
?#NAME
:?#NAME
.
#NAME?
Addin
.
!#NULL
.
#NULL!:
=SUM(C8:C14 A10:F10)
!#NUM
:
.
.
#NUM!:
=SQRT(1)
.
.
RATE.
-
7/25/2019 Advanced Excel 2013
229/243
219
!#REF
.
:
.
.
) A1(A2=A11
!#REF
.
!#VALUE
:
#VALUE!
.
Ctrl+Shift+Enter.
Formula Auditing Tools
Formula Tab
:.
Trace Dependents-1
Trace
-
7/25/2019 Advanced Excel 2013
230/243
220
Dependents.
.
.
Trace Precedents-2
.
TraceFormulas
Precedents
.
12-2
-
7/25/2019 Advanced Excel 2013
231/243
221
()Remove Arrows
.Formulas
Error Checking-3
Formulas
:
12-3
12-4
-
7/25/2019 Advanced Excel 2013
232/243
222
a-Error Checking
Error Checking
..
Show Calculation Steps
)
(
.
:12-6
)
Space
D1
).
Error in valueError Checking.
.
12-5
-
7/25/2019 Advanced Excel 2013
233/243
223
12-6
-
7/25/2019 Advanced Excel 2013
234/243
224
b-TraceError
.
c-Circular References
.
12-7
-
7/25/2019 Advanced Excel 2013
235/243
225
4-Evaluate Formula
12-8
12-9
-
7/25/2019 Advanced Excel 2013
236/243
226
5-
Show Fromulas
.
~+Ctrl
Watch Window-6
Watch Window.12-11Watch Window
Add Watch
.
12-10
-
7/25/2019 Advanced Excel 2013
237/243
227
Go To Special
:
Go To SpecialFind & SelectHome
12-12
Formulas
)Number, Text, Logical, and Errors.(
Precedents
Dependents.
12-11
-
7/25/2019 Advanced Excel 2013
238/243
228
INQUIRE
INQUIRE
2013
.
AddInsFileExcel Options
.GOCOM AddinsManage
COM AddinsINQUIRE
12-12
-
7/25/2019 Advanced Excel 2013
239/243
229
INQUIRE
.
12-13
12-14
-
7/25/2019 Advanced Excel 2013
240/243
230
12-1612-15
12-15
12-16
-
7/25/2019 Advanced Excel 2013
241/243
231
-
7/25/2019 Advanced Excel 2013
242/243
232
-
7/25/2019 Advanced Excel 2013
243/243