Book description
Do you use Excel for simple lists, but get confused and frustrated when it comes to actually doing something useful with all that data? Stop tearing your hair out: Head First Excel helps you painlessly move from spreadsheet dabbler to savvy user. Whether you're completely new to Excel, or an experienced user looking to make the program work better for you, this book will help you incorporate Excel into every aspect of your workflow.
Table of contents
 A Note Regarding Supplemental Files
 Advance Praise for Head First Excel
 Praise for other Head First books
 Author of Head First Excel
 How to Use This Book: Intro

1. Introduction to Formulas: Excel’s real power
 Can you live it up on the last night of your vacation?
 Here’s what you budgeted and what you spent
 Excel is great for keeping records...
 Formulas work with your data
 Looks like Bob forgot a receipt...
 Your friends sent you all the receipts
 References keep your formulas working even if your data changes
 Check your formulas carefully
 Refer to a bunch of cells using a range
 Use SUM to add the elements in a range
 Bob and Sasha wonder whether we’ve been taking the right approach...
 Your friends agree: split the checks individually
 When you copy and paste a formula, the references shift
 Excel formulas let you drill deep into your data
 Everyone has plenty of cash left for a foodfilled night in New York City!

2. Visual Design: Spreadsheets as art
 CRMFreak needs to present their financials to analysts
 The dollar sign is part of your cell’s formatting
 How to format your data
 The boss approves!
 Design principle: keep it simple
 Clash of the design titans...
 Use fonts to draw the eye to what is most important
 Cell styles keep formatting consistent for elements that repeat
 With your cell styles selected, use Themes to change your look
 He likes it, but there’s something else...
 Use proximity and alignment to group like things together
 Your spreadsheet is a hit!

3. References: Point in the right direction
 Your computer business is in disarray
 Your production manager has a spreadsheet with costs
 MIN returns the lowest number in a series
 Let Excel fill in ranges by starting your formula and using your mouse
 Excel got the right answer using a more sophisticated reference
 Things just got even better...
 Use absolute references to prevent shifting on copy/paste
 Your profit margin is now even higher...
 Absolute references give you a lot of options
 Named ranges simplify your formulas
 With all this data, you’d have to write a ton of formulas
 Excel’s Tables make your references quick and easy
 Structured references are a different dimension of absolute reference
 Your profitability forecasts proved accurate

4. Change your Point of View: Sort, zoom, and filter
 Political consultants need help decoding their fundraising database
 Find the names of the big contributors
 Sort changes the order of rows in your data
 Sorting shows you different perspectives on a large data set
 See a lot more of your data with Zoom
 Your client is impressed!
 Filters hide data you don’t want to see
 Use Filter drop boxes to tell Excel how to filter your data
 An unexpected note from the Main Campaign...
 The Main Campaign is delighted with your work
 Donations are pouring in!

5. Data Types: Make Excel value your values
 Your doctor friend is on a deadline and has broken data
 Somehow your average formula divided by zero
 Data in Excel can be text or numbers
 The doctor has had this problem before
 You need a function that tells Excel to treat your text as a value
 A grad student also ran some stats...and there’s a problem
 Errors are a special data type
 Now you’re a published scientist

6. Dates and Times: Stay on time
 Do you have time to amp up your training for the Massachusetts Marathon?
 VALUE() returns a number on dates stored as text
 Excel sees dates as integers
 Subtracting one date from another tells you the number of days between the two dates
 When subtracting dates, watch your formatting
 Looks like you don’t have time to complete training before a 10K
 Coach has a better idea
 DATEDIF() will calculate time between dates using a variety of measures
 Coach is happy to have you in her class
 Excel represents time as decimal numbers from 0 to 1
 Coach has an Excel challenge for you
 You qualified for the Massachusetts Marathon

7. Finding Functions: Mine Excel’s features on your own
 Should you rent additional parking?
 You need a plan to find more functions
 Excel’s help screens are loaded with tips and tricks
 Here’s the convention center’s ticket database for the next month
 Anatomy of a function reference
 The Dataville Convention Center COO checks in...
 Functions are organized by data type and discipline
 Your spreadsheet shows ticket counts summarized for each date
 Box tickets for you!

8. Formula Auditing: Visualize your formulas
 Should you buy a house or rent?
 Use Net Present Value to discount future costs to today’s values
 The broker has a spreadsheet for you
 Models in Excel can get complicated
 Formula auditing shows you the location of your formula’s arguments
 Excel’s loan functions all use the same basic elements
 The PMT formula in the broker’s spreadsheet calculates your monthly payment
 Formulas must be correct, and assumptions must be reasonable
 The broker weighs in...
 Your house was a good investment!
 9. Charts: Graph your data

10. What if Analysis: Alternate realities
 Should your friend Betty advertise?
 Betty has projections of best and worst cases for different ad configurations
 You need to evaluate all her scenarios
 Scenarios helps you keep track of different inputs to the same model
 Scenarios saves different configurations of the elements that change
 Betty wants to know her breakeven
 Goal Seek optimizes a value by trying a bunch of different candidate values
 Betty needs you to add complexity to the model
 Solver can handle much more complex optimization problems
 Do a sanity check on your Solver model
 Solver calculated your projections
 Betty’s bestcase scenario came to pass...

11. Text Functions: Letters as data
 Your database of analytic customers just crashed!
 Here’s the data
 Text to Columns uses a delimiter to split up your data
 Text to Columns doesn’t work in all cases
 Excel has a suite of functions for dealing with text
 LEFT and RIGHT are basic text extraction functions
 You need to vary the values that go into the second argument
 Business is starting to suffer for lack of customer data
 This spreadsheet is starting to get large!
 FIND returns a number specifying the position of text
 Text to Columns sees your formulas, not their results
 Paste Special lets you paste with options
 Looks like time’s running out...
 Your data crisis is solved!

12. Pivot Tables: Hardcore grouping
 Head First Automotive Weekly needs an analysis for their annual car review issue
 You’ve been asked to do a lot of repetitive operations
 Pivot tables are an incredibly powerful tool for summarizing data
 Pivot table construction is all about previsualizing where your fields should go
 The pivot table summarized your data way faster than formulas would have
 Your editor is impressed!
 You’re ready to finish the magazine’s data tables
 Your pivot tables are a big hit!

13. Booleans: TRUE and FALSE
 Are fishermen behaving on Lake Dataville?
 You have data on catch amounts for each boat
 Boolean expressions return a result of TRUE or FALSE
 IF gives results based on a Boolean condition
 Your IF formulas need to accommodate the complete naming scheme
 Summarize how many boats fall into each category
 COUNTIFS is like COUNTIF, only way more powerful
 When working with complex conditions, break your formula apart into columns
 Justice for fishies!

14. Segmentation: Slice and dice
 You are with a watchdog that needs to tally budget money
 Here’s the graph they want
 Here’s the federal spending data, broken out by county
 Sometimes the data you get isn’t enough
 Your problems with region are bigger
 Here’s a lookup key
 VLOOKUP will crossreference the two data sources
 Create segments to feed the right data into your analysis
 Geopolitical Grunts would like a little more nuance
 You’ve enabled Geopolitical Grunts to follow the money trail...
 Leaving town...
 It’s been great having you here in Dataville!
 A. Leftovers: The Top Ten Things (we didn’t cover)
 B. Install Excel’s Solver: The Solver
 Index
 About the Author
 Copyright
Product information
 Title: Head First Excel
 Author(s):
 Release date: March 2010
 Publisher(s): O'Reilly Media, Inc.
 ISBN: 9780596807696
You might also like
book
40 Algorithms Every Programmer Should Know
Learn algorithms for solving classic computer science problems with this concise guide covering everything from fundamental …
book
Critical Thinking Skills For Dummies
Turbocharge your reasoning with Critical Thinking Just what are the ingredients of a great argument? What …
book
Excel 2019 Bible
The complete guide to Excel 2019 Whether you are just starting out or an Excel novice, …
book
Head First Design Patterns, 2nd Edition
You know you don’t want to reinvent the wheel, so you look to design patterns—the lessons …