OmniDocHow to use Excel formulas: Introductory tutorial on input and calculation for beginners
Explain the basic steps from inputting, modifying and copying Excel formulas, as well as how to check when the results are incorrect. It does not involve specific functions and is suitable for novices who are just starting to make tables and follow the steps.
Key points of this article
- The formula must start with an equal sign so that Excel will treat the input as a calculation.
- Try to quote cell addresses in formulas and do not fill in numbers by hand. The results will be updated automatically if the source data is changed.
- When copying a formula, the cell that needs to be fixed must add a $ symbol before the column label and row number, otherwise the reference will be offset with the position.
- When the result is abnormal, first check whether the number is in text format, and then use Ctrl+` to display the formula and check it item by item.
When they first start using Excel, many people want to calculate a total price or proportion, but don't know where to start writing the formula. After inputting, only a string of text is displayed in the cell, and the result is wrong, or the entire column is messed up when you drag it down. This article is written for novices who have just started making spreadsheets. It explains how to use Excel formulas: from inputting, modifying, copying, and how to troubleshoot when the results are wrong.
After reading this, you should be able to: write a formula starting with an equal sign in a cell, use the cell address instead of hand-filled numbers, copy the formula to other rows by dragging, and find out why the results are incorrect. I won’t talk about specific functions here, but only the basic rules of the formula itself. These rules are applicable to any function you learn later.
This is my first time writing a formula. How can I input it without making mistakes? One sentence conclusion
Type the equal sign first, then use the cell address and operators to spell out the formula, and press Enter to confirm; do not type numbers directly into the formula. Please refer to the table below for how to do it in different situations.
| your situation | Recommended practices | reason |
|---|---|---|
| Just want to do simple addition, subtraction, multiplication and division | Enter a formula similar to =A1+B1 in the target cell | Intuitive structure, easy to check item by item |
| Data may be updated later | Quote the cell address, do not fill in the value by hand | After the source data is changed, the results will automatically change accordingly. |
| The same formula should be used in the entire column | After writing the first line, drag the fill handle to copy | Avoid line-by-line rewriting and reduce missed changes |
| Fixed reference to a certain cell in the formula | Lock a row or column with $ (fixed reference) | The reference position will not be offset when copying |
| Results are displayed as text or date | Check the cell format and make sure there is an equal sign at the beginning | Format errors will prevent the formula from calculating properly |
Before you start: Confirm these points first
- confirm that the formula begins with an equal sign. Excel only executes content that begins with an equal sign as a formula. If an apostrophe is entered first, or if the cell is formatted as text, the content will be displayed as is and will not be counted. - confirm that the numbers are involved in the calculation. some numbers are in text format, often with a small green triangle in the upper-left corner of the cell and aligned to the left. The text does not participate in addition, subtraction, multiplication and division, and the result will be error or zero. First figure out where to put the results and which cells to use. it is easy to quote the wrong position while writing. Sort out the formula with a pen and paper or a blank column, and then enter it. -understand the cell address. columns use letters and rows with numbers, for example, B3 represents column B, row 3. These are the addresses quoted in the formula, and we will not know what the formula is until we find out about them. - try and make mistakes with the exercise sheet first. it is safer to find a few lines of simple data than to change the formal form directly, and it is also convenient to check whether the results are correct.
# steps to enter a formula in Excel
Suppose a table has 30 rows of sales records, column C is the unit price, column D is the quantity, and you want to calculate the amount in column E. Let's do this with behavior example 2.
- Click the cell in which you want to display the result, such as E2. the result of the calculation will appear here, and the formula will be written here.
- enter the equal sign =. * this step tells Excel that the next step is calculation, not plain text.
- Click the first referenced cell, such as C2. clicking the address with the mouse is less prone to error than typing the address by hand, and the edit bar will display the address synchronously.
- Enter the operator, and then click the second cell. the common operators are + addition,-subtraction, multiplication, / division, and ^ multiplication. When D2 is selected, the formula becomes = C2D2.
- press Enter to confirm. the cell displays the result of the calculation; when selected, the edit bar still displays the formula itself.
- when you need to modify, double-click the cell or press F2 after it is selected. after entering the editing state, press Enter to save; press Esc to discard the modification.
- drag the fill handle to copy down. Select E2 and drag the small square in the lower right corner to E31. The formula for other lines automatically changes the line number to the corresponding line, for example, E3 becomes = C3*D3.
- check all formulas with Ctrl+ `. this shortcut toggles the display mode so that all cells display the formula itself, making it easy to quickly see if the reference is correct. Press again to restore the display of the results.
# check item by item after completion
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Is the result a number? | The numbers are aligned to the right by default, and the ones to the left may be text | Check the cell format, change it to a numeric value and re-enter the formula |
| Whether the referenced address is correct | Select the cell to see the edit bar, or use Ctrl+ `to display the formula | Re-click the correct cell and press Enter after modification |
| Whether to run off after copying | Click a few lines randomly to see if the reference changes by line | Where you need to be fixed, add $before the column label and line number, for example, change C2 to the form with $. |
| Whether there is an error value that begins with # | For example # DIV/0! Means divided by zero. | Check the divisor and source data to confirm that there are no empty cells involved in the calculation |
| Hand count one or two lines of comparison | Pick a line and calculate it with a calculator. | When the results are inconsistent, check from the operation order and reference location. |
# Core comparison
When writing a formula, the way cells are referenced will directly affect the copied result. The following three ways of writing are common in novice forms.
| Writing method | Examples | The effect after copying to the next line |
|---|---|---|
| Relative reference (default) | =C2*D2 | Change to = C3*D3, move with position |
| Fixed reference | = C2*F1, and the column label and line number of F1 are preceded by $ | F1 is always quoted, which is suitable to quote fixed values such as tax rate and exchange rate. |
| Fill in the number by hand | =C2*0.13 | The figures are written in the formula, and the tax rate should be changed one by one. |
The basic idea of the optimization formula is: put the changeable data into the cell, the formula only refers to the address; need a fixed parameter to put a separate grid, with a fixed reference; the same formula is only written once, and then copy to complete the whole column. In this way, it is easier and less error-prone to change the form.
# frequently asked questions (FAQ)
1. What should I do if the text is displayed after entering the formula?
First check whether the cell format is text. After changing to normal or numeric, double-click the cell and press Enter to reconfirm, the formula usually calculates normally. Also make sure that it does start with an equal sign, not an apostrophe or space.
2. After copying the formula, the result is all wrong, why?
For the most part, the reference is not locked, and the reference position is moved as it is copied. Find the cell that should be fixed, add a $symbol before its column label and line number, and copy it again.
3. After entering the formula, only the formula itself is displayed, not the result?
This is usually because the cell is formatted as text, or the mode of displaying the formula is turned on. First press Ctrl+ `to switch back, and if it still doesn't work, change the cell format and re-enter the formula.
4. There is no Excel, is there a free tool to practice formulas?
You can practice with other table software or online table tools, and the basic equal sign, operator, and reference rules are common. When choosing a free tool, it is recommended to look at the file size limit, watermarking, data privacy policy, and whether the formula is retained after import and export.
5. After learning the basic formula, what's the next step?
It is recommended that you learn common functions and understand their parameters and regional references, such as how to reference an entire column of data. First of all, practice the citation and replication rules in this article, and it will be much easier to learn functions.
# Summary
How to use the Excel formula, the core is three points: start with the equal sign, use the cell address instead of filling in the number by hand, and use $to fix the position that needs to be locked when copying. Follow the steps in this article to input, modify and check, and you can use the first formula.
