E-Book
ohne Abo 32,99 €

Excel 2013 Bible E-Book

John Walkenbach

0,0

32,99 €

Sammeln Sie Punkte in unserem Gutscheinprogramm und kaufen Sie E-Books und Hörbücher mit bis zu 100% Rabatt.
Mehr erfahren.

Herausgeber: John Wiley & Sons
Kategorie: Wissenschaft und neue Technologien
Serie: Bible
Sprache: Englisch
Veröffentlichungsjahr: 2013

Beschreibung

Excel at Excel with the help of this bestselling spreadsheet guide John Walkenbach's name is synonymous with excellence in computer books that decipher the complexities of Microsoft Excel. Known as "Mr. Spreadsheet," Walkenbach shows you how to maximize the power of Excel 2013 while bringing you up to speed on the latest features. This perennial bestseller is fully updated to cover all the new features of Excel 2013, including how to navigate the user interface, take advantage of various file formats, master formulas, analyze data with PivotTables, and more. Whether you're an Excel beginner who is looking to get more savvy or an advanced user looking to become a power user, this latest edition provides you with comprehensive coverage as well as helpful tips, tricks, and techniques that you won't find anywhere else. * Shares the invaluable insight of Excel guru and bestselling author "Mr. Spreadsheet" John Walkenbach as he guides you through every aspect of Excel 2013 * Provides essential coverage of all the newest features of Excel 2013 * Presents material in a clear, concise, logical format that is ideal for all levels of Excel experience * Features a website that includes downloadable templates and worksheets from the book Chart your path to fantastic formulas and stellar spreadsheets with Excel 2013 Bible!

Details

Sie lesen das E-Book in den Legimi-Apps auf:

Android

iOS

von Legimi
zertifizierten E-Readern

Seitenzahl: 1249

Bewertungen

0,0

Rezensionen(0 Rezensionen)

Leseprobe

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

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

John Walkenbach

Excel® 2013 Bible

Published byJohn Wiley & Sons, Inc.10475 Crosspoint BoulevardIndianapolis, IN 46256www.wiley.com

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 CreaseyJennifer Mayberry

Quality Control Technicians

Jessica KramerLauren 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.

For Tablet Users

Excel 2013 is also available for mobile devices such as tablets and smartphones. If you happen to be using one of these devices, you probably already know the basic touch gestures.

This book doesn't cover specific touchscreen gestures, but these three guidelines should work most of the time:

• When you read “click,” you should tap. Quickly touching and releasing your finger on a button is the same as clicking it with a mouse.

• When you read “double-click,” tap twice. Touching twice in rapid succession is equivalent to double-clicking.

• When you read “right-click,” press and hold your finger on the item until a menu appears. Tap an item on the pop-up menu to execute the command.

Make sure you enable Touch mode from the Quick Access toolbar. Touch mode increases the spacing between the Ribbon commands, making it less likely that you'll touch the wrong command. If the Touch mode command is not in your Quick Access toolbar, touch the rightmost control and select Touch Mode. This command toggles between normal mode and Touch mode.

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

Chapter 1: Introducing Excel

In This Chapter

Understanding what Excel is used for

Looking at what's new in Excel 2013

Learning the parts of an Excel window

Introducing the Ribbon, shortcut menus, dialog boxes, and task panes

Navigating Excel worksheets

Introducing Excel with a step-by-step hands-on session

This chapter is an introductory overview of Excel 2013. If you're already familiar with a previous version of Excel, reading (or at least skimming) this chapter is still a good idea.

Identifying What Excel Is Good For

Excel, as you probably know, is the world's most widely used spreadsheet software and part of the Microsoft Office suite. Other spreadsheet software is available, but Excel is by far the most popular and has been the world standard for many years.

Much of the appeal of Excel is due to the fact 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 of the 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: Clean up and standardize 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.

Seeing What's New in Excel 2013

When a new version of Microsoft Office is released, sometimes Excel gets lots of new features and other times it gets very few new features. In the case of Office 2013, Excel got quite a few new features.

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. The default color scheme is white, but you can choose from two other color schemes (light gray and dark gray) in the General tab of the Excel Options dialog box.

• 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: Fill Flash 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 Apps for Office: You can download or purchase apps that can be embedded in a workbook file.

• The Data Model: Create pivot tables from multiple data tables, combined in a relational manner.

• 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 the Slicer, the Timeline makes it easy to filter data by dates.

• Quick Analysis: Quick Analysis 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.

Understanding Workbooks and Worksheets

The work you do in Excel is performed in a workbook file. You can have as many workbooks open as you need, and each one appears in its own window. By default, Excel workbooks use an .xlsx file extension.

Note

In previous versions of Excel, users could work with multiple workbooks in a single window. That is no longer an option in Excel 2013. Every workbook that you open has its own window.

Each workbook contains one or more worksheets, and each worksheet is made up of individual cells. Each cell can contain a value, a formula, or text. A worksheet also has an invisible draw layer, which holds charts, images, and diagrams. Each worksheet in a workbook is accessible by clicking the tab at the bottom of the workbook window. In addition, a workbook can store chart sheets; a chart sheet displays a single chart and is also accessible by clicking a tab.

Newcomers to Excel are often intimidated by all the different elements that appear within Excel's window. After you become familiar with the various parts, it all starts to make sense, and you'll feel right at home.

Figure 1.1 shows you the more important bits and pieces of Excel. As you look at the figure, refer to Table 1.1 for a brief explanation of the items shown in the figure.

Figure 1.1

The Excel screen has many useful elements that you will use often.

Table 1.1 Parts of the Excel Screen That You Need to Know

Name

Description

Active cell indicator

This dark outline indicates the currently active cell (one of the 17,179,869,184 cells on each worksheet).

Collapse the Ribbon button

Click this button to temporarily hide the Ribbon. Click it again to make the Ribbon remain visible.

Column letters

Letters range from A to XFD — one for each of the 16,384 columns in the worksheet. You can click a column heading to select an entire column of cells or drag a column border to change its width.

File button

Click this button to open Backstage view, which contains many options for working with your document (including printing) and setting Excel options.

Formula bar

When you enter information or formulas into a cell, it appears in this bar.

Help button

Click this button to display the Excel Help system window.

Horizontal scrollbar

Use this tool to scroll the sheet horizontally.

Macro recorder indicator

Click to start recording a VBA macro. The icon changes while your actions are being recorded. Click again to stop recording.

Name box

This box displays the active cell address or the name of the selected cell, range, or object.

New Sheet button

Add a new worksheet by clicking the New Sheet button (which is displayed after the last sheet tab).

Page View buttons

Click these buttons to change the way the worksheet is displayed.

Quick Access toolbar

This customizable toolbar holds commonly used commands. The Quick Access toolbar is always visible, regardless of which tab is selected.

Ribbon

This is the main location for Excel commands. Clicking an item in the tab list changes the Ribbon that is displayed.

Ribbon Display Options

A drop-down control that offers three options related to displaying the Ribbon.

Row numbers

Numbers range from 1 to 1,048,576 — one for each row in the worksheet. You can click a row number to select an entire row of cells.

Sheet tabs

Each of these notebook-like tabs represents a different sheet in the workbook. A workbook can have any number of sheets, and each sheet has its name displayed in a sheet tab.

Sheet tab scroll buttons

Use these buttons to scroll the sheet tabs to display tabs that aren't visible. You can also right-click to get a list of sheets.

Status bar

This bar displays various messages, as well as the status of the Num Lock, Caps Lock, and Scroll Lock keys on your keyboard. It also shows summary information about the range of cells selected. Right-click the status bar to change the information displayed.

Tab list

Use these commands to display a different Ribbon, similar to a menu.

Title bar

This displays the name of the program and the name of the current workbook. It also holds the Quick Access toolbar (on the left) and some control buttons that you can use to modify the window (on the right).

Vertical scrollbar

Use this to scroll the sheet vertically.

Window Close button

Click this button to close the active workbook window.

Window Maximize/Restore button

Click this button to increase the workbook window's size to fill the entire screen. If the window is already maximized, clicking this button “unmaximizes” Excel's window so that it no longer fills the entire screen.

Window Minimize button

Click this button to minimize the workbook window. The window displays as an icon in the Windows taskbar.

Zoom control

Use this to zoom your worksheet in and out.

Moving Around a Worksheet

This section describes various ways to navigate the cells in a worksheet.

Every worksheet consists of rows (numbered 1 through 1,048,576) and columns (labeled A through XFD). Column labeling works like this: After column Z comes column AA, which is followed by AB, AC, and so on. After column AZ comes BA, BB, and so on. After column ZZ is AAA, AAB, and so on.

The intersection of a row and a column is a single cell, and each cell has a unique address made up of its column letter and row number. For example, the address of the upper-left cell is A1. The address of the cell at the lower right of a worksheet is XFD1048576.

At any given time, one cell is the active cell. The active cell is the cell that accepts keyboard input, and its contents can be edited. You can identify the active cell by its darker border, as shown in Figure 1.2. Its address appears in the Name box. Depending on the technique that you use to navigate through a workbook, you may or may not change the active cell when you navigate.

Figure 1.2

The active cell is the cell with the dark border — in this case, cell C8.

Notice that the row and column headings of the active cell appear in a different color to make it easier to identify the row and column of the active cell.

Note

Excel 2013 is also available for devices such as tablets and phones. These devices use a touch interface. This book assumes the reader has a traditional keyboard and mouse — it doesn't cover the touch-related commands.

Navigating with your keyboard

Not surprisingly, you can use the standard navigational keys on your keyboard to move around a worksheet. These keys work just as you'd expect: The down arrow moves the active cell down one row, the right arrow moves it one column to the right, and so on. PgUp and PgDn move the active cell up or down one full window. (The actual number of rows moved depends on the number of rows displayed in the window.)

Tip

You can use the keyboard to scroll through the worksheet without changing the active cell by turning on Scroll Lock, which is useful if you need to view another area of your worksheet and then quickly return to your original location. Just press Scroll Lock and use the navigation keys to scroll through the worksheet. When you want to return to the original position (the active cell), press Ctrl+Backspace. Then, press Scroll Lock again to turn it off. When Scroll Lock is turned on, Excel displays ScrollLock in the status bar at the bottom of the window.

The Num Lock key on your keyboard controls how the keys on the numeric keypad behave. When Num Lock is on, the keys on your numeric keypad generate numbers. Many keyboards have a separate set of navigation (arrow) keys located to the left of the numeric keypad. The state of the Num Lock key doesn't affect these keys.

Table 1.2 summarizes all the worksheet movement keys available in Excel.

Table 1.2 Excel Worksheet Movement Keys

Key

Action

Up arrow ()

Moves the active cell up one row

Down arrow ()

Moves the active cell down one row

Left arrow (←) or Shift+Tab

Moves the active cell one column to the left

Right arrow (→) or Tab

Moves the active cell one column to the right

PgUp

Moves the active cell up one screen

PgDn

Moves the active cell down one screen

Alt+PgDn

Moves the active cell right one screen

Alt+PgUp

Moves the active cell left one screen

Ctrl+Backspace

Scrolls the screen so that the active cell is visible

Scrolls the screen up one row (active cell does not change)

Scrolls the screen down one row (active cell does not change)

←*

Scrolls the screen left one column (active cell does not change)

→*

Scrolls the screen right one column (active cell does not change)

* With Scroll Lock on

Navigating with your mouse

To change the active cell by using the mouse, just click another cell, and it becomes the active cell. If the cell that you want to activate isn't visible in the workbook window, you can use the scrollbars to scroll the window in any direction. To scroll one cell, click either of the arrows on the scrollbar. To scroll by a complete screen, click either side of the scrollbar's scroll box. You can also drag the scroll box for faster scrolling.

Tip

If your mouse has a wheel, you can use the mouse wheel to scroll vertically. Also, if you click the wheel and move the mouse in any direction, the worksheet scrolls automatically in that direction. The more you move the mouse, the faster the scrolling.

Press Ctrl while you use the mouse wheel to zoom the worksheet. If you prefer to use the mouse wheel to zoom the worksheet without pressing Ctrl, choose File ⇒ Options and select the Advanced section. Place a check mark next to the Zoom on Roll with IntelliMouse check box.

Using the scrollbars or scrolling with your mouse doesn't change the active cell. It simply scrolls the worksheet. To change the active cell, you must click a new cell after scrolling.

Using the Ribbon

In Office 2007, Microsoft made a dramatic change to the user interface. Traditional menus and toolbars were replaced with the Ribbon, a collection of icons at the top of the screen. The words above the icons are known as tabs: the Home tab, the Insert tab, and so on. Most users find that the Ribbon is easier to use than the old menu system; it can also be customized to make it even easier to use (see Chapter 24).

The Ribbon can either be hidden or visible (it's your choice). To toggle the Ribbon's visibility, press Ctrl+F1 (or double-click a tab at the top). If the Ribbon is hidden, it temporarily appears when you click a tab and hides itself when you click in the worksheet. The title bar has a control named Ribbon Display Options (next to the Help button). Click the control and choose one of three Ribbon options: Auto-hide, Show Tabs, or Show Tabs and Commands.

Ribbon tabs

The commands available in the Ribbon vary, depending upon which tab is selected. The Ribbon is arranged into groups of related commands. Here's a quick overview of Excel's tabs:

• Home: You'll probably spend most of your time with the Home tab selected. This tab contains the basic Clipboard commands, formatting commands, style commands, commands to insert and delete rows or columns, plus an assortment of worksheet editing commands.

• Insert: Select this tab when you need to insert something in a worksheet — a table, a diagram, a chart, a symbol, and so on.

• Page Layout: This tab contains commands that affect the overall appearance of your worksheet, including some settings that deal with printing.

• Formulas: Use this tab to insert a formula, name a cell or a range, access the formula auditing tools, or control how Excel performs calculations.

• Data: Excel's data-related commands are on this tab, including data validation commands.

• Review: This tab contains tools to check spelling, translate words, add comments, or protect sheets.

• View: The View tab contains commands that control various aspects of how a sheet is viewed. Some commands on this tab are also available in the status bar.

• Developer: This tab isn't visible by default. It contains commands that are useful for programmers. To display the Developer tab, choose File ⇒ Options and then select Customize Ribbon. In the Customize the Ribbon section on the right, make sure Main Tabs is selected in the drop-down control, and place a check mark next to Developer.

• Add-Ins: This tab is visible only if you loaded an older workbook or add-in that customizes the menu or toolbars. Because menus and toolbars are no longer available in Excel 2013, these user interface customizations appear on the Add-Ins tab.

The preceding list contains the standard Ribbon tabs. Excel may display additional Ribbon tabs, resulting from add-ins or macros.

Note

Although the File button shares space with the tabs, it's not actually a tab. Clicking the File button displays a different screen (known as Backstage view), where you perform actions with your documents. This screen has commands along the left side. To exit the Backstage view, click the back arrow button in the upper-left corner.

The appearance of the commands on the Ribbon varies, depending on the width of the Excel window. When the Excel window is too narrow to display everything, the commands adapt; some of them might seem to be missing, but the commands are still available. Figure 1.3 shows the Home tab of the Ribbon with all controls fully visible. Figure 1.4 shows the Ribbon when Excel's window is made more narrow. Notice that some of the descriptive text is gone, but the icons remain. Figure 1.5 shows the extreme case when the window is made very narrow. Some groups display a single icon; however, if you click the icon, all the group commands are available to you.

Figure 1.3

The Home tab of the Ribbon.

Figure 1.4

The Home tab when Excel's window is made narrower.

Figure 1.5

The Home tab when Excel's window is made very narrow.

Contextual tabs

In addition to the standard tabs, Excel also includes contextual tabs. Whenever an object (such as a chart, a table, or a SmartArt diagram) is selected, specific tools for working with that object are made available in the Ribbon.

Figure 1.6 shows the contextual tabs that appear when a chart is selected. In this case, it has two contextual tabs: Design and Format. Notice that the contextual tabs contain a description (Chart Tools) in Excel's title bar. When contextual tabs appear, you can, of course, continue to use all the other tabs.

Figure 1.6

When you select an object, contextual tabs contain tools for working with that object.

Types of commands on the Ribbon

When you hover your mouse pointer over a Ribbon command, you'll see a pop-up box that contains the command's name, as well as a brief description. For the most part, the commands in the Ribbon work just as you would expect them to. You'll find several different styles of commands on the Ribbon:

• Simple buttons: Click the button, and it does its thing. An example of a simple button is the Increase Font Size button in the Font group of the Home tab. Some buttons perform the action immediately; others display a dialog box so that you can enter additional information. Button controls may or may not be accompanied by a descriptive label.

• Toggle buttons: A toggle button is clickable and conveys some type of information by displaying two different colors. An example is the Bold button in the Font group of the Home tab. If the active cell isn't bold, the Bold button displays in its normal color. If the active cell is already bold, the Bold button displays a different background color. If you click the Bold button, it toggles the Bold attribute for the selection.

• Simple drop-downs: If the Ribbon command has a small down arrow, the command is a drop-down. Click it, and additional commands appear below it. An example of a simple drop-down is the Conditional Formatting command in the Styles group of the Home tab. When you click this control, you see several options related to conditional formatting.

• Split buttons: A split button control combines a one-click button with a drop-down. If you click the button part, the command is executed. If you click the drop-down part (a down arrow), you choose from a list of related commands. An example of a split button is the Merge & Center command in the Alignment group of the Home tab (see Figure 1.7). Clicking the left part of this control merges and centers text in the selected cells. If you click the arrow part of the control (on the right), you get a list of commands related to merging cells.

Figure 1.7

The Merge & Center command is a split button control.

• Check boxes: A check box control turns something on or off. An example is the Gridlines control in the Show group of the View tab. When the Gridlines check box is checked, the sheet displays gridlines. When the control isn't checked, the gridlines don't appear.

• Spinners: Excel's Ribbon has only one spinner control: the Scale to Fit group of the Page Layout tab. Click the top part of the spinner to increase the value; click the bottom part of the spinner to decrease the value.

Some of the Ribbon groups contain a small icon in the bottom-right corner, known as a dialog box launcher.

Tausende von E-Books und Hörbücher

Ihre Zahl wächst ständig und Sie haben eine Fixpreisgarantie.

Sie haben über uns geschrieben: