Index

A note on the digital index

A link in an index entry is displayed as the section title in which that entry appears. Because some sections have multiple index markers, it is not unusual for an entry to have several links to the same section. Clicking on any link will take you directly to the place in the text in which the marker appears.

Symbols

& (ampersand), in formulas, When working with complex conditions, break your formula apart into columns
* (asterisk), multiplication operator, Formulas work with your data
[] (brackets), in structured references, Excel’s Tables make your references quick and easy
: (colon), in cell references, Refer to a bunch of cells using a range
$ (dollar sign), in cell references, Use absolute references to prevent shifting on copy/paste
= (equals sign)
in logical expressions, IF gives results based on a Boolean condition
preceding formulas, Formulas work with your data
! (exclamation point), in cell references, Excel got the right answer using a more sophisticated reference, Excel got the right answer using a more sophisticated reference
/ (forward slash), division operator, Formulas work with your data
> (greater than sign), IF gives results based on a Boolean condition
>= (greater than or equal sign), IF gives results based on a Boolean condition
< (less than sign), IF gives results based on a Boolean condition
<= (less than or equal sign), IF gives results based on a Boolean condition
- (minus sign), subtraction operator, Formulas work with your data
<> (not equal to sign), IF gives results based on a Boolean condition
(()) parentheses
enclosing function arguments, Refer to a bunch of cells using a range
enclosing numbers, When you copy and paste a formula, the references shift
to group values in formulas, References keep your formulas working even if your data changes, Your computer business is in disarray
+ (plus sign), addition operator, Formulas work with your data

A

absolute references, Use absolute references to prevent shifting on copy/paste
addition, Formulas work with your data
(see also COUNTIF function; COUNTIFS function; SUM function)
advertising example, Should your friend Betty advertise?
advertising costs, adding to data, Should your friend Betty advertise?
breakeven points for multiple scenarios, Betty wants to know her breakeven
new customers, adding to data, Should your friend Betty advertise?
optimization model, checking, Do a sanity check on your Solver model
optimizing based on multiple, constrained cells, Solver can handle much more complex optimization problems
projections for TV and magazine ads, Betty has projections of best and worst cases for different ad configurations
alignment of cell contents, How to format your data
alignment principle of design, Use proximity and alignment to group like things together
ampersand (&), in formulas, When working with complex conditions, break your formula apart into columns
AND function, When working with complex conditions, break your formula apart into columns
appreciation calculation, The PMT formula in the broker’s spreadsheet calculates your monthly payment
arguments for functions, Refer to a bunch of cells using a range
array formulas, #3: The Data Analysis ToolPak
asterisk (*), multiplication operator, Formulas work with your data
auditing formulas, Formula auditing shows you the location of your formula’s arguments
Automotive Weekly example, Head First Automotive Weekly needs an analysis for their annual car review issue
average satisfaction and reliability by make, You’re ready to finish the magazine’s data tables
filtering average MPG by reliability, Your editor is impressed!
most expensive price for each make, Your pivot tables are a big hit!
rating by make and model, Your pivot tables are a big hit!
summarizing average MPG by make and type, You’ve been asked to do a lot of repetitive operations
AVERAGE function, Your doctor friend is on a deadline and has broken data, Should you rent additional parking?

B

balance sheet example, He likes it, but there’s something else...
bar charts, Your pie chart isn’t going over well with the corporate graphic artist, Your pie chart isn’t going over well with the corporate graphic artist
bold text, Use fonts to draw the eye to what is most important
Boolean data type, You need a function that tells Excel to treat your text as a value, Boolean expressions return a result of TRUE or FALSE
(see also logical expressions)
borders around cells, How to format your data, Use fonts to draw the eye to what is most important
brackets ([]), in structured references, Excel’s Tables make your references quick and easy
bubble charts, Your pie chart isn’t going over well with the corporate graphic artist

C

calculations (see formulas; functions)
cell ranges
named, automatically generating, Excel’s Tables make your references quick and easy
named, specifying manually, Named ranges simplify your formulas
selecting with mouse, Let Excel fill in ranges by starting your formula and using your mouse
specifying in formulas, Refer to a bunch of cells using a range, Excel got the right answer using a more sophisticated reference
Cell Styles, Use fonts to draw the eye to what is most important
cells, Formulas work with your data
formatting for (see formatting)
green triangles in, Somehow your average formula divided by zero
references to (see references)
characters (see text)
charts
appropriateness of, Head First Investments needs charts for its investment report, Use the Design and Layout tabs to rework your chart, Your pie chart isn’t going over well with the corporate graphic artist
bar charts, Your pie chart isn’t going over well with the corporate graphic artist, Your pie chart isn’t going over well with the corporate graphic artist
changing type of, Your pie chart isn’t going over well with the corporate graphic artist
creating, Create charts using the Insert tab
data for, selecting, Use the Design and Layout tabs to rework your chart
design and layout, Use the Design and Layout tabs to rework your chart, You’re starting to get tight on time...
from pivot tables, Your editor is impressed!, VLOOKUP will cross-reference the two data sources, Geopolitical Grunts would like a little more nuance
limitations of, Use the Design and Layout tabs to rework your chart
line charts, Your pie chart isn’t going over well with the corporate graphic artist, Your pie chart isn’t going over well with the corporate graphic artist
list of, Your pie chart isn’t going over well with the corporate graphic artist
pie charts, Head First Investments needs charts for its investment report, Your pie chart isn’t going over well with the corporate graphic artist
transforming data for, Your pie chart isn’t going over well with the corporate graphic artist
collaboration, #9: Collaboration
colon (:), in cell references, Refer to a bunch of cells using a range
color
of cell background, How to format your data, Use fonts to draw the eye to what is most important
of cell contents, How to format your data
columns
referencing all cells in, Excel got the right answer using a more sophisticated reference
sorting data by a specific column, Find the names of the big contributors
sorting data by multiple columns, grouped, Sorting shows you different perspectives on a large data set
splitting text data into, Text to Columns uses a delimiter to split up your data, FIND returns a number specifying the position of text
computer manufacturing example, Your computer business is in disarray
discounted prices, incorporating, Things just got even better...
gross profit margin, Your computer business is in disarray
least expensive parts, Your production manager has a spreadsheet with costs
projected profitability, Excel got the right answer using a more sophisticated reference
projected profitability for all computer models, Structured references are a different dimension of absolute reference
CONCATENATE function, Excel has a suite of functions for dealing with text
convention center example (see parking spaces example)
copying and pasting
cell formatting, #2: The format painter
formulas, Your friends agree: split the checks individually
references not shifted during, Use absolute references to prevent shifting on copy/paste, Structured references are a different dimension of absolute reference
references shifted during, Your friends agree: split the checks individually, Use absolute references to prevent shifting on copy/paste
with modifications, Text to Columns sees your formulas, not their results, #2: The format painter
COUNTIF function, Functions are organized by data type and discipline
COUNTIFS function, Summarize how many boats fall into each category
cross-referencing data sources, VLOOKUP will cross-reference the two data sources, Create segments to feed the right data into your analysis
CUMIPMT function, The PMT formula in the broker’s spreadsheet calculates your monthly payment
currency symbols, CRMFreak needs to present their financials to analysts
customer database example, Your database of analytic customers just crashed!
counting “s” characters, You need to vary the values that go into the second argument
extracting names, Business is starting to suffer for lack of customer data
extracting phone numbers, LEFT and RIGHT are basic text extraction functions
formatting data, Looks like time’s running out...
splitting text data into columns, Text to Columns uses a delimiter to split up your data

D

data analysis, The Main Campaign is delighted with your work, The pivot table summarized your data way faster than formulas would have, #1: Data analysis
Data Analysis ToolPak, #3: The Data Analysis ToolPak
data for exercises, downloading, Read Me
data types, Data in Excel can be text or numbers
(see also dates and times; numbers; text)
Boolean data type, You need a function that tells Excel to treat your text as a value, Boolean expressions return a result of TRUE or FALSE
changing, You need a function that tells Excel to treat your text as a value, You need a function that tells Excel to treat your text as a value
converting text to values, You need a function that tells Excel to treat your text as a value, Should you rent additional parking?
converting values to text, You need a function that tells Excel to treat your text as a value
determining data type of cell, functions for, You need a function that tells Excel to treat your text as a value
errors as, Errors are a special data type
numbers stored as text, Somehow your average formula divided by zero
database example (see customer database example)
databases, using with pivot tables, The pivot table summarized your data way faster than formulas would have, #7: Connecting to the Web
DATE function, Here’s the convention center’s ticket database for the next month
DATEDIF function, DATEDIF() will calculate time between dates using a variety of measures, Should you rent additional parking?
dates and times, Should you rent additional parking?
(see also parking spaces example; training program example)
converting serial numbers to, Here’s the convention center’s ticket database for the next month
converting to serial numbers, Subtracting one date from another tells you the number of days between the two dates, Should you rent additional parking?
differences between, DATEDIF() will calculate time between dates using a variety of measures, Should you rent additional parking?
formatting, Subtracting one date from another tells you the number of days between the two dates, Coach has an Excel challenge for you
functions for, finding, Here’s the convention center’s ticket database for the next month
mathematical operations on, Subtracting one date from another tells you the number of days between the two dates, DATEDIF() will calculate time between dates using a variety of measures, Excel represents time as decimal numbers from 0 to 1
sorting, Do you have time to amp up your training for the Massachusetts Marathon?
stored as numbers, Do you have time to amp up your training for the Massachusetts Marathon?, Excel represents time as decimal numbers from 0 to 1, Coach has an Excel challenge for you
DATEVALUE function, Subtracting one date from another tells you the number of days between the two dates, Should you rent additional parking?
delimiter, Text to Columns uses a delimiter to split up your data
design and layout, Use proximity and alignment to group like things together
(see also formatting)
alignment principle, Use proximity and alignment to group like things together
Cell Styles for, Use fonts to draw the eye to what is most important
for charts, Use the Design and Layout tabs to rework your chart, You’re starting to get tight on time...
keep it simple principle, Design principle: keep it simple
proximity principle, Use proximity and alignment to group like things together
Themes for, With your cell styles selected, use Themes to change your look
diagrams, #5: Shapes and SmartArt
#DIV/0! error, Your doctor friend is on a deadline and has broken data, Errors are a special data type
division, Formulas work with your data
dollar sign ($), in cell references, Use absolute references to prevent shifting on copy/paste
downloading data for exercises, Read Me
drug study example, Your doctor friend is on a deadline and has broken data
attempting caculations on text data, Your doctor friend is on a deadline and has broken data
converting text to numbers, You need a function that tells Excel to treat your text as a value
errors in spreadsheet, analyzing, A grad student also ran some stats...and there’s a problem

E

equals sign (=)
in logical expressions, IF gives results based on a Boolean condition
preceding formulas, Formulas work with your data
ERROR.TYPE function, Errors are a special data type
errors
#DIV/0! error, Your doctor friend is on a deadline and has broken data, Errors are a special data type
#N/A! error, Errors are a special data type
#NAME! error, A grad student also ran some stats...and there’s a problem, Errors are a special data type
#REF! error, A grad student also ran some stats...and there’s a problem, Errors are a special data type
#VALUE! error, A grad student also ran some stats...and there’s a problem, Errors are a special data type
analyzing, Your doctor friend is on a deadline and has broken data, A grad student also ran some stats...and there’s a problem
data type for, Errors are a special data type
functions for, Errors are a special data type
examples
advertising, Should your friend Betty advertise?
Automotive Weekly, Head First Automotive Weekly needs an analysis for their annual car review issue
balance sheet, He likes it, but there’s something else...
computer manufacturing, Your computer business is in disarray
customer database, Your database of analytic customers just crashed!
data for, downloading, Read Me
drug study, Your doctor friend is on a deadline and has broken data
fishermen, Are fishermen behaving on Lake Dataville?
fundraising, Political consultants need help decoding their fundraising database
government spending, You are with a watchdog that needs to tally budget money
house purchase, Should you buy a house or rent?
income statement, CRMFreak needs to present their financials to analysts
investment report, Head First Investments needs charts for its investment report
parking spaces, Should you rent additional parking?
restaurant expenses, Can you live it up on the last night of your vacation?
training program, Do you have time to amp up your training for the Massachusetts Marathon?
Excel, Your doctor friend is on a deadline and has broken data
(see also spreadsheets)
versions of, covered in this book, Read Me
website resources, Read Me
exclamation point (!), in cell references, Excel got the right answer using a more sophisticated reference, Excel got the right answer using a more sophisticated reference
exercise data, downloading, Read Me

F

filtering data
applying multiple filters, An unexpected note from the Main Campaign...
applying one filter, Filters hide data you don’t want to see, The Main Campaign is delighted with your work, Your spreadsheet shows ticket counts summarized for each date
in pivot tables, Your editor is impressed!
uses of, The Main Campaign is delighted with your work
FIND function, Excel has a suite of functions for dealing with text, FIND returns a number specifying the position of text
fishermen example, Are fishermen behaving on Lake Dataville?
counting boats in each category, Summarize how many boats fall into each category
counting boats over their limits, When working with complex conditions, break your formula apart into columns
determining type of boat from boat ID codes, You have data on catch amounts for each boat
font, changing, How to format your data
format painter, #2: The format painter
formatting, Use proximity and alignment to group like things together
(see also design and layout)
alignment of cell contents, How to format your data
bold text, Use fonts to draw the eye to what is most important
borders around cells, How to format your data, Use fonts to draw the eye to what is most important
Cell Styles, Use fonts to draw the eye to what is most important
color of cell background, How to format your data, Use fonts to draw the eye to what is most important
color of cell contents, How to format your data
copying from one cell to another, #2: The format painter
currency, CRMFreak needs to present their financials to analysts
dates and times, Subtracting one date from another tells you the number of days between the two dates, Coach has an Excel challenge for you
decimal places, How to format your data
excessive, avoiding, Design principle: keep it simple
font, How to format your data
formulas interpreting, How to format your data
italic text, Use fonts to draw the eye to what is most important
menu buttons for, How to format your data
percentages, How to format your data, Your computer business is in disarray
Shapes for, #5: Shapes and SmartArt
SmartArt for, #5: Shapes and SmartArt
formula bar, Formulas work with your data
formulas, Formulas work with your data
accuracy of, checking, References keep your formulas working even if your data changes
auditing, Formula auditing shows you the location of your formula’s arguments
combining results of, When you copy and paste a formula, the references shift
complex, breaking into columns, When working with complex conditions, break your formula apart into columns
concatenating references in, When working with complex conditions, break your formula apart into columns
copying and pasting, Your friends agree: split the checks individually
definition of, Formulas work with your data
entering in cells, Formulas work with your data, Your friends agree: split the checks individually
errors produced by (see errors)
formatting interpreted by, How to format your data
formatting not allowed in, CRMFreak needs to present their financials to analysts
functions in (see functions)
grouping parts of, with parentheses, References keep your formulas working even if your data changes, Your computer business is in disarray
mathematical operations in, Formulas work with your data
modeling (see models)
performance of recalculations, #6: Controlling recalculation and performance tuning
references to cell ranges in, Refer to a bunch of cells using a range, Let Excel fill in ranges by starting your formula and using your mouse
references to cells in, References keep your formulas working even if your data changes, Your friends agree: split the checks individually, Use absolute references to prevent shifting on copy/paste
references to named cell ranges in, Named ranges simplify your formulas, Excel’s Tables make your references quick and easy
references to worksheets in, MIN returns the lowest number in a series
forward slash (/), division operator, Formulas work with your data
functions, Refer to a bunch of cells using a range
(see also specific functions)
arguments for, Refer to a bunch of cells using a range
categories of, Functions are organized by data type and discipline
finding, You need a plan to find more functions, Here’s the convention center’s ticket database for the next month, Functions are organized by data type and discipline, Your spreadsheet shows ticket counts summarized for each date
for data type testing and conversions, You need a function that tells Excel to treat your text as a value, Should you rent additional parking?
for date and time operations, Here’s the convention center’s ticket database for the next month
for errors, Errors are a special data type
for logical expressions, IF gives results based on a Boolean condition
for pivot tables, Pivot table construction is all about previsualizing where your fields should go
for statistical calculations, Functions are organized by data type and discipline
for text operations, Excel has a suite of functions for dealing with text
fundraising example, Political consultants need help decoding their fundraising database
filtering data, Filters hide data you don’t want to see, The Main Campaign is delighted with your work
sorting data by largest contributors, Find the names of the big contributors
sorting data by largest contributors and location, Sorting shows you different perspectives on a large data set
zooming data, See a lot more of your data with Zoom
future value calculation, Excel’s loan functions all use the same basic elements
FV function, Excel’s loan functions all use the same basic elements

G

GETPIVOTDATA function, The pivot table summarized your data way faster than formulas would have
Goal Seek, Goal Seek optimizes a value by trying a bunch of different candidate values
government spending example, You are with a watchdog that needs to tally budget money
determining region from state, Your problems with region are bigger
filtering by department-level projects, Geopolitical Grunts would like a little more nuance
grouping by years, Geopolitical Grunts would like a little more nuance
pivot chart for household by region, VLOOKUP will cross-reference the two data sources
spending per household by agency and county, Sometimes the data you get isn’t enough
graphs (see charts)
greater than or equal sign (>=), IF gives results based on a Boolean condition
greater than sign (>), IF gives results based on a Boolean condition
green triangles in cells, Somehow your average formula divided by zero
grouping and summarizing data (see pivot tables)
grouping parts of formulas (see parentheses (()))
grouping sort results, Sorting shows you different perspectives on a large data set

H

headings, Cell Styles for, Cell styles keep formatting consistent for elements that repeat
help, on functions, You need a plan to find more functions
hiding data (see filtering data)
HLOOKUP function, Create segments to feed the right data into your analysis
Home tab in Ribbon, How to format your data
house purchase example, Should you buy a house or rent?
assumptions, analyzing, Formulas must be correct, and assumptions must be reasonable
determining money able to borrow, Should you buy a house or rent?
incorporating net present value, Use Net Present Value to discount future costs to today’s values
mortgage model, Mortgage model, The PMT formula in the broker’s spreadsheet calculates your monthly payment
rent model, Models in Excel can get complicated, Formula auditing shows you the location of your formula’s arguments

I

IF function, Boolean expressions return a result of TRUE or FALSE
IFERROR function, Errors are a special data type
income statement example, CRMFreak needs to present their financials to analysts
balance sheet, He likes it, but there’s something else...
design and layout, Design principle: keep it simple
formatting, CRMFreak needs to present their financials to analysts
income statement calculations, CRMFreak needs to present their financials to analysts
interest payments calculation, The PMT formula in the broker’s spreadsheet calculates your monthly payment
interest rate calculation, Excel’s loan functions all use the same basic elements
investment report example, Head First Investments needs charts for its investment report
chart for portfolio allocation, Head First Investments needs charts for its investment report, Your pie chart isn’t going over well with the corporate graphic artist
design and layout for chart, Use the Design and Layout tabs to rework your chart
time chart comparing stocks, Your pie chart isn’t going over well with the corporate graphic artist
ISBLANK function, You need a function that tells Excel to treat your text as a value
ISERR function, Errors are a special data type
ISNONTEXT function, You need a function that tells Excel to treat your text as a value
ISREF function, You need a function that tells Excel to treat your text as a value
ISTEXT function, You need a function that tells Excel to treat your text as a value, Should you rent additional parking?
italic text, Use fonts to draw the eye to what is most important

K

keep it simple principle of design, Design principle: keep it simple

L

Lake Dataville example (see fishermen example)
layout (see design and layout; formatting)
LEFT function, Excel has a suite of functions for dealing with text, LEFT and RIGHT are basic text extraction functions, FIND returns a number specifying the position of text
LEN function, Business is starting to suffer for lack of customer data
less than or equal sign (<=), IF gives results based on a Boolean condition
less than sign (<), IF gives results based on a Boolean condition
line charts, Your pie chart isn’t going over well with the corporate graphic artist, Your pie chart isn’t going over well with the corporate graphic artist
loans
appreciation calculation, The PMT formula in the broker’s spreadsheet calculates your monthly payment
assumptions of, analyzing, Formulas must be correct, and assumptions must be reasonable
future value calculation, Excel’s loan functions all use the same basic elements
interest payments calculation, The PMT formula in the broker’s spreadsheet calculates your monthly payment
interest rate calculation, Excel’s loan functions all use the same basic elements
net present value calculation, Use Net Present Value to discount future costs to today’s values
number of periods calculation, Excel’s loan functions all use the same basic elements
opportunity cost calculation, The PMT formula in the broker’s spreadsheet calculates your monthly payment
payment calculation, Formula auditing shows you the location of your formula’s arguments
present value calculation, Should you buy a house or rent?
logical expressions
counting elements based on, Functions are organized by data type and discipline, Summarize how many boats fall into each category
functions for, IF gives results based on a Boolean condition
operators for, IF gives results based on a Boolean condition
testing if a cell contains an error, Errors are a special data type
testing if all expressions are TRUE, When working with complex conditions, break your formula apart into columns
testing if at least one expression is TRUE, When working with complex conditions, break your formula apart into columns
testing if TRUE or FALSE, Boolean expressions return a result of TRUE or FALSE
looking up values in a table, VLOOKUP will cross-reference the two data sources, Create segments to feed the right data into your analysis
lowest value, finding, MIN returns the lowest number in a series, Should you rent additional parking?

M

magazine example (see Automotive Weekly example)
Massachusetts Marathon example (see training program example)
mathematical operations
on dates and times, Subtracting one date from another tells you the number of days between the two dates, DATEDIF() will calculate time between dates using a variety of measures, Excel represents time as decimal numbers from 0 to 1
specifying in formulas, Formulas work with your data
totals (see SUM function)
MAX function, MIN returns the lowest number in a series
menu row at top of screen (Ribbon), How to format your data
MIN function, MIN returns the lowest number in a series, Should you rent additional parking?
minus sign (–), subtraction operator, Formulas work with your data
models, Models in Excel can get complicated
assumptions of, analyzing, Formulas must be correct, and assumptions must be reasonable, Goal Seek optimizes a value by trying a bunch of different candidate values
different scenarios for, Scenarios helps you keep track of different inputs to the same model
optimizations for, Goal Seek optimizes a value by trying a bunch of different candidate values, Solver can handle much more complex optimization problems, Do a sanity check on your Solver model
money values, currency symbols for, CRMFreak needs to present their financials to analysts
mortgages (see loans)
mouse, selecting cell ranges using, Let Excel fill in ranges by starting your formula and using your mouse
multiplication, Formulas work with your data

N

#N/A! error, Errors are a special data type
#NAME! error, A grad student also ran some stats...and there’s a problem, Errors are a special data type
named ranges
automatically generating, Excel’s Tables make your references quick and easy
specifying manually, Named ranges simplify your formulas
negative numbers, When you copy and paste a formula, the references shift
nesting IF functions, Your IF formulas need to accommodate the complete naming scheme
net present value calculation, Use Net Present Value to discount future costs to today’s values
not equal to sign (<>), IF gives results based on a Boolean condition
NPER function, Excel’s loan functions all use the same basic elements
NPV function, Use Net Present Value to discount future costs to today’s values
numbers
converting dates to serial numbers, Subtracting one date from another tells you the number of days between the two dates, Should you rent additional parking?
converting serial numbers to dates, Here’s the convention center’s ticket database for the next month
currency symbols for, CRMFreak needs to present their financials to analysts
dates and times stored as, Do you have time to amp up your training for the Massachusetts Marathon?, Coach has an Excel challenge for you
dates stored as, Excel represents time as decimal numbers from 0 to 1
negative, When you copy and paste a formula, the references shift
stored as text, converting to numbers, You need a function that tells Excel to treat your text as a value, Should you rent additional parking?
stored as text, green triangle indicating, Somehow your average formula divided by zero

O

OLAP (Online Analytical Processing), #7: Connecting to the Web
Online Analytical Processing (OLAP), #7: Connecting to the Web
operators
logical operators, IF gives results based on a Boolean condition
mathematical operators, Formulas work with your data
opportunity cost calculation, The PMT formula in the broker’s spreadsheet calculates your monthly payment
optimization problems, solving, Solver can handle much more complex optimization problems, Do a sanity check on your Solver model
OR function, When working with complex conditions, break your formula apart into columns

P

parentheses (())
enclosing function arguments, Refer to a bunch of cells using a range
enclosing numbers, When you copy and paste a formula, the references shift
to group values in formulas, References keep your formulas working even if your data changes, Your computer business is in disarray
parking spaces example, Should you rent additional parking?
counting tickets sold on weekend dates, The Dataville Convention Center COO checks in...
determining weekend dates, Here’s the convention center’s ticket database for the next month
filtering dates with tickets over criteria, Your spreadsheet shows ticket counts summarized for each date
Paste Special, Text to Columns sees your formulas, not their results, #2: The format painter
payment calculation for loans, Formula auditing shows you the location of your formula’s arguments
percentages, How to format your data, Your computer business is in disarray
performance of recalculations, #6: Controlling recalculation and performance tuning
pie charts, Head First Investments needs charts for its investment report, Your pie chart isn’t going over well with the corporate graphic artist
pivot tables
charts from, Your editor is impressed!, VLOOKUP will cross-reference the two data sources, Geopolitical Grunts would like a little more nuance
creating, Pivot tables are an incredibly powerful tool for summarizing data
filtering data in, Your editor is impressed!
getting data from, The pivot table summarized your data way faster than formulas would have
uses of, You’ve been asked to do a lot of repetitive operations, The pivot table summarized your data way faster than formulas would have, Your editor is impressed!
using databases with, The pivot table summarized your data way faster than formulas would have
using with Solver, The pivot table summarized your data way faster than formulas would have
plus sign (+), addition operator, Formulas work with your data
PMT function, Formula auditing shows you the location of your formula’s arguments
political campaign example (see fundraising example)
present value calculation, Should you buy a house or rent?
profitability example (see computer manufacturing example)
PROPER function, Looks like time’s running out...
proximity principle of design, Use proximity and alignment to group like things together
PV function, Should you buy a house or rent?

R

ranges (see cell ranges)
RATE function, Excel’s loan functions all use the same basic elements
#REF! error, A grad student also ran some stats...and there’s a problem, Errors are a special data type
references
absolute references, Use absolute references to prevent shifting on copy/paste
concatenating, When working with complex conditions, break your formula apart into columns
relative references, Use absolute references to prevent shifting on copy/paste
shifted during copying and pasting, Your friends agree: split the checks individually
structured references, Excel’s Tables make your references quick and easy
testing if cell contains, You need a function that tells Excel to treat your text as a value
to all cells in a column, Excel got the right answer using a more sophisticated reference
to cell ranges, Refer to a bunch of cells using a range, Let Excel fill in ranges by starting your formula and using your mouse
to cells, References keep your formulas working even if your data changes, Your friends agree: split the checks individually, Use absolute references to prevent shifting on copy/paste
to named cell ranges, Named ranges simplify your formulas, Excel’s Tables make your references quick and easy
to worksheets, MIN returns the lowest number in a series
relative references, Use absolute references to prevent shifting on copy/paste
renting versus buying example (see house purchase example)
resources, Read Me
restaurant expenses example, Can you live it up on the last night of your vacation?
separating expenses individually, Your friends agree: split the checks individually
splitting total between each person, Formulas work with your data, References keep your formulas working even if your data changes, Use SUM to add the elements in a range
totaling money spent, Here’s what you budgeted and what you spent, References keep your formulas working even if your data changes
Ribbon (menu row at top of screen), How to format your data
RIGHT function, Excel has a suite of functions for dealing with text, LEFT and RIGHT are basic text extraction functions
running example (see training program example)

S

scaling spreadsheets, Use proximity and alignment to group like things together
scatter (XY) charts, Your pie chart isn’t going over well with the corporate graphic artist
scenarios, Scenarios helps you keep track of different inputs to the same model, Solver calculated your projections
segmentation of data
creating new fields based on existing fields, Sometimes the data you get isn’t enough, Geopolitical Grunts would like a little more nuance
lookup tables for, Here’s a lookup key, Create segments to feed the right data into your analysis
Shapes, #5: Shapes and SmartArt
size of spreadsheet, changing, Use proximity and alignment to group like things together
slash (/), division operator, Formulas work with your data
SmartArt, #5: Shapes and SmartArt
Solver, Solver can handle much more complex optimization problems, Do a sanity check on your Solver model
installing, Install Solver in Excel
saving results as scenario, Solver calculated your projections
using with pivot tables, The pivot table summarized your data way faster than formulas would have
sorting data
by multiple columns, grouped, Sorting shows you different perspectives on a large data set
by one column, Find the names of the big contributors
checking for accuracy of, Sorting shows you different perspectives on a large data set, Sorting shows you different perspectives on a large data set
customizing criteria for, Sorting shows you different perspectives on a large data set
dates and times, Do you have time to amp up your training for the Massachusetts Marathon?
tables used to improve accuracy of, Sorting shows you different perspectives on a large data set
uses of, The Main Campaign is delighted with your work
warnings during, Sorting shows you different perspectives on a large data set
spreadsheets
errors in (see errors)
files for (see workbooks)
formatting in (see formatting)
formulas in (see formulas)
functions in (see functions)
scaling, Use proximity and alignment to group like things together
worksheets in (see worksheets)
zooming, See a lot more of your data with Zoom, The Main Campaign is delighted with your work
square brackets ([]), in structured references, Excel’s Tables make your references quick and easy
statistical calculations, Your doctor friend is on a deadline and has broken data
(see also drug study example; parking spaces example)
averages, Your doctor friend is on a deadline and has broken data, Should you rent additional parking?
counting elements based on logical expression, Functions are organized by data type and discipline, Summarize how many boats fall into each category
functions for, Functions are organized by data type and discipline
maximum values, MIN returns the lowest number in a series
minimum values, MIN returns the lowest number in a series, Should you rent additional parking?
standard deviation, Errors are a special data type, Should you rent additional parking?
STDEV function, Errors are a special data type, Should you rent additional parking?
stock charts, Your pie chart isn’t going over well with the corporate graphic artist
strings (see text)
structured references, Excel’s Tables make your references quick and easy
styles for cells, Use fonts to draw the eye to what is most important
SUBSTITUTE function, Looks like time’s running out...
subtraction, Formulas work with your data, Your friends agree: split the checks individually
SUM function, Refer to a bunch of cells using a range, Should you rent additional parking?
summarizing and grouping data (see pivot tables)
surface charts, Your pie chart isn’t going over well with the corporate graphic artist

T

tables
creating for structured references, Excel’s Tables make your references quick and easy
grouping and summarizing data in (see pivot tables)
improving sorting accuracy with, Sorting shows you different perspectives on a large data set
looking up values in, VLOOKUP will cross-reference the two data sources, Create segments to feed the right data into your analysis
text, Your database of analytic customers just crashed!
(see also customer database example)
capitalizing words in, Looks like time’s running out...
concatenating, Excel has a suite of functions for dealing with text
copying and pasting with modifications, Text to Columns sees your formulas, not their results, #2: The format painter
determining length of a string, Business is starting to suffer for lack of customer data
finding location of specific characters in, Excel has a suite of functions for dealing with text, FIND returns a number specifying the position of text
functions for, list of, Excel has a suite of functions for dealing with text
getting left substring, Excel has a suite of functions for dealing with text, LEFT and RIGHT are basic text extraction functions, FIND returns a number specifying the position of text
getting right substring, Excel has a suite of functions for dealing with text, LEFT and RIGHT are basic text extraction functions
numbers stored as, converting to numbers, You need a function that tells Excel to treat your text as a value, Should you rent additional parking?
numbers stored as, green triangle indicating, Somehow your average formula divided by zero
removing extraneous spaces in, Excel has a suite of functions for dealing with text
replacing characters in, Looks like time’s running out...
splitting into columns, Text to Columns uses a delimiter to split up your data, FIND returns a number specifying the position of text
TEXT function, You need a function that tells Excel to treat your text as a value
Text to Columns, Text to Columns uses a delimiter to split up your data, FIND returns a number specifying the position of text
Themes, With your cell styles selected, use Themes to change your look
time information (see dates and times)
Track Changes, #9: Collaboration
training program example, Do you have time to amp up your training for the Massachusetts Marathon?
finding 10K races after training completed, Do you have time to amp up your training for the Massachusetts Marathon?
finding marathons after training completed, Coach has a better idea
time calculations, Excel represents time as decimal numbers from 0 to 1
triangles in cells, Somehow your average formula divided by zero
TRIM function, Excel has a suite of functions for dealing with text
TYPE function, You need a function that tells Excel to treat your text as a value

U

Undo feature, CRMFreak needs to present their financials to analysts

V

#VALUE! error, A grad student also ran some stats...and there’s a problem, Errors are a special data type
VALUE function
converting dates to serial numbers, VALUE() returns a number on dates stored as text
converting text to numbers, You need a function that tells Excel to treat your text as a value, Should you rent additional parking?
values (see numbers)
VBA (Visual Basic for Applications), #9: Collaboration
VLOOKUP function, VLOOKUP will cross-reference the two data sources, Create segments to feed the right data into your analysis

W

website resources, Read Me
what if analysis
finding optimal outcome based on multiple variables, Solver can handle much more complex optimization problems, Do a sanity check on your Solver model
finding optimal outcome based on one variable, Goal Seek optimizes a value by trying a bunch of different candidate values
multiple scenarios (see scenarios)
workbooks, Your production manager has a spreadsheet with costs
(see also spreadsheets)
worksheets, Your doctor friend is on a deadline and has broken data
(see also spreadsheets)
referencing in formulas, MIN returns the lowest number in a series
selecting with tabs, Your production manager has a spreadsheet with costs

X

.xls file extension, Read Me
.xlsx file extension, Read Me
XY (scatter) charts, Your pie chart isn’t going over well with the corporate graphic artist

Z

zooming data, See a lot more of your data with Zoom, The Main Campaign is delighted with your work