Learn Excel 2011 for Mac
von: Guy Hart-Davis
Apress, 2011
ISBN: 9781430235224
Sprache: Englisch
475 Seiten, Download: 12530 KB
Format: PDF, auch als Online-Lesen
geeignet für:
| Title Page | 2 | ||
| Copyright Page | 3 | ||
| Contents at a Glance | 5 | ||
| Table of Contents | 6 | ||
| About the Author | 14 | ||
| About the Technical Reviewer | 15 | ||
| Acknowledgments | 16 | ||
| Introduction | 17 | ||
| Who Is This Book For? | 17 | ||
| What Does This Book Cover? | 17 | ||
| Conventions Used in This Book | 19 | ||
| Part I Becoming Proficient with Excel:Mac | 21 | ||
| Chapter 1 Learning the Secrets of the Excel:Mac Interface | 22 | ||
| Getting Ready to Learn Excel’s Secrets | 22 | ||
| Four Ways to Control Excel | 23 | ||
| Secrets of the Ribbon | 24 | ||
| Understanding How the Ribbon’s Tabs Work | 24 | ||
| Understanding How the Ribbon’s Groups and Controls Work | 26 | ||
| Collapsing the Ribbon | 27 | ||
| Secrets of the Toolbars | 28 | ||
| Choosing Which Toolbars to Display | 28 | ||
| Switching the Standard Toolbar Between Icons and Textand Icons Only | 29 | ||
| Undocking and Docking the Formatting Toolbar | 29 | ||
| Secrets of the Menu Bar | 30 | ||
| Driving Excel with Keyboard Shortcuts | 31 | ||
| Making the Toolbox Work Your Way | 32 | ||
| Navigating Quickly Through Worksheets andWorkbooks | 34 | ||
| Elements of the Excel User Interface | 34 | ||
| Navigating Among Worksheets | 36 | ||
| Changing the Active Cell | 37 | ||
| Selecting and Manipulating Cells | 38 | ||
| Tools for Entering Text and Formulas Quickly | 40 | ||
| Importing Data | 40 | ||
| Importing Data from a Comma-Separated Values File or a Text File | 42 | ||
| Importing Data from a FileMaker Pro Database | 45 | ||
| Importing Data from an HTML File | 48 | ||
| Connecting a Worksheet to External Data Sources | 48 | ||
| Entering Text Using AutoCorrect | 48 | ||
| Opening the AutoCorrect Preferences Pane | 48 | ||
| Choosing Options to Make AutoCorrect Work Your Way | 49 | ||
| Creating AutoCorrect Exceptions | 50 | ||
| Creating Replace-As-You-Type Entries | 51 | ||
| Creating AutoCorrect Entries | 51 | ||
| Using Your AutoCorrect Entries | 52 | ||
| Entering Text with AutoFill and Custom Lists | 52 | ||
| Using AutoFill’s Built-in Capabilities | 52 | ||
| Creating Your Own Custom AutoFill Lists | 55 | ||
| Entering Text Using Paste and Paste Options | 56 | ||
| Switching Data from Rows to Columns | 57 | ||
| Pasting in a Table from Word | 58 | ||
| Getting Comma-Separated Data into a Worksheet | 59 | ||
| Pasting in Multiple Items with the Scrapbook | 59 | ||
| Opening the Scrapbook | 59 | ||
| Adding an Item to the Scrapbook | 60 | ||
| Inserting an Item from the Scrapbook | 61 | ||
| Deleting an Item from the Scrapbook | 61 | ||
| Entering Text with Find and Replace | 61 | ||
| Inserting Symbols in a Document | 63 | ||
| Viewing Your Workbooks | 64 | ||
| Splitting the Window to View Separate Parts of aWorksheet | 65 | ||
| Opening Extra Windows to Show Other Parts of aWorkbook | 67 | ||
| Changing the Window and Arranging Open Windows | 67 | ||
| Zooming to Show the Data You Need to See | 68 | ||
| Freezing Rows and Columns So They Stay Onscreen | 69 | ||
| Using Custom Views | 70 | ||
| Summary | 71 | ||
| Chapter 2 Configuring Excel:Mac to Suit the Way You Work | 72 | ||
| Opening the Excel Preferences Dialog Box | 72 | ||
| Controlling How the Excel Window Appears | 74 | ||
| Choosing Options in the Settings Area of View Preferences | 76 | ||
| Choosing How to Display Comments | 76 | ||
| Choosing How to Display Objects | 76 | ||
| Choosing Window Options | 77 | ||
| Choosing Editing Options | 78 | ||
| Setting Preferences for Creating and Saving Your Workbooks | 81 | ||
| Creating Workbooks with the Number of Worksheets You Need | 81 | ||
| Choosing the Default Folder for Opening and Saving Workbooks | 81 | ||
| Choosing the Default Format for Saving Workbooks | 82 | ||
| Setting AutoRecover to Keep Backups of Your Workbooks for Safety | 83 | ||
| Making Excel Prompt You to Enter Workbook Properties | 84 | ||
| Creating Custom Keyboard Shortcuts | 85 | ||
| Customizing the Toolbars with the Commands You Need | 87 | ||
| Getting Ready to Customize the Toolbars | 87 | ||
| Creating a New Toolbar | 89 | ||
| Finding the Commands to Add to the Toolbars or Menus | 89 | ||
| Adding an Item to a Toolbar | 90 | ||
| Removing an Item from a Toolbar | 90 | ||
| Repositioning Items on a Toolbar | 90 | ||
| Changing How a Toolbar Button Appears | 90 | ||
| Adding a Built-in Menu to a Toolbar | 92 | ||
| Customizing the Menus and the Menu Bar | 92 | ||
| Customizing the Menus | 93 | ||
| Customizing the Menu Bar | 94 | ||
| Turning Off or Customizing the Ribbon | 96 | ||
| Turning the Ribbon Off So It Doesn’t Appear | 96 | ||
| Customizing the Ribbon | 98 | ||
| Customizing the Ribbon the Quick Way | 98 | ||
| Customizing the Ribbon Using the Ribbon Preferences Pane | 98 | ||
| Opening One or More Workbooks Automatically with Excel | 99 | ||
| Saving the Layout of Open Workbooks as a Workspace | 100 | ||
| Summary | 100 | ||
| Chapter 3 Creating Effective Workbooks and Templates | 101 | ||
| Creating Workbooks from Scratch or fromTemplates | 101 | ||
| Creating a New Blank Workbook | 102 | ||
| Creating a New Workbook Based on a Template | 103 | ||
| Creating a New Workbook Based on an Existing Workbook | 103 | ||
| Saving a Workbook | 104 | ||
| Protecting a Workbook with Automatic Backups andPasswords | 105 | ||
| Entering Workbook Properties | 108 | ||
| Entering Standard Properties on the Summary Tab | 109 | ||
| Entering Extra Data on the Custom Tab | 109 | ||
| Finding Your Workbooks by Using Properties | 111 | ||
| Saving Your Workbooks for Use with Older Versionsof Excel | 113 | ||
| Organizing the Worksheets in a Workbook | 115 | ||
| Inserting a New Worksheet | 116 | ||
| Naming a Worksheet | 116 | ||
| Changing a Worksheet’s Tab Color | 116 | ||
| Deleting a Worksheet | 117 | ||
| Rearranging the Worksheets in a Workbook | 117 | ||
| Four Easy Rules for Laying Out Your Worksheets | 118 | ||
| Entering Data on Multiple Worksheets at Once | 119 | ||
| Identifying Parts with Named Ranges | 120 | ||
| Assigning a Name to a Cell or Range | 120 | ||
| Assigning a Name to a Cell or Range Quickly | 121 | ||
| Assigning a Name to a Cell or Range with the Define Name Dialog Box | 121 | ||
| Creating Range Names Automatically | 122 | ||
| Using a Range Name in Your Formulas | 123 | ||
| Deleting a Range Name | 124 | ||
| Changing the Cell or Range a Name Refers To | 124 | ||
| Creating a Collapsible Worksheet by Outlining It | 125 | ||
| Having Excel Create an Outline Automatically | 126 | ||
| Changing the Settings for Outlining | 128 | ||
| Creating an Outline Manually | 129 | ||
| Grouping Rows or Columns | 129 | ||
| Ungrouping Rows or Columns | 130 | ||
| Expanding and Collapsing an Outline | 130 | ||
| Updating the Outline After Adding or Deleting Rows or Columns | 130 | ||
| Remove an Outline | 131 | ||
| Making the Most of Templates | 131 | ||
| Creating a Template Based on an Existing Workbook | 132 | ||
| Saving a Template | 132 | ||
| Summary | 133 | ||
| Chapter 4 Formatting Your Worksheets Quickly and Efficiently | 134 | ||
| Working with Rows and Columns | 134 | ||
| Inserting and Deleting Rows, Columns, and Cells | 135 | ||
| Inserting Columns and Rows | 135 | ||
| Inserting Some Cells | 136 | ||
| Deleting Columns or Rows | 136 | ||
| Deleting Some Cells | 136 | ||
| Setting Row Height | 137 | ||
| Setting Column Width | 138 | ||
| Hiding Rows and Columns | 139 | ||
| Formatting Cells and Ranges | 139 | ||
| Understanding the Three Main Tools for Applying Formatting | 140 | ||
| Controlling How Data Appears by Applying Number Formatting | 142 | ||
| Setting the Workbook’s Overall Look by Applying a Theme | 150 | ||
| Choosing How to Align Cell Contents | 150 | ||
| Choosing Font Formatting | 152 | ||
| Applying Borders and Fills | 153 | ||
| Applying Protection to Cells | 154 | ||
| Using Paste Special to Paste Formatting and Perform Actions | 155 | ||
| Identifying Unusual Values with Conditional Formatting | 157 | ||
| Understanding Excel’s Preset Types of Conditional Formatting | 157 | ||
| Applying a Preset Form of Conditional Formatting | 159 | ||
| Creating Custom Conditional Formatting | 160 | ||
| Changing the Order in Which Excel Applies Conditional Formatting Rules | 161 | ||
| Clearing Conditional Formatting from a Cell, Range, or Worksheet | 162 | ||
| Checking Input with Data Validation | 163 | ||
| Formatting Quickly with Table Formatting and Styles | 167 | ||
| Formatting with Table Formatting | 167 | ||
| Formatting with Styles | 167 | ||
| Meeting Excel’s Styles | 168 | ||
| Applying a Style | 169 | ||
| Creating Custom Styles | 169 | ||
| Copying Styles from One Workbook to Another | 171 | ||
| Deleting Styles You Don’t Need | 172 | ||
| Adding Headers and Footers to Your Worksheets | 172 | ||
| Adding Headers and Footers Using the Page Setup Dialog Box | 172 | ||
| Adding Headers and Footers Directly on the Worksheet | 175 | ||
| Summary | 177 | ||
| Part II Performing Calculations and Presenting Data | 178 | ||
| Chapter 5 Performing CustomCalculations with Formulas | 180 | ||
| Understanding the Difference Between Formulas and Functions | 180 | ||
| Referring to Cells and Ranges in Formulas and Functions | 181 | ||
| Referring to a Cell | 181 | ||
| Referring to a Range | 183 | ||
| Making One Row or Column Refer to Another Row or Column | 183 | ||
| Referring to Named Cells and Ranges | 184 | ||
| Understanding the Components of Formulas | 184 | ||
| Meeting Excel’s Calculation Operators | 184 | ||
| Creating Straightforward Formulas | 186 | ||
| Creating Complex Formulas | 189 | ||
| Understanding the Order in Which Excel Evaluates Operators | 189 | ||
| Nesting Parts of a Formula to Control Operator Precedence | 191 | ||
| Breaking Up a Complex Formula into Separate Steps | 191 | ||
| Entering Formulas Quickly by Copying and Using AutoFill | 191 | ||
| Choosing Preferences for Error Checking | 192 | ||
| Troubleshooting Common Problems with Formulas | 195 | ||
| Understanding Common Errors—and Resolving Them | 195 | ||
| Seeing the Details of an Error in a Formula | 197 | ||
| Tracing an Error Back to Its Source | 197 | ||
| Displaying All the Formulas in a Worksheet | 198 | ||
| Seeing Which Cells a Formula Uses | 198 | ||
| Removing Circular References | 199 | ||
| Summary | 200 | ||
| Chapter 6 Using Excel’s Built-In Functions | 201 | ||
| Understanding the Components of a Function | 201 | ||
| Entering Functions in Your Worksheets | 202 | ||
| Inserting Functions with the AutoSum Pop-up Menu | 203 | ||
| Inserting Functions with the Formula Builder | 204 | ||
| Inserting Functions with the Insert Pop-up Menu | 208 | ||
| Inserting Functions by Typing Them into a Worksheet | 209 | ||
| Nesting One Function Inside Another Function | 210 | ||
| Meeting Excel’s Built-in Functions | 211 | ||
| Database Functions | 211 | ||
| Date and Time Functions | 213 | ||
| Engineering Functions | 215 | ||
| Financial Functions | 218 | ||
| Logical Functions | 225 | ||
| Information Functions | 226 | ||
| Lookup and Reference Functions | 228 | ||
| Mathematical and Trigonometric Functions | 229 | ||
| Statistical Functions | 233 | ||
| Text Functions | 234 | ||
| Choosing the Right Calculation Preferences for Your Needs | 236 | ||
| Choosing When to Calculate Worksheets | 237 | ||
| Controlling Iteration of Calculations | 238 | ||
| Choosing Workbook Options | 239 | ||
| Summary | 239 | ||
| Chapter 7 Creating Clear and Persuasive Charts | 240 | ||
| Learning the Essentials of Charts in Excel | 240 | ||
| Understanding Embedded Charts and Chart Sheets | 240 | ||
| Understanding the Components of a Chart | 242 | ||
| Chart Area and Plot Area | 244 | ||
| Chart Axes | 244 | ||
| Categories and Data Series | 244 | ||
| Chart Title and Axis Titles | 244 | ||
| Data Markers, Gridlines, and Data Labels | 245 | ||
| Choosing the Best Chart Type for Your Data | 245 | ||
| Creating, Laying Out, and Formatting a Chart | 246 | ||
| Creating a Chart | 246 | ||
| Changing a Chart from an Embedded Chart to a Chart Sheet | 248 | ||
| Changing the Chart Type | 249 | ||
| Switching the Rows and Columns in a Chart | 250 | ||
| Changing the Source Data for a Chart | 250 | ||
| Choosing the Layout for the Chart | 251 | ||
| Adding a Separate Data Series to a Chart | 252 | ||
| Applying a Style to a Chart | 253 | ||
| Adding a Title to a Chart | 254 | ||
| Adding Axis Titles to the Chart | 254 | ||
| Changing the Scale or Numbering of an Axis | 255 | ||
| Adding a Legend to a Chart | 258 | ||
| Adding Axis Labels from a Range Separate from the Chart Data | 259 | ||
| Adding Data Labels to the Chart | 259 | ||
| Choosing Which Gridlines to Display | 260 | ||
| Formatting a Chart Wall and Chart Floor | 261 | ||
| Formatting Individual Chart Elements | 264 | ||
| Copying a Chart’s Formatting to Another Chart | 265 | ||
| Reusing Your Own Designs by Creating Custom Chart Types | 266 | ||
| Choosing Chart Preferences | 267 | ||
| Using Your Charts in Word Documents andPower Point Presentations | 269 | ||
| Understanding How You Can Add a Chart to a Document or Slide | 269 | ||
| Creating a Chart in a New Embedded Workbook | 270 | ||
| Pasting a Chart from Excel into a Word Document or PowerPoint Slide | 272 | ||
| Summary | 274 | ||
| Chapter 8 Using Data Bars, Color Scales, Icon Sets, and Sparklines | 275 | ||
| Using Data Bars | 275 | ||
| Creating Data Bars | 276 | ||
| Creating Data Bars in the Same Cells as Their Data | 276 | ||
| Creating Data Bars in Different Cells Than Their Data | 277 | ||
| Using Color Scales | 279 | ||
| Representing Data Graphically with Icon Sets | 281 | ||
| Showing Data Trends with Sparklines | 284 | ||
| Inserting Sparklines | 285 | ||
| Formatting Your Sparklines | 285 | ||
| Summary | 288 | ||
| Chapter 9 Illustrating Your Worksheets with Pictures, SmartArt, and More | 289 | ||
| Inserting Clip Art | 290 | ||
| Inserting a Clip Art Picture Using the Clip Art Browser | 290 | ||
| Inserting a Clip Art Picture Using the Clip Gallery | 291 | ||
| Managing Your Clip Art Items with the Clip Gallery | 292 | ||
| Adding Your Own Pictures to the Clip Gallery | 292 | ||
| Downloading Clip Art Items from the Microsoft Office Web Site | 296 | ||
| Organizing the Clip Gallery with Categories | 297 | ||
| Inserting Pictures in Your Workbooks | 298 | ||
| Inserting Pictures from iPhoto | 298 | ||
| Inserting Pictures from Your Mac’s File System | 299 | ||
| Adding and Formatting a Shape | 300 | ||
| Applying a Style to a Shape | 302 | ||
| Rotating a Graphical Object | 304 | ||
| Positioning a Graphical Object | 304 | ||
| Making a Picture Look the Way You Want It | 305 | ||
| Adjusting a Picture’s Sharpness, Brightness, Contrast, and Colors | 305 | ||
| Applying a Picture Style | 306 | ||
| Cropping a Picture | 307 | ||
| Saving Space by Compressing Pictures | 309 | ||
| Inserting SmartArt Diagrams | 310 | ||
| Adding Decorative Text with WordArt | 312 | ||
| Positioning Graphical Objects Relative to Cells | 313 | ||
| Arranging Graphical Objects to Control Which Is Visible | 315 | ||
| Summary | 315 | ||
| Part III Analyzing Data and Sharing and Automating Workbooks | 316 | ||
| Chapter 10 Creating Databases Using Tables | 318 | ||
| Creating Databases in Excel | 318 | ||
| Understanding What You Can and Can’t Do with Excel Tables | 318 | ||
| Creating a Table and Entering Data | 319 | ||
| Creating a Table | 320 | ||
| Customizing the Table’s Looks | 321 | ||
| Entering Data in a Table | 323 | ||
| Entering Data Directly in the Table | 323 | ||
| Entering Data Using a Data-Entry Form | 324 | ||
| Connecting a Table to an External Data Source | 325 | ||
| Getting and Installing an ODBC Driver | 326 | ||
| Establishing a Connection to a Database | 326 | ||
| Connecting to a Database | 325 | ||
| Getting and Installing an ODBC Driver | 326 | ||
| Establishing a Connection to a Database | 326 | ||
| Refreshing the Data from a Database | 329 | ||
| Importing Data from a FileMaker Pro Database | 330 | ||
| Resizing a Table | 330 | ||
| Sorting a Table by One or More Fields | 331 | ||
| Sorting Quickly by a Single Field | 331 | ||
| Sorting a Table by Multiple Fields | 331 | ||
| Identifying and Removing Duplicate Records in a Table | 333 | ||
| Filtering a Table | 335 | ||
| Using Database Functions with Tables | 337 | ||
| Summary | 339 | ||
| Chapter 11 Solving Business Questions with What-If Analysis, Goal Seek, and Solver | 340 | ||
| Assessing the Impact of Variables Using Data Tables | 341 | ||
| Creating a Data Table with One Variable | 341 | ||
| Creating a Data Table with Two Variables | 344 | ||
| Examining Different Scenarios in a Worksheet | 345 | ||
| Creating the Worksheet for Your Scenarios | 345 | ||
| Opening the Scenario Manager Dialog Box | 347 | ||
| Creating Scenarios | 348 | ||
| Applying Protection to Your Scenarios | 351 | ||
| Editing and Deleting Scenarios | 351 | ||
| Switching Among Your Scenarios | 352 | ||
| Merging Scenarios into a Single Worksheet | 352 | ||
| Creating Reports from Your Scenarios | 354 | ||
| Using Goal Seek | 355 | ||
| Solving Multiple-Variable Problems with Solver | 357 | ||
| Downloading and Installing Solver | 357 | ||
| Using Solver | 358 | ||
| Summary | 362 | ||
| Chapter 12 Analyzing Data with PivotTables | 363 | ||
| Understanding What PivotTables Are and What You Can Do with Them | 363 | ||
| Creating and Laying Out a PivotTable | 365 | ||
| Creating a PivotTable Automatically | 365 | ||
| Creating a PivotTable Manually | 367 | ||
| Understanding How the PivotTable Framework and PivotTable Builder Window Work | 369 | ||
| Adding the Fields to the PivotTable Framework | 370 | ||
| Changing the PivotTable to Show Different Data | 374 | ||
| Changing the Function Used to Summarize a Field | 378 | ||
| Controlling the Design of a PivotTable | 379 | ||
| Formatting a PivotTable | 381 | ||
| Applying a PivotTable Style | 381 | ||
| Choosing Options for a PivotTable Style | 381 | ||
| Naming a PivotTable and Setting Options for It | 382 | ||
| Renaming a PivotTable | 383 | ||
| Choosing Display Options for a PivotTable | 383 | ||
| Choosing Layout Options for a PivotTable | 384 | ||
| Choosing Data Options for a PivotTable | 386 | ||
| Refreshing the Data in a PivotTable | 387 | ||
| Changing the Source of a PivotTable | 388 | ||
| Sorting and Filtering a PivotTable | 388 | ||
| Summary | 391 | ||
| Chapter 13 Collaborating and Sharing with Macs and Windows PCs | 392 | ||
| Making Your Worksheets Print Correctly | 392 | ||
| Telling Excel Which Part of the Worksheet to Print | 393 | ||
| Checking the Page Layout and Where the Page Breaks Fall | 393 | ||
| Printing a Worksheet or Workbook | 395 | ||
| Sharing Your Worksheets as PDFs | 397 | ||
| Exporting Data to CSV Files | 399 | ||
| Documenting Your Workbooks | 400 | ||
| Adding Explanatory Text to Workbooks | 401 | ||
| Adding Comments to Cells | 401 | ||
| Adding a Comment | 401 | ||
| Viewing Comments | 402 | ||
| Deleting Comments | 403 | ||
| Adding Information with Data Validation | 403 | ||
| Sharing Your Workbooks with Your Colleagues | 404 | ||
| Protecting a Workbook or Some of Its Worksheets | 404 | ||
| Protecting a Workbook | 404 | ||
| Protecting a Worksheet | 405 | ||
| Tracking Changes to a Workbook | 406 | ||
| Sharing a Workbook So That Your Colleagues Can Edit It | 408 | ||
| Working in a Shared Workbook | 410 | ||
| Resolving Conflicts in a Shared Workbook | 411 | ||
| Reviewing Tracked Changes in a Shared Workbook | 411 | ||
| Merging Multiple Workbooks into a Single Workbook | 413 | ||
| Consolidating Multiple Worksheets into a Single Worksheet | 413 | ||
| Preparing to Consolidate Worksheets | 414 | ||
| Consolidating Worksheets by Their Position | 414 | ||
| Consolidating Worksheets by Category | 416 | ||
| Summary | 418 | ||
| Chapter 14 Automating Tasks with Macros and VBA | 419 | ||
| Understanding Your Options for Automating Tasks | 420 | ||
| Recording Macros | 421 | ||
| Recording an Example Macro | 425 | ||
| Running a Macro | 426 | ||
| Running a Macro from the Macro dialog Box | 427 | ||
| Running a Macro Using Its Keyboard Shortcut | 428 | ||
| Running a Macro from a Toolbar Button or Menu Item | 429 | ||
| Running a Macro from an Object in a Worksheet | 432 | ||
| Deleting a Macro | 433 | ||
| Editing Macros in the Visual Basic Editor | 433 | ||
| Opening the Sample Macro in the Visual Basic Editor | 433 | ||
| Meeting the Visual Basic Editor’s Interface | 434 | ||
| Examining the Sample Macro | 435 | ||
| Editing the Sample Macro | 437 | ||
| Testing the Macro | 437 | ||
| Saving the Changes | 437 | ||
| Returning from the Visual Basic Editor to Excel | 437 | ||
| Hiding the Personal Macro Workbook | 438 | ||
| Summary | 438 | ||
| Index | 439 |