Learn Excel 2016 for OS X, Second Edition

Table of contents

1. Cover
2. Title
3. Copyright
4. Contents at a Glance
5. Contents
6. About the Author
7. About the Technical Reviewer
8. Acknowledgments
9. Introduction
10. Part I: Becoming Proficient with Excel for Mac
    1. Chapter 1: Learning the Secrets of the Excel for Mac Interface
       1. Getting Ready to Learn Excel’s Secrets
       2. Three Ways to Control Excel
          1. Secrets of the Ribbon
          2. Using the Menu Bar
          3. Driving Excel with Keyboard Shortcuts
       3. Navigating Quickly Through Worksheets and Workbooks
          1. Elements of the Excel User Interface
          2. Navigating Among Worksheets
          3. Changing the Active Cell
          4. Selecting and Manipulating Cells
       4. Tools for Entering Text and Formulas Quickly
          1. Importing Data
          2. Connecting a Worksheet to External Data Sources
          3. Entering Text Using AutoCorrect
          4. Entering Text with AutoFill and Custom Lists
          5. Entering Text Using Copy, Paste, and Paste Options
          6. Entering Text with Find and Replace
          7. Inserting Symbols in a Document
       5. Viewing Your Workbooks
          1. Splitting the Window to View Separate Parts of a Worksheet
          2. Opening Extra Windows to Show Other Parts of a Workbook
          3. Changing the Window and Arranging Open Windows
          4. Zooming to Show the Data You Need to See
          5. Freezing Rows and Columns So They Stay Onscreen
       6. Using Custom Views
       7. Summary
    2. Chapter 2: Configuring Excel to Suit the Way You Work
       1. Opening the Excel Preferences Window
       2. Controlling How the Excel Window Appears
          1. Choosing Options in the Show in Workbook Area of View Preferences
          2. Choosing How to Display Comments
          3. Choosing How to Display Objects
          4. Choosing Ribbon Options
       3. Choosing Editing Options
          1. Choosing Options in the Edit Options Area
          2. Choosing Options in the Cut and Paste Options Area
          3. Choosing Options in the Date Options Area
       4. Setting Preferences for Creating and Saving Your Workbooks
          1. Creating Workbooks with the Number of Worksheets You Need
          2. Choosing the Default Format for Saving Workbooks
          3. Setting AutoRecover to Keep Backups of Your Workbooks for Safety
          4. Making Excel Prompt You to Enter Workbook Properties
       5. Creating Custom Keyboard Shortcuts
       6. Opening One or More Workbooks Automatically with Excel
       7. Summary
    3. Chapter 3: Creating Effective Workbooks and Templates
       1. Creating Workbooks from Scratch or from Templates
          1. Creating a New Blank Workbook
          2. Creating a New Workbook Based on a Template
          3. Creating a New Workbook Based on an Existing Workbook
          4. Saving a Workbook
          5. Protecting a Workbook with Passwords
          6. Entering Workbook Properties
          7. Finding Your Workbooks by Using Properties
       2. Saving Your Workbooks for Use with Older Versions of Excel
       3. Organizing the Worksheets in a Workbook
          1. Inserting a New Worksheet
          2. Naming a Worksheet
          3. Changing a Worksheet’s Tab Color
          4. Deleting a Worksheet
          5. Rearranging the Worksheets in a Workbook
       4. Four Easy Rules for Laying Out Your Worksheets
       5. Entering Data on Multiple Worksheets at Once
       6. Identifying Parts with Named Ranges
          1. Assigning a Name to a Cell or Range
          2. Creating Range Names Automatically
          3. Using a Range Name in Your Formulas
          4. Deleting a Range Name
          5. Changing the Cell or Range a Name Refers To
       7. Creating a Collapsible Worksheet by Outlining It
          1. Having Excel Create an Outline Automatically
          2. Changing the Settings for Outlining
          3. Creating an Outline Manually
          4. Expanding and Collapsing an Outline
          5. Updating the Outline After Adding or Deleting Rows or Columns
          6. Removing an Outline
       8. Making the Most of Templates
          1. Creating a Template Based on an Existing Workbook
          2. Saving a Template
       9. Summary
    4. Chapter 4: Formatting Your Worksheets Quickly and Efficiently
       1. Working with Rows and Columns
          1. Inserting and Deleting Rows, Columns, and Cells
          2. Setting Row Height
          3. Setting Column Width
          4. Hiding Rows and Columns
       2. Formatting Cells and Ranges
          1. Understanding the Two Main Tools for Applying Formatting
          2. Controlling How Data Appears by Applying Number Formatting
          3. Setting the Workbook’s Overall Look by Applying a Theme
          4. Choosing How to Align Cell Contents
          5. Choosing Font Formatting
          6. Applying Borders and Fills
          7. Applying Protection to Cells
       3. Using Paste Special to Paste Formatting and Perform Actions
       4. Identifying Unusual Values with Conditional Formatting
          1. Understanding Excel’s Preset Types of Conditional Formatting
          2. Applying a Preset Form of Conditional Formatting
          3. Creating Custom Conditional Formatting
          4. Changing the Order in Which Excel Applies Conditional Formatting Rules
          5. Clearing Conditional Formatting from a Cell, Range, or Worksheet
       5. Checking Input with Data Validation
       6. Formatting Quickly with Table Formatting and Styles
          1. Formatting with Table Formatting
          2. Formatting with Styles
       7. Adding Headers and Footers to Your Worksheets
       8. Summary
11. Part II: Performing Calculations and Presenting Data
    1. Chapter 5: Performing Custom Calculations with Formulas
       1. Understanding the Difference between Formulas and Functions
       2. Referring to Cells and Ranges in Formulas and Functions
          1. Referring to a Cell
          2. Referring to a Range
          3. Making One Row or Column Refer to another Row or Column
          4. Referring to Named Cells and Ranges
       3. Understanding the Components of Formulas
          1. Meet Excel’s Calculation Operators
       4. Creating Straightforward Formulas
       5. Creating Complex Formulas
          1. Understanding the Order in Which Excel Evaluates Operators
          2. Nesting Parts of a Formula to Control Operator Precedence
          3. Breaking up a Complex Formula into Separate Steps
       6. Entering Formulas Quickly by Copying and Using AutoFill
       7. Choosing Preferences for Error Checking
       8. Troubleshooting Common Problems with Formulas
       9. Summary
    2. Chapter 6: Using Excel’s Built-In Functions
       1. Understanding the Components of a Function
       2. Entering Functions in Your Worksheets
          1. Inserting Functions with the AutoSum Pop-up Menu
          2. Inserting Functions with the Formula Builder
          3. Inserting Functions with the Function Library Pop-up Menus
          4. Inserting Functions by Typing Them into a Worksheet
       3. Nesting One Function inside Another Function
       4. Meeting Excel’s Built-in Functions
          1. Database Functions
          2. Date and Time Functions
          3. Engineering Functions
          4. Financial Functions
          5. Logical Functions
          6. Information Functions
          7. Lookup and Reference Functions
          8. Mathematical and Trigonometric Functions
          9. Statistical Functions
          10. Text Functions
          11. Cube Functions
       5. Choosing the Right Calculation Preferences for Your Needs
          1. Choosing When to Calculate Worksheets
          2. Controlling Iteration of Calculations
          3. Choosing Workbook Options
       6. Summary
    3. Chapter 7: Creating Clear and Persuasive Charts
       1. Learning the Essentials of Charts in Excel
          1. Understanding Embedded Charts and Chart Sheets
          2. Understanding the Components of a Chart
       2. Choosing the Best Chart Type for Your Data
       3. Creating, Laying Out, and Formatting a Chart
          1. Creating a Chart
          2. Changing a Chart from an Embedded Chart to a Chart Sheet
          3. Changing the Chart Type
          4. Switching the Rows and Columns in a Chart
          5. Changing the Source Data for a Chart
          6. Choosing the Layout for the Chart
          7. Adding a Separate Data Series to a Chart
          8. Applying a Style to a Chart
          9. Adding a Title to a Chart
          10. Adding Axis Titles to the Chart
          11. Changing the Scale or Numbering of an Axis
          12. Adding a Legend to a Chart
          13. Adding Axis Labels from a Range Separate from the Chart Data
          14. Adding Data Labels to the Chart
          15. Choosing Which Gridlines to Display
          16. Formatting a Chart Wall and Chart Floor
          17. Formatting Individual Chart Elements
       4. Copying a Chart’s Formatting to Another Chart
       5. Reusing Your Own Designs by Creating Custom Chart Types
       6. Controlling Which Chart ScreenTips Excel Displays
       7. Using Your Charts in Word Documents and PowerPoint Presentations
          1. Understanding How You Can Add a Chart to a Document or Slide
       8. Summary
    4. Chapter 8: Using Data Bars, Color Scales, Icon Sets, and Sparklines
       1. Using Data Bars
          1. Creating Data Bars
       2. Using Color Scales
       3. Representing Data Graphically with Icon Sets
       4. Showing Data Trends with Sparklines
          1. Inserting Sparklines
          2. Formatting Your Sparklines
       5. Summary
    5. Chapter 9: Illustrating Your Worksheets with Pictures, SmartArt, and More
       1. Inserting Pictures in Your Workbooks
          1. Inserting Pictures from the Photos App
          2. Inserting Pictures from Your Mac’s File System
       2. Adding and Formatting a Shape
          1. Applying a Style to a Shape
       3. Rotating a Graphical Object
       4. Positioning a Graphical Object
       5. Making a Picture Look the Way You Want It
          1. Adjusting a Picture’s Sharpness, Brightness, Contrast, and Colors
          2. Applying a Picture Style
          3. Cropping a Picture
          4. Saving Space by Compressing Pictures
       6. Inserting SmartArt Diagrams
       7. Adding Decorative Text with WordArt
       8. Positioning Graphical Objects Relative to Cells
       9. Arranging Graphical Objects to Control Which Is Visible
       10. Summary
12. Part III: Analyzing Data and Sharing and Automating Workbooks
    1. Chapter 10: Creating Databases Using Tables
       1. Creating Databases in Excel
          1. Understanding What You Can and Can’t Do with Excel Tables
          2. Creating a Table and Entering Data
          3. Connecting a Table to an External Data Source
          4. Connecting to a Database
          5. Importing Data from a FileMaker Pro Database
          6. Resizing a Table
          7. Sorting a Table by One or More Fields
          8. Identifying and Removing Duplicate Records in a Table
          9. Filtering a Table
       2. Using Database Functions with Tables
       3. Summary
    2. Chapter 11: Solving Business Questions with What-If Analysis, Goal Seek, and Solver
       1. Assessing the Impact of Variables Using Data Tables
          1. Creating a Data Table with One Variable
          2. Creating a Data Table with Two Variables
       2. Examining Different Scenarios in a Worksheet
          1. Creating the Worksheet for Your Scenarios
          2. Opening the Scenario Manager Dialog Box
          3. Creating Scenarios
          4. Applying Protection to Your Scenarios
          5. Editing and Deleting Scenarios
          6. Switching Among Your Scenarios
          7. Merging Scenarios into a Single Worksheet
          8. Creating Reports from Your Scenarios
       3. Using Goal Seek
       4. Solving Multiple-Variable Problems with Solver
          1. Enabling the Solver Add-In
          2. Using Solver
       5. Summary
    3. Chapter 12: Analyzing Data with PivotTables
       1. Understanding What PivotTables Are and What You Can Do with Them
       2. Creating and Laying Out a PivotTable
          1. Creating a PivotTable Automatically
          2. Creating a PivotTable Manually
          3. Changing the PivotTable to Show Different Data
          4. Changing the Function Used to Summarize a Field
       3. Controlling the Design of a PivotTable
       4. Formatting a PivotTable
          1. Applying a PivotTable Style
          2. Choosing Options for a PivotTable Style
       5. Naming a PivotTable and Setting Options for It
          1. Renaming a PivotTable
          2. Choosing Display Options for a PivotTable
          3. Choosing Layout Options for a PivotTable
          4. Choosing Data Options for a PivotTable
          5. Refreshing the Data in a PivotTable
          6. Changing the Source of a PivotTable
       6. Sorting and Filtering a PivotTable
       7. Summary
    4. Chapter 13: Collaborating and Sharing with Macs and Windows PCs
       1. Making Your Worksheets Print Correctly
          1. Telling Excel Which Part of the Worksheet to Print
          2. Checking the Page Layout and Where the Page Breaks Fall
          3. Printing a Worksheet or Workbook
       2. Sharing Your Worksheets as PDFs
       3. Exporting Data to CSV Files
       4. Documenting Your Workbooks
          1. Adding Explanatory Text to Workbooks
          2. Adding Comments to Cells
          3. Adding Information with Data Validation
       5. Sharing Your Workbooks with Your Colleagues
          1. Protecting a Workbook or Some of Its Worksheets
          2. Tracking Changes to a Workbook
          3. Sharing a Workbook So That Your Colleagues Can Edit It
          4. Working in a Shared Workbook
          5. Resolving Conflicts in a Shared Workbook
          6. Reviewing Tracked Changes in a Shared Workbook
       6. Merging Multiple Workbooks into a Single Workbook
       7. Consolidating Multiple Worksheets into a Single Worksheet
          1. Preparing to Consolidate Worksheets
          2. Consolidating Worksheets by Their Position
          3. Consolidating Worksheets by Category
       8. Summary
13. Index