Contents

Introduction

Who Should Read This Book

How This Book Is Organized

Downloading the Example Files

Conventions Used In This Book

Special Elements

1 Understanding the Microsoft Excel Interface

Taking a Closer Look at the Excel Window

Customizing the Excel Window

Moving Around and Making Selections on a Sheet

Installing Optional Components

2 Working with Workbooks, Sheets, Rows, Columns, and Cells

Managing Workbooks

Using Templates to Quickly Create New Workbooks

Working with Sheets and Tabs

Working with Rows and Columns

Working with Cells

3 Getting Data onto a Sheet

Types of Data You Enter into Excel

Entering Different Types of Data into a Cell

Controlling the Next Cell Selection

Using Copy, Cut, Paste, Paste Special to Enter Data

Using Text to Columns to Separate Data in a Single Column

Inserting Symbols and Equations into a Cell

Using Web Queries to Get Data onto a Sheet

Using Series to Quickly Fill a Range

Editing Data

Working with Tables

Fixing Numbers Stored as Text

Spellchecking Your Sheet

Finding Data on Your Sheet

Using Data Validation to Limit Data Entry in a Cell

4 Formatting Sheets and Cells

Adjusting Row Heights and Column Widths

Changing the Font Settings of a Cell

Aligning Text in a Cell

Reflowing Text in a Paragraph

Applying Number Formats with Format Cells

Creating a Custom Format

When Cell Formatting Doesn’t Seem to Be Working Right

The Number Group on the Ribbon

Adding a Border Around a Range

Coloring the Inside of a Cell

Creating Hyperlinks

Quick Formatting with the Format Painter

Dynamic Cell Formatting with Conditional Formatting

Using Cell Styles to Quickly Apply Formatting

Using Themes to Ensure Uniformity in Design

5 Using Formulas

The Importance of Laying Out Data Properly

Adjusting Calculation Settings

Viewing Formulas Versus Values

Entering a Formula into a Cell

Three Ways of Entering a Formula’s Cell References

Relative Versus Absolute Formulas

Using a Cell on Another Sheet in a Formula

Using R1C1 Notation to Reference Cells

Using F4 to Change the Cell Referencing

Mathematical Operators

Copying a Formula to Another Cell

Using Names to Simplify References

Inserting Formulas into Tables

Using Table Names in Table Formulas

Writing Table Formulas Outside the Table

Using Array Formulas

Converting Formulas to Values

Troubleshooting Formulas

6 Using Functions

Breaking Down a Function

Finding Functions

Entering Functions Using the Function Arguments Dialog Box

Entering Functions Using In-Cell Tips

Using the AutoSum Button

Using Lookup Functions to Match a Value and Return Another

SUMIFS

SUMPRODUCT

Logical Functions

Nested IF Statements

IFERROR

Date and Time Functions

Troubleshooting Dates and Times Stored as Strings

Goal Seek

Using the Function Arguments Dialog Box to Troubleshoot Formulas

7 Sorting Data

Preparing Data

Opening the Sort Dialog Box

Sorting by Values

Sorting by Color or Icon

Using the Quick Sort Buttons

Quick Sorting Multiple Columns

Randomly Sorting Data

Sorting with a Custom Sequence

Rearranging Columns Using the Sort Dialog Box

Rearranging Columns Using the Mouse

Fixing Sort Problems

8 Filtering and Consolidating Data

Preparing Data

Applying a Filter to a Data Set

Clearing a Filter

Reapplying a Filter

Turning Filtering On for One Column

Filtering for Listed Items

Filtering the Grouped Dates Listing

Using the Search Function to Filter for or Exclude Items

Using the Search Function on Grouped Dates

Using Text, Number, and Date Special Filters

Filtering by Color or Icon

Filtering by Selection

Allowing Filtering on a Protected Sheet

Using the Advanced Filter Option

Removing Duplicates from a Data Set

Consolidating Data

9 Distributing and Printing a Workbook

Using Cell Comments to Add Notes to Cells

Allowing Multiple Users to Edit a Workbook at the Same Time

Hiding and Unhiding Sheets

Locking Rows or Columns in Place

Creating Custom Views of Your Data

Configuring the Page Setup

Creating a Custom Header or Footer

Using Page Break Preview to Set Page Breaks

Printing Sheets

Protecting Your Workbook from Unwanted Changes

Protecting the Data on a Sheet

Preventing Changes by Marking a File as Final

Restricting Access Using IRM

Certifying a Workbook with a Digital Signature

Sharing Files Between Excel Versions

Removing Hidden or Confidential Information

Recovering Lost Changes

Sending an Excel File as an Attachment

Sharing a File Online

10 Subtotals and Grouping

Using the SUBTOTAL Function

Summarizing Data Using the Subtotal Tool

Copying the Subtotals to a New Location

Formatting the Subtotals

Applying Different Subtotal Function Types

Combining Multiple Subtotal Results to One Row

Subtotaling by Multiple Columns

Sorting Subtotals

Adding Space Between Subtotaled Groups

Grouping and Outlining Rows and Columns

11 Creating Charts and Sparklines

Preparing Data

Elements of a Chart

Types of Charts

Adding a Chart to a Sheet

Adding, Removing, and Formatting Chart Elements

Editing and Formatting a Chart Title

Changing the Display Units in an Axis

Applying Chart Styles and Colors

Applying Chart Layouts

Moving or Resizing a Chart

Switching Rows and Columns

Changing an Existing Chart’s Type

Creating a Chart with Multiple Chart Types

Updating Chart Data

Creating Stock Charts

Creating Bubble Charts

Pie Chart Issue: Small Slices

Adding Sparklines to Data

Creating a Chart Using a User-Created Template

12 PivotTables and Slicers

Preparing Data for Use in a PivotTable

PivotTable Limitations

PivotTable Compatibility

PivotTable Field List

Creating a PivotTable

Changing How a PivotTable Appears on a Sheet

PivotTable Sorting

Expanding and Collapsing Fields

Viewing the Records Used to Calculate a Value

Grouping Dates

Filtering Data in a PivotTable

Creating a Calculated Field

Hiding Totals

Formatting Values

Slicers

13 Using Power View to Create Reports

Power View Requirements

Creating Reports

Inserting and Formatting a Report Title

Changing Data Visualizations

Combining Multiple Visualizations

Changing Colors

Sorting

Filtering

Sharing Power View Reports

14 Inserting SmartArt, WordArt, and Pictures

Working with SmartArt

Inserting WordArt

Inserting Pictures

15 An Introduction to Using Macros and UDFs

Enabling VBA Security

Developer Tab

Introduction to the Visual Basic Editor

Understanding How the Macro Recorder Works

Recording a Macro

Running a Macro

User-Defined Functions

16 Introducing the Excel Web App

Requirements

Acquiring a Microsoft Account

Uploading and Downloading Workbooks

Opening a Workbook

Creating a New Workbook Online

Saving a File to Your Local Drive

Sharing a Folder or Workbook

Interacting with a Sheet Online

Configuring Browser View Options

Setting Up a Survey

Index