How to create formulas in Excel

This tutorial guides you through creating Excel formulas, starting with the basics. You'll learn to build formulas using constants, cell references, defined names, and functions. We'll also explore using the Function Wizard and direct formula entry.

In our previous article, we began exploring the world of Microsoft Excel formulas. Why are they so fascinating? Because Excel offers formulas for virtually any task. Whatever challenge you face, a formula likely provides a solution. You just need to know how to create the right one! This tutorial shows you how.

All Excel formulas start with an equals sign (=). Type this into a cell or the formula bar before beginning your formula. Let's examine different formula creation methods.

• Creating formulas with constants and operators
• Creating formulas with cell references
• Creating formulas with defined names
• Creating formulas with functions

Creating Simple Formulas Using Constants and Operators

In Excel, constants are numbers, dates, or text values directly entered into a formula. To create a simple formula using constants:

1. Select the output cell.
2. Type = followed by your equation.
3. Press Enter.

Example: =100-50 (subtraction)

Creating Formulas Using Cell References

Instead of directly entering values, refer to cells containing those values.

For instance, to subtract cell B2 from A2, use: =A2-B2

You can type cell references or click cells to have Excel insert them. To add a range reference, select the cell range.

Note: Excel uses relative cell references by default. Press F4 to change reference types.

Using cell references offers a key advantage: when a referenced cell's value changes, the formula automatically recalculates.

Creating Formulas Using Defined Names

For advanced formulas, assign names to cells or ranges. Then, use these names in your formulas.

The quickest way is to select a cell and type its name in the Name Box. (See image below for example of naming cell A2).

Alternatively, use the Formulas tab > Defined Names group, or Ctrl F3.

Example: Naming cell A2 "revenue" and B2 "expenses," the net income formula is: =revenue-expenses

Similarly, use names in function arguments. For example, if A2:A100 is named 2015_sales, the SUM formula is: =SUM(2015_sales)

Defined names improve formula readability and efficiency, especially when reusing cell ranges.

Creating Formulas Using Functions

Excel functions are pre-built formulas performing calculations. Each function starts with =, followed by the function name and arguments in parentheses.

For details, see a list of common Excel functions.

Two methods exist for creating function-based formulas:

1. Using the Function Wizard:

a. Click the Insert Function button (Formulas tab > Function Library group). (See image below). Or, click the button to the left of the formula bar, or type = and select from the dropdown.

b. Search for a function by name or description. Select a category if needed.

c. Specify arguments using the wizard. You can type or click cells to add references.

d. Click OK (or press Enter) to complete the formula.

2. Direct Formula Entry:

Type = followed by the function name. Excel will suggest matching functions. Type the opening parenthesis, and Excel displays a screen tip guiding argument entry. (See image below).

Enter arguments, close the parenthesis, and press Enter. For syntax help, click the function name.

That's how you create Excel formulas! Future articles will offer tips for more efficient formula use. Stay tuned!

The above is the detailed content of How to create formulas in Excel. For more information, please follow other related articles on the PHP Chinese website!