Hart-Davis Learn Excel 2011 for Mac

1. Auflage 2011
ISBN: 978-1-4302-3522-4
Verlag: APRESS
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 475 Seiten, eBook

Microsoft Excel 2011 for Mac OS X is a powerful application, but many of its most impressive features can be difficult to find.  Learn Excel 2011 for Mac by Guy Hart-Davis  is a practical, hands-on approach to learning all of the details of Excel 2011 in order to get work done efficiently on Mac OS X. From using formulas and functions to creating databases, from analyzing data to automating tasks, you'll learn everything you need to know to put this powerful application to use for a variety of tasks.

Zielgruppe

Popular/general

Autoren/Hrsg.

Hart-Davis, Guy

Inhaltsverzeichnis

1;Title Page;2
2;Copyright Page;3
3;Contents at a Glance;5
4;Table of Contents;6
5;About the Author;14
6;About the Technical Reviewer;15
7;Acknowledgments;16
8;Introduction;17
8.1;Who Is This Book For?;17
8.2;What Does This Book Cover?;17
8.3;Conventions Used in This Book;19
9;Part I Becoming Proficient with Excel:Mac;21
10;Chapter 1 Learning the Secrets of the Excel:Mac Interface;22
10.1;Getting Ready to Learn Excel’s Secrets;22
10.2;Four Ways to Control Excel;23
10.3;Secrets of the Ribbon;24
10.3.1;Understanding How the Ribbon’s Tabs Work;24
10.3.2;Understanding How the Ribbon’s Groups and Controls Work;26
10.3.3;Collapsing the Ribbon;27
10.4;Secrets of the Toolbars;28
10.4.1;Choosing Which Toolbars to Display;28
10.4.2;Switching the Standard Toolbar Between Icons and Textand Icons Only;29
10.4.3;Undocking and Docking the Formatting Toolbar;29
10.5;Secrets of the Menu Bar;30
10.6;Driving Excel with Keyboard Shortcuts;31
10.7;Making the Toolbox Work Your Way;32
10.8;Navigating Quickly Through Worksheets andWorkbooks;34
10.8.1;Elements of the Excel User Interface;34
10.8.2;Navigating Among Worksheets;36
10.8.3;Changing the Active Cell;37
10.8.4;Selecting and Manipulating Cells;38
10.9;Tools for Entering Text and Formulas Quickly;40
10.9.1;Importing Data;40
10.9.1.1;Importing Data from a Comma-Separated Values File or a Text File;42
10.9.1.2;Importing Data from a FileMaker Pro Database;45
10.9.1.3;Importing Data from an HTML File;48
10.9.2;Connecting a Worksheet to External Data Sources;48
10.9.3;Entering Text Using AutoCorrect;48
10.9.3.1;Opening the AutoCorrect Preferences Pane;48
10.9.3.2;Choosing Options to Make AutoCorrect Work Your Way;49
10.9.3.3;Creating AutoCorrect Exceptions;50
10.9.3.4;Creating Replace-As-You-Type Entries;51
10.9.3.5;Creating AutoCorrect Entries;51
10.9.3.6;Using Your AutoCorrect Entries;52
10.9.4;Entering Text with AutoFill and Custom Lists;52
10.9.4.1;Using AutoFill’s Built-in Capabilities;52
10.9.4.2;Creating Your Own Custom AutoFill Lists;55
10.9.5;Entering Text Using Paste and Paste Options;56
10.9.5.1;Switching Data from Rows to Columns;57
10.9.5.2;Pasting in a Table from Word;58
10.9.5.3;Getting Comma-Separated Data into a Worksheet;59
10.9.6;Pasting in Multiple Items with the Scrapbook;59
10.9.6.1;Opening the Scrapbook;59
10.9.6.2;Adding an Item to the Scrapbook;60
10.9.6.3;Inserting an Item from the Scrapbook;61
10.9.6.4;Deleting an Item from the Scrapbook;61
10.9.7;Entering Text with Find and Replace;61
10.9.8;Inserting Symbols in a Document;63
10.10;Viewing Your Workbooks;64
10.10.1;Splitting the Window to View Separate Parts of aWorksheet;65
10.10.2;Opening Extra Windows to Show Other Parts of aWorkbook;67
10.10.3;Changing the Window and Arranging Open Windows;67
10.10.4;Zooming to Show the Data You Need to See;68
10.10.5;Freezing Rows and Columns So They Stay Onscreen;69
10.11;Using Custom Views;70
10.12;Summary;71
11;Chapter 2 Configuring Excel:Mac to Suit the Way You Work;72
11.1;Opening the Excel Preferences Dialog Box;72
11.2;Controlling How the Excel Window Appears;74
11.2.1;Choosing Options in the Settings Area of View Preferences;76
11.2.2;Choosing How to Display Comments;76
11.2.3;Choosing How to Display Objects;76
11.2.4;Choosing Window Options;77
11.3;Choosing Editing Options;78
11.4;Setting Preferences for Creating and Saving Your Workbooks;81
11.4.1;Creating Workbooks with the Number of Worksheets You Need;81
11.4.2;Choosing the Default Folder for Opening and Saving Workbooks;81
11.4.3;Choosing the Default Format for Saving Workbooks;82
11.4.4;Setting AutoRecover to Keep Backups of Your Workbooks for Safety;83
11.4.5;Making Excel Prompt You to Enter Workbook Properties;84
11.5;Creating Custom Keyboard Shortcuts;85
11.6;Customizing the Toolbars with the Commands You Need;87
11.6.1;Getting Ready to Customize the Toolbars;87
11.6.2;Creating a New Toolbar;89
11.6.3;Finding the Commands to Add to the Toolbars or Menus;89
11.6.4;Adding an Item to a Toolbar;90
11.6.5;Removing an Item from a Toolbar;90
11.6.6;Repositioning Items on a Toolbar;90
11.6.7;Changing How a Toolbar Button Appears;90
11.6.8;Adding a Built-in Menu to a Toolbar;92
11.7;Customizing the Menus and the Menu Bar;92
11.7.1;Customizing the Menus;93
11.7.2;Customizing the Menu Bar;94
11.8;Turning Off or Customizing the Ribbon;96
11.8.1;Turning the Ribbon Off So It Doesn’t Appear;96
11.8.2;Customizing the Ribbon;98
11.8.2.1;Customizing the Ribbon the Quick Way;98
11.8.2.2;Customizing the Ribbon Using the Ribbon Preferences Pane;98
11.9;Opening One or More Workbooks Automatically with Excel;99
11.10;Saving the Layout of Open Workbooks as a Workspace;100
11.11;Summary;100
12;Chapter 3 Creating Effective Workbooks and Templates;101
12.1;Creating Workbooks from Scratch or fromTemplates;101
12.1.1;Creating a New Blank Workbook;102
12.1.2;Creating a New Workbook Based on a Template;103
12.1.3;Creating a New Workbook Based on an Existing Workbook;103
12.1.4;Saving a Workbook;104
12.1.5;Protecting a Workbook with Automatic Backups andPasswords;105
12.1.6;Entering Workbook Properties;108
12.1.6.1;Entering Standard Properties on the Summary Tab;109
12.1.6.2;Entering Extra Data on the Custom Tab;109
12.1.7;Finding Your Workbooks by Using Properties;111
12.2;Saving Your Workbooks for Use with Older Versionsof Excel;113
12.3;Organizing the Worksheets in a Workbook;115
12.3.1;Inserting a New Worksheet;116
12.3.2;Naming a Worksheet;116
12.3.3;Changing a Worksheet’s Tab Color;116
12.3.4;Deleting a Worksheet;117
12.3.5;Rearranging the Worksheets in a Workbook;117
12.4;Four Easy Rules for Laying Out Your Worksheets;118
12.5;Entering Data on Multiple Worksheets at Once;119
12.6;Identifying Parts with Named Ranges;120
12.6.1;Assigning a Name to a Cell or Range;120
12.6.1.1;Assigning a Name to a Cell or Range Quickly;121
12.6.1.2;Assigning a Name to a Cell or Range with the Define Name Dialog Box;121
12.6.2;Creating Range Names Automatically;122
12.6.3;Using a Range Name in Your Formulas;123
12.6.4;Deleting a Range Name;124
12.6.5;Changing the Cell or Range a Name Refers To;124
12.7;Creating a Collapsible Worksheet by Outlining It;125
12.7.1;Having Excel Create an Outline Automatically;126
12.7.2;Changing the Settings for Outlining;128
12.7.3;Creating an Outline Manually;129
12.7.3.1;Grouping Rows or Columns;129
12.7.3.2;Ungrouping Rows or Columns;130
12.7.4;Expanding and Collapsing an Outline;130
12.7.5;Updating the Outline After Adding or Deleting Rows or Columns;130
12.7.6;Remove an Outline;131
12.8;Making the Most of Templates;131
12.8.1;Creating a Template Based on an Existing Workbook;132
12.8.2;Saving a Template;132
12.9;Summary;133
13;Chapter 4 Formatting Your Worksheets Quickly and Efficiently;134
13.1;Working with Rows and Columns;134
13.1.1;Inserting and Deleting Rows, Columns, and Cells;135
13.1.1.1;Inserting Columns and Rows;135
13.1.1.2;Inserting Some Cells;136
13.1.1.3;Deleting Columns or Rows;136
13.1.1.4;Deleting Some Cells;136
13.1.2;Setting Row Height;137
13.1.3;Setting Column Width;138
13.1.4;Hiding Rows and Columns;139
13.2;Formatting Cells and Ranges;139
13.2.1;Understanding the Three Main Tools for Applying Formatting;140
13.2.2;Controlling How Data Appears by Applying Number Formatting;142
13.2.3;Setting the Workbook’s Overall Look by Applying a Theme;150
13.2.4;Choosing How to Align Cell Contents;150
13.2.5;Choosing Font Formatting;152
13.2.6;Applying Borders and Fills;153
13.2.7;Applying Protection to Cells;154
13.3;Using Paste Special to Paste Formatting and Perform Actions;155
13.4;Identifying Unusual Values with Conditional Formatting;157
13.4.1;Understanding Excel’s Preset Types of Conditional Formatting;157
13.4.2;Applying a Preset Form of Conditional Formatting;159
13.4.3;Creating Custom Conditional Formatting;160
13.4.4;Changing the Order in Which Excel Applies Conditional Formatting Rules;161
13.4.5;Clearing Conditional Formatting from a Cell, Range, or Worksheet;162
13.5;Checking Input with Data Validation;163
13.6;Formatting Quickly with Table Formatting and Styles;167
13.6.1;Formatting with Table Formatting;167
13.6.2;Formatting with Styles;167
13.6.2.1;Meeting Excel’s Styles;168
13.6.2.2;Applying a Style;169
13.6.2.3;Creating Custom Styles;169
13.6.2.4;Copying Styles from One Workbook to Another;171
13.6.2.5;Deleting Styles You Don’t Need;172
13.7;Adding Headers and Footers to Your Worksheets;172
13.7.1;Adding Headers and Footers Using the Page Setup Dialog Box;172
13.7.2;Adding Headers and Footers Directly on the Worksheet;175
13.8;Summary;177
14;Part II Performing Calculations and Presenting Data;178
15;Chapter 5 Performing CustomCalculations with Formulas;180
15.1;Understanding the Difference Between Formulas and Functions;180
15.2;Referring to Cells and Ranges in Formulas and Functions;181
15.2.1;Referring to a Cell;181
15.2.2;Referring to a Range;183
15.2.3;Making One Row or Column Refer to Another Row or Column;183
15.2.4;Referring to Named Cells and Ranges;184
15.3;Understanding the Components of Formulas;184
15.3.1;Meeting Excel’s Calculation Operators;184
15.4;Creating Straightforward Formulas;186
15.5;Creating Complex Formulas;189
15.5.1;Understanding the Order in Which Excel Evaluates Operators;189
15.5.2;Nesting Parts of a Formula to Control Operator Precedence;191
15.5.3;Breaking Up a Complex Formula into Separate Steps;191
15.6;Entering Formulas Quickly by Copying and Using AutoFill;191
15.7;Choosing Preferences for Error Checking;192
15.8;Troubleshooting Common Problems with Formulas;195
15.8.1;Understanding Common Errors—and Resolving Them;195
15.8.2;Seeing the Details of an Error in a Formula;197
15.8.3;Tracing an Error Back to Its Source;197
15.8.4;Displaying All the Formulas in a Worksheet;198
15.8.5;Seeing Which Cells a Formula Uses;198
15.8.6;Removing Circular References;199
15.9;Summary;200
16;Chapter 6 Using Excel’s Built-In Functions;201
16.1;Understanding the Components of a Function;201
16.2;Entering Functions in Your Worksheets;202
16.2.1;Inserting Functions with the AutoSum Pop-up Menu;203
16.2.2;Inserting Functions with the Formula Builder;204
16.2.3;Inserting Functions with the Insert Pop-up Menu;208
16.2.4;Inserting Functions by Typing Them into a Worksheet;209
16.3;Nesting One Function Inside Another Function;210
16.4;Meeting Excel’s Built-in Functions;211
16.4.1;Database Functions;211
16.4.2;Date and Time Functions;213
16.4.3;Engineering Functions;215
16.4.4;Financial Functions;218
16.4.5;Logical Functions;225
16.4.6;Information Functions;226
16.4.7;Lookup and Reference Functions;228
16.4.8;Mathematical and Trigonometric Functions;229
16.4.9;Statistical Functions;233
16.4.10;Text Functions;234
16.5;Choosing the Right Calculation Preferences for Your Needs;236
16.5.1;Choosing When to Calculate Worksheets;237
16.5.2;Controlling Iteration of Calculations;238
16.5.3;Choosing Workbook Options;239
16.6;Summary;239
17;Chapter 7 Creating Clear and Persuasive Charts;240
17.1;Learning the Essentials of Charts in Excel;240
17.1.1;Understanding Embedded Charts and Chart Sheets;240
17.1.2;Understanding the Components of a Chart;242
17.1.2.1;Chart Area and Plot Area;244
17.1.2.2;Chart Axes;244
17.1.2.3;Categories and Data Series;244
17.1.2.4;Chart Title and Axis Titles;244
17.1.2.5;Data Markers, Gridlines, and Data Labels;245
17.2;Choosing the Best Chart Type for Your Data;245
17.3;Creating, Laying Out, and Formatting a Chart;246
17.3.1;Creating a Chart;246
17.3.2;Changing a Chart from an Embedded Chart to a Chart Sheet;248
17.3.3;Changing the Chart Type;249
17.3.4;Switching the Rows and Columns in a Chart;250
17.3.5;Changing the Source Data for a Chart;250
17.3.6;Choosing the Layout for the Chart;251
17.3.7;Adding a Separate Data Series to a Chart;252
17.3.8;Applying a Style to a Chart;253
17.3.9;Adding a Title to a Chart;254
17.3.10;Adding Axis Titles to the Chart;254
17.3.11;Changing the Scale or Numbering of an Axis;255
17.3.12;Adding a Legend to a Chart;258
17.3.13;Adding Axis Labels from a Range Separate from the Chart Data;259
17.3.14;Adding Data Labels to the Chart;259
17.3.15;Choosing Which Gridlines to Display;260
17.3.16;Formatting a Chart Wall and Chart Floor;261
17.3.17;Formatting Individual Chart Elements;264
17.4;Copying a Chart’s Formatting to Another Chart;265
17.5;Reusing Your Own Designs by Creating Custom Chart Types;266
17.6;Choosing Chart Preferences;267
17.7;Using Your Charts in Word Documents andPower Point Presentations;269
17.7.1;Understanding How You Can Add a Chart to a Document or Slide;269
17.7.1.1;Creating a Chart in a New Embedded Workbook;270
17.7.1.2;Pasting a Chart from Excel into a Word Document or PowerPoint Slide;272
17.8;Summary;274
18;Chapter 8 Using Data Bars, Color Scales, Icon Sets, and Sparklines;275
18.1;Using Data Bars;275
18.1.1;Creating Data Bars;276
18.1.1.1;Creating Data Bars in the Same Cells as Their Data;276
18.1.1.2;Creating Data Bars in Different Cells Than Their Data;277
18.1.2;Using Color Scales;279
18.1.3;Representing Data Graphically with Icon Sets;281
18.1.4;Showing Data Trends with Sparklines;284
18.1.4.1;Inserting Sparklines;285
18.1.4.2;Formatting Your Sparklines;285
18.2;Summary;288
19;Chapter 9 Illustrating Your Worksheets with Pictures, SmartArt, and More;289
19.1;Inserting Clip Art;290
19.1.1;Inserting a Clip Art Picture Using the Clip Art Browser;290
19.1.2;Inserting a Clip Art Picture Using the Clip Gallery;291
19.1.3;Managing Your Clip Art Items with the Clip Gallery;292
19.1.3.1;Adding Your Own Pictures to the Clip Gallery;292
19.1.3.2;Downloading Clip Art Items from the Microsoft Office Web Site;296
19.1.3.3;Organizing the Clip Gallery with Categories;297
19.2;Inserting Pictures in Your Workbooks;298
19.2.1;Inserting Pictures from iPhoto;298
19.2.2;Inserting Pictures from Your Mac’s File System;299
19.3;Adding and Formatting a Shape;300
19.3.1;Applying a Style to a Shape;302
19.4;Rotating a Graphical Object;304
19.5;Positioning a Graphical Object;304
19.6;Making a Picture Look the Way You Want It;305
19.6.1;Adjusting a Picture’s Sharpness, Brightness, Contrast, and Colors;305
19.6.2;Applying a Picture Style;306
19.6.3;Cropping a Picture;307
19.6.4;Saving Space by Compressing Pictures;309
19.7;Inserting SmartArt Diagrams;310
19.8;Adding Decorative Text with WordArt;312
19.9;Positioning Graphical Objects Relative to Cells;313
19.10;Arranging Graphical Objects to Control Which Is Visible;315
19.11;Summary;315
20;Part III Analyzing Data and Sharing and Automating Workbooks;316
21;Chapter 10 Creating Databases Using Tables;318
21.1;Creating Databases in Excel;318
21.1.1;Understanding What You Can and Can’t Do with Excel Tables;318
21.1.2;Creating a Table and Entering Data;319
21.1.2.1;Creating a Table;320
21.1.2.2;Customizing the Table’s Looks;321
21.1.2.3;Entering Data in a Table;323
21.1.2.3.1;Entering Data Directly in the Table;323
21.1.2.3.2;Entering Data Using a Data-Entry Form;324
21.1.3;Connecting a Table to an External Data Source;325
21.1.3.1;Getting and Installing an ODBC Driver;326
21.1.3.2;Establishing a Connection to a Database;326
21.1.4;Connecting to a Database;325
21.1.4.1;Getting and Installing an ODBC Driver;326
21.1.4.2;Establishing a Connection to a Database;326
21.1.4.3;Refreshing the Data from a Database;329
21.1.5;Importing Data from a FileMaker Pro Database;330
21.1.6;Resizing a Table;330
21.1.7;Sorting a Table by One or More Fields;331
21.1.7.1;Sorting Quickly by a Single Field;331
21.1.7.2;Sorting a Table by Multiple Fields;331
21.1.8;Identifying and Removing Duplicate Records in a Table;333
21.1.9;Filtering a Table;335
21.2;Using Database Functions with Tables;337
21.3;Summary;339
22;Chapter 11 Solving Business Questions with What-If Analysis, Goal Seek, and Solver;340
22.1;Assessing the Impact of Variables Using Data Tables;341
22.1.1;Creating a Data Table with One Variable;341
22.1.2;Creating a Data Table with Two Variables;344
22.2;Examining Different Scenarios in a Worksheet;345
22.2.1;Creating the Worksheet for Your Scenarios;345
22.2.2;Opening the Scenario Manager Dialog Box;347
22.2.3;Creating Scenarios;348
22.2.4;Applying Protection to Your Scenarios;351
22.2.5;Editing and Deleting Scenarios;351
22.2.6;Switching Among Your Scenarios;352
22.2.7;Merging Scenarios into a Single Worksheet;352
22.2.8;Creating Reports from Your Scenarios;354
22.3;Using Goal Seek;355
22.4;Solving Multiple-Variable Problems with Solver;357
22.4.1;Downloading and Installing Solver;357
22.4.2;Using Solver;358
22.5;Summary;362
23;Chapter 12 Analyzing Data with PivotTables;363
23.1;Understanding What PivotTables Are and What You Can Do with Them;363
23.2;Creating and Laying Out a PivotTable;365
23.2.1;Creating a PivotTable Automatically;365
23.2.2;Creating a PivotTable Manually;367
23.2.2.1;Understanding How the PivotTable Framework and PivotTable Builder Window Work;369
23.2.2.2;Adding the Fields to the PivotTable Framework;370
23.2.3;Changing the PivotTable to Show Different Data;374
23.2.4;Changing the Function Used to Summarize a Field;378
23.3;Controlling the Design of a PivotTable;379
23.4;Formatting a PivotTable;381
23.4.1;Applying a PivotTable Style;381
23.4.2;Choosing Options for a PivotTable Style;381
23.5;Naming a PivotTable and Setting Options for It;382
23.5.1;Renaming a PivotTable;383
23.5.2;Choosing Display Options for a PivotTable;383
23.5.3;Choosing Layout Options for a PivotTable;384
23.5.4;Choosing Data Options for a PivotTable;386
23.5.5;Refreshing the Data in a PivotTable;387
23.5.6;Changing the Source of a PivotTable;388
23.6;Sorting and Filtering a PivotTable;388
23.7;Summary;391
24;Chapter 13 Collaborating and Sharing with Macs and Windows PCs;392
24.1;Making Your Worksheets Print Correctly;392
24.1.1;Telling Excel Which Part of the Worksheet to Print;393
24.1.2;Checking the Page Layout and Where the Page Breaks Fall;393
24.1.3;Printing a Worksheet or Workbook;395
24.2;Sharing Your Worksheets as PDFs;397
24.3;Exporting Data to CSV Files;399
24.4;Documenting Your Workbooks;400
24.4.1;Adding Explanatory Text to Workbooks;401
24.4.2;Adding Comments to Cells;401
24.4.2.1;Adding a Comment;401
24.4.2.2;Viewing Comments;402
24.4.2.3;Deleting Comments;403
24.4.3;Adding Information with Data Validation;403
24.5;Sharing Your Workbooks with Your Colleagues;404
24.5.1;Protecting a Workbook or Some of Its Worksheets;404
24.5.1.1;Protecting a Workbook;404
24.5.1.2;Protecting a Worksheet;405
24.5.2;Tracking Changes to a Workbook;406
24.5.3;Sharing a Workbook So That Your Colleagues Can Edit It;408
24.5.4;Working in a Shared Workbook;410
24.5.5;Resolving Conflicts in a Shared Workbook;411
24.5.6;Reviewing Tracked Changes in a Shared Workbook;411
24.6;Merging Multiple Workbooks into a Single Workbook;413
24.7;Consolidating Multiple Worksheets into a Single Worksheet;413
24.7.1;Preparing to Consolidate Worksheets;414
24.7.2;Consolidating Worksheets by Their Position;414
24.7.3;Consolidating Worksheets by Category;416
24.8;Summary;418
25;Chapter 14 Automating Tasks with Macros and VBA;419
25.1;Understanding Your Options for Automating Tasks;420
25.2;Recording Macros;421
25.3;Recording an Example Macro;425
25.4;Running a Macro;426
25.4.1;Running a Macro from the Macro dialog Box;427
25.4.2;Running a Macro Using Its Keyboard Shortcut;428
25.4.3;Running a Macro from a Toolbar Button or Menu Item;429
25.4.4;Running a Macro from an Object in a Worksheet;432
25.5;Deleting a Macro;433
25.6;Editing Macros in the Visual Basic Editor;433
25.6.1;Opening the Sample Macro in the Visual Basic Editor;433
25.6.2;Meeting the Visual Basic Editor’s Interface;434
25.6.3;Examining the Sample Macro;435
25.6.4;Editing the Sample Macro;437
25.6.5;Testing the Macro;437
25.6.6;Saving the Changes;437
25.6.7;Returning from the Visual Basic Editor to Excel;437
25.6.8;Hiding the Personal Macro Workbook;438
25.7;Summary;438
26;Index;439

Über Autor(innen)

Guy Hart-Davis is the author of more than 50 computer books, including How to Do Everything with Microsoft Excel 2007 and How to Do Everything with Microsoft Word 2007.