| Introduction |
|
1 | (4) |
|
|
|
1 | (1) |
|
Conventions Used in This Book |
|
|
2 | (1) |
|
|
|
2 | (1) |
|
|
|
3 | (1) |
|
|
|
3 | (1) |
|
|
|
4 | (1) |
| Part 1: Building Worksheets |
|
5 | (96) |
|
Chapter 1 Getting Familiar with the Excel Interface |
|
|
7 | (14) |
|
|
|
7 | (2) |
|
Identifying the Parts of the Excel Window |
|
|
9 | (1) |
|
Selecting Commands in the Backstage View |
|
|
10 | (1) |
|
Selecting Commands from the Ribbon |
|
|
11 | (4) |
|
Adding a custom tab to the Excel Ribbon |
|
|
13 | (1) |
|
Adding commands to groups on your custom tab |
|
|
14 | (1) |
|
Selecting Commands on the Quick Access Toolbar |
|
|
15 | (6) |
|
Displaying the Quick Access toolbar |
|
|
16 | (1) |
|
Customizing the Quick Access toolbar |
|
|
16 | (1) |
|
Adding more commands to the Quick Access toolbar |
|
|
17 | (4) |
|
Chapter 2 Entering the Worksheet Data |
|
|
21 | (16) |
|
|
|
21 | (3) |
|
Moving Around the Workbook |
|
|
24 | (4) |
|
Moving within the displayed area |
|
|
24 | (1) |
|
Moving to a new area of the worksheet |
|
|
25 | (2) |
|
Moving to a different sheet in the workbook |
|
|
27 | (1) |
|
|
|
28 | (2) |
|
|
|
30 | (5) |
|
Entering data in a single cell |
|
|
30 | (2) |
|
|
|
32 | (1) |
|
Filling in a data series with the fill handle |
|
|
33 | (1) |
|
Copying a formula with the fill handle |
|
|
34 | (1) |
|
Saving the Spreadsheet Data in a Workbook File |
|
|
35 | (2) |
|
Chapter 3 Formatting the Worksheet |
|
|
37 | (22) |
|
Resizing Columns and Rows |
|
|
37 | (3) |
|
Making column widths suit the data |
|
|
38 | (1) |
|
Manipulating the height of certain rows |
|
|
39 | (1) |
|
Cell Formatting Techniques |
|
|
40 | (16) |
|
Formatting cells with the Ribbon's Home tab |
|
|
41 | (5) |
|
Formatting cells with the Format Cells dialog box |
|
|
46 | (6) |
|
|
|
52 | (1) |
|
Using conditional formatting |
|
|
53 | (3) |
|
|
|
56 | (3) |
|
Chapter 4 Printing Worksheet Reports |
|
|
59 | (20) |
|
Previewing Pages in the Worksheet and Backstage View |
|
|
59 | (2) |
|
|
|
61 | (2) |
|
Adding Headers and Footers |
|
|
63 | (3) |
|
Adding Print Titles to a Report |
|
|
66 | (2) |
|
Modifying the Print Settings for a Report |
|
|
68 | (3) |
|
Printing All or Part of the Workbook |
|
|
71 | (8) |
|
|
|
71 | (1) |
|
Printing the entire workbook |
|
|
72 | (2) |
|
Printing charts in the worksheet |
|
|
74 | (2) |
|
Printing the worksheet formulas |
|
|
76 | (3) |
|
Chapter 5 Modifying the Worksheet |
|
|
79 | (22) |
|
Finding and Identifying the Region That Needs Editing |
|
|
79 | (3) |
|
Selecting the Ranges to Edit |
|
|
82 | (2) |
|
|
|
84 | (2) |
|
Deleting and Inserting Data and Cells |
|
|
86 | (2) |
|
Moving and Copying Data and Cells |
|
|
88 | (4) |
|
Adding Notes to the Worksheet |
|
|
92 | (1) |
|
Using Find and Replace and Spell Checking |
|
|
93 | (4) |
|
Editing Multiple Worksheets |
|
|
97 | (4) |
| Part 2: Using Formulas And Functions |
|
101 | (116) |
|
Chapter 6 Building Formulas |
|
|
103 | (20) |
|
|
|
103 | (15) |
|
Building formulas by hand |
|
|
104 | (6) |
|
Building formulas with built-in functions |
|
|
110 | (4) |
|
|
|
114 | (1) |
|
Altering the natural order of operations |
|
|
115 | (3) |
|
Using External Reference Links |
|
|
118 | (2) |
|
Controlling When Formulas Are Recalculated |
|
|
120 | (3) |
|
Chapter 7 Copying and Correcting Formulas |
|
|
123 | (22) |
|
Copying Formulas with Relative References |
|
|
124 | (2) |
|
Copying Formulas with Absolute References |
|
|
126 | (2) |
|
Copying Formulas with Mixed References |
|
|
128 | (4) |
|
Using Range Names in Formulas |
|
|
132 | (4) |
|
|
|
136 | (4) |
|
Tracing and Eliminating Formula Errors |
|
|
140 | (2) |
|
Dealing with Circular References |
|
|
142 | (3) |
|
Chapter 8 Creating Date and Time Formulas |
|
|
145 | (10) |
|
Constructing Date and Time Formulas |
|
|
145 | (3) |
|
Working with Simple Date Functions |
|
|
148 | (2) |
|
Working with Excel's Fancier Date Functions |
|
|
150 | (2) |
|
Working with the Time Functions |
|
|
152 | (3) |
|
Chapter 9 Financial Formulas and Functions |
|
|
155 | (12) |
|
Working with Financial Functions |
|
|
155 | (1) |
|
Using the Basic Investment Functions |
|
|
156 | (6) |
|
Figuring the Depreciation of an Asset |
|
|
162 | (5) |
|
Chapter 10 Using Math Functions |
|
|
167 | (12) |
|
|
|
167 | (2) |
|
Finding Products, Powers, and Square Roots |
|
|
169 | (3) |
|
|
|
172 | (7) |
|
Summing products, squares, and their differences |
|
|
172 | (3) |
|
|
|
175 | (4) |
|
Chapter 11 Using Common Statistical Functions |
|
|
179 | (8) |
|
|
|
179 | (2) |
|
Finding the Highest and Lowest Values |
|
|
181 | (1) |
|
|
|
182 | (3) |
|
Using the Statistical Functions in Analysis ToolPak Add-in |
|
|
185 | (2) |
|
Chapter 12 Using Lookup Functions |
|
|
187 | (10) |
|
Looking Up Stuff with XLOOKUP |
|
|
188 | (2) |
|
Returning Single Values from a Lookup Table |
|
|
190 | (7) |
|
Performing a horizontal lookup |
|
|
191 | (2) |
|
Performing a vertical lookup |
|
|
193 | (4) |
|
Chapter 13 Using Logical Functions |
|
|
197 | (12) |
|
Working with the Logical Functions |
|
|
197 | (1) |
|
Constructing Decision-Making Formulas |
|
|
198 | (7) |
|
Choosing between alternate values |
|
|
198 | (4) |
|
Selecting between alternate calculations |
|
|
202 | (1) |
|
|
|
203 | (2) |
|
Constructing Error-Trapping Formulas |
|
|
205 | (4) |
|
Chapter 14 Text Formulas and Functions |
|
|
209 | (8) |
|
Constructing Text Formulas |
|
|
209 | (4) |
|
|
|
213 | (4) |
| Part 3: Working With Graphics |
|
217 | (32) |
|
Chapter 15 Charting Worksheet Data |
|
|
219 | (12) |
|
Understanding Excel Charts |
|
|
219 | (5) |
|
|
|
224 | (2) |
|
|
|
226 | (3) |
|
|
|
229 | (2) |
|
Chapter 16 Adding Graphics to Worksheets |
|
|
231 | (18) |
|
Understanding Graphic Objects |
|
|
231 | (4) |
|
Adding Various Types of Graphic Objects |
|
|
235 | (16) |
|
|
|
236 | (1) |
|
|
|
237 | (4) |
|
Adding graphic shapes and text boxes |
|
|
241 | (2) |
|
|
|
243 | (2) |
|
|
|
245 | (4) |
| Part 4: Managing And Securing Data |
|
249 | (34) |
|
Chapter 17 Building and Maintaining Tables |
|
|
251 | (20) |
|
|
|
251 | (6) |
|
Adding records to a new table |
|
|
252 | (3) |
|
Editing records in the data form |
|
|
255 | (2) |
|
|
|
257 | (5) |
|
|
|
258 | (1) |
|
Sorting a table on multiple keys |
|
|
259 | (1) |
|
Sorting the fields (columns) in a table |
|
|
260 | (2) |
|
|
|
262 | (2) |
|
|
|
264 | (3) |
|
Querying External Database Tables |
|
|
267 | (4) |
|
Chapter 18 Protecting Workbooks and Worksheet Data |
|
|
271 | (12) |
|
Password-Protecting the Workbook |
|
|
272 | (2) |
|
|
|
274 | (4) |
|
Doing Data Entry in a Protected Worksheet |
|
|
278 | (2) |
|
Protecting the Entire Workbook |
|
|
280 | (3) |
| Part 5: Doing Data Analysis |
|
283 | (32) |
|
Chapter 19 Performing What-If Analysis |
|
|
285 | (14) |
|
Performing What-If Analysis with Data Tables |
|
|
285 | (7) |
|
Creating a single-variable data table |
|
|
286 | (3) |
|
Creating a two-variable data table |
|
|
289 | (3) |
|
Analyzing Data with Scenarios |
|
|
292 | (2) |
|
Goal Seeking in a Worksheet |
|
|
294 | (2) |
|
Creating Complex Models with Solver |
|
|
296 | (3) |
|
Chapter 20 Generating PivotTables |
|
|
299 | (16) |
|
|
|
299 | (3) |
|
|
|
302 | (2) |
|
|
|
304 | (6) |
|
Modifying the PivotTable formatting |
|
|
305 | (1) |
|
Pivoting the PivotTable's fields |
|
|
306 | (1) |
|
Changing the PivotTable summary function and adding calculated fields |
|
|
307 | (3) |
|
|
|
310 | (5) |
| Part 6: Macros And Visual Basic For Applications |
|
315 | (26) |
|
|
|
317 | (10) |
|
|
|
317 | (6) |
|
Recording and playing back macros |
|
|
318 | (3) |
|
Recording macros with relative cell references |
|
|
321 | (2) |
|
Assigning Macros to the Quick Access Toolbar |
|
|
323 | (1) |
|
Assigning Macros to the Ribbon |
|
|
324 | (3) |
|
Chapter 22 Using the Visual Basic Editor |
|
|
327 | (14) |
|
Using the Visual Basic Editor |
|
|
328 | (6) |
|
|
|
329 | (2) |
|
Adding a dialog box that processes user input |
|
|
331 | (3) |
|
Creating User-Defined Functions |
|
|
334 | (9) |
|
Using a custom function in your worksheet |
|
|
337 | (1) |
|
Saving custom functions in add-in files |
|
|
338 | (3) |
| Part 7: The Part Of Tens |
|
341 | (14) |
|
Chapter 23 Top Ten Features in Excel |
|
|
343 | (4) |
|
The Excel Ribbon and Backstage View |
|
|
343 | (1) |
|
|
|
344 | (1) |
|
PivotTable Filtering with Slicers |
|
|
344 | (1) |
|
Home Tab Formatting and Editing |
|
|
344 | (1) |
|
|
|
345 | (1) |
|
|
|
345 | (1) |
|
The Zoom Slider on the Status Bar |
|
|
345 | (1) |
|
|
|
345 | (1) |
|
|
|
346 | (1) |
|
|
|
346 | (1) |
|
Chapter 24 Top Ten Tips for Using Excel Like a Pro |
|
|
347 | (8) |
|
Generating New Workbooks from Templates |
|
|
347 | (1) |
|
Organizing Data on Different Worksheets |
|
|
348 | (1) |
|
Creating Data Series with AutoFill |
|
|
349 | (1) |
|
|
|
349 | (1) |
|
Freezing Column and Row Headings |
|
|
350 | (1) |
|
Preventing Data Entry Errors with Data Validation |
|
|
350 | (1) |
|
|
|
351 | (1) |
|
Saving Memory by Using Array Formulas |
|
|
352 | (1) |
|
Using Outlines to Control the Display of Data |
|
|
352 | (1) |
|
Using View Side by Side to Work with Two Workbooks |
|
|
353 | (2) |
| Index |
|
355 | |
