Excel Functions and Formulas: Getting Started Guide

Excel's functions and formulas are its core functions, both of which perform calculations, but are created, function and work in different ways. This article will explain both in an easy-to-understand manner to help you become an Excel expert. If you don't understand any terms, please refer to our Excel Glossary.

Detailed explanation of functions and formulas

The main difference between a formula and a function is that anyone can create a formula, and a function is predefined by a Microsoft programmer.

Excel formula

Excel formulas are used to perform basic mathematical calculations. Create a formula, first enter the equal sign (=), and then write the calculated parameters.

For example, enter:

<code>=20+40</code>

Press Enter, Excel adds 20 and 40, and the result (60) will be displayed in the cell where the formula is entered.

Excel can also calculate the values ??already in the spreadsheet. Enter:

<code>=B2*10</code>

Press Enter to multiply the value in cell B2 by 10.

Same, enter:

<code>=B2*B3</code>

Press Enter to multiply the values ??in cells B2 (60) and B3 (600) to a result of 36,000.

When creating an Excel formula, the number of parameters is not limited. For example, enter:

<code>=B3*5-(2+8)</code>

Add 2 and 8, then multiply the value in B3 by 5, and subtract the former by the latter, and the result is 2,990.

Excel follows the standard mathematical order of operations - PEMDAS (first brackets, then exponents, then multiplications and divisions, and finally adds and subtractions).

Excel function

Excel functions work similarly. They also start with an equal sign (=) and can perform calculations. However, Excel formulas are limited to basic mathematical operations, while functions are more powerful.

For example, the AVERAGE function can calculate the average value of a set of numbers, and the MAX function can find the maximum value in the range.

Excel functions follow a specific syntax:

<code>=a(b)</code>

where a is the function name (for example, AVERAGE or MAX), and b is the parameter used to enable the function to perform calculations.

For example, enter:

<code>=AVERAGE(20,30)</code>

Press Enter to calculate the average of 20 and 30, and the result is 25.

We can also enter in cell B2:

<code>=AVERAGE(A1:A5)</code>

Let Excel calculate the average of all values ??in cells A1 to A5 (the colon indicates that the cells mentioned and all cells in between).

Excel has hundreds of functions, from the most basic functions to more complex functions. It's nearly impossible to remember all functions, especially considering that Microsoft developers are constantly adding new functions. Excel provides a function assistant to help you choose the most appropriate function and guide you through the entire process.

To start this assistant, click the "fx" icon above the first row of the spreadsheet, or press Shift F3.

You can then enter some keywords in the Search Function field to find the function you want. The Select Category drop-down menu displays different groups of function, including financial, statistical, and logical categories. When you select a function in the list under category, you will see a short description of the function's function's function.

After finding the function you want to use, click OK. You will then see a new dialog box that guides you through the entire process.

Combination of formulas and functions

Formulas and functions can be used in combination. For example, enter:

<code>=20+40</code>

calculates the sum of the values ??in cells A1 to A10 and divides the sum by 2.

Cells and formula bars

In Excel, once you enter a formula in a cell or use a function, the cell will display the calculation result instead of the formula itself. For example, when we enter:

<code>=B2*10</code>

and press Enter, the calculation results will be displayed in cell A3, not the formula itself.

If you find an error that requires modification of the formula, you can use the formula bar located at the top of the Excel worksheet. You can also see the name box in the upper left corner, which indicates the active cell.

In other words, in the example below, the name box tells us that A3 is the active cell, the formula bar tells us what is entered in cell A3, and cell A3 itself shows the result of what we entered.

Copy formulas and functions

Excel provides convenient copying functions.

In the following example, we want to add all the values ??in cells A1 to A8, so we will enter:

<code>=B2*B3</code>

and press Enter.

We also want to add the values ??in cells B1 to B8. However, instead of entering the new formula using the SUM function in B9, we can:

• Select cell A9, press Ctrl C, and then press Ctrl V in cell B9, or
• Drag the fill handle in the lower right corner of cell A9 to cell B9.

Because the cell references in the formula are relative by default, the method of calculating the values ??of cells A1 to A8 entered in cell A9 is also suitable for calculating the values ??of cells B1 to B8 in B9.

In some cases, you may not want the references in the formula to be relative. In this case, take some time to understand the difference between relative citations, absolute citations and mixed citations.

10 commonly used basic functions

If you are new to Excel or its functions, open a new spreadsheet and enter some random values ??in cells A1 to A9 (A10 is left blank). Then, try some of the following functions:

Volatile functions are recalculated when you make any changes to an Excel spreadsheet or reopen the spreadsheet.

5 more advanced functions

In a new worksheet or new workbook, enter Laura, Lucy, Liam, Lilly, Liz, and Luke in cells A1 to A6, and enter the numbers 1 to 6 in cells B1 to B6.

Now, try some of the following functions:

Learning Excel's formulas and functions is a continuous learning process! We also have a lot of articles about Excel to help you become an Excel power user.

The above is the detailed content of The Beginner's Guide to Excel's Formulas and Functions. For more information, please follow other related articles on the PHP Chinese website!