THINKING SPREADSHEET

An Opinionated Guide to Problem Solving and Data Analysis
Using Microsoft Excel (or Your Favorite Alternative)

JOEL GRUS

A Note To The Reader

I wrote this book in 2010, after which it never sold as many copies as it should have. For years I've thought about web-ifying (and free-ifying) it, since there's a lot of useful (and entertaining) information in it, but I never got around to it until now.

It's mostly based on Excel 2007, but (let's face it) Excel doesn't change all that much anymore, and my estimate is that approximately 98% of what I've written still applies to the latest version. I've strikethroughed a few parts that don't. I spent a small amount of time trying to make the CSS attractive, but I'm not a web designer, and it shows.

If you find this useful and/or entertaining, I'd love to hear from you. You can email me at joelgrus@gmail.com, or find me on Twitter @joelgrus. If after this taste you want to buy a hard copy, it's available from Amazon.

Anyway, read it, enjoy it, recommend it to your friends!

Contents

Acknowledgements
Introduction
What Spreadsheets Are and Are Not
Spreadsheets are Structured Data
Spreadsheets are Relationships
Spreadsheets are not Databases
Spreadsheets are not Computer Programs
The Fundamentals
Cells
Ranges and Arrays
Selecting Cells and Ranges
Arrays
Constant Arrays
Values
Formulas
The Simplest Formula
Sorting
How to sort
Formats
Decoration
Resizing Rows and Columns
Row Heights
Conditional Formatting
Extensibility
Cut Copy Paste
Paste Special
Non-Excel Paste-Specials
Having Your Fill
Drag Fill
Double-Click Fill
The Almighty $
When to Use a $
Insert and Delete
Inserting a row or column
Inserting a smaller range
Delete
Insert Copied Cells
All Clear
Find and Replace
Shortcuts That Will Amaze Your Friends and Confound Your Enemies
Tab and Enter
Cut and Copy and Paste
Going To
Zipping around with Ctrl-Arrow
Selecting with Shift-Arrow
Filling Multiple Cells With Ctrl-Enter
Combining Shift-Arrow and Ctrl-Arrow
Keyboard Fills
Pete and Repeat
Do and Undo
The Quick Access Toolbar
Functions
Using a Function
Function Inputs
Using $ to Tweak Functions
Special
Function Pop-ups
Investigating Function Parts
Eliminating Function Parts
Showing Your Work
I AM ERROR
Error Messages
########
#NULL!
#DIV/0!
#REF!
#NAME?
#VALUE!
#NUM!
#N/A
Invisible Errors
Intentional Errors
Handling Errors
Circular References
Data Validation
List Validation
Custom Validation
Finding and Importing Data
Excel-Readable Formats
Pasting from the Web
Text to Column
Importing Text Files
Importing from a Database
Cleaning and Normalizing
Basics of Visualization
The Charts We Care About
Tweaking Charts
Pluses and Minuses
Rithmetic
Division Joy
The MOD() Squad
Pseudo-randomness
Divisibility
Give Me a SIGN()
Short ROUND()
No Parameters
Decimal Places
Multiples
Summary Judgment
SUM() Like It Hot
Summing Values
Summing References
Multiplication
Example: Keeping the Pennies
"You got your SUM() in my PRODUCT()!"
Example: The Gradesheet
Down for the COUNT()
Quick Sum and Count
Example: A Pseudo-Random Number Generator
MAX() and MIN()
Example: Tax Brackets
Technique: Running Totals
The LARGE() and SMALL() of it
Technique: Sorting Numbers Without Sorting
Date Night
How Excel Represents Dates
Example: Balancing Your Checkbook
TODAY() is the Greatest
A Very Good YEAR()
Adding Months with EDATE()
Right NOW() TIME() Is Having Its Way With You
How Long Is That Movie?
You Got Your DATE() in My TIME()
NOW()
Example: A Timesheet
TRUE or FALSE
Comparisons
Equality
Inequality
IF()s and Buts
Nested IF()
Technique: The Odometer Pattern
AND() OR() NOT()
Short Circuit
Technique: Cleaning Data with IF()
Filling in Blanks
Everything ISLOGICAL()
Careful Counting with COUNTIF()
Value
Wildcard
Comparison
More Complicated Criteria
Clever Reasoning
Exclusive Or Addition
Extra Columns
Array Formulas
COUNTIFS()
Strategic Summing with SUMIF()
And AVERAGEIF()?
Example: Lucky Number Seven
Frivolous Example: Brute-Forcing SUBSET-SUM
Technique: Making a Histogram
Some Pitfalls of Importing Data
SUMIFS() and COUNTIFS()
Data Validation, Revisited
String Theory
TEXT() Messages
Digits
Dates
Time
VALUE()
CONCATENATE() & CONCATENATE()
Rep[ea]ting Text with REPT()
Technique: Concatenating Arrays Using Running Totals
CODE() and CHAR()
Example: First Letters of Popular Names
The Curious Case of UPPER() and LOWER() and EXACT()
Extracting String Parts
LEN()
The Missing MID() Case
The Missing RIGHT() Case
Seek and You Shall FIND()
REPLACE() You With a SUBSTITUTE()
Example: Checking an ISBN
Technique: Counting Spaces in a String
Just a TRIM()
Parsing
Example: Pulling Apart Addresses
Things are Looking Up
CHOOSE() Wisely
Look in the INDEX()
Example: A Deck of Cards
Finding Your MATCH()
Exact MATCH()
Encyclopedia MATCH()
Technique: Sorting Numbers Without Sorting, Part 2
Technique: Sorting Strings Without Sorting
Technique: Scenario Analysis
VLOOKUP() and Friends
HLOOKUP()
Example: Tax Brackets, Revisited
Technique: The Join
Technique: Multi-Column Lookups
Frivolous Example: The Prime Number Sieve
Indirection
ROW() and COLUMN()
Finding an ADDRESS()
OFFSET() References
Getting a Cell
Getting a Range of Cells
Technique: Sorting Numbers Without Sorting, Part 3
Technique: Dynamic Ranges
Technique: Moving Averages
INDIRECT() References
Technique: Data Segregation
Big Scenarios
Frivolous Example: License Plates
Mathemagic
I've Got the POWER()
Whole Number Exponents
Other Exponents
Order of Operations
Square Roots
Example: Extracting Digits
Exponentially Great
It's LOG()
Percent Changes and Rates of Return
Log Ten
Trigonometry
Angles
Trig Functions
Counting Things
The Factorial
Sampling Objects
Permutations
Combinations
Number Theory
Finance
That's So Random!
The Basics of Randomness
Random or Pseudo-Random?
Volatility and Recalculation
Uniform Randoms
Random Integers
Example: Rolling Dice
Frivolous Example: Dungeons and Dragons
Technique: Discrete Distributions
Bernoulli Trials
More Elaborate Discrete Distributions
Technique: Random Ordering
Sampling Without Replacement
Technique: Fake Data
Statistics
The Basics of Descriptive Statistics
One Set of Data
Measures of Central Tendency
Measures of Dispersion
Quantiles
Quantiles in Reverse
Correlation
Correlation is not causation
Autocorrelation
Covariance
Example: A Pseudo-Random Number Generator, Revisited
The Bell Curve
Example: Closing the Honors Gap
The Central Limit Theorem
Technique: Random Normals
Simple Linear Regression
Goodness of Fit
Forecasting
Instant Scatterplot Regression
Multiple Regression
Example: Predicting Football Wins
Monte Carlo
What is Monte Carlo?
Frivolous Example: Estimating PI() by Throwing Darts
Technique: Using a Data Table
Data Tables and Monte Carlo
Data Tables and Graphing
Example: Catching the Cheaters
Example: The Birthday Problem
Example: A Poker Hand Simulator
Array Formulas
The Basics of Array Formulas
The Idea Behind Array Formulas
Entering an Array Formula
Putting One Formula in One Cell
Technique: Using Constant Arrays
Technique: Abusing SUM()
Example: The Histogram, Revisited
Example: A Case-Sensitive SUMIF()
Example: Working Around Errors
Example: COUNTIFS() with OR() Conditions
Exclusive OR()
Non-Exclusive OR()
Technique: Using ROW() to Generate Sequences
Example: Finding the Last Occurrence
The Last MATCH()
The Last Character
Example: Converting Column Names to Numbers
Example: Multi-MATCH()
Cross Products
One Formula, Multiple Cells
Technique: Building Histograms with FREQUENCY()
Buckets in Order
Buckets Out of Order
Duplicate Buckets
Example: Counting Unique Items
Counting Unique Numbers
Counting Unique Non-Numerics
Technique: Multiple Regression with LINEST()
Standard Errors
Analysis of Variance
TREND()
Multiple Dependent Variables
Example: Predicting Football Wins, Revisited
The Pivot
What Is a Pivot Table?
Aggregating Data
Bucketing Data
Filtering Data
In a Dynamic Way
A Quick Detour: Tables
Sorting and Filtering with Tables
Creating Pivot Tables
Using Pivot Tables
Subtotals and Grand Totals
Value Field Settings
Tweaking Buckets
Manual Groups
Automatic Groups
Changing the Data
Technique: Calculated Fields and Items
Calculated Fields
Calculated Fields as Row Labels?
Calculated Items
Refreshing Data
Example: Baby Names, Revisited
Androgynous Names
A Frivolous Bug
Technique: Pivot Charts
Goal Seek and Solver
Goal Seek
Solver
Installing Solver
Using Solver
Example: Optimal Alchemy
Integral Constraints
Some General Solver Tips
Be Constrained
Be Smooth
But Don't Be Too Smooth
Be Unique
Be Diverse
Be Clever
Macros, Custom Functions, and Philosophy
Philosophy
Macros
Custom Functions
Final Projects
Counting Letters
Where's Starbucks?
Soccer Standings
JOLTS
Economic Assistance
Proposition 8
The 4-Minute Mile
Population Growth
Tweet of the Union
What Color is Baby Poop?
About the Author