Exploring Microsoft Office Excel 2010: comprehensive

657 Pages·58.703 MB·English

by  Grauer, Robert T;Poatsy, Mary Anne;Mulbery, Keith;Hogan, Lynn

E
x
p
l
o
r
i
n
g

M
i
c
r
o
s
o
f
t

O
f
f
i
c
e

E
x
c
e
l

2
0
1
0
Exploring Microsoft Office Excel 2010
C
o
Comprehensive
m
p
Grauer Poatsy Mulbery Hogan
r
e
h First Edition
e
n
s
i
v
e

G
r
a
u
e
r

e
t

a
l
.

1
e
ISBN 978-1-29202-704-3
9 781292 027043
Pearson New International Edition

Exploring Microsoft Office Excel 2010
Comprehensive
Grauer Poatsy Mulbery Hogan
First Edition
International_PCL_TP.indd 1 7/29/13 11:23 AM
ISBN 10: 1-292-02704-5
ISBN 13: 978-1-292-02704-3
Pearson Education Limited
Edinburgh Gate
Harlow
Essex CM20 2JE
England and Associated Companies throughout the world
Visit us on the World Wide Web at: www.pearsoned.co.uk
© Pearson Education Limited 2014
All rights reserved. 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 or otherwise, without either the
prior written permission of the publisher or a licence permitting restricted copying in the United Kingdom
issued by the Copyright Licensing Agency Ltd, Saffron House, 6–10 Kirby Street, London EC1N 8TS.
All trademarks used herein are the property of their respective owners. The use of any trademark
in this text does not vest in the author or publisher any trademark ownership rights in such
trademarks, nor does the use of such trademarks imply any affi liation with or endorsement of this
book by such owners.
ISBN 10: 1-292-02704-5
ISBN 10: 1-269-37450-8
ISBN 13: 978-1-292-02704-3
ISBN 13: 978-1-269-37450-7
British Library Cataloguing-in-Publication Data
A catalogue record for this book is available from the British Library
Printed in the United States of America
Copyright_Pg_7_24.indd 1 7/29/13 11:28 AM
1122334455738394959381975759315795
P E A R S O N C U S T O M L I B R AR Y
Table of Contents
Glossary
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 1
1. Office Fundamentals and File Management
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 9
2. Introduction to Excel
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 75
3. Formulas and Functions
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 137
4. Charts
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 185
5. Datasets and Tables
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 237
6. Subtotals, PivotTables, and PivotCharts
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 295
7. What-If Analysis
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 349
8. Specialized Functions
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 393
9. Multiple-Sheet Workbook Management
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 451
10. Imports, Web Queries, and XML
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 495
11. Collaboration and Workbook Distribution
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 537
12. Templates, Styles, and Macros
Robert T. Grauer/Keith Mulbery/Mary Anne Poatsy 589
I
639
Index 639
II
GLOSSARY
100% stacked column chart A chart type that places (stacks) Bar chart A chart type that compares values across categories
data in one column per category, with each column having the using horizontal bars. In a bar chart, the horizontal axis
same height of 100%. displays values, and the vertical axis displays categories.
3-D chart A chart that contains a third dimension to each data Binding constraint A constraint that Solver enforces to reach the
series, creating a distorted perspective of the data. target value.
3-D formula A formula or function that refers to the same range in Border A line that surrounds a paragraph, a page, a table, or an
multiple worksheets, such as =SUM(Oct:Dec!C5:C10). image in a document, or that surrounds a cell or range of cells
in a worksheet.
Absolute cell reference A designation that provides a
permanentreference to a specific cell. When you copy a Breakpoint The lowest value for a specific category or series in a
formula containing an absolute cell reference, the cell lookup table.
reference in thecopied formula does not change, regardless
Bubble chart A chart type that shows relationships among three
of where you copy the formula. An absolute cell reference
values by using bubbles to show a third dimension.
appears with a dollar sign before both the column letter and
row number, such as $B$5. Calculated field A user-defined field that performs a calculation
based on other fields in a PivotTable.
Access A database program that is included in Microsoft
Office that enables you to store and manage database Category axis The chart element that displays descriptive group
objects, such as tables, queries, forms, and reports. names or labels, such as college names or cities, to identify
data.
Accessibility Checker A tool that detects issues that could hinder
a disabled person’s ability to use a workbook. Category label Text that describes a collection of data points in
achart.
Active cell The current cell in a worksheet. It is indicated by a dark
border onscreen. Cell The intersection of a column or row in a worksheet or table.
Analysis ToolPak An add-in program that contains tools for Cell address The unique identifier of a cell, starting with the
performing complex statistical analyses, such as ANOVA, column letter and then the row number, such as A9.
correlation, and t-tests.
Cell style A set of formatting options applied to worksheet cells.
AND function A logical function that returns TRUE when all
arguments are true and FALSE when at least one argument Change Tracking A collaboration tool that records certain types of
isfalse. changes made in a workbook.
Area chart A chart type that emphasizes magnitude of changes Changing variable cell A cell containing a variable whose value
over time by filling in the space between lines with acolor. changes until Solver optimizes the value in the objective cell.
Argument A variable or constant input, such as a cell reference Chart A visual representation of numerical data that compares
orvalue, needed to complete a function. The entire group data and helps reveal trends or patterns to help people make
ofarguments for a function is enclosed within parentheses. informed decisions.
Auto fill A feature that enables you to copy the contents of a cell Chart area A boundary that contains the entire chart and all of its
or a range of cells or to continue a sequence by dragging the elements, including the plot area, titles, legends, and labels.
fill handle over an adjacent cell or range of cells.
Chart sheet A sheet within a workbook that contains a single
AVERAGE function A predefined formula that calculates the chart and no spreadsheet data.
arithmetic mean, or average, of values in a range.
Chart title The label that describes the entire chart.
AVERAGEIF function A statistical function that calculates the
Circular reference A situation that occurs when a formula contains a
average of values in a range when a specified condition is met.
direct or an indirect reference to the cell containing the formula.
AVERAGEIFS function A statistical function that calculates the
Clip art An electronic illustration that can be inserted into an Office
average of a range of values when multiple conditions are met.
project.
Axis title A label that describes either the category axis or the
Clipboard An Office feature that temporarily holds selections that
value axis.
have been cut or copied.
Background An image that appears behind the worksheet data
Clustered column chart A type of chart that groups or clusters
onscreen; it does not print.
similar data into columns to compare values across categories.
Backstage view Display that includes commands related to common
Collaboration A process that occurs when multiple people work
file activities and that provides information on an open file.
together to achieve a common goal by using technology to
Backup A copy of a file, usually on another storage medium. edit the contents of a file.
From the Glossary ofExploring Microsoft® Office Excel 2010 Comprehensive, First Edition, Robert T. Grauer, Keith Mulbery, Mary Anne Poatsy.
Copyright © 2011 by Pearson Education, Inc. Printed by Pearson Prentice Hall. All rights reserved.
Color scale A conditional format that displays a particular color Cut Removes a selection from the original location and places it in
based on the relative value of the cell contents to other the Office Clipboard.
selected cells.
Data bar A conditional format that displays horizontal gradient or
Column chart A type of chart that displays data vertically in solid fill indicating the cell’s relative value compared to other
columns to compare values across different categories. selected cells.
Column index number The number of the column in the lookup Data label A descriptive label that shows the exact value of the
table that contains the return values. data points on the value axis in a chart.
Column width The horizontal measurement of a column in a table Data mining The process of analyzing large volumes of data to
or a worksheet. In Excel, it is measured by the number of identify patterns and trends.
characters or pixels.
Data point A numeric value that describes a single value on achart.
Comma-separated value (CSV) file A text file that uses commas
Data range property A setting that controls the format, refresh
to separate text into columns and a newline character to
rate, and other characteristics of a connection to external data.
separate data into rows.
Data series A group of related data points that appear in row(s) or
Command A button or area within a group that you click to
column(s) in the worksheet.
perform tasks.
Data validation Requires that rules be followed, such as entering
Comment (1) A notation attached to a cell to pose a question or
a value between 1 and 10, in order to allow data to be entered
provide commentary. (2) Documents programming code,
in a cell.
starts with an apostrophe, and appears in green in the VBA
Editor. Database function A function that analyzes data for selected
records in a table.
Comment indicator A colored triangle in the top-right corner of
acell to indicate that the cell contains a comment. DAVERAGE function A database function that calculates the
arithmetic mean of values in a database column based on
Compatibility Checker A tool that detects data and features that
specified conditions.
are not compatible with previous versions of Excel.
DCOUNT function A database function that counts the cells that
CONCATENATE function A text function that joins two or more contain numbers in a database column based on specified
text strings into one text string. conditions.
Conditional formatting A set of rules that apply special formatting Default A setting that is in place unless you specify otherwise.
to highlight or emphasize cells that meet specific conditions.
Delimiter A character, such as a comma or tab, used to separate
Constraint A limitation that imposes restrictions on a spreadsheet data in a text file.
model as Solver determines the optimum value for the
objective cell. Dependent cell A cell containing a formula that is dependent on
other cells to obtain its value.
Contextual tab A Ribbon tab that displays when an object, such
as a picture or clip art, is selected. Destination file A target file that contains a pointer to the sourcefile.
Dialog box A window that opens when you are accomplishing a
Copy Duplicates a selection from the original location and places
task that enables you to make selections or indicate settings
the copy in the Office Clipboard.
beyond those provided on the Ribbon.
COUNT function A statistical function that tallies the number
Dialog Box Launcher An icon in a Ribbon group that you can
ofcells in a range that contain values you can use in
click to open a related dialog box.
calculations, such as numerical and date data, but excludes
blank cells or text entries from the tally. Digital signature An electronic notation in a document to
authenticate the contents, confirms that a particular person
COUNTA function A statistical function that tallies the number
authorized it, and marks the workbook as final.
ofcells in a range that are not blank; that is, cells that contain
data whether a value, text, or a formula. DMAX function A database function that identifies the highest
value in a database column based on specified conditions.
COUNTBLANK function A statistical function that tallies the
number of cells in a range that are blank. DMIN function A database function that identifies the lowest value
in a database column based on specified conditions.
COUNTIF function A statistical function that counts the number of
cells in a range when a specified condition is met. Document Inspector A tool that detects hidden and personal
data in a workbook to remove.
COUNTIFS function A statistical function that counts the number
of cells within a range when multiple conditions are met. Document property An attribute, such as an author’s name or a
keyword, that describes a file.
Criteria range An area separate from the data table that specifies
the conditions used to filter the table. Doughnut chart A chart type that displays values as percentages
of the whole but may contain more than one data series.
CUMIPMT function A financial function that calculates cumulative
interest for specified payment periods. DSUM function A database function that adds values in a
database column based on specified conditions.
CUMPRINC function A financial function that calculates
cumulative principal for specified payment periods. Element An XML component, such as a start tag or data.
GLOSSARY
2
End tag An XML code that indicates the end of an element and Function ScreenTip A small pop-up description that displays the
contains the element’s name and slash character, such as arguments for a function as you enter it.
</Rent>.
FV function A financial function that calculates the future value of
Enhanced ScreenTip Provides a brief summary of a an investment, given a fixed interest rate, term, and periodic
commandwhen you place the mouse pointer on the payments.
command button.
Gallery A set of selections that appears when you click a More
Error alert A message that appears when the user enters invalid button, or in some cases when you click a command, in a
data in a cell containing a validation rule. Ribbon group.
Excel A spreadsheet program that is included in Microsoft Office Goal Seek A what-if analysis tool that identifies the necessary
that enables you to organize and manipulate quantitative data. input value to obtain a desired calculated goal.
Exploded pie chart A chart type in which one or more pie slices Gridline A horizontal or vertical line that extends from the
are separated from the rest of the pie chart. horizontal or vertical axis through the plot area.
Extensible Indicates that XML can be expanded to include Group A subset of a tab that organizes similar tasks together.
additional data.
Grouping (1) The process of selecting worksheets to perform the
eXtensible Markup Language (XML) A data-structuring standard same action at the same time. (2) A process of joining rows or
that enables data to be shared across applications, operating columns of related data into a single entity so that groups can
systems, and hardware. be collapsed or expanded for data analysis.
Field The smallest data element contained in a table, such as first History worksheet A specially created worksheet through the
name, last name, address, and phone number. Change Tracking feature that lists particular types of changes
made to a workbook; Excel deletes the History worksheet
File A document or item of information that you create with
when you save the workbook.
software and to which you give a name.
HLOOKUP function A lookup and reference function that looks up
Fill color The background color that appears behind data in a cell.
a value in a horizontal lookup table where the first row
Fill handle A small black square at the bottom-right corner of a contains the values to compare with the lookup value.
cell used to copy cell contents or text or number patterns to
Horizontal alignment The placement of data or text between the
adjacent cells.
left and right margins in a document, or cell margins in a
Filtering The process of specifying conditions to display only spreadsheet.
those records that meet the conditions.
Hyperlink (or link) An electronic marker that, when clicked,
Find Locates a word or phrase that you indicate in a document. connects to another location in a worksheet, workbook, file,
Fixed-width text file A text file that stores data in columns that Web page, or e-mail.
have a specific number of characters designated for each Icon set A conditional format that displays an icon representing a
column. value in the top third, quarter, or fifth based on values in the
Folder A named storage location where you can save files. selected range.
Font A complete set of characters—upper- and lowercase letters, IF function A logical function that evaluates a condition and
numbers, punctuation marks, and special symbols with the returns one value if the condition is true and a different value if
same design that includes size, spacing, and shape. the condition is false.
Format Painter A Clipboard group command that copies the IFERROR function A logical function that checks a value and
formatting of text from one location to another. returns the result if possible or an error message.
Formula A combination of cell references, operators, values, Importing The process of inserting data from one application or
and/or functions used to perform a calculation. file into another.
Formula auditing Tools that enable you to detect and correct INDEX function A lookup and reference function that returns a
errors in formulas by identifying relationships among cells. value or reference to a value within a range.
Formula AutoComplete A feature that displays a list of functions Input area A range of cells to enter values for variables or
and defined names that match letters as you type aformula. assumptions that will be used in formulas within a
workbook.
Formula Bar An element in Excel that appears below the Ribbon
and to the right of the Insert command that shows the Input message A description or instructions for data entry.
contents of the active cell so that you edit the text, value,
IPMT function A financial function that calculates periodic interest
date, formula, or function.
for a fixed-term, fixed-rate loan, or investment.
Freezing The process of keeping rows and/or columns visible
Key Tip The letter or number that displays over features on the
onscreen at all times even when you scroll through a large
Ribbon and Quick Access Toolbar.
dataset.
Keyword A special programming syntax used for a specific
Function A predefined computation that simplifies creating a
purpose that appears in blue in the VBA Editor.
complex calculation and produces a result based on inputs
known as arguments. Landscape Page or worksheet that is wider than it is tall.
GLOSSARY
3
Legend A key that identifies the color, gradient, picture, texture, thatis relative changes in the copied formula. A mixed cell
orpattern assigned to each data series in a chart. reference appears with the $ symbol before either the column
letter or row number, such as $B5 or B$5.
Library An organization method that collects files from different
locations and displays them as one unit. Multiple data series Two or more sets of data, such as the values
for Chicago, New York, and Los Angeles sales for 2010, 2011,
Line chart A chart type that displays lines connecting data points
and 2012.
to show trends over equal time periods, such as months,
quarters, years, or decades. Name Box An element in Excel that identifies the address or range
name of the active cell in a worksheet.
Linking The process of connecting cells from worksheets in one
workbook to cells on a worksheet in another workbook. Navigation Pane Located on the left side ofthe Windows Explorer
window, providing access to Favorites, Libraries, Homegroup,
Live Preview An Office feature that provides a preview of the
Computer, and Network areas.
results of a selection when you point to an option in a list.
Nested function A function that contains another function
Loan amortization table A schedule showing monthly payments,
embedded inside one or more of its arguments, such as the
interest per payment, amount toward paying off the loan, and
MIN function nested in =SUM(MIN(A1:A5),D10:D15).
the remaining balance for each payment.
Nonadjacent range A collection of multiple ranges that are not
Locked cell A cell that prevents users from making changes to
positioned in a contiguous cluster in an Excel worksheet.
that cell in a protected worksheet.
Nonbinding constraint A constraint that does not restrict the
Logic error Occurs when a formula adheres to syntax rules but
target value that Solver finds.
produces inaccurate results, such as using a wrong operator.
NOT function A logical function that reverses the truth value of its
Logical test An expression that evaluates to true or false.
argument by returning TRUE if the argument is false and
Lookup table A range that contains data for the basis of the FALSE if the argument is true.
lookup and data to be retrieved.
NOW function A date and time function that uses the computer’s
Lookup value The cell reference of the cell that contains the value clock to display the current date and time in a cell.
to look up within a lookup table. Nper The number of payment periods over the life of the loan.
LOWER function A text function that converts all uppercase NPER function A financial function that calculates the number of
letters to lowercase. periods for an investment or loan, given a fixed interest rate,
Macro A set of instructions that tells Excel which commands to periodic payment, and present value.
execute to automate repetitive tasks. NPV function A financial function that calculates the net present value
Macro Recorder A tool that records a series of commands in the ofan investment with periodic payments and a discount rate.
sequence performed by a user and converts the commands Objective cell The cell that contains the formula-based value that
into programming syntax. you want to maximize, minimize, or set to a value in Solver.
Margin The blank space around the sides, top, and bottom of a One-variable data table A data analysis tool that provides one or
document or worksheet. more results for comparison based on changing one variable.
MATCH function A lookup and reference function that identifies a OR function A logical function that returns TRUE if any argument
searched item’s position in a list. is true and FALSE if all arguments are false.
MAX function A statistical function that finds the highest value Order of precedence A rule that controls the sequence in which
ina range. arithmetic operations are performed.
MEDIAN function A statistical function that finds the midpoint Outline A hierarchical structure of data organized in a range.
value, which is the value that one-half of the population is
Output area A range of cells that contains the results of
above or below.
manipulating values in an input area.
Metadata Pieces of data, such as a keyword, that describe other
Page break An indication where data will start on another printed
data, such as the contents of a file.
page. The software inserts automatic page breaks based on data,
Microsoft Office A productivity software suite that includes word margins, and paper size. Users can insert additional page breaks.
processing, spreadsheet, presentation, and database software
Paste Places a cut or copied item in another location.
components.
PERCENTILE.EXC function A statistical function that identifies the
MIN function A statistical function that finds the lowest value in
kthpercentile of a value, exclusive of 0th and 100th percentiles.
arange.
PERCENTILE.INC function A statistical function that identifies the
Mini toolbar An Office feature that provides access to common
kthpercentile of a value, including the 0th and 100th percentiles.
formatting commands when text is selected.
PERCENTRANK.EXC function A statistical function that identifies
Mixed cell reference A designation that combines an absolute
a value’s rank as a percentile—excluding 0 and 1—of a list of
cell reference with a relative cell reference. When you copy
values.
aformula containing a mixed cell reference, either the column
letter or the row number that has the absolute reference PERCENTRANK.INC function A statistical function that identifies a
remains fixed, whereas the other part of the cell reference value’s rank as a percentile—between 0 and 1—of a list of values.
GLOSSARY
4
Personal macro workbook A hidden workbook stored in the RANK.EQ function A statistical function that identifies the rank of
XLStart folder that contains macros and opens automatically a value within a range of values, omitting the next rank when
when you start Excel. tie values exist.
Picture A graphic file that is retrieved from the Internet, a disk, Rate The periodic interest rate; the percentage of interest paid for
orCD and placed in an Office project. each payment period.
Pie chart A chart type that shows each data point in proportion to RATE function A financial function that calculates the periodic rate
the whole data series as a slice in a circular pie. for an investment or loan, given a set number of payments, a
fixed periodic payment, and present value.
PivotChart A graphical representation of data in a PivotTable.
Record A group of related fields, representing one entity, such as
PivotTable An organized, interactive tool that can sort, filter, and
data for one person, place, event, or concept.
summarize large amounts of data.
Plot area The region containing the graphical representation of the Refresh The process of updating data in Excel to match current
values in the data series. data in the external data source.
PMT function A financial function that calculates theperiodic Relative cell reference A designation that indicates a cell’s relative
payment for a loan with a fixed interest rate and fixed term. location within the worksheet using the column letter and row
number, such as B5. When a formula containing a relative cell
Pointing The process of using the mouse pointer to select cells
reference is copied, the cell references in the copied formula
while building a formula. Also known as semi-selection.
change relative to the position of the copied formula.
Portable Document Format (PDF) A universal file format that
Replace Finds text and replaces it with a word or phrase that you
preserves a document’s original data and formatting for
indicate.
multiplatform use.
Ribbon The long bar of tabs, groups, and commands located just
Portrait Page or worksheet that is taller than it is wide.
beneath the Title bar.
PowerPoint A presentations program that is included in Microsoft
Row height The vertical measurement of a row in a table or a
Office that enables you to prepare slideshow presentations
worksheet.
foraudiences.
Scenario A set of values that represent a possible situation.
PPMT function A financial function that calculates the principal
payment for a specified payment period given a fixed interest Scenario Manager A what-if analysis tool that enables you to
rate, term, and periodic payments. define and manage scenarios to compare how they affect
calculated results.
Precedent cell A cell that is referenced by a formula in another cell.
Scenario summary report A worksheet that contains an
Print area The range of cells within a worksheet that will print.
organized structured table of scenarios, their input values,
Print order The sequence in which the pages are printed. andtheir respective results.
Procedure A named sequence of statements that execute as Semi-selection The process of using the mouse pointer to select
oneunit. cells while building a formula. Also known as pointing.
PROPER function A text function that capitalizes the first letter in Shared workbook A file that enables multiple users to make
a text string and any other letters in text that follow any changes at the same time.
character other than a letter.
SharePoint site A central location for storing and accessing files.
Pv The present value of the loan or an annuity.
Sheet tab A visual item in Excel that looks like a folder tab that
PV function A financial function that calculates the present value displays the name of a worksheet, such as Sheet1or June Sales.
of an investment.
Shortcut A link, or pointer, to a program or computer resource.
QUARTILE.EXC function A statistical function that identifies the
value at a specific quartile, exclusive of 0 and 4. Signature line Enables a person to type or insert a visible digital
signature to authenticate the workbook.
QUARTILE.INC function A statistical function that identifies the
value at a specific quartile. Sizing handles A series of faint dots on the outside border of a
selected chart; enables you to adjust the size of the chart.
Quick Access Toolbar Provides one-click access to commonly
used commands. SkyDrive A central storage location where you can save and
access files via an Internet connection.
Radar chart A chart type that compares aggregate values of three or
more variables represented on axes starting from the same point. Slicer A window listing all items in a field that enables efficient
filtering.
Range A group of adjacent or contiguous cells in an Excel worksheet.
Solver An add-in application that manipulates variables based on
Range name A word or string of characters assigned to one or
constraints to find the optimal solution to a problem.
more cells. It can be up to 255 letters, characters, or numbers,
starting with a letter. Sorting Listing records or text in a specific sequence, such as
alphabetically by last name.
RANK.AVG function A statistical function that identifies the rank
of a value with a range of values, providing an average ranking Source file A file that contains original data that can be used in
for identical values. another file.
GLOSSARY
5