Table of Contents

Introduction

What’s in This Book

Guidance for Beginners

1 Understanding the Microsoft Excel Interface

Identifying Parts of the Excel Window

Using the Built-in Help

Perform a Search

Making Selections from the Ribbon

Customizing the Ribbon

Minimize the Ribbon Size

Add More Commands to the Ribbon

Customizing the QAT

Move the QAT to a New Location

Add More Commands to the QAT

Viewing Multiple Sheets at the Same Time

Arrange Multiple Sheets

Scroll Two Sheets Side by Side

Changing the Zoom on a Sheet

Use Excel’s Zoom Controls

Moving Around on a Sheet

Keyboard Shortcuts for Quicker Navigation

Selecting a Range of Cells

Select a Range Using the Mouse

2 Working with Workbooks and Templates

Managing Workbooks

Create a New Workbook

Open an Existing Workbook

Use the Recent Workbooks List

Save a Workbook

Close a Workbook

Using Templates to Quickly Create New Workbooks

Use Microsoft’s Online Templates

Save a Template

Open a Locally Saved Template to Enter Data

Edit the Design of a Locally Saved Template

Change Personal Templates Location

3 Working with Sheets

Adding and Deleting Sheets

Add a New Sheet

Delete a Sheet

Navigating and Selecting Sheets

Activate Another Sheet

Select Multiple Sheets

Moving or Copying Sheets

Move or Copy a Sheet in the Same Workbook

Move or Copy a Sheet Between Workbooks

Renaming a Sheet

Change a Sheet’s Name

4 Getting Data onto a Sheet

Entering Different Types of Data into a Cell

Type Numbers or Text into a Cell

Enter Numbers as Text

Type Dates and Times into a Cell

Undo an Entry

Using Lists to Quickly Fill a Range

Extend a Series Containing Text

Extend a Numerical Series

Create Your Own List

Using Paste Special

Paste Values Only

Combine Multiple Paste Special Options

Multiply the Range by a Specific Value

Use Paste to Merge a Noncontiguous Selection

Using Text to Columns to Separate Data in a Single Column

Work with Delimited Text

Using Data Validation to Limit Data Entry in a Cell

Limit User Entry to a Selection from a List

Using Web Queries to Get Data onto a Sheet

Insert a Web Query

Editing Data

Modify Cell Data

Clearing the Contents of a Cell

Clear Only Data from a Cell

Clearing an Entire Sheet

Clear an Entire Sheet

Working with Tables

Define a Table

Add a Total Row to a Table

Change the Total Row Function

Expand a Table

Fixing Numbers Stored as Text

Use Convert to Number on Multiple Cells

Use Paste Special to Force a Number

Spell Checking a Sheet

Finding Data on a Sheet

Perform a Search

Perform a Wildcard Search

Replace Data on a Sheet

5 Selecting and Moving Data on a Sheet

Working with Rows and Columns

Select a Row or Column

Insert a New Row or Column

Delete a Row or Column

Move Rows or Columns by Dragging

Move Rows or Columns by Cutting

Copy Rows or Columns

Working with Cells

Select a Cell Using the Name Box

Select Noncontiguous Cells and Ranges

Insert Cells

Delete Cells

Move Cells

6 Formatting Sheets and Cells

Changing the Font Settings of a Cell

Select a New Font Typeface

Increase and Decrease the Font Size

Apply Bold, Italic, and Underline to Text

Apply Strikethrough, Superscript, and Subscript

Change the Font Color

Format a Character or Word in a Cell

Format Quickly with the Format Painter

Adjusting the Row Height

Modify the Row Height by Dragging

Modify the Row Height by Entering a Value

Use Font Size to Automatically Adjust the Row Height

Adjusting the Column Width

Modify the Column Width by Dragging

Modify the Column Width by Entering a Value

Aligning Text in a Cell

Change Text Alignment

Merging Two or More Cells

Merge and Center Data

Merge Across Columns

Unmerge Cells

Centering Text Across Multiple Cells

Center Text Without Merging

Wrapping Text in a Cell to the Next Line

Wrap Text in a Cell

Reflowing Text in a Paragraph

Fit Text to a Specific Range

Indenting Cell Contents

Indent Data

Applying Number Formats

Modify the Number Format

Change the Format of Negative Numbers

Apply a Currency Symbol

Format Dates and Times

Format as Percentage

Format as Text

Apply the Special Number Format

Adding a Border Around a Range

Format a Range with a Thick Outer Border and Thin Inner Lines

Add a Colored Border

Coloring the Inside of a Cell

Apply a Two-Color Gradient to a Cell

7 Advanced Formatting

Creating Custom Number Formats

The Four Sections of a Custom Number Format

Optional Versus Required Digits

Use the Thousands Separator, Color Codes, and Text

Line Up Decimals

Fill Leading and Trailing Spaces

Show More Than 24 Hours in a Time Format

Creating Hyperlinks

Create a Hyperlink to Another Sheet

Link to a Web Page

Dynamic Cell Formatting with Conditional Formatting

Use Icons to Mark Data

Highlight the Top 10

Highlight Duplicate or Unique Values

Create a Custom Rule

Clear Conditional Formatting

Edit Conditional Formatting

Using Cell Styles to Apply Cell Formatting

Apply a Style

Create a Custom Style

Using Themes to Ensure Uniformity in Design

Apply a New Theme

Create a New Theme

Share a Theme

8 Using Formulas

Entering a Formula into a Cell

Calculate a Formula

View All Formulas on a Sheet

Relative Versus Absolute Referencing

Lock the Row When Copying a Formula Down

Copying Formulas

Copy and Paste Formulas

Copy by Dragging the Fill Handle

Copy Rapidly Down a Column

Copy Between Workbooks Without Creating a Link

Converting Formulas to Values

Paste as Values

Select and Drag

Using Names to Simplify References

Create a Named Cell

Use a Name in a Formula

Inserting Formulas into Tables

Write a Formula in a Table

Write Table Formulas Outside the Table

Using Array Formulas

Enter an Array Formula

Delete a Multicell Array Formula

Working with Links

Control the Prompt

Refresh Data

Change the Source Workbook

Break the Link

Troubleshooting Formulas

Fix ###### in a Cell

Understand a Formula Error

Use Trace Precedents and Dependents

Track Formulas on Other Sheets with Watch Window

Use the Evaluate Formula Dialog Box

Evaluate with F9

Adjusting Calculation Settings

Set Calculations to Manual

9 Using Functions

Understanding Functions

Look Up Functions

Use the Function Arguments Dialog Box

Enter Functions Using Formula Tips

Using the AutoSum Button

Calculate a Single Range

Sum Rows and Columns at the Same Time

Quick Calculations

Calculate Results Quickly

Using Quick Analysis Functions

Using Lookup Functions

Use CHOOSE to Return the nth Value from a List

Use VLOOKUP to Return a Value from a Table

Use INDEX and MATCH to Return a Value from the Left

Using SUMIFS to Sum Based on Multiple Criteria

Sum a Column Based on Two Criteria

Using IF Statements

Compare Two Values

Hiding Errors with IFERROR

Hide a #DIV/0! Error

Understanding Dates and Times

Return a New Date X Workdays from Date

Calculate the Number of Days Between Dates

Using Goal Seek

Calculate the Best Payment

Using the Function Arguments Dialog Box to Troubleshoot Formulas

Narrow Down a Formula Error

10 Sorting Data

Using the Sort Dialog Box

Sort by Values

Sort by Color or Icon

Doing Quick Sorts

Quick Sort a Single Column

Quick Sort Multiple Columns

Performing Custom Sorts

Perform a Random Sort

Sort with a Custom Sequence

Rearranging Columns

Sort Columns with the Sort Dialog Box

Fixing Sort Problems

11 Filtering and Consolidating Data

Using the Filter Tool

Apply a Filter

Clear a Filter

Reapply a Filter

Turn the Filter On for One Column

Filtering Grouped Dates

Turn On Grouped Dates

Filter by Date

Using Special Filters

Filter for Items that Include a Specific Term

Filter for Values Within a Range

Filter for the Top 25 Items

Filter Dates by Quarter

Filtering by Color or Icon

Filtering by Selection

Allowing Users to Filter a Protected Sheet

Filter a Protected Sheet

Using the Advanced Filter

Reorganize Columns

Create a List of Unique Items

Filter Records Using Criteria

Use Formulas as Criteria

Removing Duplicates

Delete Duplicate Rows

Consolidating Data

Merge Values from Two Datasets

Merge Data Based on Matching Labels

12 Distributing and Printing a Workbook

Using Cell Comments to Add Notes to Cells

Insert a New Cell Comment

Edit a Cell Comment

Format a Cell Comment

Insert an Image into a Cell Comment

Resize a Cell Comment

Show and Hide Cell Comments

Delete a Cell Comment

Allowing Multiple Users to Edit a Workbook at the Same Time

Share a Workbook

Hiding and Unhiding Sheets

Hide a Sheet

Unhide a Sheet

Using Freeze Panes

Lock the Top Row

Lock Multiple Rows and Columns

Configuring the Page Setup

Set Paper Size, Margins, and Orientation

Set the Print Area

Set Page Breaks

Scale the Data to Fit a Printed Page

Repeat Specific Rows on Each Printed Page

Creating a Custom Header or Footer

Add an Image to the Header or Footer

Add Page Numbering to the Header and Footer

Printing Sheets

Configure Print Options

Protecting a Workbook from Unwanted Changes

Set File-Level Protection

Set Workbook-Level Protection

Protecting the Data on a Sheet

Protect a Sheet

Unlock Cells

Allow Users to Edit Specific Ranges

Preventing Changes by Marking a File as Final

Mark a Workbook as Final

Sharing Files Between Excel Versions

Check Version Compatibility

Recovering Lost Changes

Configure Backups

Recover a Backup

Recover Unsaved Files

Sending an Excel File as an Attachment

Email a Workbook

Sharing a File Online

Save to OneDrive

13 Inserting Subtotals and Grouping Data

Using the SUBTOTAL Function

Calculate Visible Rows

Summarizing Data Using the Subtotal Tool

Apply a Subtotal

Expand and Collapse Subtotals

Remove Subtotals or Groups

Sort Subtotals

Copying the Subtotals to a New Location

Copy Subtotals

Applying Different Subtotal Function Types

Create Multiple Subtotal Results on Multiple Rows

Combine Multiple Subtotal Results to One Row

Adding Space Between Subtotaled Groups

Separate Subtotaled Groups for Print

Separate Subtotaled Groups for Distributed Files

Grouping and Outlining Rows and Columns

Apply Auto Outline

Group Data Manually

14 Creating Charts and Sparklines

Adding a Chart

Add a Chart with the Quick Analysis Tool

Preview All Charts

Switch Rows and Columns

Apply Chart Styles or Colors

Apply Chart Layouts

Resizing or Moving a Chart

Resize a Chart

Move to a New Location on the Same Sheet

Relocate to Another Sheet

Editing Chart Elements

Use the Format Task Pane

Edit the Chart or Axis Titles

Change the Display Units in an Axis

Customize a Series Color

Changing an Existing Chart’s Type

Change the Chart Type

Creating a Chart with Multiple Chart Types

Insert a Multiple Type Chart

Add a Secondary Axis

Updating Chart Data

Change the Data Source

Adding Special Charts

Create a Stock Chart

Create a Bubble Chart

Pie Chart Issue: Small Slices

Rotate the Pie

Create a Bar of Pie Chart

Using a User-Created Template

Save a Chart Template

Use a Chart Template

Adding Sparklines to Data

Insert a Sparkline

Emphasize Points on a Sparkline

Space Markers by Date

Delete Sparklines

15 Summarizing Data with PivotTables

Creating a PivotTable

Use the Quick Analysis Tool

Create a PivotTable from Scratch

Change the Calculation Type of a Field Value

Format Values

Changing the PivotTable Layout

Choose a New Layout

PivotTable Sorting

Click and Drag

Use Quick Sort

Expanding and Collapsing Fields

Expand and Collapse a Field

Grouping Dates

Group by Week

Group by Month and Year

Filtering Data in a PivotTable

Filter for Listed Items

Clear a Filter

Creating a Calculated Field

Add a Calculated Field

Hiding Totals

Hide Totals

Hide Subtotals

Viewing the Records Used to Calculate a Value

Unlinking PivotTables

Unlink a PivotTable Report

Refreshing the PivotTable

Refresh on Open

Refresh After Adding New Data

Refresh After Editing the Data Source

Working with Slicers

Create a Slicer

Use a Slicer

16 Inserting SmartArt, WordArt, and Pictures

Working with SmartArt

Insert a SmartArt Graphic

Insert Images into SmartArt

Move and Resize SmartArt

Reorder Placeholders

Change the Layout

Change an Individual Shape

Working with WordArt

Insert WordArt

Inserting Pictures

Insert a Picture

Resize and Crop a Picture

Apply Corrections, Color, and Artistic Effects

Reduce a File’s Size

17 Introducing the Excel Web App

Acquiring a Microsoft Account

Create an Account

Uploading a Workbook

Upload Through OneDrive

Save from Excel

Delete a File from OneDrive

Opening a Workbook Online or Locally

Open a Workbook

Download a Workbook

Creating a New Workbook Online

Create a Workbook

Rename the New Workbook

Sharing a Folder or Workbook

Create a View-Only Folder

Remove Sharing

Edit Simultaneously

Configuring Browser View Options

Create an Online Form

Designing a Survey Through the Web App

Create a Survey

Index