| Introduction |
|
vii | |
| Who this book is for |
|
vii | |
| How this book is organized |
|
viii | |
| Download the practice files |
|
viii | |
| Sidebar: Adapting exercise steps |
|
x | |
| Ebook edition |
|
xi | |
| Get support and give feedback |
|
xi | |
| Errata |
|
xi | |
| We want to hear from you |
|
xii | |
| Stay in touch |
|
xii | |
| Taking a Microsoft Office Specialist exam |
|
xiii | |
| Microsoft Office Specialist certification |
|
xiii | |
| Selecting a certification path |
|
xiii | |
| Test-taking tips |
|
xiv | |
| Certification benefits |
|
xvi | |
| For more information |
|
xvi | |
|
Exams 77-427 and 77-428 Microsoft Excel 2013 Expert |
|
|
|
|
|
1 | (2) |
|
1 Manage and share workbooks |
|
|
3 | (38) |
|
1.1 Manage multiple workbooks |
|
|
3 | (13) |
|
Modifying workbook templates |
|
|
4 | (2) |
|
Managing workbook versions |
|
|
6 | (1) |
|
Copying styles between templates |
|
|
7 | (1) |
|
Sidebar: Merging styles that have the same name |
|
|
8 | (1) |
|
Copying macros between workbooks |
|
|
9 | (1) |
|
Connecting to external data |
|
|
10 | (1) |
|
Sidebar: About the Excel Data Model |
|
|
11 | (1) |
|
Sidebar: Editing formula links |
|
|
12 | (3) |
|
|
|
15 | (1) |
|
1.2 Prepare workbooks for review |
|
|
16 | (14) |
|
|
|
16 | (1) |
|
Sidebar: Setting tracking options |
|
|
16 | (3) |
|
Protecting workbooks for sharing |
|
|
19 | (5) |
|
Sidebar Properties vs. metadata |
|
|
24 | (5) |
|
|
|
29 | (1) |
|
1.3 Manage workbook changes |
|
|
30 | (11) |
|
|
|
30 | (1) |
|
|
|
31 | (1) |
|
|
|
32 | (1) |
|
|
|
33 | (2) |
|
|
|
35 | (2) |
|
Troubleshooting by using tracing |
|
|
37 | (2) |
|
Sidebar: Tracing formulas in separate worksheets |
|
|
39 | (1) |
|
|
|
40 | (1) |
|
|
|
40 | (1) |
|
2 Apply custom formats and layouts |
|
|
41 | (44) |
|
2.1 Apply custom data formats |
|
|
41 | (14) |
|
Creating custom formats (number, time, date) |
|
|
42 | (9) |
|
Using advanced Fill Series options |
|
|
51 | (3) |
|
|
|
54 | (1) |
|
2.2 Apply advanced conditional formatting and filtering |
|
|
55 | (8) |
|
Creating custom conditional formats |
|
|
56 | (2) |
|
Using functions to format cells |
|
|
58 | (2) |
|
Creating advanced filters |
|
|
60 | (1) |
|
Sidebar: Managing conditional formatting rules |
|
|
61 | (2) |
|
|
|
63 | (1) |
|
2.3 Apply custom styles and templates |
|
|
63 | (13) |
|
Creating custom templates |
|
|
63 | (2) |
|
Creating and modifying cell styles |
|
|
65 | (1) |
|
Creating custom color and font formats |
|
|
66 | (2) |
|
|
|
68 | (2) |
|
|
|
70 | (3) |
|
Sidebar: Controlling the tab order of objects |
|
|
73 | (2) |
|
|
|
75 | (1) |
|
2.4 Prepare workbooks for internationalization and accessibility |
|
|
76 | (9) |
|
Modifying worksheets for use with accessibility tools |
|
|
76 | (3) |
|
Displaying data in multiple international formats |
|
|
79 | (1) |
|
Sidebar: Proofing in other languages |
|
|
80 | (3) |
|
Sidebar: Managing multiple options for body and heading fonts |
|
|
83 | (1) |
|
|
|
83 | (1) |
|
|
|
84 | (1) |
|
3 Create advanced formulas |
|
|
85 | (34) |
|
3.1 Apply functions in formulas |
|
|
85 | (11) |
|
|
|
87 | (1) |
|
Using the If, And, and Or functions |
|
|
87 | (1) |
|
Using the SUMIFS, AVERAGEIFS, and COUNTIFS functions |
|
|
88 | (2) |
|
Using financial functions |
|
|
90 | (5) |
|
|
|
95 | (1) |
|
3.2 Look up data by using functions |
|
|
96 | (4) |
|
Using the VLOOKUP and HLOOKUP functions |
|
|
96 | (2) |
|
Using the LOOKUP function |
|
|
98 | (1) |
|
Using the Transpose function |
|
|
98 | (2) |
|
|
|
100 | (1) |
|
3.3 Apply advanced date and time functions |
|
|
100 | (5) |
|
Using the Now and Today functions |
|
|
101 | (1) |
|
Using functions to serialize dates and times |
|
|
102 | (1) |
|
Sidebar: Concatenating text in formulas |
|
|
103 | (2) |
|
|
|
105 | (1) |
|
|
|
105 | (14) |
|
Using what-if analysis tools |
|
|
106 | (1) |
|
Sidebar: Enabling iterative calculations |
|
|
107 | (3) |
|
Sidebar: Using the watch window |
|
|
110 | (1) |
|
Using the Scenario Manager |
|
|
111 | (1) |
|
Sidebar: Merging scenarios |
|
|
112 | (1) |
|
|
|
113 | (4) |
|
|
|
117 | (1) |
|
|
|
117 | (2) |
|
4 Create advanced charts and tables |
|
|
119 | (36) |
|
4.1 Create advanced chart elements |
|
|
119 | (10) |
|
Adding trendlines to charts |
|
|
120 | (2) |
|
Sidebar: Working with other elements |
|
|
122 | (1) |
|
Creating dual-axis charts |
|
|
123 | (4) |
|
Creating custom chart templates |
|
|
127 | (2) |
|
|
|
129 | (1) |
|
4.2 Create and manage PivotTables |
|
|
129 | (19) |
|
|
|
129 | (5) |
|
Modifying field selections and options |
|
|
134 | (3) |
|
|
|
137 | (2) |
|
|
|
139 | (9) |
|
|
|
148 | (1) |
|
4.3 Create and manage PivotCharts |
|
|
148 | (7) |
|
|
|
149 | (1) |
|
Sidebar: Viewing chart animations |
|
|
150 | (1) |
|
Manipulating options in existing PivotCharts |
|
|
150 | (2) |
|
Applying styles to PivotCharts |
|
|
152 | (1) |
|
|
|
153 | (1) |
|
|
|
153 | (2) |
| Index |
|
155 | (12) |
| About the author |
|
167 | (1) |
| Survey page |
|
168 | |
