Introduction |
|
xxiii | |
|
Navigating in Excel and Understanding the Interface |
|
|
1 | (44) |
|
Understanding Excel's Interface |
|
|
2 | (7) |
|
Investigating the Default Bars at the Top of Your Screen |
|
|
3 | (2) |
|
|
5 | (2) |
|
|
7 | (1) |
|
|
8 | (1) |
|
|
9 | (3) |
|
Moving Around in Workbooks |
|
|
9 | (1) |
|
Moving Around in Worksheets |
|
|
10 | (2) |
|
Adding and Editing Data in a Workbook |
|
|
12 | (17) |
|
|
12 | (4) |
|
|
16 | (1) |
|
Managing Rows and Columns |
|
|
17 | (5) |
|
|
22 | (5) |
|
Saving and Managing Workbooks |
|
|
27 | (2) |
|
|
29 | (12) |
|
|
29 | (2) |
|
|
31 | (1) |
|
Selecting Contiguous and Noncontiguous Ranges |
|
|
32 | (4) |
|
Using the Office Clipboard |
|
|
36 | (2) |
|
Using the Mouse to Drag and Drop or Fill Data |
|
|
38 | (3) |
|
|
41 | (1) |
|
|
42 | (1) |
|
|
42 | (3) |
|
Formats, Graphics, and Excel's Templates |
|
|
45 | (52) |
|
Using the Format Cells Dialog Box and the Formatting Toolbar |
|
|
46 | (13) |
|
|
48 | (3) |
|
Using the Different Cell Formats |
|
|
51 | (4) |
|
|
55 | (2) |
|
Applying Cell Borders and Patterns |
|
|
57 | (2) |
|
|
59 | (3) |
|
Creating and Modifying Styles |
|
|
60 | (1) |
|
|
61 | (1) |
|
|
62 | (3) |
|
|
62 | (2) |
|
|
64 | (1) |
|
|
65 | (13) |
|
Understanding the Drawing Toolbar |
|
|
65 | (4) |
|
Adding and Editing Graphics in a Worksheet |
|
|
69 | (6) |
|
Adding and Editing Diagrams and Organization Charts in a Worksheet |
|
|
75 | (3) |
|
Creating and Modifying Charts |
|
|
78 | (14) |
|
|
78 | (5) |
|
|
83 | (3) |
|
|
86 | (2) |
|
Editing the Data Used to Produce Charts |
|
|
88 | (3) |
|
|
91 | (1) |
|
|
92 | (2) |
|
|
92 | (1) |
|
Adding Data Using Templates |
|
|
93 | (1) |
|
|
94 | (1) |
|
|
94 | (1) |
|
|
95 | (2) |
|
Organizing Data, Changing Views, and Printing |
|
|
97 | (34) |
|
|
98 | (7) |
|
Sorting Data, Based on Single and Multiple Columns |
|
|
99 | (3) |
|
|
102 | (3) |
|
|
105 | (8) |
|
Freezing Panes and Splitting Worksheets |
|
|
106 | (1) |
|
Using Multiple Windows to View and Edit Data |
|
|
107 | (3) |
|
|
110 | (2) |
|
Understanding Custom Views |
|
|
112 | (1) |
|
|
113 | (11) |
|
Orienting a Page and Repeating Rows and Columns |
|
|
114 | (3) |
|
Adding Headers and Footers |
|
|
117 | (4) |
|
|
121 | (1) |
|
|
122 | (2) |
|
|
124 | (4) |
|
|
125 | (1) |
|
Printing Multiple Copies and Specific Pages |
|
|
126 | (2) |
|
|
128 | (1) |
|
|
129 | (1) |
|
|
129 | (2) |
|
Editing Tools, Comments, and Hyperlinks |
|
|
131 | (28) |
|
Using Excel's Editing Tools |
|
|
132 | (20) |
|
|
132 | (3) |
|
|
135 | (2) |
|
|
137 | (5) |
|
Using Automated Features to Maintain Accuracy and Consistency |
|
|
142 | (4) |
|
|
146 | (4) |
|
Converting Text to Columns |
|
|
150 | (2) |
|
|
152 | (5) |
|
|
153 | (2) |
|
|
155 | (2) |
|
|
157 | (1) |
|
|
157 | (1) |
|
|
158 | (1) |
|
Formulas and Functions in Excel |
|
|
159 | (34) |
|
Creating Basic Arithmetic Formulas |
|
|
160 | (6) |
|
Simple Arithmetic Using Operators |
|
|
161 | (3) |
|
Simple Addition Using the SUM Function |
|
|
164 | (2) |
|
Working with Statistical Functions |
|
|
166 | (4) |
|
Entering Statistical Functions Directly into Cells |
|
|
166 | (2) |
|
Using the Insert Function Utility |
|
|
168 | (2) |
|
Working with Date and Time Functions |
|
|
170 | (4) |
|
Date and Time Formats in Formulas |
|
|
171 | (1) |
|
Calculating Dates and Times |
|
|
172 | (2) |
|
Working with Financial Functions |
|
|
174 | (6) |
|
|
175 | (3) |
|
|
178 | (2) |
|
Working with Logical Functions |
|
|
180 | (9) |
|
|
181 | (2) |
|
|
183 | (3) |
|
Adding Multiple Nested Conditions and Multiple Return Values in IF Statements |
|
|
186 | (3) |
|
|
189 | (1) |
|
|
190 | (1) |
|
|
191 | (1) |
|
|
191 | (2) |
|
Advanced Functions, Table Arrays, and Database Features |
|
|
193 | (36) |
|
Understanding Cell References |
|
|
194 | (3) |
|
Using Named Ranges, Cells, and Constants |
|
|
197 | (8) |
|
|
198 | (4) |
|
Naming Cells as Bookmarks |
|
|
202 | (2) |
|
|
204 | (1) |
|
Using Lookup and Reference Functions |
|
|
205 | (10) |
|
|
206 | (7) |
|
|
213 | (2) |
|
Using Database Functions and Features |
|
|
215 | (7) |
|
Excel's Database Features |
|
|
215 | (5) |
|
|
220 | (2) |
|
|
222 | (2) |
|
|
222 | (1) |
|
|
223 | (1) |
|
|
223 | (1) |
|
|
224 | (2) |
|
Constructing Array-Entered Formulas |
|
|
224 | (2) |
|
Using Array-Entered Formulas |
|
|
226 | (1) |
|
|
226 | (1) |
|
|
227 | (1) |
|
|
228 | (1) |
|
Advanced Data Organization and Display |
|
|
229 | (44) |
|
|
230 | (6) |
|
Using Outlining When Data Is Already Subtotaled |
|
|
231 | (3) |
|
Creating Subtotals and Outlines at the Same Time |
|
|
234 | (2) |
|
|
236 | (4) |
|
|
240 | (7) |
|
Creating Simple Links to External Data |
|
|
240 | (2) |
|
Using Linked Data in Formulas |
|
|
242 | (2) |
|
Dealing with Broken Links |
|
|
244 | (3) |
|
|
247 | (11) |
|
Manipulating Data Layout with a PivotTable Report |
|
|
248 | (8) |
|
|
256 | (2) |
|
Using Advanced Formatting Techniques |
|
|
258 | (11) |
|
Controlling Data Display with Custom Formats |
|
|
258 | (7) |
|
Controlling Data Display Using Conditional Formatting |
|
|
265 | (4) |
|
|
269 | (1) |
|
|
269 | (1) |
|
|
270 | (3) |
|
Data Analysis and Sharing Workbooks |
|
|
273 | (32) |
|
Troubleshooting Formula Errors |
|
|
274 | (11) |
|
|
276 | (4) |
|
Evaluating and Correcting Formula Errors |
|
|
280 | (5) |
|
Understanding What-If Analysis |
|
|
285 | (9) |
|
Using Goal Seek and Trendlines |
|
|
285 | (3) |
|
Using Scenarios to Compare Predictions |
|
|
288 | (3) |
|
|
291 | (3) |
|
|
294 | (8) |
|
Creating a Shared Workbook |
|
|
296 | (2) |
|
|
298 | (4) |
|
|
302 | (1) |
|
|
302 | (1) |
|
|
303 | (2) |
|
Advanced Template Design and Workbook Security |
|
|
305 | (34) |
|
|
306 | (17) |
|
Understanding Templates and How They Work |
|
|
307 | (1) |
|
Editing Existing Templates |
|
|
308 | (3) |
|
Creating Your Own Templates |
|
|
311 | (10) |
|
Organizing Your Templates |
|
|
321 | (2) |
|
Workbook Security and Protection |
|
|
323 | (13) |
|
The Importance of Workbook Properties |
|
|
324 | (2) |
|
Worksheet and Workbook Protection |
|
|
326 | (5) |
|
|
331 | (3) |
|
|
334 | (2) |
|
|
336 | (1) |
|
|
337 | (1) |
|
|
337 | (2) |
|
|
339 | (32) |
|
Introduction to Converters |
|
|
340 | (1) |
|
|
341 | (15) |
|
Opening Different File Types in Excel |
|
|
341 | (5) |
|
Bringing Data into Excel Using Alternative Methods |
|
|
346 | (10) |
|
|
356 | (11) |
|
Moving and Copying Data Out of a Worksheet |
|
|
357 | (3) |
|
Saving and Editing Excel Files in Different Formats |
|
|
360 | (5) |
|
Importing and Inserting Excel Worksheets into Other File Types |
|
|
365 | (1) |
|
Saving Workbook Structure Using XML |
|
|
366 | (1) |
|
|
367 | (1) |
|
|
368 | (1) |
|
|
369 | (2) |
|
|
371 | (24) |
|
Working with Macros in One Workbook |
|
|
372 | (14) |
|
|
374 | (5) |
|
|
379 | (3) |
|
|
382 | (3) |
|
Assigning Macros to Command Buttons |
|
|
385 | (1) |
|
Working with Macros in All Workbooks |
|
|
386 | (5) |
|
Creating a Personal Macro Workbook to Store Global Macros |
|
|
387 | (2) |
|
Assigning Macros to Toolbars and Menus |
|
|
389 | (2) |
|
|
391 | (1) |
|
|
392 | (1) |
|
|
393 | (2) |
|
Customizing Excel and Setting Defaults |
|
|
395 | (30) |
|
Customizing Toolbars and Menus |
|
|
397 | (10) |
|
Setting Toolbar and Menu Options |
|
|
398 | (3) |
|
Creating New Toolbars and Adding Buttons |
|
|
401 | (3) |
|
Editing Toolbar Buttons and Menu Items |
|
|
404 | (3) |
|
Working with Excel's Options Dialog Box |
|
|
407 | (13) |
|
|
408 | (1) |
|
Excel's Calculation Options |
|
|
409 | (1) |
|
|
410 | (1) |
|
|
411 | (1) |
|
Excel's Transition Options |
|
|
412 | (1) |
|
|
413 | (1) |
|
|
414 | (1) |
|
Excel's International Options |
|
|
415 | (1) |
|
|
416 | (1) |
|
Excel's Error Checking Options |
|
|
417 | (1) |
|
|
418 | (1) |
|
|
419 | (1) |
|
Creating Default Workbooks and Worksheets |
|
|
420 | (3) |
|
Changing Your Default Workbook |
|
|
420 | (2) |
|
Changing Your Default Worksheet |
|
|
422 | (1) |
|
Restoring Excel's Default Workbook and Worksheet |
|
|
422 | (1) |
|
|
423 | (1) |
|
|
423 | (1) |
|
|
424 | (1) |
|
Appendix. Scenario Solutions |
|
|
425 | (18) |
|
|
426 | (1) |
|
Scenario 1, Suggested Steps |
|
|
426 | (1) |
|
Scenario 2, Suggested Steps |
|
|
426 | (1) |
|
Scenario 3, Suggested Steps |
|
|
427 | (1) |
|
|
427 | (2) |
|
Scenario 4, Suggested Steps |
|
|
427 | (1) |
|
Scenario 5, Suggested Steps |
|
|
428 | (1) |
|
Scenario 6, Suggested Steps |
|
|
428 | (1) |
|
|
429 | (2) |
|
Scenario 7, Suggested Steps |
|
|
429 | (1) |
|
Scenario 8, Suggested Steps |
|
|
429 | (1) |
|
Scenario 9, Suggested Steps |
|
|
430 | (1) |
|
|
431 | (1) |
|
Scenario 10, Suggested Steps |
|
|
431 | (1) |
|
Scenario 11, Suggested Steps |
|
|
431 | (1) |
|
|
432 | (1) |
|
Scenario 12, Suggested Steps |
|
|
432 | (1) |
|
Scenario 13, Suggested Steps |
|
|
432 | (1) |
|
Scenario 14, Suggested Steps |
|
|
432 | (1) |
|
|
433 | (1) |
|
Scenario 15, Suggested Steps |
|
|
433 | (1) |
|
Scenario 16, Suggested Steps |
|
|
433 | (1) |
|
Scenario 17, Suggested Steps |
|
|
434 | (1) |
|
|
434 | (2) |
|
Scenario 18, Suggested Steps |
|
|
434 | (1) |
|
Scenario 19, Suggested Steps |
|
|
435 | (1) |
|
Scenario 20, Suggested Steps |
|
|
435 | (1) |
|
|
436 | (2) |
|
Scenario 21, Suggested Steps |
|
|
436 | (1) |
|
Scenario 22, Suggested Steps |
|
|
436 | (1) |
|
Scenario 23, Suggested Steps |
|
|
437 | (1) |
|
|
438 | (1) |
|
Scenario 24, Suggested Steps |
|
|
438 | (1) |
|
Scenario 25, Suggested Steps |
|
|
438 | (1) |
|
|
439 | (1) |
|
Scenario 26, Suggested Steps |
|
|
439 | (1) |
|
Scenario 27, Suggested Steps |
|
|
439 | (1) |
|
Scenario 28, Suggested Steps |
|
|
440 | (1) |
|
|
440 | (1) |
|
Scenario 29, Suggested Steps |
|
|
440 | (1) |
|
Scenario 30, Suggested Steps |
|
|
440 | (1) |
|
|
441 | (2) |
|
Scenario 31, Suggested Steps |
|
|
441 | (1) |
|
Scenario 32, Suggested Steps |
|
|
441 | (2) |
Glossary |
|
443 | (16) |
Index |
|
459 | |