| Introduction |
|
xxxix | |
|
Part I Getting Started with Excel |
|
|
1 | (208) |
|
Chapter 1 Introducing Excel |
|
|
3 | (24) |
|
Understanding What Excel Is Used For |
|
|
3 | (1) |
|
Understanding Workbooks and Worksheets |
|
|
4 | (1) |
|
Moving around a Worksheet |
|
|
4 | (3) |
|
Navigating with your keyboard |
|
|
7 | (1) |
|
Navigating with your mouse |
|
|
8 | (1) |
|
|
|
9 | (1) |
|
|
|
9 | (2) |
|
|
|
11 | (1) |
|
Types of commands on the Ribbon |
|
|
12 | (1) |
|
Accessing the Ribbon by using your keyboard |
|
|
12 | (3) |
|
|
|
15 | (1) |
|
Customizing Your Quick Access Toolbar |
|
|
16 | (2) |
|
Working with Dialog Boxes |
|
|
18 | (1) |
|
|
|
18 | (1) |
|
Using tabbed dialog boxes |
|
|
19 | (1) |
|
|
|
20 | (1) |
|
Creating Your First Excel Workbook |
|
|
20 | (1) |
|
Getting started on your worksheet |
|
|
20 | (1) |
|
Filling in the month names |
|
|
21 | (1) |
|
|
|
22 | (1) |
|
|
|
23 | (1) |
|
Making your worksheet look a bit fancier |
|
|
23 | (1) |
|
|
|
24 | (1) |
|
|
|
25 | (1) |
|
|
|
25 | (1) |
|
|
|
26 | (1) |
|
Chapter 2 Entering and Editing Worksheet Data |
|
|
27 | (26) |
|
|
|
27 | (1) |
|
|
|
27 | (1) |
|
|
|
28 | (1) |
|
|
|
29 | (1) |
|
|
|
29 | (1) |
|
Entering Text and Values into Your Worksheets |
|
|
30 | (1) |
|
|
|
30 | (1) |
|
|
|
30 | (1) |
|
|
|
31 | (1) |
|
Entering Dates and Times into Your Worksheets |
|
|
31 | (1) |
|
|
|
31 | (1) |
|
|
|
32 | (1) |
|
|
|
32 | (1) |
|
Deleting the contents of a cell |
|
|
32 | (1) |
|
Replacing the contents of a cell |
|
|
33 | (1) |
|
Editing the contents of a cell |
|
|
33 | (2) |
|
Learning some handy data-entry techniques |
|
|
35 | (1) |
|
Automatically moving the selection after entering data |
|
|
35 | (1) |
|
Selecting a range of input cells before entering data |
|
|
36 | (1) |
|
Using Ctrl+Enter to place information into multiple cells simultaneously |
|
|
36 | (1) |
|
|
|
36 | (1) |
|
Entering decimal points automatically |
|
|
36 | (1) |
|
Using AutoFill to enter a series of values |
|
|
37 | (1) |
|
Using AutoComplete to automate data entry |
|
|
37 | (1) |
|
Forcing text to appear on a new line within a cell |
|
|
38 | (1) |
|
Using AutoCorrect for shorthand data entry |
|
|
39 | (1) |
|
Entering numbers with fractions |
|
|
40 | (1) |
|
Using a form for data entry |
|
|
40 | (1) |
|
Entering the current date or time into a cell |
|
|
41 | (1) |
|
Applying Number Formatting |
|
|
42 | (1) |
|
Using automatic number formatting |
|
|
43 | (1) |
|
Formatting numbers by using the Ribbon |
|
|
43 | (1) |
|
Using shortcut keys to format numbers |
|
|
43 | (2) |
|
Formatting numbers by using the Format Cells dialog box |
|
|
45 | (2) |
|
Adding your own custom number formats |
|
|
47 | (1) |
|
|
|
47 | (1) |
|
Exploring Excel's tablet interface |
|
|
48 | (1) |
|
Entering formulas on a tablet |
|
|
49 | (1) |
|
Introducing the Draw Ribbon |
|
|
49 | (4) |
|
Chapter 3 Performing Basic Worksheet Operations |
|
|
53 | (20) |
|
Learning the Fundamentals of Excel Worksheets |
|
|
53 | (1) |
|
Working with Excel windows |
|
|
53 | (1) |
|
Moving and resizing windows |
|
|
54 | (1) |
|
|
|
55 | (1) |
|
|
|
55 | (1) |
|
|
|
56 | (1) |
|
Adding a new worksheet to your workbook |
|
|
56 | (1) |
|
Deleting a worksheet you no longer need |
|
|
57 | (1) |
|
Changing the name of a worksheet |
|
|
57 | (1) |
|
Changing a sheet tab color |
|
|
58 | (1) |
|
Rearranging your worksheets |
|
|
58 | (2) |
|
Hiding and unhiding a worksheet |
|
|
60 | (1) |
|
Controlling the Worksheet View |
|
|
60 | (1) |
|
Zooming in or out for a better view |
|
|
60 | (1) |
|
Viewing a worksheet in multiple windows |
|
|
61 | (1) |
|
Comparing sheets side by side |
|
|
62 | (1) |
|
Splitting the worksheet window into panes |
|
|
63 | (1) |
|
Keeping the titles in view by freezing panes |
|
|
63 | (2) |
|
Monitoring cells with a Watch Window |
|
|
65 | (1) |
|
Working with Rows and Columns |
|
|
66 | (1) |
|
Selecting rows and columns |
|
|
66 | (1) |
|
Inserting rows and columns |
|
|
66 | (2) |
|
Deleting rows and columns |
|
|
68 | (1) |
|
Changing column widths and row heights |
|
|
68 | (1) |
|
|
|
69 | (1) |
|
|
|
69 | (1) |
|
|
|
70 | (3) |
|
Chapter 4 Working with Excel Ranges and Tables |
|
|
73 | (48) |
|
Understanding Cells and Ranges |
|
|
73 | (1) |
|
|
|
74 | (1) |
|
Selecting complete rows and columns |
|
|
75 | (1) |
|
Selecting noncontiguous ranges |
|
|
75 | (1) |
|
Selecting multi-sheet ranges |
|
|
76 | (3) |
|
Selecting special types of cells |
|
|
79 | (2) |
|
Selecting cells by searching |
|
|
81 | (2) |
|
|
|
83 | (1) |
|
Copying by using Ribbon commands |
|
|
84 | (1) |
|
Copying by using shortcut menu commands |
|
|
85 | (1) |
|
Copying by using shortcut keys |
|
|
85 | (2) |
|
Copying or moving by using drag-and-drop |
|
|
87 | (1) |
|
Copying to adjacent cells |
|
|
88 | (1) |
|
Copying a range to other sheets |
|
|
89 | (1) |
|
Using the Office Clipboard to paste |
|
|
89 | (2) |
|
|
|
91 | (1) |
|
Using the Paste Special dialog box |
|
|
92 | (2) |
|
Performing mathematical operations without formulas |
|
|
94 | (1) |
|
Skipping blanks when pasting |
|
|
94 | (1) |
|
|
|
94 | (1) |
|
Using Names to Work with Ranges |
|
|
95 | (1) |
|
Creating range names in your workbooks |
|
|
96 | (1) |
|
|
|
96 | (1) |
|
Using the New Name dialog box |
|
|
96 | (1) |
|
Using the Create Names from Selection dialog box |
|
|
97 | (2) |
|
|
|
99 | (1) |
|
|
|
100 | (1) |
|
|
|
101 | (1) |
|
|
|
102 | (1) |
|
Editing comments and replies |
|
|
102 | (1) |
|
Deleting comments and replies |
|
|
103 | (1) |
|
Resolving comment threads |
|
|
103 | (1) |
|
|
|
104 | (1) |
|
|
|
105 | (1) |
|
|
|
106 | (2) |
|
|
|
108 | (1) |
|
|
|
108 | (1) |
|
|
|
108 | (1) |
|
Understanding a table's structure |
|
|
108 | (1) |
|
|
|
109 | (1) |
|
|
|
109 | (1) |
|
|
|
109 | (1) |
|
|
|
110 | (1) |
|
|
|
110 | (1) |
|
|
|
111 | (1) |
|
Sorting and filtering table data |
|
|
111 | (1) |
|
|
|
112 | (2) |
|
|
|
114 | (2) |
|
Filtering a table with slicers |
|
|
116 | (1) |
|
Changing the table's appearance |
|
|
117 | (4) |
|
Chapter 5 Formatting Worksheets |
|
|
121 | (36) |
|
Getting to Know the Formatting Tools |
|
|
121 | (1) |
|
Using the formatting tools on the Home tab |
|
|
122 | (1) |
|
|
|
123 | (1) |
|
Using the Format Cells dialog box |
|
|
124 | (1) |
|
Formatting Your Worksheet |
|
|
124 | (1) |
|
Using fonts to format your worksheet |
|
|
124 | (3) |
|
|
|
127 | (1) |
|
Choosing horizontal alignment options |
|
|
127 | (2) |
|
Choosing vertical alignment options |
|
|
129 | (1) |
|
Wrapping or shrinking text to fit the cell |
|
|
129 | (1) |
|
Merging worksheet cells to create additional text space |
|
|
129 | (2) |
|
Displaying text at an angle |
|
|
131 | (1) |
|
|
|
131 | (1) |
|
|
|
132 | (3) |
|
Using Conditional Formatting |
|
|
135 | (1) |
|
Specifying conditional formatting |
|
|
135 | (1) |
|
Using graphical conditional formats |
|
|
135 | (1) |
|
|
|
135 | (2) |
|
|
|
137 | (1) |
|
|
|
138 | (1) |
|
Creating formula-based rules |
|
|
139 | (2) |
|
Understanding relative and absolute references |
|
|
141 | (1) |
|
Conditional formatting formula examples |
|
|
142 | (1) |
|
|
|
142 | (1) |
|
Highlighting a row based on a value |
|
|
142 | (1) |
|
Displaying alternate-row shading |
|
|
143 | (1) |
|
Creating checkerboard shading |
|
|
144 | (1) |
|
|
|
144 | (1) |
|
Working with conditional formats |
|
|
144 | (1) |
|
|
|
145 | (1) |
|
Copying cells that contain conditional formatting |
|
|
145 | (1) |
|
Deleting conditional formatting |
|
|
146 | (1) |
|
Locating cells that contain conditional formatting |
|
|
146 | (1) |
|
Using Named Styles for Easier Formatting |
|
|
146 | (1) |
|
|
|
147 | (1) |
|
Modifying an existing style |
|
|
147 | (2) |
|
|
|
149 | (1) |
|
Merging styles from other workbooks |
|
|
150 | (1) |
|
Controlling styles with templates |
|
|
150 | (1) |
|
Understanding Document Themes |
|
|
150 | (2) |
|
|
|
152 | (1) |
|
|
|
153 | (4) |
|
Chapter 6 Understanding Excel Files and Templates |
|
|
157 | (20) |
|
|
|
157 | (1) |
|
Opening an Existing Workbook |
|
|
158 | (2) |
|
|
|
160 | (1) |
|
Choosing your file display preferences |
|
|
161 | (1) |
|
|
|
161 | (2) |
|
|
|
163 | (1) |
|
Recovering versions of the current workbook |
|
|
164 | (1) |
|
|
|
164 | (1) |
|
|
|
165 | (1) |
|
Password-Protecting a Workbook |
|
|
165 | (1) |
|
|
|
166 | (1) |
|
Other Workbook Info Options |
|
|
166 | (1) |
|
|
|
166 | (1) |
|
|
|
167 | (1) |
|
|
|
167 | (1) |
|
|
|
167 | (1) |
|
|
|
168 | (1) |
|
Compatibility Mode section |
|
|
168 | (1) |
|
|
|
168 | (1) |
|
|
|
168 | (1) |
|
|
|
169 | (1) |
|
Exploring Excel templates |
|
|
169 | (1) |
|
|
|
169 | (1) |
|
Creating a workbook from a template |
|
|
170 | (2) |
|
|
|
172 | (1) |
|
|
|
172 | (1) |
|
Using the workbook template to change workbook defaults |
|
|
173 | (1) |
|
Creating a worksheet template |
|
|
174 | (1) |
|
|
|
174 | (1) |
|
Resetting the default workbook |
|
|
174 | (1) |
|
Using custom workbook templates |
|
|
174 | (1) |
|
Creating custom templates |
|
|
174 | (1) |
|
Saving your custom templates |
|
|
175 | (1) |
|
|
|
176 | (1) |
|
Chapter 7 Printing Your Work |
|
|
177 | (22) |
|
|
|
177 | (2) |
|
|
|
179 | (1) |
|
|
|
179 | (1) |
|
|
|
180 | (1) |
|
|
|
181 | (2) |
|
Adjusting Common Page Setup Settings |
|
|
183 | (1) |
|
|
|
184 | (1) |
|
Specifying what you want to print |
|
|
184 | (1) |
|
Changing page orientation |
|
|
185 | (1) |
|
|
|
185 | (1) |
|
Printing multiple copies of a report |
|
|
186 | (1) |
|
Adjusting the page margins |
|
|
186 | (1) |
|
Understanding page breaks |
|
|
187 | (1) |
|
|
|
187 | (1) |
|
Removing manual page breaks |
|
|
188 | (1) |
|
Printing row and column titles |
|
|
188 | (1) |
|
|
|
189 | (1) |
|
|
|
189 | (1) |
|
Printing row and column headers |
|
|
190 | (1) |
|
|
|
190 | (2) |
|
Adding a Header or a Footer to Your Reports |
|
|
192 | (1) |
|
Selecting a predefined header or footer |
|
|
192 | (1) |
|
Understanding header and footer element codes |
|
|
192 | (2) |
|
Exploring other header and footer options |
|
|
194 | (1) |
|
Exploring Other Print-Related Topics |
|
|
194 | (1) |
|
Copying Page Setup settings across sheets |
|
|
195 | (1) |
|
Preventing certain cells from being printed |
|
|
195 | (1) |
|
Preventing objects from being printed |
|
|
196 | (1) |
|
Creating custom views of your worksheet |
|
|
197 | (1) |
|
|
|
198 | (1) |
|
Chapter 8 Customizing the Excel User Interface |
|
|
199 | (10) |
|
Customizing the Quick Access Toolbar |
|
|
199 | (1) |
|
About the Quick Access Toolbar |
|
|
200 | (1) |
|
Adding new commands to the Quick Access Toolbar |
|
|
201 | (2) |
|
Other Quick Access Toolbar actions |
|
|
203 | (1) |
|
|
|
204 | (1) |
|
Why you may want to customize the Ribbon |
|
|
205 | (1) |
|
|
|
205 | (1) |
|
How to customize the Ribbon |
|
|
205 | (1) |
|
|
|
206 | (1) |
|
|
|
206 | (1) |
|
Adding commands to a new group |
|
|
207 | (1) |
|
|
|
208 | (1) |
|
Part II Working with Formulas and Functions |
|
|
209 | (190) |
|
Chapter 9 Introducing Formulas and Functions |
|
|
211 | (36) |
|
Understanding Formula Basics |
|
|
211 | (2) |
|
Using operators in formulas |
|
|
213 | (1) |
|
Understanding operator precedence in formulas |
|
|
214 | (2) |
|
Using functions in your formulas |
|
|
216 | (1) |
|
Examples of formulas that use functions |
|
|
216 | (1) |
|
|
|
217 | (1) |
|
|
|
218 | (1) |
|
Entering Formulas into Your Worksheets |
|
|
218 | (2) |
|
Entering formulas by pointing |
|
|
220 | (1) |
|
Pasting range names into formulas |
|
|
220 | (1) |
|
Inserting functions into formulas |
|
|
221 | (2) |
|
|
|
223 | (1) |
|
|
|
224 | (1) |
|
Using Cell References in Formulas |
|
|
225 | (1) |
|
Using relative, absolute, and mixed references |
|
|
225 | (2) |
|
Changing the types of your references |
|
|
227 | (1) |
|
Referencing cells outside the worksheet |
|
|
228 | (1) |
|
Referencing cells in other worksheets |
|
|
228 | (1) |
|
Referencing cells in other workbooks |
|
|
228 | (1) |
|
Introducing Formula Variables |
|
|
229 | (1) |
|
Understanding the LET function |
|
|
230 | (1) |
|
Formula variables in action |
|
|
231 | (1) |
|
|
|
232 | (1) |
|
Summarizing data in a table |
|
|
232 | (2) |
|
Using formulas within a table |
|
|
234 | (1) |
|
Referencing data in a table |
|
|
235 | (2) |
|
Correcting Common Formula Errors |
|
|
237 | (1) |
|
Handling circular references |
|
|
238 | (1) |
|
Specifying when formulas are calculated |
|
|
238 | (1) |
|
Using Advanced Naming Techniques |
|
|
239 | (1) |
|
Using names for constants |
|
|
240 | (1) |
|
|
|
240 | (1) |
|
Using range intersections |
|
|
241 | (2) |
|
Applying names to existing references |
|
|
243 | (1) |
|
|
|
244 | (1) |
|
|
|
244 | (1) |
|
Using the Formula bar as a calculator |
|
|
244 | (1) |
|
Making an exact copy of a formula |
|
|
244 | (1) |
|
Converting formulas to values |
|
|
245 | (2) |
|
Chapter 10 Understanding and Using Array Formulas |
|
|
247 | (24) |
|
Understanding Legacy Array Formulas |
|
|
248 | (1) |
|
Example of a legacy array formula |
|
|
248 | (1) |
|
Editing legacy array formulas |
|
|
249 | (1) |
|
Introducing Dynamic Arrays |
|
|
250 | (2) |
|
Understanding spill ranges |
|
|
252 | (2) |
|
|
|
254 | (1) |
|
Exploring Dynamic Array Functions |
|
|
255 | (1) |
|
|
|
256 | (1) |
|
|
|
257 | (1) |
|
|
|
258 | (1) |
|
|
|
259 | (1) |
|
|
|
260 | (2) |
|
|
|
262 | (1) |
|
Using multiple conditions with the FILTER function |
|
|
263 | (1) |
|
Filtering records that contain a search term |
|
|
264 | (1) |
|
|
|
265 | (3) |
|
|
|
268 | (3) |
|
Chapter 11 Using Formulas for Common Mathematical Operations |
|
|
271 | (14) |
|
|
|
271 | (1) |
|
Calculating percent of goal |
|
|
271 | (1) |
|
Calculating percent variance |
|
|
272 | (1) |
|
Calculating percent variance with negative values |
|
|
273 | (1) |
|
Calculating a percent distribution |
|
|
274 | (1) |
|
Calculating a running total |
|
|
275 | (1) |
|
Applying a percent increase or decrease to values |
|
|
276 | (1) |
|
Dealing with divide-by-zero errors |
|
|
277 | (1) |
|
|
|
278 | (1) |
|
Rounding numbers using formulas |
|
|
279 | (1) |
|
Rounding to the nearest penny |
|
|
279 | (1) |
|
Rounding to significant digits |
|
|
280 | (2) |
|
Counting Values in a Range |
|
|
282 | (1) |
|
Using Excel's Conversion Functions |
|
|
283 | (2) |
|
Chapter 12 Using Formulas to Manipulate Text |
|
|
285 | (16) |
|
|
|
285 | (1) |
|
|
|
286 | (1) |
|
|
|
286 | (2) |
|
Setting text to sentence case |
|
|
288 | (1) |
|
Removing spaces from a text string |
|
|
289 | (1) |
|
Extracting parts of a text string |
|
|
290 | (1) |
|
Finding a particular character in a text string |
|
|
291 | (1) |
|
Finding the second instance of a character |
|
|
292 | (1) |
|
Substituting text strings |
|
|
293 | (1) |
|
Counting specific characters in a cell |
|
|
294 | (1) |
|
Adding a line break within a formula |
|
|
295 | (1) |
|
Cleaning strange characters from text fields |
|
|
296 | (1) |
|
Padding numbers with zeros |
|
|
297 | (1) |
|
Formatting the numbers in a text string |
|
|
297 | (2) |
|
Using the DOLLAR function |
|
|
299 | (2) |
|
Chapter 13 Using Formulas with Dates and Times |
|
|
301 | (28) |
|
Understanding How Excel Handles Dates and Times |
|
|
301 | (1) |
|
Understanding date serial numbers |
|
|
301 | (1) |
|
|
|
302 | (1) |
|
Understanding time serial numbers |
|
|
303 | (1) |
|
|
|
304 | (1) |
|
Formatting dates and times |
|
|
305 | (1) |
|
|
|
306 | (1) |
|
|
|
306 | (1) |
|
|
|
306 | (1) |
|
Inconsistent date entries |
|
|
307 | (1) |
|
Using Excel's Date and Time Functions |
|
|
307 | (1) |
|
Getting the current date and time |
|
|
308 | (1) |
|
|
|
308 | (1) |
|
Calculating the number of days between two dates |
|
|
309 | (1) |
|
Calculating the number of workdays between two dates |
|
|
310 | (1) |
|
|
|
310 | (1) |
|
Generating a list of business days excluding holidays |
|
|
311 | (2) |
|
Extracting parts of a date |
|
|
313 | (1) |
|
Calculating number of years and months between dates |
|
|
314 | (1) |
|
Converting dates to Julian date formats |
|
|
315 | (1) |
|
Calculating the percent of year completed and remaining |
|
|
316 | (1) |
|
Returning the last date of a given month |
|
|
317 | (1) |
|
Using the EOMONTH function |
|
|
318 | (1) |
|
Calculating the calendar quarter for a date |
|
|
318 | (1) |
|
Calculating the fiscal quarter for a date |
|
|
319 | (1) |
|
Returning a fiscal month from a date |
|
|
320 | (1) |
|
Calculating the date of the Nth weekday of the month |
|
|
321 | (1) |
|
Calculating the date of the last weekday of the month |
|
|
322 | (1) |
|
Extracting parts of a time |
|
|
323 | (1) |
|
|
|
324 | (1) |
|
|
|
325 | (1) |
|
Converting decimal hours, minutes, or seconds to a time |
|
|
326 | (1) |
|
Adding hours, minutes, or seconds to a time |
|
|
326 | (3) |
|
Chapter 14 Using Formulas for Conditional Analysis |
|
|
329 | (18) |
|
Understanding Conditional Analysis |
|
|
329 | (1) |
|
Checking if a simple condition is met |
|
|
329 | (1) |
|
Checking for multiple conditions |
|
|
330 | (1) |
|
Validating conditional data |
|
|
331 | (1) |
|
|
|
332 | (1) |
|
Checking if Condition1 AND Condition2 are met |
|
|
333 | (1) |
|
Referring to logical conditions in cells |
|
|
334 | (1) |
|
Checking if Condition1 OR Condition2 are met |
|
|
335 | (1) |
|
Performing Conditional Calculations |
|
|
336 | (1) |
|
Summing all values that meet a certain condition |
|
|
336 | (2) |
|
Summing greater than zero |
|
|
338 | (1) |
|
Summing all values that meet two or more conditions |
|
|
339 | (1) |
|
Summing if values fall between a given date range |
|
|
340 | (1) |
|
|
|
341 | (1) |
|
Getting a count of values that meet a certain condition |
|
|
341 | (1) |
|
Getting a count of values that meet two or more conditions |
|
|
342 | (1) |
|
Finding nonstandard characters |
|
|
343 | (1) |
|
Getting the average of all numbers that meet a certain condition |
|
|
344 | (1) |
|
Getting the average of all numbers that meet two or more conditions |
|
|
344 | (3) |
|
Chapter 15 Using Formulas for Matching and Lookups |
|
|
347 | (18) |
|
Introducing Lookup Formulas |
|
|
347 | (1) |
|
Leveraging Excel's Lookup Functions |
|
|
348 | (1) |
|
Looking up an exact value based on a left lookup column |
|
|
348 | (3) |
|
Looking up an exact value based on any lookup column |
|
|
351 | (1) |
|
Looking up values horizontally |
|
|
352 | (1) |
|
Hiding errors returned by lookup functions |
|
|
353 | (1) |
|
Finding the closest match from a list of banded values |
|
|
354 | (2) |
|
Finding the closest match with the INDEX and MATCH functions |
|
|
356 | (1) |
|
Looking up values from multiple tables |
|
|
357 | (2) |
|
Looking up a value based on a two-way matrix |
|
|
359 | (1) |
|
Using default values for match |
|
|
360 | (1) |
|
Finding a value based on multiple criteria |
|
|
361 | (1) |
|
Returning text with SUMPRODUCT |
|
|
362 | (1) |
|
Finding the last value in a column |
|
|
362 | (1) |
|
Finding the last number using LOOKUP |
|
|
363 | (2) |
|
Chapter 16 Using Formulas with Tables and Conditional Formatting |
|
|
365 | (14) |
|
Highlighting Cells That Meet Certain Criteria |
|
|
365 | (2) |
|
Highlighting cells based on the value of another cell |
|
|
367 | (2) |
|
Highlighting Values That Exist in List1 but Not List2 |
|
|
369 | (2) |
|
Highlighting Values That Exist in List1 and List2 |
|
|
371 | (1) |
|
Highlighting Based on Dates |
|
|
372 | (2) |
|
Highlighting days between two dates |
|
|
374 | (2) |
|
Highlighting dates based on a due date |
|
|
376 | (3) |
|
Chapter 17 Making Your Formulas Error-Free |
|
|
379 | (20) |
|
Finding and Correcting Formula Errors |
|
|
379 | (1) |
|
|
|
380 | (1) |
|
Cells are filled with hash marks |
|
|
381 | (1) |
|
Blank cells are not blank |
|
|
381 | (1) |
|
|
|
382 | (1) |
|
Formulas returning an error |
|
|
382 | (1) |
|
|
|
383 | (1) |
|
|
|
383 | (1) |
|
|
|
384 | (1) |
|
|
|
384 | (1) |
|
|
|
384 | (1) |
|
|
|
385 | (1) |
|
|
|
385 | (1) |
|
|
|
386 | (1) |
|
Operator precedence problems |
|
|
386 | (1) |
|
Formulas are not calculated |
|
|
387 | (1) |
|
Problems with decimal precision |
|
|
387 | (1) |
|
|
|
388 | (1) |
|
Using Excel Auditing Tools |
|
|
388 | (1) |
|
Identifying cells of a particular type |
|
|
388 | (1) |
|
|
|
389 | (1) |
|
Tracing cell relationships |
|
|
389 | (1) |
|
|
|
390 | (1) |
|
|
|
390 | (1) |
|
|
|
391 | (1) |
|
Fixing circular reference errors |
|
|
391 | (1) |
|
Using the background error-checking feature |
|
|
391 | (1) |
|
|
|
392 | (1) |
|
|
|
393 | (1) |
|
Searching for information |
|
|
393 | (1) |
|
|
|
394 | (1) |
|
|
|
395 | (1) |
|
Spell-checking your worksheets |
|
|
396 | (1) |
|
|
|
396 | (3) |
|
Part III Creating Charts and Other Visualizations |
|
|
399 | (120) |
|
Chapter 18 Getting Started with Excel Charts |
|
|
401 | (40) |
|
|
|
401 | (1) |
|
|
|
402 | (1) |
|
|
|
403 | (1) |
|
|
|
404 | (1) |
|
|
|
405 | (3) |
|
|
|
408 | (1) |
|
Basic Steps for Creating a Chart |
|
|
408 | (1) |
|
|
|
408 | (2) |
|
Switching the row and column orientation |
|
|
410 | (1) |
|
|
|
410 | (2) |
|
|
|
412 | (1) |
|
|
|
413 | (1) |
|
Adding and deleting chart elements |
|
|
413 | (1) |
|
Formatting chart elements |
|
|
413 | (1) |
|
Modifying and Customizing Charts |
|
|
414 | (1) |
|
Moving and resizing a chart |
|
|
414 | (1) |
|
Converting an embedded chart to a chart sheet |
|
|
415 | (1) |
|
|
|
416 | (1) |
|
|
|
416 | (1) |
|
|
|
416 | (1) |
|
Moving and deleting chart elements |
|
|
416 | (1) |
|
Formatting chart elements |
|
|
416 | (1) |
|
Copying a chart's formatting |
|
|
417 | (1) |
|
|
|
418 | (1) |
|
|
|
418 | (1) |
|
Understanding Chart Types |
|
|
419 | (1) |
|
|
|
419 | (2) |
|
|
|
421 | (2) |
|
|
|
423 | (1) |
|
|
|
424 | (2) |
|
|
|
426 | (1) |
|
|
|
427 | (1) |
|
|
|
428 | (1) |
|
|
|
429 | (1) |
|
|
|
430 | (1) |
|
|
|
430 | (1) |
|
|
|
431 | (1) |
|
Newer Chart Types for Excel |
|
|
431 | (1) |
|
|
|
431 | (2) |
|
|
|
433 | (1) |
|
|
|
434 | (1) |
|
|
|
434 | (2) |
|
|
|
436 | (1) |
|
|
|
437 | (1) |
|
|
|
437 | (1) |
|
|
|
438 | (3) |
|
Chapter 19 Using Advanced Charting Techniques |
|
|
441 | (34) |
|
|
|
441 | (1) |
|
|
|
442 | (1) |
|
Selecting with the keyboard |
|
|
443 | (1) |
|
Selecting with the Chart Elements control |
|
|
443 | (1) |
|
Exploring the User Interface Choices for Modifying Chart Elements |
|
|
444 | (1) |
|
Using the Format task pane |
|
|
444 | (1) |
|
Using the chart customization buttons |
|
|
445 | (1) |
|
|
|
446 | (1) |
|
|
|
446 | (1) |
|
|
|
447 | (1) |
|
|
|
448 | (1) |
|
Working with Titles in a Chart |
|
|
449 | (1) |
|
|
|
450 | (2) |
|
|
|
452 | (1) |
|
|
|
452 | (1) |
|
|
|
452 | (4) |
|
Modifying the category axis |
|
|
456 | (4) |
|
|
|
460 | (1) |
|
Deleting or hiding a data series |
|
|
461 | (1) |
|
Adding a new data series to a chart |
|
|
462 | (1) |
|
Changing data used by a series |
|
|
462 | (1) |
|
Changing the data range by dragging the range outline |
|
|
463 | (1) |
|
Using the Edit Series dialog box |
|
|
463 | (1) |
|
Editing the Series formula |
|
|
464 | (1) |
|
Displaying data labels in a chart |
|
|
465 | (2) |
|
|
|
467 | (1) |
|
|
|
468 | (1) |
|
|
|
468 | (2) |
|
Creating combination charts |
|
|
470 | (2) |
|
|
|
472 | (1) |
|
|
|
473 | (2) |
|
Chapter 20 Creating Sparkline Graphics |
|
|
475 | (14) |
|
|
|
475 | (2) |
|
|
|
477 | (3) |
|
|
|
480 | (1) |
|
|
|
480 | (1) |
|
Handling hidden or missing data |
|
|
480 | (1) |
|
Changing the Sparkline type |
|
|
481 | (1) |
|
Changing Sparkline colors and line width |
|
|
481 | (1) |
|
Highlighting certain data points |
|
|
481 | (1) |
|
Adjusting Sparkline axis scaling |
|
|
482 | (1) |
|
|
|
483 | (1) |
|
|
|
484 | (2) |
|
|
|
486 | (1) |
|
Displaying a Sparkline for a Dynamic Range |
|
|
486 | (3) |
|
Chapter 21 Visualizing with Custom Number Formats and Shapes |
|
|
489 | (30) |
|
Visualizing with Number Formatting |
|
|
489 | (1) |
|
Doing basic number formatting |
|
|
489 | (1) |
|
Using shortcut keys to format numbers |
|
|
490 | (1) |
|
Using the Format Cells dialog box to format numbers |
|
|
491 | (1) |
|
Getting fancy with custom number formatting |
|
|
492 | (2) |
|
Formatting numbers in thousands and millions |
|
|
494 | (1) |
|
Hiding and suppressing zeros |
|
|
495 | (1) |
|
Applying custom format colors |
|
|
496 | (1) |
|
Formatting dates and times |
|
|
497 | (1) |
|
Using symbols to enhance reporting |
|
|
498 | (4) |
|
Using Shapes and Icons as Visual Elements |
|
|
502 | (1) |
|
|
|
502 | (2) |
|
Inserting SVG icon graphics |
|
|
504 | (1) |
|
|
|
504 | (2) |
|
Formatting shapes and icons |
|
|
506 | (1) |
|
Enhancing Excel reports with shapes |
|
|
507 | (1) |
|
Creating visually appealing containers with shapes |
|
|
507 | (2) |
|
Layering shapes to save space |
|
|
509 | (1) |
|
Constructing your own infographic widgets with shapes |
|
|
509 | (1) |
|
|
|
510 | (1) |
|
|
|
510 | (3) |
|
Using SmartArt and WordArt |
|
|
513 | (1) |
|
|
|
513 | (1) |
|
|
|
514 | (1) |
|
Working with Other Graphics Types |
|
|
515 | (1) |
|
|
|
515 | (1) |
|
|
|
516 | (1) |
|
Displaying a worksheet background image |
|
|
516 | (1) |
|
Using the Equation Editor |
|
|
516 | (3) |
|
Part IV Managing and Analyzing Data |
|
|
519 | (202) |
|
Chapter 22 Importing and Cleaning Data |
|
|
521 | (32) |
|
|
|
521 | (1) |
|
|
|
522 | (1) |
|
|
|
522 | (1) |
|
|
|
522 | (1) |
|
|
|
523 | (1) |
|
|
|
523 | (1) |
|
|
|
524 | (1) |
|
|
|
524 | (1) |
|
|
|
525 | (3) |
|
|
|
528 | (1) |
|
|
|
529 | (1) |
|
|
|
529 | (1) |
|
Identifying duplicate rows |
|
|
530 | (1) |
|
|
|
531 | (1) |
|
|
|
532 | (1) |
|
|
|
533 | (3) |
|
Changing the case of text |
|
|
536 | (1) |
|
|
|
537 | (1) |
|
Removing strange characters |
|
|
538 | (1) |
|
|
|
538 | (1) |
|
|
|
538 | (2) |
|
|
|
540 | (1) |
|
|
|
541 | (1) |
|
|
|
541 | (1) |
|
Extracting a filename from a URL |
|
|
541 | (1) |
|
|
|
542 | (1) |
|
Changing vertical data to horizontal data |
|
|
543 | (2) |
|
Filling gaps in an imported report |
|
|
545 | (2) |
|
|
|
547 | (1) |
|
Replacing or removing text in cells |
|
|
547 | (1) |
|
|
|
548 | (1) |
|
Fixing trailing minus signs |
|
|
549 | (1) |
|
Following a data cleaning checklist |
|
|
549 | (1) |
|
|
|
550 | (1) |
|
|
|
550 | (1) |
|
|
|
550 | (1) |
|
|
|
550 | (1) |
|
|
|
551 | (1) |
|
Exporting to other file formats |
|
|
551 | (2) |
|
Chapter 23 Using Data Validation |
|
|
553 | (14) |
|
|
|
553 | (1) |
|
Specifying Validation Criteria |
|
|
554 | (1) |
|
Types of Validation Criteria You Can Apply |
|
|
555 | (2) |
|
Creating a Drop-Down List |
|
|
557 | (1) |
|
Using Formulas for Data Validation Rules |
|
|
558 | (1) |
|
Understanding Cell References |
|
|
559 | (1) |
|
Data Validation Formula Examples |
|
|
560 | (1) |
|
|
|
561 | (1) |
|
Accepting a larger value than the previous cell |
|
|
561 | (1) |
|
Accepting nonduplicate entries only |
|
|
561 | (1) |
|
Accepting text that begins with a specific character |
|
|
561 | (1) |
|
Accepting dates by the day of the week |
|
|
562 | (1) |
|
Accepting only values that don't exceed a total |
|
|
563 | (1) |
|
Creating a dependent list |
|
|
563 | (1) |
|
Using Data Validation without Restricting Entry |
|
|
564 | (1) |
|
|
|
564 | (1) |
|
|
|
564 | (3) |
|
Chapter 24 Creating and Using Worksheet Outlines |
|
|
567 | (10) |
|
Introducing Worksheet Outlines |
|
|
567 | (3) |
|
|
|
570 | (1) |
|
|
|
571 | (1) |
|
Creating an outline automatically |
|
|
572 | (1) |
|
Creating an outline manually |
|
|
572 | (2) |
|
|
|
574 | (1) |
|
|
|
574 | (1) |
|
Adding data to an outline |
|
|
575 | (1) |
|
|
|
575 | (1) |
|
Adjusting the outline symbols |
|
|
575 | (1) |
|
Hiding the outline symbols |
|
|
575 | (2) |
|
Chapter 25 Linking and Consolidating Worksheets |
|
|
577 | (18) |
|
|
|
577 | (1) |
|
Creating External Reference Formulas |
|
|
578 | (1) |
|
Understanding link formula syntax |
|
|
578 | (1) |
|
Creating a link formula by pointing |
|
|
579 | (1) |
|
|
|
580 | (1) |
|
Working with External Reference Formulas |
|
|
580 | (1) |
|
Creating links to unsaved workbooks |
|
|
580 | (1) |
|
Opening a workbook with external reference formulas |
|
|
581 | (1) |
|
Changing the startup prompt |
|
|
582 | (1) |
|
|
|
582 | (1) |
|
|
|
583 | (1) |
|
|
|
583 | (1) |
|
Avoiding Potential Problems with External Reference Formulas |
|
|
583 | (1) |
|
Renaming or moving a source workbook |
|
|
584 | (1) |
|
Using the Save As command |
|
|
584 | (1) |
|
Modifying a source workbook |
|
|
584 | (1) |
|
|
|
585 | (1) |
|
|
|
585 | (2) |
|
Consolidating worksheets by using formulas |
|
|
587 | (1) |
|
Consolidating worksheets by using Paste Special |
|
|
587 | (1) |
|
Consolidating worksheets by using the Consolidate dialog box |
|
|
588 | (2) |
|
Viewing a workbook consolidation example |
|
|
590 | (2) |
|
Refreshing a consolidation |
|
|
592 | (1) |
|
Learning more about consolidation |
|
|
593 | (2) |
|
Chapter 26 Introducing PivotTables |
|
|
595 | (22) |
|
|
|
595 | (1) |
|
|
|
596 | (2) |
|
Data appropriate for a PivotTable |
|
|
598 | (2) |
|
Creating a PivotTable Automatically |
|
|
600 | (2) |
|
Creating a PivotTable Manually |
|
|
602 | (1) |
|
|
|
602 | (1) |
|
Specifying the location for the PivotTable |
|
|
603 | (1) |
|
Laying out the PivotTable |
|
|
603 | (4) |
|
Formatting the PivotTable |
|
|
607 | (2) |
|
|
|
609 | (2) |
|
Seeing More PivotTable Examples |
|
|
611 | (1) |
|
What is the daily total new deposit amount for each branch? |
|
|
611 | (1) |
|
Which day of the week accounts for the most deposits? |
|
|
611 | (2) |
|
How many accounts were opened at each branch, broken down by account type? |
|
|
613 | (1) |
|
How much money was used to open the accounts? |
|
|
613 | (1) |
|
What types of accounts do tellers open most often? |
|
|
614 | (1) |
|
In which branch do tellers open the most checking accounts for new customers? |
|
|
615 | (1) |
|
|
|
616 | (1) |
|
Chapter 27 Analyzing Data with PivotTables |
|
|
617 | (34) |
|
Working with Non-Numeric Data |
|
|
617 | (2) |
|
Grouping PivotTable Items |
|
|
619 | (1) |
|
|
|
619 | (2) |
|
Grouping items automatically |
|
|
621 | (1) |
|
|
|
621 | (4) |
|
|
|
625 | (1) |
|
Using a PivotTable to Create a Frequency Distribution |
|
|
626 | (2) |
|
Creating a Calculated Field or Calculated Item |
|
|
628 | (2) |
|
Creating a calculated field |
|
|
630 | (2) |
|
Inserting a calculated item |
|
|
632 | (3) |
|
Filtering PivotTables with Slicers |
|
|
635 | (2) |
|
Filtering PivotTables with a Timeline |
|
|
637 | (1) |
|
Referencing Cells within a PivotTable |
|
|
638 | (2) |
|
|
|
640 | (1) |
|
|
|
640 | (3) |
|
|
|
643 | (1) |
|
|
|
644 | (7) |
|
Chapter 28 Performing Spreadsheet What-If Analysis |
|
|
651 | (24) |
|
Looking at a What-If Example |
|
|
651 | (2) |
|
Exploring Types of What-If Analyses |
|
|
653 | (1) |
|
Performing manual what-If analysis |
|
|
653 | (1) |
|
|
|
653 | (1) |
|
Creating a one-input data table |
|
|
654 | (3) |
|
Creating a two-input data table |
|
|
657 | (4) |
|
|
|
661 | (1) |
|
|
|
662 | (2) |
|
|
|
664 | (2) |
|
|
|
666 | (1) |
|
|
|
666 | (1) |
|
Generating a scenario report |
|
|
666 | (2) |
|
Analyzing Data with Artificial Intelligence |
|
|
668 | (1) |
|
Using Excel's suggestions |
|
|
668 | (3) |
|
|
|
671 | (4) |
|
Chapter 29 Analyzing Data Using Goal Seeking and Solver |
|
|
675 | (22) |
|
Exploring What-If Analysis, in Reverse |
|
|
675 | (1) |
|
Using Single-Cell Goal Seeking |
|
|
675 | (1) |
|
Looking at a goal-seeking example |
|
|
676 | (2) |
|
Learning more about goal seeking |
|
|
678 | (1) |
|
|
|
678 | (1) |
|
Looking at appropriate problems for Solver |
|
|
679 | (1) |
|
Seeing a simple Solver example |
|
|
679 | (6) |
|
|
|
685 | (1) |
|
Seeing Some Solver Examples |
|
|
686 | (1) |
|
Solving simultaneous linear equations |
|
|
686 | (2) |
|
Minimizing shipping costs |
|
|
688 | (3) |
|
|
|
691 | (2) |
|
Optimizing an investment portfolio |
|
|
693 | (4) |
|
Chapter 30 Analyzing Data with the Analysis ToolPak |
|
|
697 | (12) |
|
The Analysis ToolPak: An Overview |
|
|
697 | (1) |
|
Installing the Analysis ToolPak Add-In |
|
|
698 | (1) |
|
|
|
698 | (1) |
|
Introducing the Analysis ToolPak Tools |
|
|
699 | (1) |
|
|
|
699 | (1) |
|
|
|
700 | (1) |
|
|
|
701 | (1) |
|
|
|
701 | (1) |
|
|
|
701 | (1) |
|
F-Test (two-sample test for variance) |
|
|
701 | (1) |
|
|
|
702 | (1) |
|
|
|
703 | (1) |
|
|
|
704 | (1) |
|
|
|
705 | (1) |
|
|
|
706 | (1) |
|
|
|
706 | (1) |
|
|
|
707 | (1) |
|
|
|
707 | (1) |
|
z-Test (two-sample test for means) |
|
|
708 | (1) |
|
Chapter 31 Protecting Your Work |
|
|
709 | (12) |
|
|
|
709 | (1) |
|
|
|
710 | (1) |
|
|
|
710 | (2) |
|
|
|
712 | (1) |
|
Assigning user permissions |
|
|
713 | (1) |
|
|
|
714 | (1) |
|
Requiring a password to open a workbook |
|
|
714 | (1) |
|
Protecting a workbook's structure |
|
|
715 | (1) |
|
|
|
716 | (1) |
|
|
|
717 | (1) |
|
Saving a worksheet as a PDF file |
|
|
717 | (1) |
|
Marking a workbook as final |
|
|
717 | (1) |
|
|
|
718 | (1) |
|
Using a digital signature |
|
|
719 | (1) |
|
|
|
719 | (1) |
|
|
|
719 | (2) |
|
Part V Understanding Power Pivot and Power Query |
|
|
721 | (146) |
|
Chapter 32 Introducing Power Pivot |
|
|
723 | (24) |
|
Understanding the Power Pivot Internal Data Model |
|
|
723 | (1) |
|
|
|
724 | (1) |
|
Linking Excel tables to Power Pivot |
|
|
725 | (1) |
|
Preparing your Excel tables |
|
|
726 | (1) |
|
Adding your Excel tables to the data model |
|
|
727 | (1) |
|
Creating relationships between your PowerPivot tables |
|
|
728 | (2) |
|
Managing existing relationships |
|
|
730 | (2) |
|
Using Power Pivot data in reporting |
|
|
732 | (1) |
|
Loading Data from Other Data Sources |
|
|
733 | (1) |
|
Loading data from relational databases |
|
|
733 | (1) |
|
Loading data from SQL Server |
|
|
733 | (5) |
|
Loading data from other relational database systems |
|
|
738 | (1) |
|
Loading data from flat files |
|
|
738 | (1) |
|
Loading data from external Excel files |
|
|
739 | (2) |
|
Loading data from text files |
|
|
741 | (1) |
|
Loading data from the Clipboard |
|
|
742 | (1) |
|
Refreshing and managing external data connections |
|
|
742 | (1) |
|
Manually refreshing your Power Pivot data |
|
|
743 | (1) |
|
Setting up automatic refreshing |
|
|
743 | (2) |
|
Editing your data connection |
|
|
745 | (2) |
|
Chapter 33 Working Directly with the Internal Data Model |
|
|
747 | (10) |
|
Directly Feeding the Internal Data Model |
|
|
747 | (7) |
|
Managing Relationships in the Internal Data Model |
|
|
754 | (1) |
|
Managing Queries & Connections |
|
|
755 | (2) |
|
Chapter 34 Adding Formulas to Power Pivot |
|
|
757 | (20) |
|
Enhancing Power Pivot Data with Calculated Columns |
|
|
757 | (1) |
|
Creating your first calculated column |
|
|
758 | (1) |
|
Formatting your calculated columns |
|
|
759 | (1) |
|
Referencing calculated columns in other calculations |
|
|
760 | (1) |
|
Hiding calculated columns from end users |
|
|
760 | (2) |
|
Utilizing DAX to Create Calculated Columns |
|
|
762 | (1) |
|
Identifying DAX functions safe for calculated columns |
|
|
762 | (2) |
|
Building DAX-driven calculated columns |
|
|
764 | (1) |
|
Month sorting in Power Pivot-driven PivotTables |
|
|
765 | (3) |
|
Referencing fields from other tables |
|
|
768 | (2) |
|
|
|
770 | (1) |
|
Understanding Calculated Measures |
|
|
770 | (3) |
|
Editing and deleting calculated measures |
|
|
773 | (1) |
|
Using Cube Functions to Free Your Data |
|
|
773 | (4) |
|
Chapter 35 Introducing Power Query |
|
|
777 | (28) |
|
Understanding Power Query Basics |
|
|
777 | (7) |
|
Understanding query steps |
|
|
784 | (1) |
|
Viewing the Advanced Query Editor |
|
|
785 | (1) |
|
Refreshing Power Query data |
|
|
786 | (1) |
|
Managing existing queries |
|
|
787 | (1) |
|
Understanding column-level actions |
|
|
788 | (2) |
|
Understanding table actions |
|
|
790 | (2) |
|
Getting Data from External Sources |
|
|
792 | (1) |
|
Importing data from files |
|
|
793 | (1) |
|
Getting data from Excel workbooks |
|
|
794 | (1) |
|
Getting data from CSV and text files |
|
|
795 | (1) |
|
Getting data from PDF files |
|
|
795 | (1) |
|
Importing data from database systems |
|
|
796 | (1) |
|
Importing data from relational and OLAP databases |
|
|
796 | (1) |
|
Importing data from Azure databases |
|
|
797 | (1) |
|
Importing data using ODBC connections to nonstandard databases |
|
|
797 | (1) |
|
Getting Data from Other Data Systems |
|
|
797 | (1) |
|
Managing Data Source Settings |
|
|
798 | (1) |
|
Editing data source settings |
|
|
798 | (2) |
|
Data Profiling with Power Query |
|
|
800 | (1) |
|
|
|
800 | (1) |
|
Data profiling quick actions |
|
|
801 | (4) |
|
Chapter 36 Transforming Data with Power Query |
|
|
805 | (32) |
|
Performing Common Transformation Tasks |
|
|
805 | (1) |
|
Removing duplicate records |
|
|
805 | (3) |
|
|
|
808 | (1) |
|
|
|
808 | (1) |
|
|
|
809 | (2) |
|
|
|
811 | (1) |
|
Finding and replacing specific text |
|
|
811 | (1) |
|
Trimming and cleaning text |
|
|
812 | (2) |
|
Extracting the left, right, and middle values |
|
|
814 | (1) |
|
Extracting first and last characters |
|
|
815 | (1) |
|
Extracting middle characters |
|
|
816 | (1) |
|
Splitting columns using character markers |
|
|
816 | (3) |
|
|
|
819 | (1) |
|
|
|
820 | (1) |
|
|
|
821 | (2) |
|
|
|
823 | (1) |
|
Concatenating with a custom column |
|
|
824 | (2) |
|
Understanding data type conversions |
|
|
826 | (1) |
|
Spicing up custom columns with functions |
|
|
826 | (3) |
|
Adding conditional logic to custom columns |
|
|
829 | (1) |
|
Grouping and Aggregating Data |
|
|
830 | (2) |
|
Working with Custom Data Types |
|
|
832 | (5) |
|
Chapter 37 Making Queries Work Together |
|
|
837 | (18) |
|
|
|
837 | (4) |
|
Understanding the Append Feature |
|
|
841 | (1) |
|
Creating the needed base queries |
|
|
841 | (1) |
|
|
|
842 | (3) |
|
Understanding the Merge Feature |
|
|
845 | (1) |
|
Understanding Power Query joins |
|
|
845 | (1) |
|
|
|
846 | (5) |
|
Understanding fuzzy matching |
|
|
851 | (4) |
|
Chapter 38 Enhancing Power Query Productivity |
|
|
855 | (12) |
|
Implementing Some Power Query Productivity Tips |
|
|
855 | (1) |
|
Getting quick information about your queries |
|
|
855 | (1) |
|
Organizing queries in groups |
|
|
856 | (1) |
|
Selecting columns in your queries faster |
|
|
857 | (1) |
|
|
|
857 | (2) |
|
Quickly creating reference tables |
|
|
859 | (1) |
|
Copying queries to save time |
|
|
859 | (1) |
|
Viewing query dependencies |
|
|
860 | (1) |
|
Setting a default load behavior |
|
|
860 | (1) |
|
Preventing automatic data type changes |
|
|
861 | (1) |
|
Avoiding Power Query Performance Issues |
|
|
862 | (1) |
|
Using views instead of tables |
|
|
862 | (1) |
|
Letting your back-end database servers do some crunching |
|
|
863 | (1) |
|
Upgrading to 64-bit Excel |
|
|
863 | (1) |
|
Disabling privacy settings to improve performance |
|
|
864 | (1) |
|
Disabling relationship detection |
|
|
864 | (3) |
|
|
|
867 | (122) |
|
Chapter 39 Introducing Visual Basic for Applications |
|
|
869 | (30) |
|
|
|
869 | (1) |
|
Displaying the Developer Tab |
|
|
870 | (1) |
|
Learning about Macro Security |
|
|
871 | (1) |
|
Saving Workbooks That Contain Macros |
|
|
872 | (1) |
|
Looking at Two Types of VBA Macros |
|
|
873 | (1) |
|
|
|
873 | (1) |
|
|
|
874 | (2) |
|
|
|
876 | (1) |
|
|
|
876 | (1) |
|
Recording your actions to create VBA code: the basics |
|
|
876 | (1) |
|
Recording a macro: a simple example |
|
|
877 | (1) |
|
|
|
878 | (1) |
|
|
|
879 | (1) |
|
|
|
879 | (1) |
|
Relative versus absolute recording |
|
|
880 | (1) |
|
|
|
881 | (1) |
|
|
|
881 | (1) |
|
|
|
882 | (1) |
|
|
|
883 | (1) |
|
|
|
883 | (1) |
|
More about recording VBA macros |
|
|
884 | (1) |
|
Storing macros in your Personal Macro Workbook |
|
|
884 | (1) |
|
Assigning a macro to a shortcut key |
|
|
885 | (1) |
|
Assigning a macro to a button |
|
|
885 | (1) |
|
Adding a macro to your Quick Access Toolbar |
|
|
886 | (1) |
|
|
|
887 | (1) |
|
The basics: entering and editing code |
|
|
887 | (1) |
|
|
|
888 | (1) |
|
|
|
889 | (1) |
|
|
|
889 | (2) |
|
|
|
891 | (1) |
|
|
|
892 | (1) |
|
|
|
892 | (1) |
|
|
|
893 | (2) |
|
A macro that can't be recorded |
|
|
895 | (1) |
|
|
|
896 | (3) |
|
Chapter 40 Creating Custom Worksheet Functions |
|
|
899 | (14) |
|
Introducing VBA Functions |
|
|
899 | (1) |
|
|
|
900 | (1) |
|
Creating a custom function |
|
|
900 | (1) |
|
Using the function in a worksheet |
|
|
901 | (1) |
|
Analyzing the custom function |
|
|
901 | (1) |
|
Learning about Function Procedures |
|
|
902 | (2) |
|
Executing Function Procedures |
|
|
904 | (1) |
|
Calling custom functions from a procedure |
|
|
904 | (1) |
|
Using custom functions in a worksheet formula |
|
|
904 | (1) |
|
Using Function Procedure Arguments |
|
|
905 | (1) |
|
Creating a function with no arguments |
|
|
905 | (1) |
|
Creating a function with one argument |
|
|
906 | (1) |
|
Creating another function with one argument |
|
|
906 | (1) |
|
Creating a function with two arguments |
|
|
907 | (1) |
|
Creating a function with a range argument |
|
|
908 | (1) |
|
Creating a simple but useful function |
|
|
909 | (1) |
|
Debugging Custom Functions |
|
|
910 | (1) |
|
Inserting Custom Functions |
|
|
910 | (2) |
|
|
|
912 | (1) |
|
Chapter 41 Creating UserForms |
|
|
913 | (22) |
|
Understanding Why to Create UserForms |
|
|
913 | (1) |
|
Exploring UserForm Alternatives |
|
|
914 | (1) |
|
Using the InputBox function |
|
|
914 | (1) |
|
Using the MsgBox function |
|
|
915 | (3) |
|
Creating UserForms: An Overview |
|
|
918 | (1) |
|
|
|
919 | (1) |
|
|
|
919 | (1) |
|
Changing the properties of a control |
|
|
920 | (1) |
|
|
|
921 | (1) |
|
|
|
922 | (1) |
|
Looking at a UserForm Example |
|
|
923 | (1) |
|
|
|
923 | (1) |
|
|
|
924 | (1) |
|
Creating an event handler procedure |
|
|
925 | (1) |
|
Looking at Another UserForm Example |
|
|
926 | (1) |
|
|
|
926 | (2) |
|
Creating event handler procedures |
|
|
928 | (2) |
|
|
|
930 | (1) |
|
|
|
931 | (1) |
|
Making the macro available from a worksheet button |
|
|
931 | (1) |
|
Making the macro available on your Quick Access Toolbar |
|
|
932 | (1) |
|
|
|
932 | (1) |
|
|
|
932 | (1) |
|
|
|
933 | (1) |
|
|
|
933 | (2) |
|
Chapter 42 Using UserForm Controls in a Worksheet |
|
|
935 | (14) |
|
Understanding Why to Use Controls on a Worksheet |
|
|
935 | (3) |
|
|
|
938 | (1) |
|
|
|
938 | (1) |
|
Learning about Design mode |
|
|
938 | (1) |
|
|
|
938 | (1) |
|
|
|
939 | (1) |
|
Linking controls to cells |
|
|
940 | (1) |
|
Creating macros for controls |
|
|
941 | (1) |
|
Reviewing the Available ActiveX Controls |
|
|
942 | (1) |
|
|
|
942 | (1) |
|
|
|
942 | (1) |
|
|
|
943 | (1) |
|
|
|
944 | (1) |
|
|
|
944 | (1) |
|
|
|
944 | (1) |
|
|
|
945 | (1) |
|
|
|
945 | (1) |
|
|
|
946 | (1) |
|
|
|
946 | (1) |
|
|
|
947 | (2) |
|
Chapter 43 Working with Excel Events |
|
|
949 | (14) |
|
|
|
949 | (1) |
|
Entering Event-Handler VBA Code |
|
|
950 | (1) |
|
Using Workbook-Level Events |
|
|
951 | (1) |
|
|
|
952 | (1) |
|
Using the SheetActivate event |
|
|
953 | (1) |
|
|
|
954 | (1) |
|
Using the BeforeSave event |
|
|
954 | (1) |
|
Using the BeforeClose event |
|
|
954 | (1) |
|
Working with Worksheet Events |
|
|
955 | (1) |
|
|
|
956 | (1) |
|
Monitoring a specific range for changes |
|
|
956 | (2) |
|
Using the SelectionChange event |
|
|
958 | (1) |
|
Using the BeforeRightClick event |
|
|
959 | (1) |
|
Using Special Application Events |
|
|
959 | (1) |
|
|
|
960 | (1) |
|
|
|
961 | (2) |
|
Chapter 44 Seeing Some VBA Examples |
|
|
963 | (16) |
|
|
|
963 | (1) |
|
|
|
964 | (1) |
|
Copying a variable-size range |
|
|
965 | (1) |
|
Selecting to the end of a row or column |
|
|
966 | (1) |
|
Selecting a row or column |
|
|
966 | (1) |
|
|
|
967 | (1) |
|
Looping through a range efficiently |
|
|
967 | (1) |
|
Prompting for a cell value |
|
|
968 | (2) |
|
Determining the type of selection |
|
|
970 | (1) |
|
Identifying a multiple selection |
|
|
970 | (1) |
|
|
|
971 | (1) |
|
|
|
972 | (1) |
|
|
|
972 | (1) |
|
Saving and closing all workbooks |
|
|
972 | (1) |
|
|
|
972 | (1) |
|
|
|
973 | (1) |
|
|
|
973 | (1) |
|
Modifying chart properties |
|
|
974 | (1) |
|
Applying chart formatting |
|
|
974 | (1) |
|
|
|
975 | (1) |
|
Turning off screen updating |
|
|
975 | (1) |
|
Preventing alert messages |
|
|
975 | (1) |
|
Simplifying object references |
|
|
976 | (1) |
|
|
|
976 | (3) |
|
Chapter 45 Creating Custom Excel Add-Ins |
|
|
979 | (10) |
|
|
|
979 | (1) |
|
|
|
980 | (2) |
|
Understanding When to Create Add-Ins |
|
|
982 | (1) |
|
|
|
982 | (1) |
|
Looking at an Add-In Example |
|
|
983 | (1) |
|
|
|
984 | (1) |
|
Learning about the UserForm |
|
|
984 | (1) |
|
|
|
985 | (1) |
|
Adding descriptive information |
|
|
985 | (1) |
|
Creating the user interface for your add-in macro |
|
|
986 | (1) |
|
|
|
986 | (1) |
|
|
|
987 | (1) |
|
|
|
987 | (2) |
| Index |
|
989 | |
