Mastering basic Excel formulas is very important for everyone. Here are tips for using excel that anyone should master. Microsoft Excel is considered the standard software in data processing. This program from Microsoft has also become one of the software used by investment bankers and financial analysts when it comes to data processing, financial modeling, and presentation. This article will provide you with indispensable tips if you want to master excel.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
Basic terms in Excel
There are two basic ways to perform calculations in Excel: Formulas and Functions.
1. Formula
In Excel, a formula is an expression that operates on the values ​​of a group of cells or a cell. For example: =A1 + A2 + A3, find the total value from cell A1 to cell A3.
2. Jaw
Functions are predefined formulas in Excel. Excel uses these functions instead of manual formulas. For example: =SUM(A1:A3). The function sums all values ​​from A1 to A3.
5 tips to use Excel you need to know
When dealing with data, there are five most common to insert basic Excel formulas. Each way has its own advantages. Therefore, before diving deeper into the main formulas, I will guide those methods.
1. Simple Insert: Enter the formula in the cell
Entering a formula in a cell or formula bar is the simplest method for inserting basic Excel formulas. Start by typing a sign =followed by the name of an Excel function.
Excel suggests the function when you enter the first characters. From this list of suggestions, you will choose the function you want. However, don’t press the Enter key. Instead, press the . key Tab so you can continue to insert other options. Otherwise you may get an invalid name error, usually ‘#NAME?’. To fix it, just reselect that cell and edit the formula again.
2. Insert Functions from Tab Formulas
If you want full control over your function insertion, use dialog Insert Function in Excel is all you need. To do this, go to tab Formulas and choose Insert Function. The dialog box will contain all the functions you need.
3. Select the formula by group in the Formula tab
This option is for those who want to dig into the functions quickly. To find this menu, go to Formulas tab and choose your preferred function. Click on that group to display a menu filled with related functions. From there, you can choose your recipe. However, if you find the function you use often isn’t on the tab, click the option More Functions – maybe the group is just hidden.
4. Use the AutoSum . option
For everyday tasks, AutoSum is the option that you should try. Go to tab Home, in the far right corner and click the AutoSum option. Then click the arrow to show other hidden formulas. This option is also available in the Formulas tab.
5. Quick Insert: Use Recently Used Functions
If you often use functions, then use menu Recently Used. It’s on the tab Formulasoption right next to AutoSum.
7 basic Excel formulas you need to remember
1. SUMMARY
The SUM function is the first must-know formula in Excel. It sums the values ​​in the range you selected.
=SUM(number1, [number2],…)
Eg:
=SUM(B2:G2) – Sum the values ​​from cell B2 to cell G2 (row).
=SUM(A2:A8) – Sum the values ​​from cell A2 to cell A8 (column).
=SUM(A2:A7, A9, A12:A15) – Sum the values ​​from cells A2 to A7, omit A8, add A9, omit A10 and A11, and then finally add from A12 to A15.
=SUM(A2:A8)/20 – Sum from cells A2 to A8 then divide by 20.
2. AVERAGE
The AVERAGE function is used to calculate an average, such as the average number of shareholders in a given group of shares.
=AVERAGE(number1, [number2], …)
Eg:
=AVERAGE(B2:B11) – Average from cell B2 to cell B11 is similar to (SUM(B2:B11)/10)
3. COUNT
The COUNT function counts all cells containing numeric values ​​in the selected range.
=COUNT(value1, [value2], …)
Eg:
COUNT(A:A) – Count all numeric values ​​in column A.
COUNT(A1:C1) – Count all numeric values ​​from drive A1 to cell C1.
4. COUNTA
Like the COUNT function, COUNTA counts all the cells in the selection. However, it counts all cells regardless of value. That is, unlike COUNT which only counts numbers, which also counts dates, times, strings, logical values, errors, or text, COUNTA only does not count empty drives.
=COUNTA(value1, [value2], …)
Eg:
COUNTA(C2:C13) – Counts the value from cell C2 to cell C13, not counting empty cells.
COUNTA(C2:H2) – Count from column C2 to column H2.
5. IF
The IF function is often used when you want to sort your data according to a certain logic.
=IF(logical_test, [value_if_true], [value_if_false])
Eg:
=IF(C2
=IF(SUM(C1:C10) > SUM(D1:D10), SUM(C1:C10), SUM(D1:D10)) – If the sum from cell C1 to cell C10 is greater than the sum from cell D1 to cell D10, return the sum from cell C1 to C10 and vice versa, return the sum from cell D1 to D10.
6. TRIM
The Trim function is used to remove excess spaces in cells.
=TRIM(text)
Eg:
TRIM(A2) – Remove blanks in cell A2.
7. MAX & MIN
The MAX and MIN functions help find the largest and smallest number in an area.
=MIN(number1, [number2], …)
Eg:
=MIN(B2:C11) – Find the minimum value from cell B2 to cell C11.
=MAX(number1, [number2], …)
Eg:
=MAX(B2:C11) – In contrast to the MIN function, find the maximum value from drive B2 to cell C11.
Above are the basic excel tips that you need to know to increase work productivity. Alternatively, you can use Kutools to power Excel with built-in tools that save more time when entering functions.
