Contents
Part I: Becoming Proficient with Excel:Mac
Chapter 1: Learning the Secrets of the Excel:Mac Interface
Getting Ready to Learn Excel's Secrets
Understanding How the Ribbon's Tabs Work
Understanding How the Ribbon's Groups and Controls Work
Choosing Which Toolbars to Display
Switching the Standard Toolbar Between Icons and Text and Icons Only
Undocking and Docking the Formatting Toolbar
Driving Excel with Keyboard Shortcuts
Making the Toolbox Work Your Way
Navigating Quickly Through Worksheets and Workbooks
Elements of the Excel User Interface
Selecting and Manipulating Cells
Tools for Entering Text and Formulas Quickly
Connecting a Worksheet to External Data Sources
Entering Text Using AutoCorrect
Entering Text with AutoFill and Custom Lists
Entering Text Using Paste and Paste Options
Pasting in Multiple Items with the Scrapbook
Entering Text with Find and Replace
Inserting Symbols in a Document
Splitting the Window to View Separate Parts of a Worksheet
Opening Extra Windows to Show Other Parts of a Workbook
Changing the Window and Arranging Open Windows
Zooming to Show the Data You Need to See
Freezing Rows and Columns So They Stay Onscreen
Chapter 2: Configuring Excel:Mac to Suit the Way You Work
Opening the Excel Preferences Dialog Box
Controlling How the Excel Window Appears
Choosing Options in the Settings Area of View Preferences
Choosing How to Display Comments
Choosing How to Display Objects
Setting Preferences for Creating and Saving Your Workbooks
Creating Workbooks with the Number of Worksheets You Need
Choosing the Default Folder for Opening and Saving Workbooks
Choosing the Default Format for Saving Workbooks
Setting AutoRecover to Keep Backups of Your Workbooks for Safety
Making Excel Prompt You to Enter Workbook Properties
Creating Custom Keyboard Shortcuts
Customizing the Toolbars with the Commands You Need
Getting Ready to Customize the Toolbars
Finding the Commands to Add to the Toolbars or Menus
Removing an Item from a Toolbar
Repositioning Items on a Toolbar
Changing How a Toolbar Button Appears
Adding a Built-in Menu to a Toolbar
Customizing the Menus and the Menu Bar
Turning Off or Customizing the Ribbon
Turning the Ribbon Off So It Doesn't Appear
Opening One or More Workbooks Automatically with Excel
Saving the Layout of Open Workbooks as a Workspace
Chapter 3: Creating Effective Workbooks and Templates
Creating Workbooks from Scratch or from Templates
Creating a New Workbook Based on a Template
Creating a New Workbook Based on an Existing Workbook
Protecting a Workbook with Automatic Backups and Passwords
Finding Your Workbooks by Using Properties
Saving Your Workbooks for Use with Older Versions of Excel
Organizing the Worksheets in a Workbook
Changing a Worksheet's Tab Color
Rearranging the Worksheets in a Workbook
Four Easy Rules for Laying Out Your Worksheets
Entering Data on Multiple Worksheets at Once
Identifying Parts with Named Ranges
Assigning a Name to a Cell or Range
Creating Range Names Automatically
Using a Range Name in Your Formulas
Changing the Cell or Range a Name Refers To
Creating a Collapsible Worksheet by Outlining It
Having Excel Create an Outline Automatically
Changing the Settings for Outlining
Expanding and Collapsing an Outline
Updating the Outline After Adding or Deleting Rows or Columns
Creating a Template Based on an Existing Workbook
Chapter 4: Formatting Your Worksheets Quickly and Efficiently
Inserting and Deleting Rows, Columns, and Cells
Understanding the Three Main Tools for Applying Formatting
Controlling How Data Appears by Applying Number Formatting
Setting the Workbook's Overall Look by Applying a Theme
Choosing How to Align Cell Contents
Using Paste Special to Paste Formatting and Perform Actions
Identifying Unusual Values with Conditional Formatting
Understanding Excel's Preset Types of Conditional Formatting
Applying a Preset Form of Conditional Formatting
Creating Custom Conditional Formatting
Changing the Order in Which Excel Applies Conditional Formatting Rules
Clearing Conditional Formatting from a Cell, Range, or Worksheet
Checking Input with Data Validation
Formatting Quickly with Table Formatting and Styles
Formatting with Table Formatting
Adding Headers and Footers to Your Worksheets
Adding Headers and Footers Using the Page Setup Dialog Box
Adding Headers and Footers Directly on the Worksheet
Part II: Performing Calculations and Presenting Data
Chapter 5: Performing Custom Calculations with Formulas
Understanding the Difference Between Formulas and Functions
Referring to Cells and Ranges in Formulas and Functions
Making One Row or Column Refer to Another Row or Column
Referring to Named Cells and Ranges
Understanding the Components of Formulas
Meeting Excel's Calculation Operators
Creating Straightforward Formulas
Understanding the Order in Which Excel Evaluates Operators
Nesting Parts of a Formula to Control Operator Precedence
Breaking Up a Complex Formula into Separate Steps
Entering Formulas Quickly by Copying and Using AutoFill
Choosing Preferences for Error Checking
Troubleshooting Common Problems with Formulas
Chapter 6: Using Excel's Built-In Functions
Understanding the Components of a Function
Entering Functions in Your Worksheets
Inserting Functions with the AutoSum Pop-up Menu
Inserting Functions with the Formula Builder
Inserting Functions with the Insert Pop-up Menu
Inserting Functions by Typing Them into a Worksheet
Nesting One Function Inside Another Function
Meeting Excel's Built-in Functions
Lookup and Reference Functions
Mathematical and Trigonometric Functions
Choosing the Right Calculation Preferences for Your Needs
Choosing When to Calculate Worksheets
Controlling Iteration of Calculations
Chapter 7: Creating Clear and Persuasive Charts
Learning the Essentials of Charts in Excel
Understanding Embedded Charts and Chart Sheets
Understanding the Components of a Chart
Choosing the Best Chart Type for Your Data
Creating, Laying Out, and Formatting a Chart
Changing a Chart from an Embedded Chart to a Chart Sheet
Switching the Rows and Columns in a Chart
Changing the Source Data for a Chart
Choosing the Layout for the Chart
Adding a Separate Data Series to a Chart
Adding Axis Titles to the Chart
Changing the Scale or Numbering of an Axis
Adding Axis Labels from a Range Separate from the Chart Data
Adding Data Labels to the Chart
Choosing Which Gridlines to Display
Formatting a Chart Wall and Chart Floor
Formatting Individual Chart Elements
Copying a Chart's Formatting to Another Chart
Reusing Your Own Designs by Creating Custom Chart Types
Using Your Charts in Word Documents and PowerPoint Presentations
Understanding How You Can Add a Chart to a Document or Slide
Chapter 8: Using Data Bars, Color Scales, Icon Sets, and Sparklines
Representing Data Graphically with Icon Sets
Showing Data Trends with Sparklines
Chapter 9: Illustrating Your Worksheets with Pictures, SmartArt, and More
Inserting a Clip Art Picture Using the Clip Art Browser
Inserting a Clip Art Picture Using the Clip Gallery
Managing Your Clip Art Items with the Clip Gallery
Inserting Pictures in Your Workbooks
Inserting Pictures from iPhoto
Inserting Pictures from Your Mac's File System
Positioning a Graphical Object
Making a Picture Look the Way You Want It
Adjusting a Picture's Sharpness, Brightness, Contrast, and Colors
Saving Space by Compressing Pictures
Adding Decorative Text with WordArt
Positioning Graphical Objects Relative to Cells
Arranging Graphical Objects to Control Which Is Visible
Part III: Analyzing Data and Sharing and Automating Workbooks
Chapter 10: Creating Databases Using Tables
Understanding What You Can and Can't Do with Excel Tables
Creating a Table and Entering Data
Connecting a Table to an External Data Source
Importing Data from a FileMaker Pro Database
Sorting a Table by One or More Fields
Identifying and Removing Duplicate Records in a Table
Using Database Functions with Tables
Chapter 11: Solving Business Questions with What-If Analysis, Goal Seek, and Solver
Assessing the Impact of Variables Using Data Tables
Creating a Data Table with One Variable
Creating a Data Table with Two Variables
Examining Different Scenarios in a Worksheet
Creating the Worksheet for Your Scenarios
Opening the Scenario Manager Dialog Box
Applying Protection to Your Scenarios
Editing and Deleting Scenarios
Switching Among Your Scenarios
Merging Scenarios into a Single Worksheet
Creating Reports from Your Scenarios
Solving Multiple-Variable Problems with Solver
Downloading and Installing Solver
Chapter 12: Analyzing Data with PivotTables
Understanding What PivotTables Are and What You Can Do with Them
Creating and Laying Out a PivotTable
Creating a PivotTable Automatically
Creating a PivotTable Manually
Changing the PivotTable to Show Different Data
Changing the Function Used to Summarize a Field
Controlling the Design of a PivotTable
Choosing Options for a PivotTable Style
Naming a PivotTable and Setting Options for It
Choosing Display Options for a PivotTable
Choosing Layout Options for a PivotTable
Choosing Data Options for a PivotTable
Refreshing the Data in a PivotTable
Changing the Source of a PivotTable
Sorting and Filtering a PivotTable
Chapter 13: Collaborating and Sharing with Macs and Windows PCs
Making Your Worksheets Print Correctly
Telling Excel Which Part of the Worksheet to Print
Checking the Page Layout and Where the Page Breaks Fall
Printing a Worksheet or Workbook
Sharing Your Worksheets as PDFs
Adding Explanatory Text to Workbooks
Adding Information with Data Validation
Sharing Your Workbooks with Your Colleagues
Protecting a Workbook or Some of Its Worksheets
Tracking Changes to a Workbook
Sharing a Workbook So That Your Colleagues Can Edit It
Resolving Conflicts in a Shared Workbook
Reviewing Tracked Changes in a Shared Workbook
Merging Multiple Workbooks into a Single Workbook
Consolidating Multiple Worksheets into a Single Worksheet
Preparing to Consolidate Worksheets
Consolidating Worksheets by Their Position
Consolidating Worksheets by Category
Chapter 14: Automating Tasks with Macros and VBA
Understanding Your Options for Automating Tasks
Running a Macro from the Macro dialog Box
Running a Macro Using Its Keyboard Shortcut
Running a Macro from a Toolbar Button or Menu Item
Running a Macro from an Object in a Worksheet
Editing Macros in the Visual Basic Editor
Opening the Sample Macro in the Visual Basic Editor
Meeting the Visual Basic Editor's Interface
Returning from the Visual Basic Editor to Excel