| Introduction |
|
1 | (8) |
|
|
|
1 | (1) |
|
|
|
2 | (1) |
|
What You Can Safely Ignore |
|
|
2 | (1) |
|
|
|
2 | (1) |
|
How This Book Is Organized |
|
|
3 | (2) |
|
Part 1: Getting Started with Excel 2016 |
|
|
3 | (1) |
|
Part 2: Editing without Tears |
|
|
4 | (1) |
|
Part 3: Getting Organized and Staying That Way |
|
|
4 | (1) |
|
Part 4: Digging Data Analysis |
|
|
4 | (1) |
|
Part 5: Life beyond the Spreadsheet |
|
|
4 | (1) |
|
|
|
5 | (1) |
|
Conventions Used in This Book |
|
|
5 | (2) |
|
Selecting Ribbon commands |
|
|
5 | (2) |
|
|
|
7 | (1) |
|
|
|
7 | (2) |
| Part 1 Getting Started With Excel 2016 |
|
9 | (84) |
|
Chapter 1 The Excel 2016 User Experience |
|
|
11 | (30) |
|
Excel's Ribbon User Interface |
|
|
12 | (21) |
|
|
|
14 | (1) |
|
|
|
15 | (5) |
|
Customizing the Quick Access toolbar |
|
|
20 | (4) |
|
Having fun with the Formula bar |
|
|
24 | (1) |
|
What to do in the Worksheet area |
|
|
25 | (7) |
|
Showing off the Status bar |
|
|
32 | (1) |
|
Launching and Quitting Excel |
|
|
33 | (5) |
|
Starting Excel from the Windows 10 Start menu |
|
|
33 | (1) |
|
Starting Excel from the Windows 10 Ask Me Anything text box |
|
|
34 | (1) |
|
Starting Excel from the Windows 8 Start screen |
|
|
34 | (1) |
|
Starting Excel from the Windows 7 Start menu |
|
|
35 | (1) |
|
Adding an Excel 2016 shortcut to your Windows 7 desktop |
|
|
36 | (1) |
|
Pinning Excel 2016 to your Windows 7 Start menu |
|
|
36 | (1) |
|
Pinning Excel 2016 to the Windows 7 taskbar |
|
|
37 | (1) |
|
|
|
37 | (1) |
|
|
|
38 | (3) |
|
Using the Tell Me help feature |
|
|
38 | (1) |
|
|
|
39 | (2) |
|
Chapter 2 Creating a Spreadsheet from Scratch |
|
|
41 | (52) |
|
So What Ya Gonna Put in That New Workbook of Yours? |
|
|
42 | (3) |
|
The ins and outs of data entry |
|
|
44 | (1) |
|
|
|
45 | (1) |
|
Doing the Data-Entry Thing |
|
|
45 | (3) |
|
|
|
48 | (14) |
|
The telltale signs of text |
|
|
48 | (1) |
|
How Excel evaluates its values |
|
|
49 | (7) |
|
Fabricating those fabulous formulas! |
|
|
56 | (3) |
|
If you want it, just point it out |
|
|
59 | (1) |
|
Altering the natural order of operations |
|
|
59 | (1) |
|
|
|
60 | (2) |
|
Fixing Those Data Entry Flub-Ups |
|
|
62 | (3) |
|
You really AutoCorrect that for me |
|
|
62 | (1) |
|
|
|
63 | (2) |
|
Taking the Drudgery out of Data Entry |
|
|
65 | (13) |
|
I'm just not complete without you |
|
|
65 | (1) |
|
Fill 'er up with AutoFill |
|
|
66 | (7) |
|
|
|
73 | (2) |
|
Inserting special symbols |
|
|
75 | (1) |
|
Entries all around the block |
|
|
76 | (1) |
|
|
|
77 | (1) |
|
How to Make Your Formulas Function Even Better |
|
|
78 | (8) |
|
Inserting a function into a formula with the Insert Function button |
|
|
79 | (2) |
|
Editing a function with the Insert Function button |
|
|
81 | (1) |
|
I'd be totally lost without AutoSum |
|
|
82 | (2) |
|
Sums via Quick Analysis Totals |
|
|
84 | (2) |
|
Making Sure That the Data Is Safe and Sound |
|
|
86 | (4) |
|
Changing the default file location |
|
|
88 | (1) |
|
The difference between the XLSX and XLS file formats |
|
|
89 | (1) |
|
Saving the Workbook as a PDF File |
|
|
90 | (1) |
|
Document Recovery to the Rescue |
|
|
91 | (2) |
| Part 2 Editing Without Tears |
|
93 | (110) |
|
Chapter 3 Making It All Look Pretty |
|
|
95 | (50) |
|
Choosing a Select Group of Cells |
|
|
96 | (8) |
|
Point-and-click cell selections |
|
|
97 | (4) |
|
|
|
101 | (3) |
|
Using the Format as Table Gallery |
|
|
104 | (5) |
|
Customizing table formats |
|
|
106 | (1) |
|
Creating a new custom Table Style |
|
|
107 | (2) |
|
Cell Formatting from the Home Tab |
|
|
109 | (3) |
|
Formatting Cells Close to the Source with the Mini-bar |
|
|
112 | (1) |
|
Using the Format Cells Dialog Box |
|
|
113 | (9) |
|
Understanding the number formats |
|
|
114 | (5) |
|
The values behind the formatting |
|
|
119 | (1) |
|
|
|
120 | (1) |
|
Ogling some of the other number formats |
|
|
121 | (1) |
|
|
|
122 | (3) |
|
|
|
123 | (1) |
|
Now you see it, now you don't |
|
|
123 | (2) |
|
|
|
125 | (2) |
|
|
|
127 | (8) |
|
|
|
128 | (1) |
|
|
|
128 | (1) |
|
Tampering with how the text wraps |
|
|
128 | (2) |
|
|
|
130 | (2) |
|
|
|
132 | (1) |
|
|
|
132 | (2) |
|
Applying fill colors, patterns, and gradient effects to cells |
|
|
134 | (1) |
|
|
|
135 | (2) |
|
Creating a new style for the gallery |
|
|
135 | (1) |
|
Copying custom styles from one workbook into another |
|
|
136 | (1) |
|
Fooling Around with the Format Painter |
|
|
137 | (1) |
|
|
|
138 | (7) |
|
Formatting with scales and markers |
|
|
139 | (2) |
|
Highlighting cells ranges |
|
|
141 | (1) |
|
Formatting via the Quick Analysis tool |
|
|
142 | (3) |
|
Chapter 4 Going Through Changes |
|
|
145 | (32) |
|
Opening Your Workbooks for Editing |
|
|
147 | (5) |
|
Opening files in the Open screen |
|
|
147 | (1) |
|
Operating the Open dialog box |
|
|
148 | (2) |
|
Changing the Recent files settings |
|
|
150 | (1) |
|
Opening multiple workbooks |
|
|
151 | (1) |
|
|
|
151 | (1) |
|
Using the Open file options |
|
|
151 | (1) |
|
|
|
152 | (2) |
|
Undo is Redo the second time around |
|
|
153 | (1) |
|
What to do when you can't Undo? |
|
|
153 | (1) |
|
Doing the Old Drag-and-Drop Thing |
|
|
154 | (3) |
|
Copies, drag-and-drop style |
|
|
156 | (1) |
|
Insertions courtesy of drag and drop |
|
|
156 | (1) |
|
Copying Formulas with AutoFill |
|
|
157 | (5) |
|
|
|
159 | (1) |
|
Some things are absolutes! |
|
|
159 | (3) |
|
Cut and Paste, Digital Style |
|
|
162 | (7) |
|
|
|
163 | (1) |
|
Keeping pace with Paste Options |
|
|
163 | (3) |
|
Paste it from the Clipboard task pane |
|
|
166 | (1) |
|
So what's so special about Paste Special? |
|
|
167 | (2) |
|
Let's Be Clear about Deleting Stuff |
|
|
169 | (2) |
|
|
|
169 | (1) |
|
Get these cells outta here! |
|
|
170 | (1) |
|
Staying in Step with Insert |
|
|
171 | (1) |
|
Stamping Out Your Spelling Errors |
|
|
172 | (2) |
|
Eliminating Errors with Text to Speech |
|
|
174 | (3) |
|
Chapter 5 Printing the Masterpiece |
|
|
177 | (26) |
|
Previewing Pages in Page Layout View |
|
|
178 | (1) |
|
Using the Backstage Print Screen |
|
|
179 | (3) |
|
Printing the Current Worksheet |
|
|
182 | (2) |
|
|
|
184 | (8) |
|
Using the buttons in the Page Setup group |
|
|
184 | (6) |
|
Using the buttons in the Scale to Fit group |
|
|
190 | (1) |
|
Using the Print buttons in the Sheet Options group |
|
|
191 | (1) |
|
|
|
192 | (6) |
|
Adding an Auto Header and Footer |
|
|
192 | (2) |
|
Creating a custom header or footer |
|
|
194 | (4) |
|
Solving Page Break Problems |
|
|
198 | (3) |
|
Letting Your Formulas All Hang Out |
|
|
201 | (2) |
| Part 3 Getting Organized And Staying That Way |
|
203 | (52) |
|
Chapter 6 Maintaining the Worksheet |
|
|
205 | (28) |
|
|
|
206 | (3) |
|
Splitting the Worksheet into Windows |
|
|
209 | (1) |
|
Fixed Headings with Freeze Panes |
|
|
210 | (3) |
|
|
|
213 | (4) |
|
Adding a comment to a cell |
|
|
214 | (1) |
|
|
|
215 | (1) |
|
Editing comments in a worksheet |
|
|
216 | (1) |
|
Getting your comments in print |
|
|
217 | (1) |
|
|
|
217 | (4) |
|
|
|
217 | (2) |
|
|
|
219 | (1) |
|
|
|
220 | (1) |
|
|
|
221 | (3) |
|
|
|
224 | (2) |
|
Doing Your Research with Smart Lookup |
|
|
226 | (1) |
|
Controlling Recalculation |
|
|
227 | (1) |
|
Putting on the Protection |
|
|
228 | (5) |
|
Chapter 7 Maintaining Multiple Worksheets |
|
|
233 | (22) |
|
Juggling Multiple Worksheets |
|
|
234 | (4) |
|
Sliding between the sheets |
|
|
235 | (2) |
|
|
|
237 | (1) |
|
|
|
238 | (4) |
|
A worksheet by any other name |
|
|
239 | (1) |
|
A sheet tab by any other color |
|
|
239 | (1) |
|
Getting your sheets in order |
|
|
240 | (2) |
|
Opening Windows on Your Worksheets |
|
|
242 | (5) |
|
Comparing Worksheets Side by Side |
|
|
247 | (1) |
|
Shifting Sheets to Other Workbooks |
|
|
248 | (4) |
|
Summing Stuff on Different Worksheets |
|
|
252 | (3) |
| Part 4 Digging Data Analysis |
|
255 | (38) |
|
Chapter 8 Doing What-If Analysis |
|
|
257 | (14) |
|
Playing What-If with Data Tables |
|
|
258 | (6) |
|
Creating a one-variable data table |
|
|
258 | (3) |
|
Creating a two-variable data table |
|
|
261 | (3) |
|
Playing What-If with Goal Seeking |
|
|
264 | (2) |
|
Making the Case with Scenario Manager |
|
|
266 | (5) |
|
Setting up the various scenarios |
|
|
266 | (2) |
|
Producing a summary report |
|
|
268 | (3) |
|
Chapter 9 Playing with Pivot Tables |
|
|
271 | (22) |
|
Data Analysis with Pivot Tables |
|
|
272 | (7) |
|
Pivot tables via the Quick Analysis tool |
|
|
272 | (2) |
|
Pivot tables by recommendation |
|
|
274 | (2) |
|
Manually producing pivot tables |
|
|
276 | (3) |
|
|
|
279 | (1) |
|
Refining the Pivot Table style |
|
|
279 | (1) |
|
Formatting values in the pivot table |
|
|
280 | (1) |
|
Sorting and Filtering Pivot Table Data |
|
|
280 | (5) |
|
|
|
281 | (1) |
|
Filtering column and row fields |
|
|
281 | (2) |
|
|
|
283 | (1) |
|
|
|
283 | (2) |
|
|
|
285 | (1) |
|
|
|
285 | (3) |
|
Modifying the pivot table fields |
|
|
286 | (1) |
|
Pivoting the table's fields |
|
|
286 | (1) |
|
Modifying the table's summary function |
|
|
287 | (1) |
|
|
|
288 | (7) |
|
Moving pivot charts to separate sheets |
|
|
289 | (1) |
|
|
|
290 | (1) |
|
|
|
291 | (2) |
| Part 5 Life Beyond The Spreadsheet |
|
293 | (86) |
|
Chapter 10 Charming Charts and Gorgeous Graphics |
|
|
295 | (36) |
|
Making Professional-Looking Charts |
|
|
296 | (13) |
|
Charts thanks to Recommendation |
|
|
296 | (1) |
|
|
|
297 | (1) |
|
Charts via the Quick Analysis tool |
|
|
298 | (3) |
|
Charts on their own chart sheets |
|
|
301 | (1) |
|
Moving and resizing embedded charts |
|
|
301 | (1) |
|
Moving embedded charts to chart sheets |
|
|
302 | (1) |
|
Customizing charts from the Design tab |
|
|
303 | (1) |
|
Customizing chart elements |
|
|
304 | (2) |
|
Editing the generic titles in a chart |
|
|
306 | (3) |
|
Adding Great-Looking Graphics |
|
|
309 | (17) |
|
Sparking up the data with sparklines |
|
|
309 | (3) |
|
Telling all with a text box |
|
|
312 | (3) |
|
|
|
315 | (2) |
|
|
|
317 | (1) |
|
Editing inserted pictures |
|
|
317 | (1) |
|
Formatting inserted images |
|
|
318 | (1) |
|
Adding preset graphic shapes |
|
|
319 | (2) |
|
|
|
321 | (1) |
|
|
|
322 | (2) |
|
|
|
324 | (1) |
|
|
|
325 | (1) |
|
Controlling How Graphic Objects Overlap |
|
|
326 | (3) |
|
Reordering the layering of graphic objects |
|
|
326 | (2) |
|
|
|
328 | (1) |
|
|
|
328 | (1) |
|
|
|
329 | (2) |
|
Chapter 11 Getting on the Data List |
|
|
331 | (24) |
|
|
|
331 | (8) |
|
Adding records to data lists |
|
|
332 | (5) |
|
Moving through records in the data form |
|
|
337 | (1) |
|
Finding records with the data form |
|
|
338 | (1) |
|
|
|
339 | (5) |
|
Sorting on a single field |
|
|
341 | (1) |
|
Sorting on multiple fields |
|
|
341 | (3) |
|
|
|
344 | (6) |
|
Using ready-made number filters |
|
|
346 | (1) |
|
Using ready-made date filters |
|
|
346 | (1) |
|
|
|
347 | (3) |
|
|
|
350 | (5) |
|
Querying Access database tables |
|
|
350 | (2) |
|
|
|
352 | (3) |
|
Chapter 12 Linking, Automating, and Sharing Spreadsheets |
|
|
355 | (24) |
|
|
|
356 | (2) |
|
|
|
358 | (1) |
|
Adding Hyperlinks to a Worksheet |
|
|
359 | (3) |
|
Automating Commands with Macros |
|
|
362 | (8) |
|
|
|
363 | (4) |
|
|
|
367 | (1) |
|
Assigning macros to the Ribbon and the Quick Access toolbar |
|
|
368 | (2) |
|
|
|
370 | (1) |
|
Sharing workbooks saved on OneDrive |
|
|
370 | (9) |
|
|
|
373 | (1) |
|
Sharing workbooks with IM |
|
|
374 | (1) |
|
Presenting worksheets online |
|
|
374 | (1) |
|
Editing worksheets online |
|
|
375 | (4) |
| Part 6 The Part Of Tens |
|
379 | (18) |
|
Chapter 13 Top Ten Beginner Basics |
|
|
381 | (4) |
|
Chapter 14 The Ten Commandments of Excel 2016 |
|
|
385 | (2) |
|
Chapter 15 Top Ten Ways to Manage Your Data |
|
|
387 | (6) |
|
Chapter 16 Top Ten Ways to Analyze Your Data |
|
|
393 | (4) |
| Index |
|
397 | |
Lisainfo e-raamatute kohta