logo

TEN (10) COMMON EXCEL FORMULAS FOR BEGINNERS

blog-image

WHAT IS A FORMULA?

A Formula is set of written queries that provides solution to a given problem. It is an expression that performs calculations or operations on data in your worksheet. Formulas begin with an equal sign‘ = ‘ and executed with ENTER


Some of the most used formulas in Excel are:

  • MINIMUM
  • MAXIMUM
  • SUM
  • TRIM
  • IF
  • IFS
  • TEXT
  • LEFT
  • RIGHT
  • CONCATENATION

1. The MINIMUM is used to get the lowest number in a set of numbers or a particular column. To arrive at the minimum value of a given set of number in a column, the FORMULA to use is: =MIN(A:A)

Where A:A refers to the column range where you intend to get the minimum result from.

2. While minimum returns the lowest value, MAXIMUM returns the highest number in a given set or range of numbers. In Excel, these numbers could be in a single column or in multiple columns. If it is in a single range or column, the FORMULA is given thus: =MAX(A:A)

If you intend to get the MAXIMUM from a different number range or column, the FORMULA is: = MAX(A:A, B:B, C:C, D:D, …… , Z:Z)

In the above MAX formula, A:A represents the first range of numbers or the first column to consider. B:B represents the second column, C:C represents the third column, D:D represent the forth, ‘……..’ represents AS MANY COLUMN AS POSSIBLE, Z:Z represent the last column.

NOTE: it is important to note the presence of comma(,) as a separator used to differentiate the end of a column or range and the beginning of another column or range of numbers. Always remember to use the EQUAL TO ‘ = ’ sign to begin a FORMULA and the ENTER-KEY to execute a formula.

3. SUM is one of the most used formula and it is used to add one or more columns. In Excel, the sum FORMULA is: =SUM(A:A)

The above sum formula is used for just one range of data. Formula for several data range is: =SUM(A:A, B:B, ….. , Z:Z)

Sum can also be used to add different cells. Example: = SUM(B1, Z9, G6)

Where B1, Z9, G6 are the cells and comma the separator.

4. The TRIM formula is used to take out excessive space in a text in particular set of column or table as the case may be. FORMULA is: =TRIM(A:A)

Where A:A is the column.

5. IF formula is used to introduce a new/additional column. The IF is used for single condition; meaning if a certain condition is True, what should it return and if it is False, what should it return. While writing the IF statement, operators such as <, >, =, and “ ” can be used to ascertain a certain needed condition. An example of how the IF FORMULA can be used: = IF(A:A > 65, “Yes”, “No”).


The formula is going to meet a condition of where any cell within the range of A:A is greater than 65 and return it to be Yes while those less than 65 is returned as No.

6. IFS is used to meet multi conditions. Example: =IFS(D:D = "O-","Good", D:D="O+","Better", D:D="B-","Best")

The IFS formula would return a result showing Good anywhere O- is found in the selected range D:D, O+ for Better, and Best where B- is found.

7. TEXT formula is used to convert values to text but in specific format. When using the TEXT formula, you have to always specify what format you want to convert to. Example: =TEXT(E:E, ”dd-mm-yyyy”)

The formula will convert the values in E:E to a date but text format. Converting values to TEXT makes it easier to carry around and makes it easier to use the LEFT and RIGHT formula as well.

8. LEFT formula is used to extract text from the left-hand side of a lengthy length. To do this, the value count that wants to be extracted has to be specified. Example: =LEFT(A:A, 5)

The result of the example will be the first 5 values in the range A:A, this means that if there is COMMAND in A:A, it will return COMMA which is the first 5 value.

9. RIGHT formula will return values in the right-hand side and convert it to a TEXT. Remember to specify the count of values that should be extracted. Example: =RIGHT(A:A, 3)

The result of the example will be the first 3 values in the range A:A, this means that if there is COMMAND in A:A, it will return AND which is the first 3 values from the RIGHT.

10. CONCATENATE formula is used to combine two or more columns. When combining these columns, other text, numbers, emails or elements can be included by quoting “” them. Example: =CONCATENATE(C:C, “ “, B:B)

This will return what is in C:C space B:B. The space was achieved by manually inputting the quotes and then space in-between.

Example: =CONCATENATE(A:A, "_",B:B, "@gmail.com")

This will return what is in column A:A and B:B joined with _ and attached with @gmail.com to it. There, you can see that CONCATENATE can be used for several things while trying to combine two or more columns.

Author: Obiwulu Chinyeaka David

Connection: https://www.linkedin.com/in/chinyeaka-obiwulu-7b204a1a4/

Back