When we use functions in Excel, we always complain that the functions are difficult and inconvenient to use. It always takes a lot of time to find the function before it is used, and sometimes the function cannot be used normally, resulting in an error in the result.
These are common problems. in fact, they can be solved with some small tricks. This article introduces the correct usage of functions in detail, including some common function error solutions. I hope it can be helpful to you.
First Step of Using Function
If you were given a string of numbers in Excel and asked to calculate the sum of them, what would you do? Maybe an Excel noob will write the following formula:
When you finish typing, others have already calculated the all sums.
In fact, as long as you use the sum function in Excel—SUN (A1: A17), you can get the total value instantly.
However, this is not accelerated. Select the columns or rows which need to be summed, then find the Edit in the Home tab. Click on the function list at the top and select Sum. The total value is automatically calculated. And it is filled at the bottom of that column or the leftmost of that row. If you need to change its position, just copy and paste it.
How to Remember All Functions
Many people may think that those who can use functions proficiently must be able to remember many functions. Actually, it is not like this. I believe most people have discovered that Excel has a super memory function.
For example, we want to use the Rand between function. After entering “=ra” in the cell, a function list will automatically appear below the cell, listing the functions with the first two letters as “ra”. Then we enter another “n”, and the list is shortened again, showing only matching functions. At this time, we can directly double-click the RANDBETWEEN. After entering the data range, press Enter to directly insert the desired function.
Therefore, using Excel’s powerful memory function, you only need to remember the first two letters of the function name.
Find the Error in the Function
When using the VLOOKUP function, the result cannot be displayed, only a “#N/A”.
In fact, this symbol represents a function error. However, it only gives you a symbol, but there was no error message. How can I correct the error?
Excel comes with a formula error checking function. You can find the error through [Formula]— [Error Check], and then modify it.
Take the example above as an example. For the cell with an error, find “Formula”—”Error Check” in the Host tab, and open the “Display Calculation Step” window.
Then click Evaluate. You can find the error. After correction, it can run normally.
Here are a few common error indications:
[#####!]: The cell is not wide enough, or some non-negative numbers such as date and time produce negative values.
[#VALUE!]: Wrong parameter or operation object is used.
[#DIV / 0]: The formula is divided by 0 or blank cells.
[#N/A]: There is no available value in the function formula, no value, or wrong value.
[#REF!]: Deleted cells referenced by other formulas
Unable to Use Formula
Sometimes we may encounter such a problem: input “=” in a cell to add a function, but after pressing Enter, the formula is not calculated, but only the name of the formula is displayed.
This is because the cell format is set to “text”, so calculations cannot be performed.
Select the cell with the error. Find the number tab in the Home tag, and then select the upper box as normal.
Neither Column Can be Calculated Normally
In the above example, only one cell is formatted incorrectly, and formula calculations cannot be used normally. What if there is more than one cell that is formatted incorrectly, instead of several columns or rows? Do you want to modify the format one by one?
Actually, it doesn’t need to do this at all. Select the cell with the wrong format, and then click “Replace”. Enter “=” both in the [Find what] and the [Replace with], and then click OK.
The Magical VLOOKUP Function in Excel
This function is usually used to match the student information in the school or the personnel number in the company. For example, there is a complete basic student information table in our information database, which records the student’s name, student ID, class, etc.
At this time, if we need to make a new form, the content that needs to be included is the student’s name, student ID, and home address. And the separation order of the student names in the two tables is different, so you can’t copy and paste directly. So what should you do?
If you enter the student ID one by one, it is too complicated. Here I introduce the use of VLOOKUP for information matching.
1. Enter “=V” in the cell that needs to be filled, and then double-click “VLOOKUP” in the list.
2. Use the shortcut key—”SHIFT + F3″ to call up the Function Arguments, and enter the corresponding content in it. Among them, if the matching table and the current table are not in the same workspace, the second column needs to be entered: [table name] sheet2! $ A $ 1: $ B $ 5.
3. Click OK. Then the cell automatically matches the corresponding value. If you need to match the following content exactly, then place the mouse on the lower right of the matched cell until a black cross symbol appears. Drag the mouse down to auto-fill. The entire column of data has been matched.
And, relative to the previously filled data, they are equivalent to a link. When the corresponding data in sheet2 changes, the data in this table will also change accordingly. Therefore, this function can be applied to many scenarios, and it is especially convenient for data matching. You can modify all linked files directly by modifying the source file.
