Contents

Chapter 1. Working with the Excel User Interface

The Ribbon User Interface

The Anatomy of the Ribbon Interface

Familiarizing Yourself with the Ribbon Tabs

Understanding Contextual Tabs

Understanding Workbooks and Worksheets

Customizing the Quick Access Toolbar

Adding Commands to the Quick Access Toolbar

Adding Hidden Commands to the Quick Access Toolbar

Chapter 2. Managing Workbooks and Worksheets

Opening an Excel Workbook

Closing an Excel Workbook

Saving a Workbook

Switching Between Open Workbooks

Switching Between Worksheets

Viewing Multiple Workbooks

Inserting and Deleting Worksheets

Renaming Worksheets

Coloring Worksheet Tabs

Moving Worksheets Within a Workbook

Copying Worksheets Between Workbooks

Password Protect a Workbook

Protecting a Worksheet

Chapter 3. Entering and Managing Data

Entering Data

Editing and Deleting Existing Data

Zooming Into Your Data

Undoing and Redoing Changes

Copying and Pasting Data

Freezing Rows and Columns

Splitting a Worksheet

Inserting Cells

Deleting Cells

Inserting and Deleting Rows

Inserting and Deleting Columns

Moving Data

Finding Data

Replacing Data

Applying a Data Filter

Sorting Data

Adding and Managing Cell Comments

Chapter 4. Formatting Worksheet Data

Changing the Font and Font Size

Changing Column Width

Changing the Color of the Cell Background and Cell Text

Formatting the Display of Numeric Data

Using a General Format

Using a Number Format

Using a Currency Format

Using a Date Format

Using a Text Format

Applying Bold, Italic, and Underline

Using Merge and Center on Cells

Changing Horizontal Data Alignment

Changing Row Height

Changing Vertical Data Alignment

Changing Cell Orientation

Wrapping Data in a Cell

Changing Borders

Indenting Entries in a Cell

Clearing Formatting

Hiding and Unhiding Rows

Hiding and Unhiding Columns

Hiding and Unhiding a Worksheet

Using Format As Table

Copying Formatting

Creating and Applying a Formatting Style

Using Conditional Formatting

Chapter 5. Working with Formulas and Functions

Using AutoSum (SUM)

Finding a Cell Average (AVERAGE)

Finding the Largest Cell Amount (MAX)

Finding the Smallest Cell Amount (MIN)

Counting the Number of Cells (COUNT)

Entering a Formula

Editing a Formula or Function

Copying a Formula

Assigning Names to a Cell or Range

Referencing Names in a Function

Using Functions Across Worksheets

Using Auto-Calculate

Finding Functions

Calculating a Loan Payment (PMT)

Performing a Logical Test Function (IF)

Conditionally Summing a Range (SUMIF)

Finding the Future Value of an Investment (FV)

Fixing the #### Error

Fixing the #DIV/0! Error

Fixing the #NAME? Error

Fixing the #VALUE! Error

Recognizing the #REF! Error

Recognizing Circular References

Checking for Formula References (Precedents)

Checking for Cell References (Dependents)

Chapter 6. Working with Charts

The Chart Tools Contextual Tab

Creating a Chart

Changing the Chart Type

Altering the Source Data Range

Altering Chart Options

Formatting the Plot Area

Formatting the Chart Area

Formatting the Axis Scale

Altering the Original Data

Adding Data to Charts

Adding a Legend

Chapter 7. Working with Graphics

Using Drawing Tools

Inserting Clip Art

Inserting a Picture from File

Using AutoShapes

Inserting WordArt

Using Smart Art in Excel

Inserting a Diagram

Inserting an Object

Selecting an Object

Formatting an Object

Moving an Object

Resizing an Object

Deleting an Object

Chapter 8. Printing in Excel

Using Print Preview

Setting the Print Area

Adjusting Page Margins

Inserting Page Breaks

Working in Page Break Preview Mode

Printing a Worksheet on One Page

Printing in Portrait or Landscape Orientation

Centering a Worksheet on a Page

Printing Gridlines and Row/Column Headers

Printing Cell Comments

Printing Cell Error Indicators

Printing Repeating Row and Column Titles

Adding Headers and Footers

Printing Worksheets

Chapter 9. Working with Excel Pivot Tables

Creating a Pivot Table

Rearranging a Pivot Table

Refreshing Pivot Table Data

Adding a Report Filter

Adding Pivot Table Data

Changing the Pivot Table Layout

Customizing Field Names

Applying Numeric Formats to Data Fields

Changing Summary Calculations

Showing and Hiding Data Items

Sorting Your Pivot Table

Appendix. Excel Shortcut Keys

Index