Excel Formulas & Functions For Dummies book cover

Excel Formulas & Functions For Dummies

Author:
Ken Bluttman
Published: December 21, 2021

Overview

Unlock the power of Excel with a step-by-step roadmap to its formulas and functions

There's a Swiss Army knife in your digital toolbox that can multiply your productivity and make you the smartest guy or gal in almost any room. It's called Microsoft Excel.

If you're like most people, you've barely scratched the surface of what this powerful tool's hundreds of built-in functions can do. But with a little help from Excel Formulas & Functions For Dummies, you'll soon be organizing, analyzing, and interpreting data like a pro.

For those who don't know the difference between a spreadsheet and a bedsheet, the book gets you up to speed with formula and function basics first. But you can also skip ahead to the fancy stuff and learn about working with probabilities, significance tests, and lookup functions.

This easy-to-use Excel formulas and functions survival guide shows you how to:

  • Work with financial functions like PMT, PPMT, NPER, RATE, and PV
  • Calculate mean, median, mode, standard deviation, and many more statistical functions
  • Troubleshoot formulas for common errors and validate your data to avoid mistakes
  • Work with dates, times, logic operators, conditions, and basic and advanced mathematical functions

You don't need a degree in data science or advanced mathematics to take advantage of the full functionality and flexibility of Microsoft Excel. Let Excel Formulas & Functions For Dummies show you how to transform this unassuming program into the most useful tool in your toolbox.

Unlock the power of Excel with a step-by-step roadmap to its formulas and functions

There's a Swiss Army knife in your digital toolbox that can multiply your productivity and make you the smartest guy or gal in almost any room. It's called Microsoft Excel.

If you're like most people, you've barely scratched the surface of what this powerful tool's hundreds of built-in functions can do. But with a little help from Excel Formulas & Functions For Dummies, you'll soon be organizing, analyzing, and interpreting data like a pro.

For those who don't know the difference between a spreadsheet and a bedsheet, the book gets you up to speed with formula and function basics first. But you can also skip ahead to the fancy stuff and learn about working with probabilities, significance

tests, and lookup functions.

This easy-to-use Excel formulas and functions survival guide shows you how to:

  • Work with financial functions like PMT, PPMT, NPER, RATE, and PV
  • Calculate mean, median, mode, standard deviation, and many more statistical functions
  • Troubleshoot formulas for common errors and validate your data to avoid mistakes
  • Work with dates, times, logic operators, conditions, and basic and advanced mathematical functions

You don't need a degree in data science or advanced mathematics to take advantage of the full functionality and flexibility of Microsoft Excel. Let Excel Formulas & Functions For Dummies show you how to transform this unassuming program into the most useful tool in your toolbox.

Excel Formulas and Functions For Dummies Cheat Sheet

It’s easy to use Excel for many of your day-to-day number-crunching tasks, like determining your business’s average sale, computing classroom grades, or forecasting college expenses. Use this handy Cheat Sheet to discover great functions and tips to help you get the most out of Excel.

Articles From The Book

18 results

Excel Articles

How to Create Custom Excel Functions

Despite all the functions provided by Excel, you may need one that you just don't see offered. Excel lets you create your own functions by using VBA programming code; your functions show up in the Insert Function dialog box. Writing VBA code is not for everyone. But nonetheless, here is a short-and-sweet example. If you can conquer this, you may want to find out more about programming VBA. Who knows — maybe one day you'll be churning out sophisticated functions of your own! Make sure you are working in a macro-enabled workbook (one of the Excel file types). Follow along to create custom functions:

  1. Press Alt + F11.

    This gets you to the Visual Basic Editor, where VBA is written.

    You can also click the Visual Basic button on the Developer tab of the Ribbon. The Developer tab is visible only if the Developer checkbox is checked on the Customize Ribbon tab of the Excel Options dialog box.

  2. Choose Insert→Module in the editor.

    You have an empty code module sitting in front of you. Now it's time to create your very own function!

  3. Type this programming code, shown in the following figure:

    Writing your own function
    Public Function Add(number1 As Double, number2 As Double)
    Add = number1 + number2
    End Function
  4. Save the function.

    Macros and VBA programming can be saved only in a macro-enabled workbook.

    After you type the first line and press Enter, the last one appears automatically. This example function adds two numbers, and the word Public lists the function in the Insert Function dialog box. You may have to find the Excel workbook on the Windows taskbar because the Visual Basic Editor runs as a separate program. Or press Alt+ F11 to toggle back to the Workbook.

  5. Return to Excel.

  6. Click the Insert Function button on the Formulas tab to display the Insert Function dialog box.

    Finding the function in the User Defined category
  7. Click OK.

    The Function Arguments dialog box opens, ready to receive the arguments. Isn't this incredible? It's as though you are creating an extension to Excel, and in essence, you are.

    Using the custom Add function
This is a very basic example of what you can do by writing your own function. The possibilities are endless, but of course, you need to know how to program VBA. Macro-enabled workbooks have the file extension .xlsm.

Excel Articles

What Goes into an Excel Function

Most Excel functions take inputs — called arguments or parameters — that specify the data the function is to use. Some functions take no arguments, some take one, and others take many; it all depends on the function. The argument list is always enclosed in parentheses following the function name. If there's more than one argument, the arguments are separated by commas. Look at a few examples: Some functions have required arguments and optional arguments. You must provide the required ones. The optional ones are, well, optional. But you may want to include them if their presence helps the function return the value you need. The IPMT function is a good example. Four arguments are required, and two more are optional.

Excel Articles

Using the Excel Function Arguments Dialog Box to Edit Functions

Excel makes entering functions with the Insert Function dialog box easy. But what do you do when you need to change a function that has already been entered in a cell? What about adding arguments or taking some away? There is an easy way to do this! Follow these steps:

  1. Click the cell with the existing function.

  2. Click the Insert Function button.

    The Function Argument dialog box appears. This dialog box is already set to work with your function. In fact, the arguments that have already been entered in the function are displayed in the dialog box as well!

  3. Add, edit, or delete arguments, as follows:

    • To add an argument (if the function allows), use the RefEdit control to pick up the extra values from the worksheet. Alternatively, if you click the bottom argument reference, a new box opens below it, and you can enter a value or range in that box.

    • To edit an argument, simply click it and change it.

    • To delete an argument, click it and press the Backspace key.

  4. Click OK when you're finished.

    The function is updated with your changes.