Excel Formulas: Halfwit to Hero
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.
  

Best of all, you can start with this book even if you’re an Excel novice! The first chapter, “What You Should Already Know,” will fill you in on the four most essential Excel concepts before you get started with 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