by Matthew MacDonald (Author)
About the author
Matthew MacDonald has helped thousands of people learn to write code, build websites, and tame giant spreadsheets. He’s a three-time Microsoft MVP and the author of more than two dozen books for publishers including O’Reilly, McGraw-Hill, Apress, Microsoft Press, Wrox, and No Starch Press. He’s also taught the mystical ways of programming at Ryerson University and Sheridan College. Matthew’s Excel roots go deep—he published his first book about the wonderful world of Excel spreadsheets in 2005 and wrote the best-selling Excel 2013: The Missing Manual and the pop-science hit Your Brain: The Missing Manual. Matthew lives in Toronto with his wife and three daughters. You can contact him at matthew@prosetech.com.
About this book
Formulas are the heart and soul of Excel, whether you’re using Excel to build a business, run a budget, or solve your math homework. In Excel Formulas: Halfwit to Hero you get a no-fluff introduction to the essentials of formula writing by master explainer and best-selling author Matthew MacDonald.
Learn by Doing: In the past, computer books were giant doorstops that could crush you if they fell from a high shelf. This isn’t that sort of book. Instead, you'll "learn by doing" with the exercises on the companion website (http://lab.halfwit2hero.com). Each chapter has at least one step-by-step tutorial that gets you to practice the new concepts you've learned before they can drop out of your brain. Yes, this approach requires more work than breezing through the book on your sofa while watching The Bachelor in the background. But the payoff is immense. Instead of ending this book as a person who’s "kind of" familiar with Excel, you’ll be someone who’s developed real Excel skills. And when someone asks you about an Excel formula feature, your reaction won’t be "That sounds vaguely familiar"—it will be "I tried that out myself."
The Twelve-Year-Old Test: Ever struggled to learn from a book, only to be frustrated by an obvious mistake or something that didn’t work right? To prevent these types of problems, every Halfwit to Hero book is double-checked by a genuine beginner. Excel Formulas: Halfwit to Hero has successfully passed the hardest beginner challenge: the twelve-year-old test. That means a motivated twelve-year-old successfully completely all of its tutorials, with only a minimum of adult help.
What You’ll Learn:
- How to perform calculations with ordinary numbers, amounts of money, dates, times, and even text.
- How to use named cells to organize your worksheets and clear up complicated formulas.
- How to use conditions to write formulas that make decisions and deal gracefully with bad data.
- How to use lookups to scan through tables of information and build worksheets that automatically fill in data.
- How to use shortcuts to find hidden formulas, track down errors, and watch Excel as it crunches the numbers in your formulas.
Table of contents
Introduction: Welcome Aboard
About the “Halfwit to Hero” Series
The Twelve-Year-Old Test
Who Should Read This Book
What You Need to Use This Book
The Tutorial Website
Chapter 1: What You Should Already Know
Step 0: Starting Excel for the First Time
What about Excel Online?
Explaining the Excel jargon
1. Your Best Friend the Ribbon
2. The Worksheet Grid
Moving around the grid
Typing in a cell
Exercise #1: Find the cell
Overlapping cells
Try it out: Resizing columns
Another approach: Wrapping text
3. Understanding Numbers and Dates
Dates
Formatting numbers and dates
Exercise #2: Make this look like that
Formatting a bunch of cells at once
What does ##### mean?
4. Saving Your Work
Opening a workbook
Saving files in Excel Online
The Last Word
Chapter 2: Building Basic Formulas
Starting a Formula
Can I use the equal sign without starting a formula?
Editing a Formula
Formula Practice
Try it out: Excel arithmetic
The order of operations
Controlling the order with brackets
Exercise #1: Pocket calculator Excel
Formula Mistakes
Unmasking Your Formulas
The Last Word
Chapter 3: Using Cell References
Referring to Another Cell
A quick refresher on cell addresses
The #VALUE! error
Try it out: Point-and-click formulas
Exercise #1: Calculate the sales tax
Cell references are alive (sort of)
Remember, you can format your answer
Copying Formulas
Try it out: Copy a formula
Exercise #2: Copy a calorie formula
Relative references and fixed references
Exercise #3: Copy a calorie formula (again)
Mixed references
Referring to Cells in Other Sheets
A quick refresher on worksheets
Worksheet references
Try it out: Referring to another worksheet
Workbook references
Tracing References with Arrows
Try it out: Tracing the calorie counter
The Last Word
Chapter 4: Using Functions
Adding a Function to a Formula
Function parameters
Try it out: Putting a function in a formula
A quick aside about rounding
Errors that can attack your formulas
Excel’s Function Library
Putting one function inside another
Exercise #1: Princess of trig
Try it out: Excel’s handy conversions
Functions that Use Cell Ranges
Adding numbers with SUM
More about ranges
Selecting a whole column (or row)
More functions that like ranges
Exercise #2: Take the temperature
The Last Word
Chapter 5: Calculations with Dates and Time
Unmasking Excel Dates
Date formatting
Try it out: Peeking at date numbers
Basic Date Math
Adding days to a date
Finding the difference between two dates
Getting today’s date
Writing a date in a formula
Taking a date apart
Moving a date by months or years
Try it out: Watching Excel calculate a date
Exercise #1: The new hire
Even Smarter Date Functions
Finding the day of the week
Counting workdays
Finding the end of the month
Calculating your age
How Excel Sees Time
Time formatting
Math with times
Calculating intervals of time
Exercise #2: Timing your commute
Combining dates and times
Getting the time (right now)
The Last Word
Chapter 6: Calculations with Money
Excel’s Financial Toolkit
Three key financial concepts
The weird idea of negative money
Seeing the Future
Calculating the growth of an investment
Compound interest
Exercise #1: An imaginary investment
Stocks and other investments
Paying a loan
Timing payments with the type parameter
Answering More Money Questions
How much money do you need to save?
Exercise #2: Make me a millionaire
How much money can you borrow?
Finding the missing information
How many payments do you need to make?
What rate do you need to earn?
What payment do you need to make?
The Last Word
Chapter 7: Manipulating Text
Manipulating Text—Why?
Putting Text in a Formula
Functions That Change Text
Changing capitalization
Trimming and cleaning text
Replacing a piece of text
Getting a piece of text
Measuring the length of text
Searching text
Exercise #1: Cleaning up a list of names
The Last Word
Chapter 8: Naming Cells and Ranges
Cleaner, Neater Formulas
How to name a cell
Using a name in a formula
Making Excel put names in a formula
Exercise #1: Adding (and using) names
Browsing your names
Naming a range
Names are fixed references
A quick refresher: Should you use names?
The Name Manager
Pointing a name to a different cell
Renaming a name
Deleting a name
Using the same name in different worksheets
Crazy name tricks: don’t do it
Names in Tables
Try it out: Create a table
Some essential table features
Table names
Exercise #2: Your wedding budget
The Last Word
Chapter 9: Setting Conditions
Introducing Conditional Logic
The logical operators
Try it out: Writing a condition in a formula
How a Formula Makes a Choice
Making useful conditional formulas
Combining conditions
Adding more outcomes
Exercise #1: The school of Excel
Conditions with dates
Conditions with text
The Is Functions
Checking for an error
Conditional Calculations with Ranges
Conditional counting
Using multiple conditions
The family of conditional functions
Exercise #2: The school of Excel (part 2)
The Last Word
Chapter 10: Using Lookups
How to Search a Table
The anatomy of a lookup
Choosing a good lookup value
Getting the right range
Lookup forms
Dealing (gracefully) with failed lookups
Exercise #1: Look Up! Catering
Looking the other way
Super Advanced Lookups
How to find matches that aren’t exact
How to find matches using two columns
How to get values on the left of your lookup
Using lookup lists
Try it out: Make a lookup list
The Last Word
Where to Go Next…
The Future of Excel and You
Series: Halfwit to Hero
Length: 180 pages
Publisher: ProseTech; 1 edition (June 6, 2018)
Language: English
ISBN-10: 1775373703
ISBN-13: 978-1775373704
MOBI version