Table of Contents
Part I: Getting Started with Excel
Identifying What Excel Is Good For
Seeing What's New in Excel 2013
Understanding Workbooks and Worksheets
Types of commands on the Ribbon
Accessing the Ribbon by using your keyboard
Customizing Your Quick Access Toolbar
Creating Your First Excel Workbook
Getting started on your worksheet
Making your worksheet look a bit fancier
Chapter 2: Entering and Editing Worksheet Data
Entering Text and Values into Your Worksheets
Entering Dates and Times into Your Worksheets
Deleting the contents of a cell
Replacing the contents of a cell
Editing the contents of a cell
Learning some handy data-entry techniques
Using automatic number formatting
Formatting numbers by using the Ribbon
Using shortcut keys to format numbers
Formatting numbers using the Format Cells dialog box
Adding your own custom number formats
Chapter 3: Essential Worksheet Operations
Learning the Fundamentals of Excel Worksheets
Adding a new worksheet to your workbook
Deleting a worksheet you no longer need
Changing the name of a worksheet
Hiding and unhiding a worksheet
Controlling the Worksheet View
Zooming in or out for a better view
Viewing a worksheet in multiple windows
Splitting the worksheet window into panes
Keeping the titles in view by freezing panes
Monitoring cells with a Watch Window
Changing column widths and row heights
Chapter 4: Working with Cells and Ranges
Understanding Cells and Ranges
Selecting complete rows and columns
Selecting noncontiguous ranges
Selecting special types of cells
Copying by using Ribbon commands
Copying by using shortcut menu commands
Copying by using shortcut keys
Copying or moving by using drag-and-drop
Copying a range to other sheets
Using the Office Clipboard to paste
Using the Paste Special dialog box
Using Names to Work with Ranges
Creating range names in your workbooks
Removing duplicate rows from a table
Converting a table back to a range
Chapter 6: Worksheet Formatting
Getting to Know the Formatting Tools
Using the formatting tools on the Home tab
Using the Format Cells dialog box
Using Different Fonts to Format Your Worksheet
Choosing horizontal alignment options
Choosing vertical alignment options
Wrapping or shrinking text to fit the cell
Merging worksheet cells to create additional text space
Controlling the text direction
Adding a Background Image to a Worksheet
Using Named Styles for Easier Formatting
Merging styles from other workbooks
Controlling styles with templates
Chapter 7: Understanding Excel Files
Choosing your file display preferences
Recovering versions of the current workbook
Password-Protecting a Workbook
Recognizing the Excel 2013 file formats
Saving a file for use with an older version of Excel
Chapter 8: Using and Creating Templates
Creating a workbook from a template
Understanding Custom Excel Templates
Working with the default templates
Adjusting Common Page Setup Settings
Specifying what you want to print
Printing multiple copies of a report
Printing row and column titles
Printing row and column headers
Adding a Header or Footer to Your Reports
Selecting a predefined header or footer
Understanding header and footer element codes
Other header and footer options
Copying Page Setup Settings across Sheets
Preventing Certain Cells from Being Printed
Preventing Objects from Being Printed
Creating Custom Views of Your Worksheet
Part II: Working with Formulas and Functions
Chapter 10: Introducing Formulas and Functions
Understanding operator precedence in formulas
Using functions in your formulas
Entering Formulas into Your Worksheets
Pasting range names into formulas
Inserting functions into formulas
Using Cell References in Formulas
Using relative, absolute, and mixed references
Changing the types of your references
Referencing cells outside the worksheet
Correcting Common Formula Errors
Specifying when formulas are calculated
Using Advanced Naming Techniques
Applying names to existing references
Using the Formula bar as a calculator
Making an exact copy of a formula
Chapter 11: Creating Formulas That Manipulate Text
Determining whether two strings are identical
Displaying formatted values as text
Displaying formatted currency values as text
Repeating a character or string
Removing excess spaces and nonprinting characters
Counting characters in a string
Extracting characters from a string
Replacing text with other text
Finding and searching within a string
Searching and replacing within a string
Counting specific characters in a cell
Counting the occurrences of a substring in a cell
Extracting the first word of a string
Extracting the last word of a string
Extracting all but the first word of a string
Extracting first names, middle names, and last names
Counting the number of words in a cell
Chapter 12: Working with Dates and Times
How Excel Handles Dates and Times
Understanding date serial numbers
Understanding time serial numbers
Date-Related Worksheet Functions
Converting a nondate string to a date
Calculating the number of days between two dates
Calculating the number of workdays between two dates
Offsetting a date using only workdays
Calculating the number of years between two dates
Determining the day of the year
Determining the day of the week
Determining the week of the year
Determining the date of the most recent Sunday
Determining the first day of the week after a date
Determining the nth occurrence of a day of the week in a month
Determining the last day of a month
Determining whether a year is a leap year
Calculating the difference between two times
Summing times that exceed 24 hours
Converting decimal hours, minutes, or seconds to a time
Adding hours, minutes, or seconds to a time
Working with non-time-of-day values
Chapter 13: Creating Formulas That Count and Sum
Counting and Summing Worksheet Cells
Counting the total number of cells
Counting error values in a range
Counting cells by using the COUNTIF function
Counting cells based on multiple criteria
Counting the most frequently occurring entry
Counting the occurrences of specific text
Counting the number of unique values
Creating a frequency distribution
Conditional Sums Using a Single Criterion
Summing values based on a different range
Summing values based on a text comparison
Summing values based on a date comparison
Conditional Sums Using Multiple Criteria
Chapter 14: Creating Formulas That Look Up Values
Combining the MATCH and INDEX functions
Looking up a value to the left
Performing a case-sensitive lookup
Looking up a value from multiple lookup tables
Determining letter grades for test scores
Calculating a grade-point average
Performing a two-column lookup
Determining the cell address of a value within a range
Looking up a value by using the closest match
Chapter 15: Creating Formulas for Financial Applications
Worksheet functions for calculating loan information
Creating a loan amortization schedule
Summarizing loan options by using a data table
Calculating a loan with irregular payments
Future value of a single deposit
Future value of a series of deposits
Chapter 16: Miscellaneous Calculations
Area, Surface, Circumference, and Volume Calculations
Calculating the area and perimeter of a square
Calculating the area and perimeter of a rectangle
Calculating the area and perimeter of a circle
Calculating the area of a trapezoid
Calculating the area of a triangle
Calculating the surface and volume of a sphere
Calculating the surface and volume of a cube
Calculating the surface and volume of a rectangular solid
Calculating the surface and volume of a cone
Calculating the volume of a cylinder
Calculating the volume of a pyramid
Rounding to the nearest multiple
Working with fractional dollars
Using the INT and TRUNC functions
Rounding to an even or odd integer
Rounding to n significant digits
Chapter 17: Introducing Array Formulas
Understanding the Dimensions of an Array
One-dimensional horizontal arrays
One-dimensional vertical arrays
Selecting an array formula range
Expanding or contracting a multicell array formula
Using Multicell Array Formulas
Creating an array from values in a range
Creating an array constant from values in a range
Performing operations on an array
Generating an array of consecutive integers
Using Single-Cell Array Formulas
Counting characters in a range
Summing the three smallest values in a range
Counting text cells in a range
Eliminating intermediate formulas
Using an array in lieu of a range reference
Chapter 18: Performing Magic with Array Formulas
Working with Single-Cell Array Formulas
Summing a range that contains errors
Counting the number of error values in a range
Summing the n largest values in a range
Computing an average that excludes zeros
Determining whether a particular value appears in a range
Counting the number of differences in two ranges
Returning the location of the maximum value in a range
Finding the row of a value's nth occurrence in a range
Returning the longest text in a range
Determining whether a range contains valid values
Summing the digits of an integer
Summing every nth value in a range
Removing nonnumeric characters from a string
Determining the closest value in a range
Returning the last value in a column
Returning the last value in a row
Working with Multicell Array Formulas
Returning only positive values from a range
Returning nonblank cells from a range
Reversing the order of cells in a range
Sorting a range of values dynamically
Returning a list of unique items in a range
Displaying a calendar in a range
Part III: Creating Charts and Graphics
Chapter 19: Getting Started Making Charts
Understanding How Excel Handles Charts
Hands On: Creating and Customizing a Chart
Experimenting with different styles
Experimenting with different layouts
Trying another view of the data
Moving and deleting chart elements
Chapter 20: Learning Advanced Charting
Selecting with the Chart Element control
User Interface Choices for Modifying Chart Elements
Using the chart customization buttons
Working with Titles in a Chart
Deleting or hiding a data series
Adding a new data series to a chart
Changing data used by a series
Displaying data labels in a chart
Learning Some Chart-Making Tricks
Displaying conditional colors in a column chart
Creating a comparative histogram
Plotting mathematical functions with one variable
Plotting mathematical functions with two variables
Chapter 21: Visualizing Data Using Conditional Formatting
Specifying Conditional Formatting
Formatting types you can apply
Conditional Formats That Use Graphics
Understanding relative and absolute references
Conditional formatting formula examples
Working with Conditional Formats
Copying cells that contain conditional formatting
Deleting conditional formatting
Locating cells that contain conditional formatting
Chapter 22: Creating Sparkline Graphics
Handling hidden or missing data
Changing Sparkline colors and line width
Highlighting certain data points
Adjusting Sparkline axis scaling
Displaying a Sparkline for a Dynamic Range
Chapter 23: Enhancing Your Work with Pictures and Drawings
Working with Other Graphic Types
Displaying a worksheet background image
Part IV: Using Advanced Excel Features
Chapter 24: Customizing the Excel User Interface
Customizing the Quick Access Toolbar
About the Quick Access toolbar
Adding new commands to the Quick Access toolbar
Other Quick Access toolbar actions
Why you may want to customize the Ribbon
Chapter 25: Using Custom Number Formats
Formatting numbers by using the Ribbon
Using shortcut keys to format numbers
Using the Format Cells dialog box to format numbers
Creating a Custom Number Format
Parts of a number format string
Displaying a negative sign on the right
Suppressing certain types of entries
Filling a cell with a repeating character
Chapter 26: Using Data Validation
Specifying Validation Criteria
Types of Validation Criteria You Can Apply
Using Formulas for Data Validation Rules
Data Validation Formula Examples
Accepting a larger value than the previous cell
Accepting nonduplicate entries only
Accepting text that begins with a specific character
Accepting dates by the day of the week
Accepting only values that don't exceed a total
Chapter 27: Creating and Using Worksheet Outlines
Introducing Worksheet Outlines
Creating an outline automatically
Chapter 28: Linking and Consolidating Worksheets
Creating External Reference Formulas
Understanding link formula syntax
Creating a link formula by pointing
Working with External Reference Formulas
Creating links to unsaved workbooks
Opening a workbook with external reference formulas
Avoiding Potential Problems with External Reference Formulas
Renaming or moving a source workbook
Consolidating worksheets by using formulas
Consolidating worksheets by using Paste Special
Consolidating worksheets by using the Consolidate dialog box
A workbook consolidation example
Chapter 29: Excel and the Internet
Saving a Workbook on the Internet
Saving Workbooks in HTML Format
Creating a single-file web page
Chapter 30: Protecting Your Work
Requiring a password to open a workbook
Protecting a workbook's structure
Saving a worksheet as a PDF file
Chapter 31: Making Your Worksheets Error Free
Finding and Correcting Formula Errors
Cells are filled with hash marks
Absolute/relative reference problems
Actual versus displayed values
Identifying cells of a particular type
Fixing circular reference errors
Using the background error-checking feature
Spell-Checking Your Worksheets
Part V: Analyzing Data with Excel
Chapter 32: Importing and Cleaning Data
Importing a text file into a specified range
Extracting a filename from a URL
Changing vertical data to horizontal data
Filling gaps in an imported report
Replacing or removing text in cells
Exporting to other file formats
Chapter 33: Introducing Pivot Tables
Data appropriate for a pivot table
Creating a Pivot Table Automatically
Creating a Pivot Table Manually
Specifying the location for the pivot table
What is the daily total new deposit amount for each branch?
Which day of the week accounts for the most deposits?
How many accounts were opened at each branch, broken down by account type?
What's the dollar distribution of the different account types?
What types of accounts do tellers open most often?
How does the Central branch compare with the other two branches?
In which branch do tellers open the most checking accounts for new customers?
Chapter 34: Analyzing Data with Pivot Tables
Creating a Frequency Distribution
Creating a Calculated Field or Calculated Item
Filtering Pivot Tables with Slicers
Filtering Pivot Tables with a Timeline
Referencing Cells within a Pivot Table
Producing a Report with a Pivot Table
Learning More about Pivot Tables
Chapter 35: Performing Spreadsheet What-If Analysis
Performing manual what-if analysis
Chapter 36: Analyzing Data Using Goal Seeking and Solver
Appropriate problems for Solver
Solving simultaneous linear equations
Optimizing an investment portfolio
Chapter 37: Analyzing Data with the Analysis ToolPak
The Analysis ToolPak: An Overview
Installing the Analysis ToolPak Add-in
Introducing the Analysis ToolPak Tools
F-test (two-sample test for variance)
Z-Test (two-sample test for means)
Part VI: Programming Excel with VBA
Chapter 38: Introducing Visual Basic for Applications
Saving Workbooks That Contain Macros
More about recording VBA macros
Chapter 39: Creating Custom Worksheet Functions
Using the function in a worksheet
Calling custom functions from a procedure
Using custom functions in a worksheet formula
Another function with one argument
A function with a range argument
Chapter 40: Creating UserForms
Creating UserForms: An Overview
Changing the properties of a control
Creating an event handler procedure
Creating event handler procedures
Making the macro available from a worksheet button
Making the macro available on your Quick Access toolbar
Chapter 41: Using UserForm Controls in a Worksheet
Why Use Controls on a Worksheet?
Reviewing the Available ActiveX Controls
Chapter 42: Working with Excel Events
Entering Event-Handler VBA Code
Monitoring a specific range for changes
Using the SelectionChange event
Using the BeforeRightClick event
Selecting to the end of a row or column
Looping through a range efficiently
Determining the type of selection
Identifying a multiple selection
Saving and closing all workbooks
Chapter 44: Creating Custom Excel Add-Ins
Adding descriptive information
Creating the user interface for your add-in macro