Excel® 2013 Bible
Table of Contents
Introduction
Part I: Getting Started with Excel
Chapter 1: Introducing Excel
Identifying What Excel Is Good For
Seeing What's New in Excel 2013
Understanding Workbooks and Worksheets
Moving Around a Worksheet
Navigating with your keyboard
Navigating with your mouse
Using the Ribbon
Ribbon tabs
Contextual tabs
Types of commands on the Ribbon
Accessing the Ribbon by using your keyboard
Using Shortcut Menus
Customizing Your Quick Access Toolbar
Working with Dialog Boxes
Navigating dialog boxes
Using tabbed dialog boxes
Using Task Panes
Creating Your First Excel Workbook
Getting started on your worksheet
Filling in the month names
Entering the sales data
Formatting the numbers
Making your worksheet look a bit fancier
Summing the values
Creating a chart
Printing your worksheet
Saving your workbook
Chapter 2: Entering and Editing Worksheet Data
Exploring Data Types
Numeric values
Text entries
Formulas
Entering Text and Values into Your Worksheets
Entering Dates and Times into Your Worksheets
Entering date values
Entering time values
Modifying Cell Contents
Deleting the contents of a cell
Replacing the contents of a cell
Editing the contents of a cell
Learning some handy data-entry techniques
Applying Number Formatting
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
Working with Excel windows
Activating a worksheet
Adding a new worksheet to your workbook
Deleting a worksheet you no longer need
Changing the name of a worksheet
Changing a sheet tab color
Rearranging your worksheets
Hiding and unhiding a worksheet
Controlling the Worksheet View
Zooming in or out for a better view
Viewing a worksheet in multiple windows
Comparing sheets side by side
Splitting the worksheet window into panes
Keeping the titles in view by freezing panes
Monitoring cells with a Watch Window
Working with Rows and Columns
Inserting rows and columns
Deleting rows and columns
Hiding rows and columns
Changing column widths and row heights
Chapter 4: Working with Cells and Ranges
Understanding Cells and Ranges
Selecting ranges
Selecting complete rows and columns
Selecting noncontiguous ranges
Selecting multisheet ranges
Selecting special types of cells
Selecting cells by searching
Copying or Moving Ranges
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 to adjacent cells
Copying a range to other sheets
Using the Office Clipboard to paste
Pasting in special ways
Using the Paste Special dialog box
Using Names to Work with Ranges
Creating range names in your workbooks
Managing names
Adding Comments to Cells
Formatting comments
Changing a comment's shape
Reading comments
Printing comments
Hiding and showing comments
Selecting comments
Editing comments
Deleting comments
Chapter 5: Introducing Tables
What Is a Table?
Creating a Table
Changing the Look of a Table
Working with Tables
Navigating in a table
Selecting parts of a table
Adding new rows or columns
Deleting rows or columns
Moving a table
Working with the Total Row
Removing duplicate rows from a table
Sorting and filtering 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 Mini toolbar
Using the Format Cells dialog box
Using Different Fonts to Format Your Worksheet
Changing Text Alignment
Choosing horizontal alignment options
Choosing vertical alignment options
Wrapping or shrinking text to fit the cell
Merging worksheet cells to create additional text space
Displaying text at an angle
Controlling the text direction
Using Colors and Shading
Adding Borders and Lines
Adding a Background Image to a Worksheet
Using Named Styles for Easier Formatting
Applying styles
Modifying an existing style
Creating new styles
Merging styles from other workbooks
Controlling styles with templates
Understanding Document Themes
Applying a theme
Customizing a theme
Chapter 7: Understanding Excel Files
Creating a New Workbook
Opening an Existing Workbook
Filtering filenames
Choosing your file display preferences
Saving a Workbook
Using AutoRecover
Recovering versions of the current workbook
Recovering unsaved work
Configuring AutoRecover
Password-Protecting a Workbook
Organizing Your Files
Other Workbook Info Options
Protect Workbook options
Check for Issues options
Compatibility Mode section
Closing Workbooks
Safeguarding Your Work
Excel File Compatibility
Checking compatibility
Recognizing the Excel 2013 file formats
Saving a file for use with an older version of Excel
Chapter 8: Using and Creating Templates
Exploring Excel Templates
Viewing templates
Creating a workbook from a template
Modifying a template
Understanding Custom Excel Templates
Working with the default templates
Creating custom templates
Chapter 9: Printing Your Work
Basic Printing
Changing Your Page View
Normal view
Page Layout view
Page Break Preview
Adjusting Common Page Setup Settings
Choosing your printer
Specifying what you want to print
Changing page orientation
Specifying paper size
Printing multiple copies of a report
Adjusting the page margins
Understanding page breaks
Printing row and column titles
Scaling printed output
Printing cell gridlines
Printing row and column headers
Using a background image
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
Creating PDF files
Part II: Working with Formulas and Functions
Chapter 10: Introducing Formulas and Functions
Understanding Formula Basics
Using operators in formulas
Understanding operator precedence in formulas
Using functions in your formulas
Entering Formulas into Your Worksheets
Entering formulas manually
Entering formulas by pointing
Pasting range names into formulas
Inserting functions into formulas
Function entry tips
Editing Formulas
Using Cell References in Formulas
Using relative, absolute, and mixed references
Changing the types of your references
Referencing cells outside the worksheet
Using Formulas in Tables
Summarizing data in a table
Using formulas within a table
Referencing data in a table
Correcting Common Formula Errors
Handling circular references
Specifying when formulas are calculated
Using Advanced Naming Techniques
Using names for constants
Using names for formulas
Using range intersections
Applying names to existing references
Working with Formulas
Not hard-coding values
Using the Formula bar as a calculator
Making an exact copy of a formula
Converting formulas to values
Chapter 11: Creating Formulas That Manipulate Text
A Few Words about Text
Text Functions
Working with character codes
Determining whether two strings are identical
Joining two or more cells
Displaying formatted values as text
Displaying formatted currency values as text
Repeating a character or string
Creating a text histogram
Padding a number
Removing excess spaces and nonprinting characters
Counting characters in a string
Changing the case of text
Extracting characters from a string
Replacing text with other text
Finding and searching within a string
Searching and replacing within a string
Advanced Text Formulas
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
Removing titles from names
Creating an ordinal number
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
Entering dates
Understanding time serial numbers
Entering times
Formatting dates and times
Problems with dates
Date-Related Worksheet Functions
Displaying the current date
Displaying any date
Generating a series of dates
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
Calculating a person's age
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
Calculating dates of holidays
Determining the last day of a month
Determining whether a year is a leap year
Determining a date's quarter
Time-Related Functions
Displaying the current time
Displaying any time
Calculating the difference between two times
Summing times that exceed 24 hours
Converting from military time
Converting decimal hours, minutes, or seconds to a time
Adding hours, minutes, or seconds to a time
Rounding time values
Working with non-time-of-day values
Chapter 13: Creating Formulas That Count and Sum
Counting and Summing Worksheet Cells
Basic Counting Formulas
Counting the total number of cells
Counting blank cells
Counting nonblank cells
Counting numeric cells
Counting text cells
Counting nontext cells
Counting logical values
Counting error values in a range
Advanced Counting Formulas
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
Summing Formulas
Summing all cells in a range
Computing a cumulative sum
Ignoring errors when summing
Summing the “top n” values
Conditional Sums Using a Single Criterion
Summing only negative values
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
Using And criteria
Using Or criteria
Using And and Or criteria
Chapter 14: Creating Formulas That Look Up Values
Introducing Lookup Formulas
Functions Relevant to Lookups
Basic Lookup Formulas
The VLOOKUP function
The HLOOKUP function
The LOOKUP function
Combining the MATCH and INDEX functions
Specialized Lookup Formulas
Looking up an exact value
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-way lookup
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
The Time Value of Money
Loan Calculations
Worksheet functions for calculating loan information
A loan calculation example
Credit card payments
Creating a loan amortization schedule
Summarizing loan options by using a data table
Calculating a loan with irregular payments
Investment Calculations
Future value of a single deposit
Future value of a series of deposits
Depreciation Calculations
Chapter 16: Miscellaneous Calculations
Unit Conversions
Solving Right Triangles
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 Numbers
Basic rounding formulas
Rounding to the nearest multiple
Rounding currency values
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 Array Formulas
A multicell array formula
A single-cell array formula
Creating an Array Constant
Understanding the Dimensions of an Array
One-dimensional horizontal arrays
One-dimensional vertical arrays
Two-dimensional arrays
Naming Array Constants
Working with Array Formulas
Entering an array formula
Selecting an array formula range
Editing an array formula
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
Using functions with an array
Transposing 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 rounded values
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
What Is a Chart?
Understanding How Excel Handles Charts
Embedded charts
Chart sheets
Creating a Chart
Hands On: Creating and Customizing a Chart
Selecting the data
Choosing a chart type
Experimenting with different styles
Experimenting with different layouts
Trying another view of the data
Trying other chart types
Working with Charts
Resizing a chart
Moving a chart
Copying a chart
Deleting a chart
Adding chart elements
Moving and deleting chart elements
Formatting chart elements
Printing charts
Understanding Chart Types
Choosing a chart type
Column charts
Bar charts
Line charts
Pie charts
XY (scatter) charts
Area charts
Radar charts
Surface charts
Bubble charts
Stock charts
Learning More
Chapter 20: Learning Advanced Charting
Selecting Chart Elements
Selecting with the mouse
Selecting with the keyboard
Selecting with the Chart Element control
User Interface Choices for Modifying Chart Elements
Using the Format task pane
Using the chart customization buttons
Using the Ribbon
Using the Mini toolbar
Modifying the Chart Area
Modifying the Plot Area
Working with Titles in a Chart
Working with a Legend
Working with Gridlines
Modifying the Axes
Value axis
Category axis
Working with Data Series
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
Handling missing data
Adding error bars
Adding a trendline
Modifying 3-D charts
Creating combination charts
Displaying a data table
Creating Chart Templates
Learning Some Chart-Making Tricks
Creating picture charts
Creating a thermometer chart
Creating a gauge chart
Displaying conditional colors in a column chart
Creating a comparative histogram
Creating a Gantt chart
Plotting mathematical functions with one variable
Plotting mathematical functions with two variables
Chapter 21: Visualizing Data Using Conditional Formatting
About Conditional Formatting
Specifying Conditional Formatting
Formatting types you can apply
Making your own rules
Conditional Formats That Use Graphics
Using data bars
Using color scales
Using icon sets
Creating Formula-Based Rules
Understanding relative and absolute references
Conditional formatting formula examples
Working with Conditional Formats
Managing rules
Copying cells that contain conditional formatting
Deleting conditional formatting
Locating cells that contain conditional formatting
Chapter 22: Creating Sparkline Graphics
Sparkline Types
Creating Sparklines
Customizing Sparklines
Sizing Sparkline cells
Handling hidden or missing data
Changing the Sparkline type
Changing Sparkline colors and line width
Highlighting certain data points
Adjusting Sparkline axis scaling
Faking a reference line
Specifying a Date Axis
Auto-Updating Sparklines
Displaying a Sparkline for a Dynamic Range
Chapter 23: Enhancing Your Work with Pictures and Drawings
Using Shapes
Inserting a Shape
Adding text to a Shape
Formatting Shapes
Stacking Shapes
Grouping objects
Aligning and spacing objects
Reshaping Shapes
Printing objects
Using SmartArt
Inserting SmartArt
Customizing SmartArt
Changing the layout
Changing the style
Learning more about SmartArt
Using WordArt
Working with Other Graphic Types
About graphics files
Inserting screenshots
Displaying a worksheet background image
Using the Equation Editor
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
Customizing the Ribbon
Why you may want to customize the Ribbon
What can be customized
How to customize the Ribbon
Resetting the Ribbon
Chapter 25: Using Custom Number Formats
About Number Formatting
Automatic number formatting
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
Custom number format codes
Custom Number Format Examples
Scaling values
Displaying leading zeros
Specifying conditions
Displaying fractions
Displaying a negative sign on the right
Formatting dates and times
Displaying text with numbers
Suppressing certain types of entries
Filling a cell with a repeating character
Chapter 26: Using Data Validation
About Data Validation
Specifying Validation Criteria
Types of Validation Criteria You Can Apply
Creating a Drop-Down List
Using Formulas for Data Validation Rules
Understanding Cell References
Data Validation Formula Examples
Accepting text only
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
Creating a dependent list
Chapter 27: Creating and Using Worksheet Outlines
Introducing Worksheet Outlines
Creating an Outline
Preparing the data
Creating an outline automatically
Creating an outline manually
Working with Outlines
Displaying levels
Adding data to an outline
Removing an outline
Adjusting the outline symbols
Hiding the outline symbols
Chapter 28: Linking and Consolidating Worksheets
Linking Workbooks
Creating External Reference Formulas
Understanding link formula syntax
Creating a link formula by pointing
Pasting links
Working with External Reference Formulas
Creating links to unsaved workbooks
Opening a workbook with external reference formulas
Changing the startup prompt
Updating links
Changing the link source
Severing links
Avoiding Potential Problems with External Reference Formulas
Renaming or moving a source workbook
Using the Save As command
Modifying a source workbook
Intermediary links
Consolidating Worksheets
Consolidating worksheets by using formulas
Consolidating worksheets by using Paste Special
Consolidating worksheets by using the Consolidate dialog box
A workbook consolidation example
Refreshing a consolidation
More about consolidation
Chapter 29: Excel and the Internet
Saving a Workbook on the Internet
Saving Workbooks in HTML Format
Creating an HTML file
Creating a single-file web page
Opening an HTML File
Working with Hyperlinks
Inserting a hyperlink
Using hyperlinks
Using Web Queries
E-Mail Features
Chapter 30: Protecting Your Work
Types of Protection
Protecting a Worksheet
Unlocking cells
Sheet protection options
Assigning user permissions
Protecting a Workbook
Requiring a password to open a workbook
Protecting a workbook's structure
VB Project Protection
Related Topics
Saving a worksheet as a PDF file
Marking a workbook final
Inspecting a workbook
Using a digital signature
Chapter 31: Making Your Worksheets Error Free
Finding and Correcting Formula Errors
Mismatched parentheses
Cells are filled with hash marks
Blank cells are not blank
Extra space characters
Formulas returning an error
Absolute/relative reference problems
Operator precedence problems
Formulas are not calculated
Actual versus displayed values
Floating point number errors
“Phantom link” errors
Using Excel Auditing Tools
Identifying cells of a particular type
Viewing formulas
Tracing cell relationships
Tracing error values
Fixing circular reference errors
Using the background error-checking feature
Using Formula Evaluator
Searching and Replacing
Searching for information
Replacing information
Searching for formatting
Spell-Checking Your Worksheets
Using AutoCorrect
Part V: Analyzing Data with Excel
Chapter 32: Importing and Cleaning Data
Importing Data
Importing from a file
Importing a text file into a specified range
Copying and pasting data
Data Clean-up Techniques
Removing duplicate rows
Identifying duplicate rows
Splitting text
Changing the case of text
Removing extra spaces
Removing strange characters
Converting values
Classifying values
Joining columns
Rearranging columns
Randomizing the rows
Extracting a filename from a URL
Matching text in a list
Changing vertical data to horizontal data
Filling gaps in an imported report
Checking spelling
Replacing or removing text in cells
Adding text to cells
Fixing trailing minus signs
A Data Cleaning Checklist
Exporting Data
Exporting to a text file
Exporting to other file formats
Chapter 33: Introducing Pivot Tables
About Pivot Tables
A pivot table example
Data appropriate for a pivot table
Creating a Pivot Table Automatically
Creating a Pivot Table Manually
Specifying the data
Specifying the location for the pivot table
Laying out the pivot table
Formatting the pivot table
Modifying the pivot table
More Pivot Table Examples
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?
Learning More
Chapter 34: Analyzing Data with Pivot Tables
Working with Non-Numeric Data
Grouping Pivot Table Items
A manual grouping example
Automatic grouping examples
Creating a Frequency Distribution
Creating a Calculated Field or Calculated Item
Creating a calculated field
Inserting a calculated item
Filtering Pivot Tables with Slicers
Filtering Pivot Tables with a Timeline
Referencing Cells within a Pivot Table
Creating Pivot Charts
A pivot chart example
More about pivot charts
Another Pivot Table Example
Producing a Report with a Pivot Table
Using the Data Model
Learning More about Pivot Tables
Chapter 35: Performing Spreadsheet What-If Analysis
A What-If Example
Types of What-If Analyses
Performing manual what-if analysis
Creating data tables
Using Scenario Manager
Chapter 36: Analyzing Data Using Goal Seeking and Solver
What-If Analysis, in Reverse
Single-Cell Goal Seeking
A goal-seeking example
More about goal seeking
Introducing Solver
Appropriate problems for Solver
A simple Solver example
More about Solver
Solver Examples
Solving simultaneous linear equations
Minimizing shipping costs
Allocating resources
Optimizing an investment portfolio
Chapter 37: Analyzing Data with the Analysis ToolPak
The Analysis ToolPak: An Overview
Installing the Analysis ToolPak Add-in
Using the Analysis Tools
Introducing the Analysis ToolPak Tools
Analysis of Variance
Correlation
Covariance
Descriptive Statistics
Exponential Smoothing
F-test (two-sample test for variance)
Fourier Analysis
Histogram
Moving Average
Random Number Generation
Rank and Percentile
Regression
Sampling
T-Test
Z-Test (two-sample test for means)
Part VI: Programming Excel with VBA
Chapter 38: Introducing Visual Basic for Applications
Introducing VBA Macros
Displaying the Developer Tab
About Macro Security
Saving Workbooks That Contain Macros
Two Types of VBA Macros
VBA Sub procedures
VBA functions
Creating VBA Macros
Recording VBA macros
More about recording VBA macros
Writing VBA code
Learning More
Chapter 39: Creating Custom Worksheet Functions
Overview of VBA Functions
An Introductory Example
A custom function
Using the function in a worksheet
Analyzing the custom function
About Function Procedures
Executing Function Procedures
Calling custom functions from a procedure
Using custom functions in a worksheet formula
Function Procedure Arguments
A function with no argument
A function with one argument
Another function with one argument
A function with two arguments
A function with a range argument
A simple but useful function
Debugging Custom Functions
Inserting Custom Functions
Learning More
Chapter 40: Creating UserForms
Why Create UserForms?
UserForm Alternatives
The InputBox function
The MsgBox function
Creating UserForms: An Overview
Working with UserForms
Adding controls
Changing the properties of a control
Handling events
Displaying a UserForm
A UserForm Example
Creating the UserForm
Testing the UserForm
Creating an event handler procedure
Another UserForm Example
Creating the UserForm
Testing the UserForm
Creating event handler procedures
Testing the UserForm
Making the macro available from a worksheet button
Making the macro available on your Quick Access toolbar
More on Creating UserForms
Adding accelerator keys
Controlling tab order
Learning More
Chapter 41: Using UserForm Controls in a Worksheet
Why Use Controls on a Worksheet?
Using Controls
Adding a control
About Design mode
Adjusting properties
Common properties
Linking controls to cells
Creating macros for controls
Reviewing the Available ActiveX Controls
CheckBox
ComboBox
CommandButton
Image
Label
ListBox
OptionButton
ScrollBar
SpinButton
TextBox
ToggleButton
Chapter 42: Working with Excel Events
Understanding Events
Entering Event-Handler VBA Code
Using Workbook-Level Events
Using the Open event
Using the SheetActivate event
Using the NewSheet event
Using the BeforeSave event
Using the BeforeClose event
Working with Worksheet Events
Using the Change event
Monitoring a specific range for changes
Using the SelectionChange event
Using the BeforeRightClick event
Using Non-Object Events
Using the OnTime event
Using the OnKey event
Chapter 43: VBA Examples
Working with Ranges
Copying a range
Copying a variable-size range
Selecting to the end of a row or column
Selecting a row or column
Moving a range
Looping through a range efficiently
Prompting for a cell value
Determining the type of selection
Identifying a multiple selection
Counting selected cells
Working with Workbooks
Saving all workbooks
Saving and closing all workbooks
Working with Charts
Modifying the chart type
Modifying chart properties
Applying chart formatting
VBA Speed Tips
Turning off screen updating
Preventing alert messages
Simplifying object references
Declaring variable types
Chapter 44: Creating Custom Excel Add-Ins
What Is an Add-In?
Working with Add-Ins
Why Create Add-Ins?
Creating Add-Ins
An Add-In Example
About Module1
About the UserForm
Testing the workbook
Adding descriptive information
Creating the user interface for your add-in macro
Protecting the project
Creating the add-in
Installing the add-in
Part VII: Appendixes
Appendix A: Worksheet Function Reference
Appendix B: Excel Shortcut Keys
Excel® 2013 Bible
Excel® 2013 Bible
Published by
John Wiley & Sons, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2013 by John Wiley & Sons, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN 978-1-118-49036-5 (pbk); ISBN 978-1-118-49030-3 (ebk); ISBN 978-1-118-49170-6 (ebk); ISBN 978-1-118-49172-0 (ebk)
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, 201-748-6011, fax 201-748-6008, or online at http://www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read.
For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at (877) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002.
Library of Congress Control Number: 2012956404
Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc., in the United States and other countries, and may not be used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Credits
Sr. Acquisitions Editor
Katie Mohr
Project Editor
Elizabeth Kuball
Technical Editor
Niek Otten
Copy Editor
Elizabeth Kuball
Editorial Manager
Jodi Jensen
Editorial Director
Mary Corder
Vice President and Executive Group Publisher
Richard Swadley
Vice President and Publisher
Andy Cummings
Project Coordinator
Sheree Montgomery
Graphics and Production Specialists
Jennifer Creasey
Jennifer Mayberry
Quality Control Technicians
Jessica Kramer
Lauren Mandelbaum
Proofreading and Indexing
BIM Indexing & Proofreading Services
Vertical Websites Project Manager
and Producer
Rich Graves
About the Author
John Walkenbach is a bestselling Excel author who has published more than 50 spreadsheet books. He lives amid the saguaros, javelinas, rattlesnakes, bobcats, and gila monsters in Southern Arizona — but the critters are mostly scared away by his clawhammer banjo playing. For more information, Google him.
Acknowledgments
Thanks again to everyone who bought the previous editions of this book. Your suggestions have helped make this edition the best one yet.
And a special thanks to two behind-the-scenes folks who helped considerably: Elizabeth Kuball (who made it more readable) and Niek Otten (who made it more accurate).
Introduction
Thank you for purchasing Excel 2013 Bible. If you're just starting with Excel, you'll be glad to know that Excel 2013 is the easiest version ever.
My goal in writing this book is to share with you some of what I know about Excel and, in the process, make you more efficient on the job. The book contains everything that you need to know to learn the basics of Excel and then move on to more advanced topics at your own pace. You'll find many useful examples and lots of tips and tricks that I've accumulated over the years.
Is This Book for You?
The Bible series from John Wiley & Sons, Inc., is designed for beginning, intermediate, and advanced users. This book covers all the essential components of Excel and provides clear and practical examples that you can adapt to your own needs.
In this book, I've tried to maintain a good balance between the basics that every Excel user needs to know and the more complex topics that will appeal to power users. I've used Excel for more than 20 years, and I realize that almost everyone still has something to learn (including myself). My goal is to make that learning an enjoyable process.
Software Versions
This book was written for Excel 2013 for Windows. Much of the information also applies to Excel 2007 and Excel 2010, but if you're using an older version of Excel, I suggest that you put down this book immediately and find a book that's appropriate for your version of Excel. The user interface changes introduced in Excel 2007 are so extensive that this book will be very confusing if you use an earlier version.
Also, please note that this book is not applicable to Excel for Mac.
Office 2013 is available in several versions, including a web version, and a version for tablets and phones. This book covers only the standard desktop version of Excel 2013.
Conventions Used in This Book
Take a minute to scan this section to learn some of the typographical and organizational conventions that this book uses.
Excel commands
Excel 2013 (like the two previous versions) features a “menu-less” user interface. In place of a menu system, Excel uses a context-sensitive Ribbon system. The words along the top (such as File, Insert, Page Layout, and so on) are known as tabs. Click a tab, and the Ribbon displays the commands for the selected tab. Each command has a name, which is (usually) displayed next to or below the icon. The commands are arranged in groups, and the group name appears at the bottom of the Ribbon.
The convention I use is to indicate the tab name, followed by the group name, followed by the command name. So, the command used to toggle word wrap within a cell is indicated as
Home ⇒ Alignment ⇒ Wrap Text
You'll learn more about the Ribbon user interface in Chapter 1.
Filenames, named ranges, and your input
Anything you're supposed to type using the keyboard appears in bold. Named ranges appear in a monofont. Lengthy input usually appears on a separate line. For example, I may instruct you to enter a formula such as the following:
=”Part Name: “ &VLOOKUP(PartNumber,PartList,2)
Key names
Names of the keys on your keyboard appear in normal type. When two keys should be pressed simultaneously, they're connected with a plus sign, like this: “Press Ctrl+C to copy the selected cells.”
The four “arrow” keys are collectively known as the navigation keys.
Functions
Excel built-in worksheet functions appear in uppercase monofont, like this: “Note the SUM formula in cell C20.”
Mouse conventions
You'll come across some of the following mouse-related terms, all standard fare:
• Mouse pointer: The small graphic figure that moves onscreen when you move your mouse. The mouse pointer is usually an arrow, but it changes shape when you move to certain areas of the screen or when you're performing certain actions.
• Point: Move the mouse so that the mouse pointer is on a specific item: for example, “Point to the Save button on the toolbar.”
• Click: Press the left mouse button once and release it immediately.
• Right-click: Press the right mouse button once and release it immediately. The right mouse button is used in Excel to pop up shortcut menus that are appropriate for whatever is currently selected.
• Double-click: Press the left mouse button twice in rapid succession.
• Drag: Press the left mouse button and keep it pressed while you move the mouse. Dragging is often used to select a range of cells or to change the size of an object.
How This Book Is Organized
Notice that the book is divided into six main parts, followed by three appendixes.
• Part I: Getting Started with Excel: This part consists of nine chapters that provide background about Excel. These chapters are considered required reading for Excel newcomers, but even experienced users will probably find some new information here.
• Part II: Working with Formulas and Functions: The chapters in Part II cover everything that you need to know to become proficient with performing calculations in Excel.
• Part III: Creating Charts and Graphics: The chapters in Part III describe how to create effective charts. In addition, you'll find chapters on the conditional formatting visualization features, Sparkline graphics, and a chapter with lots of tips on integrating graphics into your worksheet.
• Part IV: Using Advanced Excel Features: This part consists of eight chapters that deal with topics that are sometimes considered advanced. However, many beginning and intermediate users may find this information useful as well.
• Part V: Analyzing Data with Excel: Data analysis is the focus of the chapters in Part V. Users of all levels will find some of these chapters of interest.
• Part VI: Programming Excel with VBA: Part VI is for those who want to customize Excel for their own use or who are designing workbooks or add-ins that are to be used by others. It starts with an introduction to recording macros and VBA programming and then provides coverage of UserForms, add-ins, and events.
• Part VII: Appendixes: This book has two appendixes that cover Excel worksheet functions and Excel shortcut keys.
How to Use This Book
Although you're certainly free to do so, I didn't write this book with the intention that you would read it cover to cover. Instead, it's a reference book that you can consult when
• You're stuck while trying to do something.
• You need to do something that you've never done before.
• You have some time on your hands, and you're interested in learning something new about Excel.
The index is comprehensive, and each chapter typically focuses on a single broad topic. If you're just starting out with Excel, I recommend that you read the first few chapters to gain a basic understanding of the product and then do some experimenting on your own. After you become familiar with Excel's environment, you can refer to the chapters that interest you most. Some readers, however, may prefer to follow the chapters in order.
Don't be discouraged if some of the material is over your head. Most users get by just fine by using only a small subset of Excel's total capabilities. In fact, the 80/20 rule applies here: 80% of Excel users use only 20% of its features. However, using only 20% of Excel's features still gives you lots of power at your fingertips.
What's on the Website
This book contains many examples, and you can download the workbooks for those examples from the web. The files are arranged in directories that correspond to the chapters.
The URL is www.wiley.com/go/excel2013bible.
Part I: Getting Started
with Excel
The chapters in this part are intended to provide essential background information for working with Excel. Here, you'll see how to make use of the basic features that are required for every Excel user. If you've used Excel (or even a different spreadsheet program) in the past, much of this information may seem like review. Even so, it's likely that you'll find quite a few tricks and techniques.
In This Part
Chapter 1
Introducing Excel
Chapter 2
Entering and Editing Worksheet Data
Chapter 3
Essential Worksheet Operations
Chapter 4
Working with Cells and Ranges
Chapter 5
Introducing Tables
Chapter 6
Worksheet Formatting
Chapter 7
Understanding Excel Files
Chapter 8
Using and Creating Templates
Chapter 9
Printing Your Work