Excel 2013 Formulas E-Book

John Walkenbach

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 961

Veröffentlichungsjahr: 2013

Beschreibung

Maximize the power of Excel 2013 formulas with this must-have Excel reference John Walkenbach, known as "Mr. Spreadsheet," is a master at deciphering complex technical topics and Excel formulas are no exception. This fully updated book delivers more than 800 pages of Excel 2013 tips, tricks, and techniques for creating formulas that calculate, developing custom worksheet functions with VBA, debugging formulas, and much more. * Demonstrates how to use all the latest features in Excel 2013 * Shows how to create financial formulas and tap into the power of array formulas * Serves as a guide to using various lookup formulas, working with conditional formatting, and developing custom functions * Shares proven solutions for handling typical (and not-so-typical) Excel formula challenges * Includes links to the "Mr. Spreadsheet" website, which contains all the templates and worksheets used in the book, plus access to John Walkenbach's award-winning Power Utility Pak. From charts to PivotTables and everything in between, Excel 2013 Formulas is your formula for Excel success.

Leseprobe

Excel® 2013 Formulas

Table of Contents

Introduction

What You Need to Know

What You Need to Have

Conventions in This Book

Keyboard conventions

Mouse conventions

What the icons mean

How This Book Is Organized

Part I: Basic Information

Part II: Using Functions in Your Formulas

Part III: Financial Formulas

Part IV: Array Formulas

Part V: Miscellaneous Formula Techniques

Part VI: Developing Custom Worksheet Functions

Part VII: Appendixes

How to Use This Book

About This Book's Website

About the Power Utility Pak Offer

Part I: Basic Information

Chapter 1: Excel in a Nutshell

Excel: What Is It Good For?

What's New in Excel 2013?

The Object Model Concept

The Workings of Workbooks

Worksheets

Chart sheets

Macro sheets and dialog sheets

The Excel User Interface

The Ribbon

Backstage View

Shortcut menus and the mini toolbar

Dialog boxes

Customizing the UI

Task panes

Keyboard shortcuts

Customized onscreen display

Object and cell selecting

The Excel Help System

Cell Formatting

Numeric formatting

Stylistic formatting

Tables

Worksheet Formulas and Functions

Objects on the Drawing Layer

Shapes and illustrations

Controls

Charts

Sparkline graphics

Customizing Excel

Macros

Add-in programs

Internet Features

Analysis Tools

Database access

Scenario management

Pivot tables

Auditing capabilities

Solver add-in

Protection Options

Protecting formulas from being overwritten

Protecting a workbook's structure

Password-protecting a workbook

Chapter 2: Basic Facts about Formulas

Entering and Editing Formulas

Formula elements

Entering a formula

Pasting names

Spaces and line breaks

Formula limits

Sample formulas

Editing formulas

Using Operators in Formulas

Reference operators

Operator precedence

Calculating Formulas

Cell and Range References

Creating an absolute or a mixed reference

Referencing other sheets or workbooks

Copying or Moving Formulas

Making an Exact Copy of a Formula

Converting Formulas to Values

Hiding Formulas

Errors in Formulas

Dealing with Circular References

Goal Seeking

A goal seeking example

More about goal seeking

Chapter 3: Working with Names

What's in a Name?

A Name's Scope

Referencing names

Referencing names from another workbook

Conflicting names

The Name Manager

Creating names

Editing names

Deleting names

Shortcuts for Creating Cell and Range Names

The New Name dialog box

Creating names using the Name box

Creating names from text in cells

Naming entire rows and columns

Names created by Excel

Creating Multisheet Names

Working with Range and Cell Names

Creating a list of names

Using names in formulas

Using the intersection operators with names

Using the range operator with names

Referencing a single cell in a multicell named range

Applying names to existing formulas

Applying names automatically when creating a formula

Unapplying names

Names with errors

Viewing named ranges

Using names in charts

How Excel Maintains Cell and Range Names

Inserting a row or column

Deleting a row or column

Cutting and pasting

Potential Problems with Names

Name problems when copying sheets

Name problems when deleting sheets

The Secret to Understanding Names

Naming constants

Naming text constants

Using worksheet functions in named formulas

Using cell and range references in named formulas

Using named formulas with relative references

Advanced Techniques That Use Names

Using the INDIRECT function with a named range

Using arrays in named formulas

Creating a dynamic named formula

Using an XLM macro in a named formula

Part II: Using Functions in Your Formulas

Chapter 4: Introducing Worksheet Functions

What Is a Function?

Simplify your formulas

Perform otherwise impossible calculations

Speed up editing tasks

Provide decision-making capability

More about functions

Function Argument Types

Names as arguments

Full-column or full-row as arguments

Literal values as arguments

Expressions as arguments

Other functions as arguments

Arrays as arguments

Ways to Enter a Function into a Formula

Entering a function manually

Using the Function Library commands

Using the Insert Function dialog box

More tips for entering functions

Function Categories

Financial functions

Date and time functions

Math and trig functions

Statistical functions

Lookup and reference functions

Database functions

Text functions

Logical functions

Information functions

User-defined functions

Engineering functions

Cube functions

Compatibility functions

Web functions

Other function categories

Chapter 5: Manipulating Text

A Few Words about Text

How many characters in a cell?

Numbers as text

Text Functions

Determining whether a cell contains text

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

Removing excess spaces and nonprinting characters

Counting characters in a string

Repeating a character or string

Creating a text histogram

Padding a number

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

Removing trailing minus signs

Expressing a number as an ordinal

Determining a column letter for a column number

Extracting a filename from a path specification

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

Counting the number of words in a cell

Chapter 6: 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 Functions

Displaying the current date

Displaying any date with a function

Generating a series of dates

Converting a non-date string to a date

Calculating the number of days between two dates

Calculating the number of work days between two dates

Offsetting a date using only work days

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

Counting the occurrences of a day of the week

Expressing a date as an ordinal number

Calculating dates of holidays

Determining the last day of a month

Determining whether a year is a leap year

Determining a date's quarter

Converting a year to roman numerals

Time-Related Functions

Displaying the current time

Displaying any time using a function

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

Converting between time zones

Rounding time values

Working with non–time-of-day values

Chapter 7: Counting and Summing Techniques

Counting and Summing Worksheet Cells

Other Counting Methods

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 with the COUNTIF function

Counting cells that meet 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

Summing a range that contains errors

Computing a cumulative sum

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 8: Using Lookup Functions

What Is a Lookup Formula?

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

Choosing among 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 address of a value within a range

Looking up a value by using the closest match

Looking up a value using linear interpolation

Chapter 9: Working with Tables and Lists

Tables and Terminology

A list example

A table example

Working with Tables

Creating a table

Changing the look of a table

Navigating and selecting in a table

Adding new rows or columns

Deleting rows or columns

Moving a table

Removing duplicate rows from a table

Sorting and filtering a table

Working with the Total row

Using formulas within a table

Referencing data in a table

Converting a table to a list

Using Advanced Filtering

Setting up a criteria range

Applying an advanced filter

Clearing an advanced filter

Specifying Advanced Filter Criteria

Specifying a single criterion

Specifying multiple criteria

Specifying computed criteria

Using Database Functions

Inserting Subtotals

Chapter 10: Miscellaneous Calculations

Unit Conversions

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

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

Solving Simultaneous Equations

Working with Normal Distributions

Part III: Financial Formulas

Chapter 11: Borrowing and Investing Formulas

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

Calculating a loan with irregular payments

Investment Calculations

Future value of a single deposit

Present value of a series of payments

Future value of a series of deposits

Chapter 12: Discounting and Depreciation Formulas

Using the NPV Function

Definition of NPV

NPV function examples

Using the IRR Function

Rate of return

Geometric growth rates

Checking results

Irregular Cash Flows

Net present value

Internal rate of return

Depreciation Calculations

Chapter 13: Financial Schedules

Creating Financial Schedules

Creating Amortization Schedules

A simple amortization schedule

A dynamic amortization schedule

Credit card calculations

Summarizing Loan Options Using a Data Table

Creating a one-way data table

Creating a two-way data table

Financial Statements and Ratios

Basic financial statements

Ratio analysis

Creating Indices

Part IV: Array Formulas

Chapter 14: Introducing Arrays

Introducing Array Formulas

A multicell array formula

A single-cell array formula

Creating an array constant

Array constant elements

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 15: 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 V: Miscellaneous Formula Techniques

Chapter 16: Importing and Cleaning Data

A Few Words about 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

Matching text in a list

Change vertical data to horizontal data

Filling gaps in an imported report

Spelling checking

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 17: Charting Techniques

Understanding the SERIES Formula

Using names in a SERIES formula

Unlinking a chart series from its data range

Creating Links to Cells

Adding a chart title link

Adding axis title links

Adding text links

Adding a linked picture to a chart

Chart Examples

Single data point charts

Displaying conditional colors in a column chart

Creating a comparative histogram

Creating a Gantt chart

Creating a box plot

Plotting every nth data point

Identifying maximum and minimum values in a chart

Creating a Timeline

Plotting mathematical functions

Plotting a circle

Creating a clock chart

Creating awesome designs

Working with Trendlines

Linear trendlines

Working with nonlinear trendlines

Summary of trendline equations

Creating Interactive Charts

Selecting a series from a drop-down list

Plotting the last n data points

Choosing a start date and number of points

Displaying population data

Displaying weather data

Chapter 18: 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

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Grouping Pivot Table Items

A manual grouping example

Viewing grouped data

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

Another Pivot Table Example

Using the Data Model

Creating Pivot Charts

A pivot chart example

More about pivot charts

Chapter 19: 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 20: 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 21: Creating Megaformulas

What Is a Megaformula?

Creating a Megaformula: A Simple Example

Megaformula Examples

Using a megaformula to remove middle names

Using a megaformula to return a string's last space character position

Using a megaformula to determine the validity of a credit card number

Generating random names

The Pros and Cons of Megaformulas

Chapter 22: Tools and Methods for Debugging Formulas

Formula Debugging?

Formula Problems and Solutions

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

Logical value errors

Circular reference errors

Excel's Auditing Tools

Identifying cells of a particular type

Viewing formulas

Tracing cell relationships

Tracing error values

Fixing circular reference errors

Using background error checking

Using Excel's Formula Evaluator

Part VI: Developing Custom Worksheet Functions

Chapter 23: Introducing VBA

About VBA

Displaying the Developer Tab

About Macro Security

Saving Workbooks That Contain Macros

Introducing the Visual Basic Editor

Activating the VB Editor

The VB Editor components

Using the Project window

Using code windows

Entering VBA code

Saving your project

Chapter 24: Function Procedure Basics

Why Create Custom Functions?

An Introductory VBA Function Example

About Function Procedures

Declaring a function

Choosing a name for your function

Using functions in formulas

Using function arguments

Using the Insert Function Dialog Box

Adding a function description

Specifying a function category

Adding argument descriptions

Testing and Debugging Your Functions

Using the VBA MsgBox statement

Using Debug.Print statements in your code

Calling the function from a Sub procedure

Setting a breakpoint in the function

Creating Add-Ins for Functions

Chapter 25: VBA Programming Concepts

An Introductory Example Function Procedure

Using Comments in Your Code

Using Variables, Data Types, and Constants

Defining data types

Declaring variables

Using constants

Using strings

Using dates

Using Assignment Statements

Using Arrays

Declaring an array

Declaring multidimensional arrays

Using Built-In VBA Functions

Controlling Execution

The If-Then construct

The Select Case construct

Looping blocks of instructions

The On Error statement

Using Ranges

The For Each-Next construct

Referencing a range

Some useful properties of ranges

The Set keyword

The Intersect function

The Union function

The UsedRange property

Chapter 26: VBA Custom Function Examples

Simple Functions

Does a cell contain a formula?

Returning a cell's formula

Is the cell hidden?

Returning a worksheet name

Returning a workbook name

Returning the application's name

Returning Excel's version number

Returning cell formatting information

Determining a Cell's Data Type

A Multifunctional Function

Generating Random Numbers

Generating random numbers that don't change

Selecting a cell at random

Calculating Sales Commissions

A function for a simple commission structure

A function for a more complex commission structure

Text Manipulation Functions

Reversing a string

Scrambling text

Returning an acronym

Does the text match a pattern?

Does a cell contain a particular word?

Does a cell contain text?

Extracting the nth element from a string

Spelling out a number

Counting Functions

Counting pattern-matched cells

Counting sheets in a workbook

Counting words in a range

Date Functions

Calculating the next monday

Calculating the next day of the week

Which week of the month?

Working with dates before 1900

Returning the Last Nonempty Cell in a Column or Row

The LASTINCOLUMN function

The LASTINROW function

Multisheet Functions

Returning the maximum value across all worksheets

The SHEETOFFSET function

Advanced Function Techniques

Returning an error value

Returning an array from a function

Returning an array of nonduplicated random integers

Randomizing a range

Using optional arguments

Using an indefinite number of arguments

Part VII: Appendixes

Appendix 1: Excel Function Reference

Appendix 2: Using Custom Number Formats

Automatic number formatting

Formatting numbers by using the Ribbon

Using shortcut keys to format numbers

Using the format cells dialog box to format numbers

Parts of a number format string

Custom number format codes

Scaling values

Hiding zeros

Displaying leading zeros

Displaying fractions

Displaying N/A for text

Displaying text in quotes

Repeating a cell entry

Displaying a negative sign on the right

Conditional number formatting

Coloring values

Formatting dates and times

Displaying text with numbers

Displaying a zero with dashes

Using special symbols

Suppressing certain types of entries

Filling a cell with a repeating character

Displaying leading dots

End User License Agreement

Excel® 2013 Formulas

Published byJohn Wiley & Sons, Inc.111 River StreetHoboken, NJ 07030-5774www.wiley.com

Copyright © 2013 by John Wiley & Sons, Inc., Hoboken, New Jersey

Published by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

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, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley and the Wiley logo, are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries, and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries. 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.

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. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone].

For technical support, please visit www.wiley.com/techsupport.

Wiley also publishes its books in a variety of electronic formats. 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.

Library of Congress Control Number: 2013932115

ISBN: 978-1-118-49044-0 (pbk); ISBN 978-1-118-49045-7 (ebk); ISBN 978-1-118-49179-9 (ebk); ISBN 978-1-118-49189-8 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

About the Author

John Walkenbach is a bestselling Excel author who has published more than 50 books on spreadsheets. 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.

Publisher's Acknowledgments

We're proud of this book; please send us your comments at http://dummies.custhelp.com. For other comments, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone].

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and Vertical Websites

Sr. Project Editor: Christopher Morris

Acquisitions Editor: Katie Mohr

Sr. Copy Editor: Teresa Artman

Technical Editor: Niek Otten

Editorial Manager: Kevin Kirschner

Vertical Websites Project Manager: Laura Moss-Hollister

Editorial Assistant: Annie Sullivan

Sr. Editorial Assistant: Cherie Case

Composition Services

Project Coordinator: Kristie Rees

Layout and Graphics: Jennifer Henry

Proofreader: ConText Editorial Services, Inc.

Indexer: BIM Indexing & Proofreading Services

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Kathleen Nebenhaus, Vice President and Executive Publisher

Composition Services

Debbie Stailey, Director of Composition Services

Part I: Basic Information

Chapter 1

Excel in a Nutshell

Chapter 2

Basic Facts about Formulas

Chapter 3

Working with Names

Chapter 1: Excel in a Nutshell

In This Chapter

• What's new in Excel 2013?

• The object model concept in Excel

• The workings of workbooks

• The user interface

• The two types of cell formatting

• Worksheet formulas and functions

• Objects on the worksheet's invisible drawing layer

• Macros, toolbars, and add-ins for Excel customization

• Internet features

• Analysis tools

• Protection options

Microsoft Excel has been referred to as “the best application ever written for Windows.” You may or may not agree with that statement, but you can't deny that Excel is one of the oldest Windows products and has undergone many reincarnations and face lifts over the years. Cosmetically, the current version — Excel 2013 — barely even resembles the original version. However, many of Excel's key elements have remained intact over the years, with significant enhancements, of course.

This chapter presents a concise overview of the features available in the more recent versions of Excel, with specific emphasis on Excel 2013. It sets the stage for the subsequent chapters and provides an overview for those who may have let their Excel skills get rusty.

Excel: What Is It Good For?

Much of the appeal of Excel is that it's so versatile. Excel's forte, of course, is performing numerical calculations, but Excel is also very useful for non-numeric applications. Here are just a few uses for Excel:

• Number crunching: Create budgets, tabulate expenses, analyze survey results, and perform just about any type of financial analysis you can think of.

• Creating charts: Create a wide variety of highly customizable charts.

• Organizing lists: Use the row-and-column layout to store lists efficiently.

• Text manipulation: Excel is a good tool for cleaning up and standardizing text-based data.

• Accessing other data: Import data from a wide variety of sources.

• Creating graphical dashboards: Summarize a large amount of business information in a concise format.

• Creating graphics and diagrams: Use Shapes and SmartArt to create professional-looking diagrams.

• Automating complex tasks: Perform a tedious task with a single mouse click with Excel's macro capabilities.

This book focuses on a single aspect of Excel: formulas. As you'll see, formulas play a significant role in all the tasks listed here.

What's New in Excel 2013?

When a new version of Microsoft Office is released, sometimes Excel gets lots of new features — and sometimes it gets very few new features. In the case of Office 2013, Excel got quite a few new features, but nothing truly earth-shattering.

Here's a quick summary of what's new in Excel 2013, relative to Excel 2010:

• Cloud storage: Excel is tightly integrated with Microsoft's SkyDrive web-based storage.

• Support for other devices: Excel is available for other devices, including touch-sensitive devices, such as Windows RT tablets and Windows phones.

• New aesthetics: Excel has a new “flat” look and displays an (optional) graphic in the title bar. Cursor movements and chart updates are animated, making it easier to see changes. Color schemes are no longer supported, and the Ribbon is hidden by default.

• Single document interface: Excel no longer supports the option to display multiple workbooks in a single window. Each workbook has its own top-level Excel window and Ribbon.

• New types of assistance: Excel provides recommended pivot tables and recommended charts.

• Fill Flash: This is a new way to extract (by example) relevant data from text strings. You can also use this feature to combine data in multiple columns.

• Support for Office Apps: You can download or purchase apps that can be embedded in a workbook file.

• Pivot charts: You can create pivot charts without creating a pivot table.

• New Slicer option: The Slicer feature, introduced in Excel 2010 for use with pivot tables, has been expanded and now works with tables.

• Timeline filtering: Similar to Slicers, a Timeline makes it easy to filter data by dates.

• Quick Analysis: This feature provides single-click access to various data analysis tools.

• Enhanced chart formatting: Modifying charts is significantly easier.

• New worksheet functions: Excel 2013 supports dozens of new worksheet functions.

• Backstage: The Backstage screen has been reorganized and is easier to use.

• New add-ins: Three new add-ins are included (for Office Professional Plus only): PowerPivot, Power View, and Inquire.

The Object Model Concept

If you've dealt with computers for any length of time, you've undoubtedly heard the term “object-oriented programming” (OOP). An object essentially represents a software element that a programmer can manipulate. When using Excel, you may find it useful to think in terms of objects, even if you have no intention of becoming a programmer. An object-oriented approach can often help you keep the various elements in perspective.

Excel objects include the following:

• Excel itself

• An Excel workbook

• A worksheet in a workbook

• A range in a worksheet

• A button on a worksheet

• A ListBox control on a UserForm (a custom dialog box)

• A chart sheet

• A chart series in a chart

Notice the existence of an object hierarchy: The Excel object contains workbook objects, which contain worksheet objects, which contain range objects. This hierarchy is called Excel's object model. Other Microsoft Office products have their own object model. The object model concept proves to be vitally important when developing VBA macros. Even if you don't create macros, you may find it helpful to think in terms of objects.

The Workings of Workbooks

The core document of Excel is a workbook. Everything that you do in Excel takes place in a workbook.

Beginning with Excel 2007, workbook “files” are actually compressed folders. You may be familiar with compressed folders if you've ever used a file with a .zip extension. Inside the compressed folders are a number of files that hold all the information about your workbook, including charts, macros, formatting, and the data in its cells.

If you're the curious type, make a copy of an XLSX workbook file and add a .zip extension to the filename. Then unzip the file to see what's inside.

An Excel workbook can hold any number of sheets. The four types of sheets are

• Worksheets

• Chart sheets

• MS Excel 4.0 macro sheets (obsolete, but still supported)

• MS Excel 5.0 dialog sheets (obsolete, but still supported)

You can open or create as many workbooks as you want (each in its own window), but only one workbook is the active workbook at any given time. Similarly, only one sheet in a workbook is the active sheet. To activate a different sheet, click its corresponding tab at the bottom of the window, or press Ctrl+PgUp (for the previous sheet) or Ctrl+PgDn (for the next sheet). To change a sheet's name, double-click its Sheet tab and type the new text for the name. Right-clicking a tab brings up a shortcut menu with some additional sheet-manipulation options.

You can also hide the window that contains a workbook by using the View⇒Window⇒Hide command. A hidden workbook window remains open but not visible. Use the View⇒Window⇒Unhide command to make the window visible again. A single workbook can display in multiple windows (choose View⇒Window⇒New Window). Each window can display a different sheet or a different area of the same sheet.

Worksheets

The most common type of sheet is a worksheet — which you normally think of when you think of a spreadsheet. Excel 2013 worksheets have 16,384 columns and 1,048,576 rows.

 How big is a worksheet?

It's interesting to stop and think about the actual size of a worksheet. Do the arithmetic (16,384 × 1,048,576), and you'll see that a worksheet has 17,179,869,184 cells. Remember that this is in just one worksheet. A single workbook can hold more than one worksheet.

If you're using a 1920 x 1200 video mode with the default row heights and column widths, you can see 29 columns and 47 rows (or 1,363 cells) at a time — which is about .0000079 percent of the entire worksheet. In other words, more than 12.6 million screens of information reside within a single worksheet.

If you entered a single digit into each cell at the relatively rapid clip of one cell per second, it would take you more than 500 years, nonstop, to fill up a worksheet. To print the results of your efforts would require more than 36 million sheets of paper — a stack about 12,000 feet high (that's 10 Empire State Buildings stacked on top of each other).

Versions prior to Excel 2007 support only 256 columns and 65,536 rows. If you open such a file, Excel enters compatibility mode to work with the smaller worksheet grid. To work with the larger grid, you must save the file in one of the newer Excel formats (XLSX or XLSM). Then close the workbook and reopen it. XLSM files can contain macros; XLSX files cannot.

Having access to more cells isn't the real value of using multiple worksheets in a workbook. Rather, multiple worksheets are valuable because they enable you to organize your work better. Back in the old days, when a spreadsheet file consisted of a single worksheet, developers wasted a lot of time trying to organize the worksheet to hold their information efficiently. Now, you can store information on any number of worksheets and still access it instantly.

You have complete control over the column widths and row heights, and you can even hide rows and columns (as well as entire worksheets). You can display the contents of a cell vertically (or at an angle) and even wrap around to occupy multiple lines. In addition, you can merge cells together to form a larger cell.

Chart sheets

A chart sheet holds a single chart. Many users ignore chart sheets, preferring to use embedded charts, which are stored on the worksheet's drawing layer. Using chart sheets is optional, but they make it a bit easier to locate a particular chart, and they prove especially useful for presentations. I discuss embedded charts (or floating charts on a worksheet) later in this chapter.

Macro sheets and dialog sheets

This section discusses two obsolete Excel features that continue to be supported.

An Excel 4.0 macro sheet is a worksheet that has some different defaults. Its purpose is to hold XLM macros. XLM is the macro system used in Excel version 4.0 and earlier. This macro system was replaced by VBA in Excel 5.0 and is not discussed in this book.

An Excel 5.0 dialog sheet is a drawing grid that can hold text and controls. In Excel 5.0 and Excel 95, dialog sheets were used to make custom dialog boxes. UserForms were introduced in Excel 97 to replace these sheets.

The Excel User Interface

A user interface (UI) is the means by which an end user communicates with a computer program. Almost every Windows program that you use employs a menu and toolbar approach. That is, at the top of the screen is a menu bar that contains virtually every command available in the application, and below that is at least one toolbar, which provides shortcuts to some of the more frequently used commands.

With the release of Office 2007, though, the days of menus and toolbars are over. The UI for Excel consists of the following components:

• Tabs and the Ribbon

• The Quick Access toolbar

• Right-click (shortcut) menus

• The mini-toolbar

• Dialog boxes

• Keyboard shortcuts

• Task panes

The Ribbon

The Ribbon is the primary UI component in Excel. Replacing the traditional menu and most toolbars common in previous versions, its introduction in Excel 2007 was a significant departure from the interfaces of most Windows-based applications.

Microsoft felt that the commands contained in the old menu and toolbar system were becoming so numerous that a new paradigm was necessary. One of the main goals for developing the Ribbon was to provide the user with a single place to look for a particular feature. Every commonly used command available in Excel would be contained in the Ribbon (or in a dialog box or task pane accessed via the Ribbon). Although Microsoft succeeded in putting most of the available commands on the Ribbon, it's still a pretty big place.

When introduced in Office 2007, the Ribbon received mixed reviews. Some people hated it, and others loved it. For some, the hatred was so severe that they sought Excel 2007 add-ins that restored the old menus. Others set up online petitions, asking Microsoft to restore the old menus for Office. Fact is, the Ribbon is here to stay. After you get used to the Ribbon, it really is easier to use than the convoluted menu system that it replaced.

A few commands failed to make the cut and do not appear on the Ribbon, but they are still available if you know where to look for them. Right-click the Quick Access toolbar and choose Customize Quick Access Toolbar. Excel displays a dialog box with a list of commands that you can add to your Quick Access toolbar. Some of these commands aren't available elsewhere in the UI. You can also add new commands to the Ribbon: Right-click the Ribbon and select Customize The Ribbon.

Tabs, groups, and tools

The Ribbon is a band of tools that stretches across the top of the Excel window. About the vertical size of three of the old-style toolbars, the Ribbon sports a number of tabs, including Home, Insert, Page Layout, and others. On each tab are groups that contain related tools. On the Home tab, for example, you find the Clipboard group, the Font group, the Alignment group, and others.

Within the groups are tools, which are similar to the tools that existed on the old-style toolbars with one major difference: their different sizes. Tools that you use most often are larger than less-frequently used tools. For example, nearly one-half of the Clipboard group is consumed by the large Paste tool; the Cut, Copy, and Format Painter tools are much smaller. Microsoft determined that the Paste tool is the most used tool and thus sized it accordingly.

The Ribbon and all its components resize dynamically as you resize the Excel window horizontally. Smaller Excel windows collapse the tools on compressed tabs and groups, and maximized Excel windows on large monitors show everything that's available. Even in a small window, all Ribbon commands remain available. You just may need to click a few extra times to access them.

Figure 1-1 shows three sizes of the Ribbon when the Home tab is displayed, using an increasingly smaller horizontal window size.

Figure 1-1: The Ribbon sizes dynamically, depending on the horizontal size of Excel's window.

Navigation

Using the Ribbon is fairly easy with a mouse. You click a tab and then click a tool. If you prefer to use the keyboard, Microsoft has a feature just for you. Pressing Alt displays tiny squares with shortcut letters in them that hover over their respective tab or tool. Each shortcut letter that you press either executes its command or drills down to another level of shortcut letters. Pressing Esc cancels the letters or moves up to the previous level.

For example, a keystroke sequence of Alt+HBB adds a double border to the bottom of the selection. The Alt key activates the shortcut letters, the H shortcut activates the Home tab, the B shortcut activates the Borders tool menu, and the second B shortcut executes the Bottom Double Border command. Note that you don't have to keep the Alt key depressed while you press the other keys.

Contextual tabs

The Ribbon contains tabs that are visible only when they are needed. Generally, when a hidden tab appears, it's because you selected an object or a range with special characteristics (like a chart or a pivot table). A typical example is the Drawing Tools contextual tab. When you select a shape or WordArt object, the Drawing Tools tab is made visible and active. It contains many tools that are applicable only to shapes, such as shape-formatting tools.

Dialog box launchers

At the bottom of many of the Ribbon groups is a small box icon (a dialog box launcher) that opens a dialog box related to that group. Some of the icons open the same dialog boxes but to different areas. For instance, the Font group icon opens the Format Cells dialog box with the Font tab activated. The Alignment group opens the same dialog box, but activates the Alignment tab. The Ribbon makes using dialog boxes a far less–frequent activity than in the past because most of the commonly used operations can be done directly from the Ribbon.

Galleries and Live Preview

A gallery is a large collection of tools that look like the choice they represent. The Styles gallery, for example, does not just list the name of the style but also displays it in the same formatting that will be applied to the cell.

Although galleries help to give you an idea of what your object will look like when an option is selected, Live Preview takes it to the next level. Live Preview displays your object or data as it will look right on the worksheet when you hover over the gallery tool. By hovering over the various tools in the Format Table gallery, you can see exactly what your table will look like before you commit to a format.

Backstage View

The File tab is unlike the other tabs. Clicking the File tab doesn't change the Ribbon but takes you to the Backstage View (see Figure 1-2). This is where you perform most of the document-related activities: creating new workbooks, opening files, saving files, printing, and so on.

Figure 1-2: Clicking the File tab takes you to the Backstage View.

The Open tab in Backstage View also contains the list of recent documents (as many as 50), with a pushpin icon next to each entry that you can use to keep that document at the top of the list regardless of how many files you open and close.

Plus, Backstage View gives you access to the Excel Options dialog box, which contains dozens of settings for customizing Excel.

Shortcut menus and the mini toolbar

Excel also features dozens of shortcut menus. These menus appear when you right-click after selecting one or more objects. The shortcut menus are context sensitive. In other words, the menu that appears depends on the location of the mouse pointer when you right-click. You can right-click just about anything — a cell, a row or column border, a workbook title bar, and so on.

Right-clicking items often displays the shortcut menu as well as a mini toolbar, which is a floating toolbar that contains a dozen or so of the most popular formatting commands. Figure 1-3 shows the shortcut menu and mini toolbar when a cell is selected.

Figure 1-3: The shortcut menu and mini toolbar appear when you right-click a cell or selected range.

Dialog boxes

Some Ribbon commands display a dialog box, from which you can specify options or issue other commands. You'll find two general classes of dialog boxes in Excel:

• Modal dialog boxes:When a modal dialog box is displayed, it must be closed to execute the commands. An example is the Format Cells dialog box. None of the options you specify is executed until you click OK. Or click the Cancel button to close the dialog box without making any changes.

• Modeless dialog boxes: These are stay-on-top dialog boxes. An example is the Find and Replace dialog box. Modeless dialog boxes usually have a Close button rather than OK and Cancel buttons.

Some Excel dialog boxes use a notebook tab metaphor, which makes a single dialog box function as several different dialog boxes. An example is the Format Cells dialog box, shown in Figure 1-4.

Figure 1-4: Tabbed dialog boxes make many options accessible without overwhelming the user.

Customizing the UI

The Quick Access toolbar is a set of tools that the user can customize. By default, the Quick Access toolbar contains three tools: Save, Undo, and Redo. If you find that you use a particular Ribbon command frequently, right-click the command and choose Add to Quick Access Toolbar. You can make other changes to the Quick Access toolbar from the Quick Access Toolbar tab of the Excel Options dialog box. To access this dialog box, right-click the Quick Access toolbar and choose Customize Quick Access Toolbar.

You can also customize the Ribbon by using the Customize Ribbon tab of the Excel Options dialog box. Choose File⇒Options to display the Excel Options dialog box.

You can customize the Ribbon in these ways:

• Add a new tab.

• Add a new group to a tab.

• Add commands to a group.

• Remove groups from a tab.

• Remove commands from custom groups.

• Change the order of the tabs.

• Change the order of the groups within a tab.

• Change the name of a tab.

• Change the name of a group.

• Move a group to a different tab

• Reset the Ribbon to remove all customizations.

That's a fairly comprehensive list of customization options, but there are some actions that you cannot do:

• You cannot remove built-in tabs — but you can hide them.

• You cannot remove commands from built-in groups.

• You cannot change the order of commands in a built-in group.

Task panes

Yet another user interface element is the task pane. Task panes appear automatically in response to several commands. For example, to work with a picture, right-click the image and choose Format Picture. Excel responds by displaying the Format Picture task pane, shown in Figure 1-5. The task pane is similar to a dialog box except that you can keep it visible as long as it's needed.

The role of task panes has increased dramatically in Excel 2013. For example, when working with a chart, you can access a task pane that has an extensive selection of commands for every element within the chart.

Many of the task panes are very complex. For example, the Format Picture task pane has four icons along the top. Clicking an icon changes the command lists displayed below. Click an item in a command list, and it expands to show the options.

By default, the task pane is docked on the right side of the Excel window, but you can move it anywhere you like by clicking the title text and dragging. Excel remembers the last position, so the next time you use that task pane, it will be where you left it. There's no OK button in a task pane. When you're finished using a task pane, click the Close button (X) in the upper-right corner.

Keyboard shortcuts

Excel has many useful keyboard shortcuts. For example, you can press Ctrl+D to copy a cell to selected cells below it. If you're a newcomer to Excel — or you just want to improve your efficiency — I urge you to check out the Help system. (Search for keyboard and go from there.) Learning these shortcuts is key to becoming proficient in Excel. The Help file has tables that summarize useful keyboard commands and shortcuts.

Figure 1-5: The Format Picture task pane allows you to adjust and format an embedded picture.

Customized onscreen display

Excel offers some flexibility regarding onscreen display (status bar, Formula bar, the Ribbon, and so on). For example, click the Ribbon Display Options control (in the title bar) and you can choose how to display the Ribbon. You can hide everything except the title bar, thereby maximizing the amount of visible information.

The status bar at the bottom of the screen can be customized. Right-click the status bar and you see lots of options that allow you to control what information is displayed in the status bar.

Many other customizations can be made by choosing File⇒Options and clicking the Advanced tab. On this tab are several sections that deal with what displays onscreen.

Object and cell selecting

Generally, selecting objects in Excel conforms to standard Windows practices. You can select a range of cells by using the keyboard (press the Shift key, along with the arrow keys) or by clicking and dragging the mouse. To select a large range, click a cell at any corner of the range, scroll to the opposite corner of the range, and press Shift while you click the opposite corner cell.

You can use Ctrl+* (asterisk) to select an entire table. And when a large range is selected, you can use Ctrl+. (period) to move among the four corners of the range.

Clicking an object placed on the drawing layer selects the object. An exception occurs if the object has a macro assigned to it. In such a case, clicking the object executes the macro. To select multiple objects or noncontiguous cells, press Ctrl while you select the objects or cells.

 Changing your mind

You can reverse almost every action in Excel by using the Undo command, located on the Quick Access toolbar. Click Undo (or press Ctrl+Z) after issuing a command in error, and it's as if you never issued the command. You can reverse the effects of the past 100 actions that you performed by executing Undo more than once.